From d57c1c2ddd654a1077ab04ba7277828d9030c23d Mon Sep 17 00:00:00 2001 From: Idan Kamara Date: Thu, 6 Dec 2012 00:11:52 +0200 Subject: 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. --- lib/sqlalchemy/sql/compiler.py | 25 +++++++++++++++++++++---- 1 file changed, 21 insertions(+), 4 deletions(-) (limited to 'lib/sqlalchemy/sql/compiler.py') diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index 6f7f1dadd..3856499fc 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -1284,6 +1284,11 @@ class SQLCompiler(engine.Compiled): "not support empty inserts." % self.dialect.name) + if insert_stmt.multi_parameters and not self.dialect.supports_multirow_insert: + raise exc.CompileError("The version of %s you are using does " + "not support multirow inserts." % + self.dialect.name) + preparer = self.preparer supports_default_values = self.dialect.supports_default_values @@ -1328,8 +1333,11 @@ class SQLCompiler(engine.Compiled): if not cols and supports_default_values: text += " DEFAULT VALUES" else: - text += " VALUES (%s)" % \ - ', '.join(params[0]) + values = [] + for row in params: + values.append('(%s)' % ', '.join(row)) + text += " VALUES %s" % \ + ', '.join(values) if self.returning and not self.returning_precedes_values: text += " " + returning_clause @@ -1445,8 +1453,10 @@ class SQLCompiler(engine.Compiled): return text - def _create_crud_bind_param(self, col, value, required=False): - bindparam = sql.bindparam(col.key, value, + def _create_crud_bind_param(self, col, value, required=False, name=None): + if name is None: + name = col.key + bindparam = sql.bindparam(name, value, type_=col.type, required=required, quote=col.quote) bindparam._is_crud = True @@ -1669,6 +1679,13 @@ class SQLCompiler(engine.Compiled): if values: values = [values] + for i, row in enumerate(stmt.multi_parameters): + r = [] + for c in columns: + r.append(self._create_crud_bind_param(c, row[c.key], + name=c.key + str(i))) + values.append(r) + return columns, values def visit_delete(self, delete_stmt, **kw): -- cgit v1.2.1