compiler: add support for multirow inserts

Some databases support this syntax for inserts:

INSERT INTO table (id, name) VALUES
('v1', 'v2'),
('v3', 'v4');

which greatly increases INSERT speed.

It is now possible to pass a list of lists/tuples/dictionaries as
the values param to the Insert construct. We convert it to a flat
dictionary so we can continue using bind params. The above query
will be converted to:

INSERT INTO table (id, name) VALUES
(:id, :name),
(:id0, :name0);

Currently only supported on postgresql, mysql and sqlite.

1 files changed, 15 insertions, 0 deletions

diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py
index 50b425a01..3b0e421ae 100644
--- a/test/sql/test_compiler.py
+++ b/test/sql/test_compiler.py
@@ -2552,6 +2552,21 @@ class CRUDTest(fixtures.TestBase, AssertsCompiledSQL):
                     table.insert(inline=True),
                     "INSERT INTO sometable (foo) VALUES (foobar())", params={})
 
+    def test_multirow_insert(self):
+        data = [(1, 'a', 'b'), (2, 'a', 'b')]
+        result = "INSERT INTO mytable (myid, name, description) VALUES " \
+                 "(%(myid)s, %(name)s, %(description)s), " \
+                 "(%(myid0)s, %(name0)s, %(description0)s)"
+
+        stmt = insert(table1, data, dialect='postgresql')
+        self.assert_compile(stmt, result, dialect=postgresql.dialect())
+
+        stmt = table1.insert(values=data, dialect='postgresql')
+        self.assert_compile(stmt, result, dialect=postgresql.dialect())
+
+        stmt = table1.insert(dialect='postgresql').values(data)
+        self.assert_compile(stmt, result, dialect=postgresql.dialect())
+
     def test_update(self):
         self.assert_compile(
                 update(table1, table1.c.myid == 7),