- The multi-values version of :meth:`.Insert.values` has been

repaired to work more usefully with tables that have Python-
side default values and/or functions, as well as server-side
defaults. The feature will now work with a dialect that uses
"positional" parameters; a Python callable will also be
invoked individually for each row just as is the case with an
"executemany" style invocation; a server- side default column
will no longer implicitly receive the value explicitly
specified for the first row, instead refusing to invoke
without an explicit value. fixes #3288

2 files changed, 129 insertions, 29 deletions

diff --git a/test/sql/test_defaults.py b/test/sql/test_defaults.py
index 10e557b76..b7893d5f1 100644
--- a/test/sql/test_defaults.py
+++ b/test/sql/test_defaults.py
@@ -336,13 +336,7 @@ class DefaultTest(fixtures.TestBase):
             [(54, 'imthedefault', f, ts, ts, ctexec, True, False,
               12, today, None, 'hi')])
 
-    @testing.fails_on('firebird', 'Data type unknown')
     def test_insertmany(self):
-        # MySQL-Python 1.2.2 breaks functions in execute_many :(
-        if (testing.against('mysql+mysqldb') and
-                testing.db.dialect.dbapi.version_info[:3] == (1, 2, 2)):
-            return
-
         t.insert().execute({}, {}, {})
 
         ctexec = currenttime.scalar()
@@ -356,6 +350,22 @@ class DefaultTest(fixtures.TestBase):
              (53, 'imthedefault', f, ts, ts, ctexec, True, False,
               12, today, 'py', 'hi')])
 
+    @testing.requires.multivalues_inserts
+    def test_insert_multivalues(self):
+
+        t.insert().values([{}, {}, {}]).execute()
+
+        ctexec = currenttime.scalar()
+        l = t.select().execute()
+        today = datetime.date.today()
+        eq_(l.fetchall(),
+            [(51, 'imthedefault', f, ts, ts, ctexec, True, False,
+              12, today, 'py', 'hi'),
+             (52, 'imthedefault', f, ts, ts, ctexec, True, False,
+              12, today, 'py', 'hi'),
+             (53, 'imthedefault', f, ts, ts, ctexec, True, False,
+              12, today, 'py', 'hi')])
+
     def test_no_embed_in_sql(self):
         """Using a DefaultGenerator, Sequence, DefaultClause
         in the columns, where clause of a select, or in the values
diff --git a/test/sql/test_insert.py b/test/sql/test_insert.py
index bd4eaa3e2..8a41d4be7 100644
--- a/test/sql/test_insert.py
+++ b/test/sql/test_insert.py
@@ -1,12 +1,12 @@
 #! coding:utf-8
 
 from sqlalchemy import Column, Integer, MetaData, String, Table,\
-    bindparam, exc, func, insert, select, column
+    bindparam, exc, func, insert, select, column, text
 from sqlalchemy.dialects import mysql, postgresql
 from sqlalchemy.engine import default
 from sqlalchemy.testing import AssertsCompiledSQL,\
     assert_raises_message, fixtures
-
+from sqlalchemy.sql import crud
 
 class _InsertTestBase(object):
 
@@ -19,6 +19,12 @@ class _InsertTestBase(object):
         Table('myothertable', metadata,
               Column('otherid', Integer, primary_key=True),
               Column('othername', String(30)))
+        Table('table_w_defaults', metadata,
+              Column('id', Integer, primary_key=True),
+              Column('x', Integer, default=10),
+              Column('y', Integer, server_default=text('5')),
+              Column('z', Integer, default=lambda: 10)
+            )
 
 
 class InsertTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL):
@@ -565,6 +571,36 @@ class MultirowTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL):
             checkpositional=checkpositional,
             dialect=dialect)
 
+    def test_positional_w_defaults(self):
+        table1 = self.tables.table_w_defaults
+
+        values = [
+            {'id': 1},
+            {'id': 2},
+            {'id': 3}
+        ]
+
+        checkpositional = (1, None, None, 2, None, None, 3, None, None)
+
+        dialect = default.DefaultDialect()
+        dialect.supports_multivalues_insert = True
+        dialect.paramstyle = 'format'
+        dialect.positional = True
+
+        self.assert_compile(
+            table1.insert().values(values),
+            "INSERT INTO table_w_defaults (id, x, z) VALUES "
+            "(%s, %s, %s), (%s, %s, %s), (%s, %s, %s)",
+            checkpositional=checkpositional,
+            check_prefetch=[
+                table1.c.x, table1.c.z,
+                crud._multiparam_column(table1.c.x, 0),
+                crud._multiparam_column(table1.c.z, 0),
+                crud._multiparam_column(table1.c.x, 1),
+                crud._multiparam_column(table1.c.z, 1)
+            ],
+            dialect=dialect)
+
     def test_inline_default(self):
         metadata = MetaData()
         table = Table('sometable', metadata,
@@ -597,6 +633,74 @@ class MultirowTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL):
             checkparams=checkparams,
             dialect=postgresql.dialect())
 
+    def test_python_scalar_default(self):
+        metadata = MetaData()
+        table = Table('sometable', metadata,
+                      Column('id', Integer, primary_key=True),
+                      Column('data', String),
+                      Column('foo', Integer, default=10))
+
+        values = [
+            {'id': 1, 'data': 'data1'},
+            {'id': 2, 'data': 'data2', 'foo': 15},
+            {'id': 3, 'data': 'data3'},
+        ]
+
+        checkparams = {
+            'id_0': 1,
+            'id_1': 2,
+            'id_2': 3,
+            'data_0': 'data1',
+            'data_1': 'data2',
+            'data_2': 'data3',
+            'foo': None,  # evaluated later
+            'foo_1': 15,
+            'foo_2': None  # evaluated later
+        }
+
+        self.assert_compile(
+            table.insert().values(values),
+            'INSERT INTO sometable (id, data, foo) VALUES '
+            '(%(id_0)s, %(data_0)s, %(foo)s), '
+            '(%(id_1)s, %(data_1)s, %(foo_1)s), '
+            '(%(id_2)s, %(data_2)s, %(foo_2)s)',
+            checkparams=checkparams,
+            dialect=postgresql.dialect())
+
+    def test_python_fn_default(self):
+        metadata = MetaData()
+        table = Table('sometable', metadata,
+                      Column('id', Integer, primary_key=True),
+                      Column('data', String),
+                      Column('foo', Integer, default=lambda: 10))
+
+        values = [
+            {'id': 1, 'data': 'data1'},
+            {'id': 2, 'data': 'data2', 'foo': 15},
+            {'id': 3, 'data': 'data3'},
+        ]
+
+        checkparams = {
+            'id_0': 1,
+            'id_1': 2,
+            'id_2': 3,
+            'data_0': 'data1',
+            'data_1': 'data2',
+            'data_2': 'data3',
+            'foo': None,  # evaluated later
+            'foo_1': 15,
+            'foo_2': None,  # evaluated later
+        }
+
+        self.assert_compile(
+            table.insert().values(values),
+            "INSERT INTO sometable (id, data, foo) VALUES "
+            "(%(id_0)s, %(data_0)s, %(foo)s), "
+            "(%(id_1)s, %(data_1)s, %(foo_1)s), "
+            "(%(id_2)s, %(data_2)s, %(foo_2)s)",
+            checkparams=checkparams,
+            dialect=postgresql.dialect())
+
     def test_sql_functions(self):
         metadata = MetaData()
         table = Table('sometable', metadata,
@@ -684,24 +788,10 @@ class MultirowTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL):
             {'id': 3, 'data': 'data3', 'foo': 'otherfoo'},
         ]
 
-        checkparams = {
-            'id_0': 1,
-            'id_1': 2,
-            'id_2': 3,
-            'data_0': 'data1',
-            'data_1': 'data2',
-            'data_2': 'data3',
-            'foo_0': 'plainfoo',
-            'foo_2': 'otherfoo',
-        }
-
-        # note the effect here is that the first set of params
-        # takes effect for the rest of them, when one is absent
-        self.assert_compile(
-            table.insert().values(values),
-            'INSERT INTO sometable (id, data, foo) VALUES '
-            '(%(id_0)s, %(data_0)s, %(foo_0)s), '
-            '(%(id_1)s, %(data_1)s, %(foo_0)s), '
-            '(%(id_2)s, %(data_2)s, %(foo_2)s)',
-            checkparams=checkparams,
-            dialect=postgresql.dialect())
+        assert_raises_message(
+            exc.CompileError,
+            "INSERT value for column sometable.foo is explicitly rendered "
+            "as a boundparameter in the VALUES clause; a Python-side value or "
+            "SQL expression is required",
+            table.insert().values(values).compile
+        )