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.
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)
```
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]>
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)
>> 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) 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)]))
> 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 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.
>
--
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.
