inserting bulk data is most efficient if you use an insert many:
connection.execute(
table.insert(),
[{..row...}, {...row...}, {...row..} ..]
)
For additional performance, you want to ensure that the above insert
is not relying on any python-level default executions (like when you
say Column(....., default=my_python_function), and a value is not
present in the insert data).
So you'd want to organize your loop such that it iterates for 1000
iterations or so, placing new rows into the buffer, and then executes
a single insertmany, clearing out the buffer.
An ORM-level approach that is similar, is to add() objects to the
session within iterations, but to only issue commit() (or flush())
every 1000 iterations.
On Jan 18, 2009, at 10:32 AM, Victor Lin wrote:
>
> Hi,
>
> I am building some applications that insert data to database heavily.
> I commit in for-loop every iteration. It seems that it is not a smart
> way to commit data every iteration. SQL queries implies expensive IO
> action. So I think that would be better to buffer data and flush them.
> The question is, how to do with sqlalchemy? Are there any build-in
> feature of sqlalchemy I can use? Is my strategy correct?
>
> Thanks.
>
> Victor Lin.
> >
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
