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

diff --git a/doc/build/changelog/changelog_10.rst b/doc/build/changelog/changelog_10.rst
index 7f9fbff91..5d8bb7b68 100644
--- a/doc/build/changelog/changelog_10.rst
+++ b/doc/build/changelog/changelog_10.rst
@@ -23,6 +23,25 @@
     on compatibility concerns, see :doc:`/changelog/migration_10`.
 
     .. change::
+        :tags: bug, sql
+        :tickets: 3288
+
+        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.
+
+        .. seealso::
+
+            :ref:`bug_3288`
+
+    .. change::
         :tags: feature, general
 
         Structural memory use has been improved via much more significant use
diff --git a/doc/build/changelog/migration_10.rst b/doc/build/changelog/migration_10.rst
index 52179a323..bd878f4cb 100644
--- a/doc/build/changelog/migration_10.rst
+++ b/doc/build/changelog/migration_10.rst
@@ -1362,6 +1362,89 @@ be qualified with :func:`.text` or similar.
 
 :ticket:`2992`
 
+.. _bug_3288:
+
+Python-side defaults invoked for each row invidually when using a multivalued insert
+------------------------------------------------------------------------------------
+
+Support for Python-side column defaults when using the multi-valued
+version of :meth:`.Insert.values` were essentially not implemented, and
+would only work "by accident" in specific situations, when the dialect in
+use was using a non-positional (e.g. named) style of bound parameter, and
+when it was not necessary that a Python-side callable be invoked for each
+row.
+
+The feature has been overhauled so that it works more similarly to
+that of an "executemany" style of invocation::
+
+    import itertools
+
+    counter = itertools.count(1)
+    t = Table(
+        'my_table', metadata,
+        Column('id', Integer, default=lambda: next(counter)),
+        Column('data', String)
+    )
+
+    conn.execute(t.insert().values([
+        {"data": "d1"},
+        {"data": "d2"},
+        {"data": "d3"},
+    ]))
+
+The above example will invoke ``next(counter)`` for each row individually
+as would be expected::
+
+    INSERT INTO my_table (id, data) VALUES (?, ?), (?, ?), (?, ?)
+    (1, 'd1', 2, 'd2', 3, 'd3')
+
+Previously, a positional dialect would fail as a bind would not be generated
+for additional positions::
+
+    Incorrect number of bindings supplied. The current statement uses 6,
+    and there are 4 supplied.
+    [SQL: u'INSERT INTO my_table (id, data) VALUES (?, ?), (?, ?), (?, ?)']
+    [parameters: (1, 'd1', 'd2', 'd3')]
+
+And with a "named" dialect, the same value for "id" would be re-used in
+each row (hence this change is backwards-incompatible with a system that
+relied on this)::
+
+    INSERT INTO my_table (id, data) VALUES (:id, :data_0), (:id, :data_1), (:id, :data_2)
+    {u'data_2': 'd3', u'data_1': 'd2', u'data_0': 'd1', 'id': 1}
+
+The system will also refuse to invoke a "server side" default as inline-rendered
+SQL, since it cannot be guaranteed that a server side default is compatible
+with this.  If the VALUES clause renders for a specific column, then a Python-side
+value is required; if an omitted value only refers to a server-side default,
+an exception is raised::
+
+    t = Table(
+        'my_table', metadata,
+        Column('id', Integer, primary_key=True),
+        Column('data', String, server_default='some default')
+    )
+
+    conn.execute(t.insert().values([
+        {"data": "d1"},
+        {"data": "d2"},
+        {},
+    ]))
+
+will raise::
+
+    sqlalchemy.exc.CompileError: INSERT value for column my_table.data is
+    explicitly rendered as a boundparameter in the VALUES clause; a
+    Python-side value or SQL expression is required
+
+Previously, the value "d1" would be copied into that of the third
+row (but again, only with named format!)::
+
+    INSERT INTO my_table (data) VALUES (:data_0), (:data_1), (:data_0)
+    {u'data_1': 'd2', u'data_0': 'd1'}
+
+:ticket:`3288`
+
 .. _change_3163:
 
 Event listeners can not be added or removed from within that event's runner