Multiple Database Inserts using 1 query

[ErikW]

It would be really nice (ie convenient, reduce load on db, improve performance) to be able to create a "collection" of model objects with a single query. I've tested the raw SQL, and it is significantly faster than using a loop to insert a bunch of database rows. All of the backends seem to support it.

I'm thinking something like
[queryset] = Model.objects.bulk_create([{object}, {object}, {object}])

​http://www.desilva.biz/mysql/insert.html
​http://www.postgresql.org/docs/8.2/interactive/sql-insert.html (search for: To insert multiple rows using the multirow VALUES syntax)
​http://codingforums.com/archive/index.php?t-109802.html

Multirow inserts are not supported in sqlite yet (​http://www.sqlite.org/cvstrac/wiki?p=UnsupportedSql), but the backend could fall back to a python loop in this case....

Thanks!

[goodrone]

Maybe use something like sessions in SQLAlchemy?

[(none)]

Milestone post-1.0 deleted

[Russell Keith-Magee]

Reverting anonymous change.

[vkailas]

Quick question: A multiple db inserts using one query would achieve more of a speed of than just using transaction managent e.g. @transaction.commit_on_success?

Thanks

[Carl Meyer]

This came up just now in discussing optimizing the Django test suite. A lot of time in TransactionTestCases is wasted in model-by-model get_or_create of content-types and permissions. As it stands, the lookups there could be optimized, but to fully optimize it a bulk-insert ORM operation would also be needed.

[Carl Meyer]

The API here still requires design discussion, but accepting this in principle.

[Russell Keith-Magee]

Alex Gaynor's patch, prepared at DjangoCon Europe 2011

[Karen Tracey]

Same patch, extension that trac will hopefully recognize properly

[Simon Meers]

Updated/tweaked version of Alex's patch

[Simon Meers]

I've attached a reviewed/updated/tweaked version of Alex's patch.

Looks pretty good to me, mostly stylistic changes though I think the previous
patch would have had some issues with primary key values which evaluated to
False; I've updated this to explicitly test for None.

I'm not a database expert, so I may have missed some intricacies, but the
code itself looks robust.

I've also added a simple proof-of-concept change to the MySQL backend which
works as far as I've tested, but I usually steer as far away from MySQL as
possible so I'm not the person to write this. So far it's identical to
PostgreSQL; I don't think MySQL can do the SQLite trick for combining inserts
with/without auto-increment primary keys?

[Anssi Kääriäinen]

Some notes on the approach.

Combining instances with and without autoincrement primary keys set, use this sql:

    insert into table_with_serial_id(id, other_col) values(1, 'foo'),(default, 'bar');

A different question is if this is something that needs to be supported at all. I don't see it particularly useful to insert anything else than default values into serial columns. Except for bulk loading, but then you don't want to have None values in the mix. Maybe there is an use case?

Also, why doesn't the code use "RETURNING serial_pk" where available? Maybe that would make the situation more confusing, because sometimes the bulk_save method would include the PKs and sometimes not depending on used backend. Maybe a kwarg 'returning_autopk' and if that is set then raise error if the backend doesn't support RETURNING?

[Alex Gaynor]

In [16739]:

Fixed #7596. Added Model.objects.bulk_create, and make use of it in several places. This provides a performance benefit when inserting multiple objects. THanks to Russ for the review, and Simon Meers for the MySQl implementation.

[Ramiro Morales]

In [16819]:

Added an implementation of bulk insert via the ORM to the Oracle DB backend.

Refs #7596, r16739 and ​http://troels.arvin.dk/db/rdbms/#insert-multiple