On Wed, Jan 25, 2017 at 7:02 AM, mike bayer <[email protected]>
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 <[email protected]
>> <mailto:[email protected]>> 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 [email protected]
>> <mailto:sqlalchemy%[email protected]>
>> <mailto:[email protected]
>> <mailto:sqlalchemy%[email protected]>>.
>> To post to this group, send email to [email protected]
>> <mailto:[email protected]>
>> <mailto:[email protected]
>> <mailto:[email protected]>>.
>> 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
>> [email protected]
>> <mailto:sqlalchemy%[email protected]>.
>> To post to this group, send email to [email protected]
>> <mailto:[email protected]>.
>> 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 [email protected]
>> <mailto:[email protected]>.
>> To post to this group, send email to [email protected]
>> <mailto:[email protected]>.
>> 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
> [email protected].
> To post to this group, send email to [email protected].
> 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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
