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, 11 insertions, 0 deletions

diff --git a/test/sql/test_query.py b/test/sql/test_query.py
index 95e159316..9da9c2ff9 100644
--- a/test/sql/test_query.py
+++ b/test/sql/test_query.py
@@ -48,6 +48,17 @@ class QueryTest(fixtures.TestBase):
     def teardown_class(cls):
         metadata.drop_all()
 
+    def test_multirow_insert(self):
+        users.insert(values=[{'user_id':7, 'user_name':'jack'},
+            {'user_id':8, 'user_name':'ed'}]).execute()
+        rows = users.select().execute().fetchall()
+        self.assert_(rows[0] == (7, 'jack'))
+        self.assert_(rows[1] == (8, 'ed'))
+        users.insert(values=[(9, 'jack'), (10, 'ed')]).execute()
+        rows = users.select().execute().fetchall()
+        self.assert_(rows[2] == (9, 'jack'))
+        self.assert_(rows[3] == (10, 'ed'))
+
     def test_insert_heterogeneous_params(self):
         """test that executemany parameters are asserted to match the
         parameter set of the first."""