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_techniques.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 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 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.
