On Wed, Jan 25, 2017 at 7:02 AM, mike bayer <mike...@zzzcomputing.com> wrote:
> > > On 01/24/2017 07:40 PM, Robert Sami wrote: > >> Thanks for the response Mike, >> >> I agree that using Core is pretty clean. One approach I considered was >> the following: >> >> ``` >> res = conn.execute(FooBase.__table__.insert(returning=[FooBase.id], >> values=[{} for i in range(100000)])) >> conn.execute(FooDerived.__table__.insert(values=[{'id': _id, data: >> 'whatever'} for _id, in res.fetchall()])) >> ``` >> >> This is similar to the approach you outlined above, but also robust to >> other transactions inserting in the table. >> > > OK, so usually RETURNING doesn't work for "executemany()", but I see there > you are packing them into one big VALUES clause and ultimately using > cursor.execute(), so that should work, though you want to chunk the sizes > into batches of 1000 or so or your SQL statement will grow too large. Ah, thanks for the tip! > > > >> The reason I would prefer to use `session.bulk_save_objects()` is that >> this method is aware of default attribute values of objects. For example: >> >> ``` >> class FooDerived(..): >> ... >> data = db.Column(db.Integer, default=17) >> > > that "default" is interpreted by the Core, not the ORM. So your core > statement should handle it too and you'd see those "17"s going in. If not, > let's get an MCVE and figure out why. > > OK, thanks for explaining. FWIW this was an incorrect assumption on my part, rather than based on any experience or observation, so I'll get you a MCVE if anything unexpected comes up. Thanks for clarifying! > > ``` >> >> Creating a bunch of `FooDerived` objects will automatically set the >> `data` attributes to their default value. So I was hoping there was some >> way of using `session.bulk_save_objects()` to a similar effect as the >> Core approach I shared above, which uses a "RETURNING" clause to know >> the primary keys of the newly inserted `FooBase` rows. If not, do you >> have any other thoughts or suggestions on how to get the best of both >> worlds? >> >> Many thanks! >> >> On Tue, Jan 24, 2017 at 3:00 PM, mike bayer <mike...@zzzcomputing.com >> <mailto:mike...@zzzcomputing.com>> wrote: >> >> >> >> On 01/24/2017 04:49 PM, Robert Sami wrote: >> >> Hi SQLAlchemy wizards. >> >> I was interested in using the new bulk operations API >> (http://docs.sqlalchemy.org/en/latest/orm/persistence_techni >> ques.html#bulk-operations >> <http://docs.sqlalchemy.org/en/latest/orm/persistence_techni >> ques.html#bulk-operations>) >> but was looking for some advice based on my use case. >> >> I have a class “FooDerived” which corresponds to a table that is >> linked >> to “FooBase” using joined table inheritance. I want to use the >> bulk_save_objects method to save, lets say 100,000 instances of >> “FooDerived”. >> >> One option would be the following: >> >> ``` >> session.bulk_save_objects([FooBase() for i in range(100000)]) >> session.flush() >> foo_base_models = FooBase.query.filter(/* Assume its possible to >> filter >> for the newly created objects*/).all() >> session.bulk_save_objects([FooDerived(id=base.id >> <http://base.id>) for base in >> foo_base_models]) >> ``` >> >> Is there a better way? >> >> >> this would be expressed much more clearly and efficiently using Core >> constructs, and you need a way of knowing that primary key for >> FooBase() because how you have it above where it auto-generates the >> primary key, it would perform 100K SELECT statements : >> >> >> foobase = FooBase.__table__ >> fooderived = FooDerived.__table__ >> with engine.begin() as conn: >> my_first_pk = conn.scalar(select([func.max(foobase.c.id >> <http://foobase.c.id>)])) >> >> conn.execute( >> foobase.insert(), >> {"id": ident + my_first_pk, "data": "whatever"} for ident in >> range(100000) >> ) >> conn.execute( >> fooderived.insert(), >> {"id": ident + my_firstpk, "data": "whatever"} for ident in >> range(100000) >> ) >> >> >> of course you need to make sure no other transactions are INSERTing >> rows with this approach or they will throw off your primary key >> counter. >> >> >> >> >> >> >> >> >> Thank you! >> >> -- >> SQLAlchemy - >> The Python SQL Toolkit and Object Relational Mapper >> >> http://www.sqlalchemy.org/ >> >> To post example code, please provide an MCVE: Minimal, Complete, >> and >> Verifiable Example. See http://stackoverflow.com/help/mcve >> <http://stackoverflow.com/help/mcve> for a full >> description. >> --- >> You received this message because you are subscribed to the Google >> Groups "sqlalchemy" group. >> To unsubscribe from this group and stop receiving emails from >> it, send >> an email to sqlalchemy+unsubscr...@googlegroups.com >> <mailto:sqlalchemy%2bunsubscr...@googlegroups.com> >> <mailto:sqlalchemy+unsubscr...@googlegroups.com >> <mailto:sqlalchemy%2bunsubscr...@googlegroups.com>>. >> To post to this group, send email to sqlalchemy@googlegroups.com >> <mailto:sqlalchemy@googlegroups.com> >> <mailto:sqlalchemy@googlegroups.com >> <mailto:sqlalchemy@googlegroups.com>>. >> Visit this group at https://groups.google.com/group/sqlalchemy >> <https://groups.google.com/group/sqlalchemy>. >> For more options, visit https://groups.google.com/d/optout >> <https://groups.google.com/d/optout>. >> >> >> -- >> SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper >> >> http://www.sqlalchemy.org/ >> >> To post example code, please provide an MCVE: Minimal, Complete, and >> Verifiable Example. See http://stackoverflow.com/help/mcve >> <http://stackoverflow.com/help/mcve> for a full description. >> --- You received this message because you are subscribed to a topic >> in the Google Groups "sqlalchemy" group. >> To unsubscribe from this topic, visit >> https://groups.google.com/d/topic/sqlalchemy/MpC7I2WV4aM/unsubscribe >> <https://groups.google.com/d/topic/sqlalchemy/MpC7I2WV4aM/unsubscribe >> >. >> To unsubscribe from this group and all its topics, send an email to >> sqlalchemy+unsubscr...@googlegroups.com >> <mailto:sqlalchemy%2bunsubscr...@googlegroups.com>. >> To post to this group, send email to sqlalchemy@googlegroups.com >> <mailto:sqlalchemy@googlegroups.com>. >> Visit this group at https://groups.google.com/group/sqlalchemy >> <https://groups.google.com/group/sqlalchemy>. >> For more options, visit https://groups.google.com/d/optout >> <https://groups.google.com/d/optout>. >> >> >> >> -- >> SQLAlchemy - >> The Python SQL Toolkit and Object Relational Mapper >> >> http://www.sqlalchemy.org/ >> >> To post example code, please provide an MCVE: Minimal, Complete, and >> Verifiable Example. See http://stackoverflow.com/help/mcve for a full >> description. >> --- >> You received this message because you are subscribed to the Google >> Groups "sqlalchemy" group. >> To unsubscribe from this group and stop receiving emails from it, send >> an email to sqlalchemy+unsubscr...@googlegroups.com >> <mailto:sqlalchemy+unsubscr...@googlegroups.com>. >> To post to this group, send email to sqlalchemy@googlegroups.com >> <mailto:sqlalchemy@googlegroups.com>. >> Visit this group at https://groups.google.com/group/sqlalchemy. >> For more options, visit https://groups.google.com/d/optout. >> > > -- > SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- You received this message because you are subscribed to a topic in the > Google Groups "sqlalchemy" group. > To unsubscribe from this topic, visit https://groups.google.com/d/to > pic/sqlalchemy/MpC7I2WV4aM/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. > Thanks for building an amazing piece of software :D -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.