Hi Michael,
I checked your post on the stackoverflow, could you help me figure
something out regarding usage of Core.
In the example you provided you have a version with test_sqlite3 which uses
customer user supplied query so it issues only one insert statement with
all rows in values clause. I wonder if this can be achieved by using Core.
I tried this approach:
session.connection().execute(
mytable.insert(),
raw_list
)
where raw_list is a list of dictionaries to insert and this issues a
separate statement for every row, which is probably expected. But then I
came across this docs page
http://docs.sqlalchemy.org/en/latest/core/tutorial.html#executing-multiple-statements
in the example with a list of dictionaries there is a single insert
statement issues with multi-row values clause. I wonder if I can achieve
the same behavior. I am using PostgreSQL 9.3.4 and SQLAlchemy==0.9.4
Thanks!
Best,
Anton.
On Wednesday, November 6, 2013 12:58:53 PM UTC-8, Michael Bayer wrote:
>
> I wrote a full post regarding this topic on stackoverflow at
> http://stackoverflow.com/questions/11769366/why-is-sqlalchemy-insert-with-sqlite-25-times-slower-than-using-sqlite3-directly/11769768#11769768
>
> . If you start with this, I can answer more specific questions.
>
>
> On Nov 6, 2013, at 10:28 AM, Achim Domma <[email protected]
> <javascript:>> wrote:
>
> > Hi,
> >
> > I want to speed up my SqlAlchemy bulk inserting code and yes, I'm aware
> that this is not the main purpose of SqlAlchemy and all databases have
> faster low level import tools.
> >
> > The background is the following: We import data from various sources and
> apply various mappings. Currently I'm willing to trade raw runtime for much
> simpler code, which is much easier to maintain. But I still want my code to
> run as fast as it's possible with those assumptions.
> >
> > There are two scenarios which I want to optimize:
> >
> > 1. Flat inserts without relations, but with unique ids generated inside
> the database: In that case, SqlAlchemy retrieves the unique ids from the
> database, but those ids are never used in my import process. I thought
> about generating an insert statement out of an object. Obviously SqlAlchemy
> has to do that too, so there might be some existing tool for that? The
> other option would be, to tell SqlAlchemy to ignore the generated id an to
> not retrieve it from the database. Is that possible?
> >
> > 2. Inserts using relations and unique ids generated in the database: I
> think SqlAlchemy is already quite good at optimizing this and sending
> objects as batches. If there are any tweeks and tricks to speed up inserts
> having relations, I would be happy to hear them.
> >
> > cheers,
> > Achim
> >
> > --
> > 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] <javascript:>.
> > To post to this group, send email to [email protected]
> <javascript:>.
> > Visit this group at http://groups.google.com/group/sqlalchemy.
> > For more options, visit https://groups.google.com/groups/opt_out.
>
>
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
