implement batched INSERT..VALUES () () for executemany

the feature is enabled for all built in backends
when RETURNING is used,
except for Oracle that doesn't need it,  and on
psycopg2 and mssql+pyodbc it is used for all INSERT statements,
not just those that use RETURNING.

third party dialects would need to opt in to the new feature
by setting use_insertmanyvalues to True.

Also adds dialect-level guards against using returning
with executemany where we dont have an implementation to
suit it.   execute single w/ returning still defers to the
server without us checking.

Fixes: #6047
Fixes: #7907
Change-Id: I3936d3c00003f02e322f2e43fb949d0e6e568304

1 files changed, 74 insertions, 17 deletions

diff --git a/test/engine/test_execute.py b/test/engine/test_execute.py
index 196b70340..2940a1e7f 100644
--- a/test/engine/test_execute.py
+++ b/test/engine/test_execute.py
@@ -3736,7 +3736,7 @@ class DialectEventTest(fixtures.TestBase):
 class SetInputSizesTest(fixtures.TablesTest):
     __backend__ = True
 
-    __requires__ = ("independent_connections",)
+    __requires__ = ("independent_connections", "insert_returning")
 
     @classmethod
     def define_tables(cls, metadata):
@@ -3752,15 +3752,6 @@ class SetInputSizesTest(fixtures.TablesTest):
         canary = mock.Mock()
 
         def do_set_input_sizes(cursor, list_of_tuples, context):
-            if not engine.dialect.positional:
-                # sort by "user_id", "user_name", or otherwise
-                # param name for a non-positional dialect, so that we can
-                # confirm the ordering.  mostly a py2 thing probably can't
-                # occur on py3.6+ since we are passing dictionaries with
-                # "user_id", "user_name"
-                list_of_tuples = sorted(
-                    list_of_tuples, key=lambda elem: elem[0]
-                )
             canary.do_set_input_sizes(cursor, list_of_tuples, context)
 
         def pre_exec(self):
@@ -3786,15 +3777,21 @@ class SetInputSizesTest(fixtures.TablesTest):
         ):
             yield engine, canary
 
-    def test_set_input_sizes_no_event(self, input_sizes_fixture):
+    @testing.requires.insertmanyvalues
+    def test_set_input_sizes_insertmanyvalues_no_event(
+        self, input_sizes_fixture
+    ):
         engine, canary = input_sizes_fixture
 
         with engine.begin() as conn:
             conn.execute(
-                self.tables.users.insert(),
+                self.tables.users.insert().returning(
+                    self.tables.users.c.user_id
+                ),
                 [
                     {"user_id": 1, "user_name": "n1"},
                     {"user_id": 2, "user_name": "n2"},
+                    {"user_id": 3, "user_name": "n3"},
                 ],
             )
 
@@ -3805,6 +3802,58 @@ class SetInputSizesTest(fixtures.TablesTest):
                     mock.ANY,
                     [
                         (
+                            "user_id_0",
+                            mock.ANY,
+                            testing.eq_type_affinity(Integer),
+                        ),
+                        (
+                            "user_name_0",
+                            mock.ANY,
+                            testing.eq_type_affinity(String),
+                        ),
+                        (
+                            "user_id_1",
+                            mock.ANY,
+                            testing.eq_type_affinity(Integer),
+                        ),
+                        (
+                            "user_name_1",
+                            mock.ANY,
+                            testing.eq_type_affinity(String),
+                        ),
+                        (
+                            "user_id_2",
+                            mock.ANY,
+                            testing.eq_type_affinity(Integer),
+                        ),
+                        (
+                            "user_name_2",
+                            mock.ANY,
+                            testing.eq_type_affinity(String),
+                        ),
+                    ],
+                    mock.ANY,
+                )
+            ],
+        )
+
+    def test_set_input_sizes_no_event(self, input_sizes_fixture):
+        engine, canary = input_sizes_fixture
+
+        with engine.begin() as conn:
+            conn.execute(
+                self.tables.users.update()
+                .where(self.tables.users.c.user_id == 15)
+                .values(user_id=15, user_name="n1"),
+            )
+
+        eq_(
+            canary.mock_calls,
+            [
+                call.do_set_input_sizes(
+                    mock.ANY,
+                    [
+                        (
                             "user_id",
                             mock.ANY,
                             testing.eq_type_affinity(Integer),
@@ -3814,6 +3863,11 @@ class SetInputSizesTest(fixtures.TablesTest):
                             mock.ANY,
                             testing.eq_type_affinity(String),
                         ),
+                        (
+                            "user_id_1",
+                            mock.ANY,
+                            testing.eq_type_affinity(Integer),
+                        ),
                     ],
                     mock.ANY,
                 )
@@ -3924,11 +3978,9 @@ class SetInputSizesTest(fixtures.TablesTest):
 
         with engine.begin() as conn:
             conn.execute(
-                self.tables.users.insert(),
-                [
-                    {"user_id": 1, "user_name": "n1"},
-                    {"user_id": 2, "user_name": "n2"},
-                ],
+                self.tables.users.update()
+                .where(self.tables.users.c.user_id == 15)
+                .values(user_id=15, user_name="n1"),
             )
 
         eq_(
@@ -3947,6 +3999,11 @@ class SetInputSizesTest(fixtures.TablesTest):
                             (SPECIAL_STRING, None, 0),
                             testing.eq_type_affinity(String),
                         ),
+                        (
+                            "user_id_1",
+                            mock.ANY,
+                            testing.eq_type_affinity(Integer),
+                        ),
                     ],
                     mock.ANY,
                 )