Support batch inserts with multiple values() calls

[Michael Schurter]

Support batch inserts using multiple values() calls like:

>>> tbl.insert().values(a=1, b=1).values(a=2, b=2)
INSERT INTO tbl (a, b) VALUES (1, 1) (2, 2);

This would make tbl.insert().values(...).values(...) conceptually similar to tbl.select().where(...).where(...) where multiple calls extend the existing query instead of replacing it.

[Michael Schurter]

Argh, I forgot to mention it does appear to be a widely supported SQL standard as well:

• http://www.postgresql.org/docs/9.0/static/sql-insert.html
• http://www.postgresql.org/docs/current/interactive/sql-values.html
• http://dev.mysql.com/doc/refman/5.5/en/insert.html
• http://msdn.microsoft.com/en-us/library/ms174335.aspx
• http://psoug.org/reference/insert.html (slightly different syntax?)

I only use pgsql, so please forgive my ignorance if I mis-referenced any of the other databases.

[Michael Schurter]

http://troels.arvin.dk/db/rdbms/#insert seems to indicate widespread support as well.

[Mike Bayer]

yeah it's not a trivial feature add as the _get_colparams() method in compiler.py is tasked with assigning bound parameters to everything in values(). The scheme by which bound param keys are generated would need to change, at least when a multiple list of values() is detected. If you look at how _get_colparams() works you'll see this would require almost a total rewrite - especially one that doesn't add significant method call overhead to the vast majority of cases that don't use this feature.

Also "VALUES () () ()" is kind of a SQL typing saver. When we're using programmatic inserts, especially when we can use executemany(), it's not as clear what the not-yet-supported use case is here.

[Michael Schurter]

My use case is that I have index corruption which means fulfilling unique constraints on INSERTs can only be (safely) done via full table scans, and I'm hoping pgsql is smart enough to only do 1 full table scan on a bulk INSERT instead of 1 scan per row.

It's a long shot, and I doubt it save me the extra table scans. However, I do think this would be a useful feature under normal circumstances. Some quick googling revealed quite a few other people looking for bulk INSERT functionality via SQLA.

[Mike Bayer]

Can we use more descriptive terminology here...I'd call this "multi-valued INSERT". A "bulk" insert is via executemany() and supports an arbitrary number of record sets (though its true, the DBAPI internally emits the same INSERT statement for each parameter set).

The current situation is that this isn't a quickie. I'd suggest working with a hardcoded statement for the moment to establish that it does what you need. A @compiles recipe can also get you going in the short term.

[Michael Schurter]

❤

[Mike Bayer]

this was implemented six months ago as #2623.