yes because you'll note because sub1 and sub2 have different columns
mapped, the actual columns in each INSERT statement are different.
At the same time it is maintaining INSERT order into the table
overall, so it has no choice but to break things up in this way.
> 2) If this is desired behavior, do you have any suggestions of how to
> optimize the number of queries here in e.g. a before_flush hook? Obviously
> one way would be to rewrite our application such that inserts of like tables
> happen together, but if there is a way to do this without rewriting the
> application code, that would be even better.
if your INSERTs are very simplistic then you can still consider using
the bulk_save option, gathering your classes into lists and then doing
your own bulk operation where you ensure all like-rows are grouped
together ahead of time.
Otherwise, if you really are trying to thread the needle through "I
want full ORM flush convenience" plus "I need to optimize INSERTs into
batches", you have one more option which is to manipulate the
insert_order on the states like this:
for i in xrange(0, 10, 2):
s.add(SubBase1(i))
s.add(SubBase2(i + 1))
from sqlalchemy import inspect
for idx, obj in enumerate(
sorted(
s.new,
key=lambda obj: (obj.__class__, inspect(obj).insert_order)
)
):
inspect(obj).insert_order = idx
I've not documented "insert_order" as an official thing but I can support that.
But also if you're really trying to maximize INSERT performance you
need to use the psycopg2 executemany() extensions:
http://initd.org/psycopg/docs/extras.html#fast-execution-helpers.
These were added specifically from an email thread I started with them
in response to user complaints about performance, but SQLAlchemy has
not yet built these in as features.
I've done no testing at all of this, however if you have the
resources, you can turn on statement logging on your Postgresql
database which will hopefully show INSERT statements being collapsed
into a single statement with multiple VALUES:
from psycopg2 import extras
from sqlalchemy import event
e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
@event.listens_for(e, "do_executemany")
def do_executemany(cursor, statement, parameters, context):
context.dialect.supports_sane_multi_rowcount = False
extras.execute_batch(cursor, statement, parameters)
return True
if you get the time to test this, please let me know as I do want to
add support for "execute_batch" directly into the psycopg2 dialect.
thanks!
One option I've thought of is:
> - Overwrite the insert_order in the before_flush hook to rearrange the
> models in the order I want. (Not sure if this has effects that I'm unaware
> of though)
>
> Thanks!
>
> --
> 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+...@googlegroups.com.
> To post to this group, send email to
sqlal...@googlegroups.com.
> Visit this group at
https://groups.google.com/group/sqlalchemy.
> For more options, visit
https://groups.google.com/d/optout.