Provide primary key values for data in tests that aren't about primary keys.

(message written by Mike) some backends such as BigQuery have no
autoincrement mechanism at all.
while we would like to pursue a strategy where provisioning.py could
provide for an in-Python sequence generator, at least remove
the need for autoincrement in suite tests that don't need it.

Fixes: #6469
Closes: #6504
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/6504
Pull-request-sha: c075014ef7de33e6eb3f389d24251ba184655e0b

Change-Id: I98e237a38417b68c87d0201717205d7655b1f44e

3 files changed, 33 insertions, 17 deletions

diff --git a/lib/sqlalchemy/testing/suite/test_insert.py b/lib/sqlalchemy/testing/suite/test_insert.py
index 3c033a774..3c22f50b2 100644
--- a/lib/sqlalchemy/testing/suite/test_insert.py
+++ b/lib/sqlalchemy/testing/suite/test_insert.py
@@ -109,6 +109,7 @@ class InsertBehaviorTest(fixtures.TablesTest):
             ),
         )
 
+    @requirements.autoincrement_insert
     def test_autoclose_on_insert(self):
         if requirements.returning.enabled:
             engine = engines.testing_engine(
diff --git a/lib/sqlalchemy/testing/suite/test_rowcount.py b/lib/sqlalchemy/testing/suite/test_rowcount.py
index bb344237a..504ac13a5 100644
--- a/lib/sqlalchemy/testing/suite/test_rowcount.py
+++ b/lib/sqlalchemy/testing/suite/test_rowcount.py
@@ -2,7 +2,6 @@ from sqlalchemy import bindparam
 from sqlalchemy import Column
 from sqlalchemy import Integer
 from sqlalchemy import select
-from sqlalchemy import Sequence
 from sqlalchemy import String
 from sqlalchemy import Table
 from sqlalchemy import testing
@@ -25,7 +24,7 @@ class RowCountTest(fixtures.TablesTest):
             Column(
                 "employee_id",
                 Integer,
-                Sequence("employee_id_seq", optional=True),
+                autoincrement=False,
                 primary_key=True,
             ),
             Column("name", String(50)),
@@ -49,7 +48,10 @@ class RowCountTest(fixtures.TablesTest):
         employees_table = cls.tables.employees
         connection.execute(
             employees_table.insert(),
-            [{"name": n, "department": d} for n, d in data],
+            [
+                {"employee_id": i, "name": n, "department": d}
+                for i, (n, d) in enumerate(data)
+            ],
         )
 
     def test_basic(self, connection):
diff --git a/lib/sqlalchemy/testing/suite/test_types.py b/lib/sqlalchemy/testing/suite/test_types.py
index 3c4a80573..3e54cc2e4 100644
--- a/lib/sqlalchemy/testing/suite/test_types.py
+++ b/lib/sqlalchemy/testing/suite/test_types.py
@@ -116,7 +116,9 @@ class _UnicodeFixture(_LiteralRoundTripFixture, fixtures.TestBase):
     def test_round_trip(self, connection):
         unicode_table = self.tables.unicode_table
 
-        connection.execute(unicode_table.insert(), {"unicode_data": self.data})
+        connection.execute(
+            unicode_table.insert(), {"id": 1, "unicode_data": self.data}
+        )
 
         row = connection.execute(select(unicode_table.c.unicode_data)).first()
 
@@ -128,27 +130,31 @@ class _UnicodeFixture(_LiteralRoundTripFixture, fixtures.TestBase):
 
         connection.execute(
             unicode_table.insert(),
-            [{"unicode_data": self.data} for i in range(3)],
+            [{"id": i, "unicode_data": self.data} for i in range(1, 4)],
         )
 
         rows = connection.execute(
             select(unicode_table.c.unicode_data)
         ).fetchall()
-        eq_(rows, [(self.data,) for i in range(3)])
+        eq_(rows, [(self.data,) for i in range(1, 4)])
         for row in rows:
             assert isinstance(row[0], util.text_type)
 
     def _test_null_strings(self, connection):
         unicode_table = self.tables.unicode_table
 
-        connection.execute(unicode_table.insert(), {"unicode_data": None})
+        connection.execute(
+            unicode_table.insert(), {"id": 1, "unicode_data": None}
+        )
         row = connection.execute(select(unicode_table.c.unicode_data)).first()
         eq_(row, (None,))
 
     def _test_empty_strings(self, connection):
         unicode_table = self.tables.unicode_table
 
-        connection.execute(unicode_table.insert(), {"unicode_data": u("")})
+        connection.execute(
+            unicode_table.insert(), {"id": 1, "unicode_data": u("")}
+        )
         row = connection.execute(select(unicode_table.c.unicode_data)).first()
         eq_(row, (u(""),))
 
@@ -211,7 +217,9 @@ class TextTest(_LiteralRoundTripFixture, fixtures.TablesTest):
     def test_text_roundtrip(self, connection):
         text_table = self.tables.text_table
 
-        connection.execute(text_table.insert(), {"text_data": "some text"})
+        connection.execute(
+            text_table.insert(), {"id": 1, "text_data": "some text"}
+        )
         row = connection.execute(select(text_table.c.text_data)).first()
         eq_(row, ("some text",))
 
@@ -219,14 +227,14 @@ class TextTest(_LiteralRoundTripFixture, fixtures.TablesTest):
     def test_text_empty_strings(self, connection):
         text_table = self.tables.text_table
 
-        connection.execute(text_table.insert(), {"text_data": ""})
+        connection.execute(text_table.insert(), {"id": 1, "text_data": ""})
         row = connection.execute(select(text_table.c.text_data)).first()
         eq_(row, ("",))
 
     def test_text_null_strings(self, connection):
         text_table = self.tables.text_table
 
-        connection.execute(text_table.insert(), {"text_data": None})
+        connection.execute(text_table.insert(), {"id": 1, "text_data": None})
         row = connection.execute(select(text_table.c.text_data)).first()
         eq_(row, (None,))
 
@@ -303,7 +311,9 @@ class _DateFixture(_LiteralRoundTripFixture, fixtures.TestBase):
     def test_round_trip(self, connection):
         date_table = self.tables.date_table
 
-        connection.execute(date_table.insert(), {"date_data": self.data})
+        connection.execute(
+            date_table.insert(), {"id": 1, "date_data": self.data}
+        )
 
         row = connection.execute(select(date_table.c.date_data)).first()
 
@@ -315,7 +325,7 @@ class _DateFixture(_LiteralRoundTripFixture, fixtures.TestBase):
         date_table = self.tables.date_table
 
         connection.execute(
-            date_table.insert(), {"decorated_date_data": self.data}
+            date_table.insert(), {"id": 1, "decorated_date_data": self.data}
         )
 
         row = connection.execute(
@@ -329,7 +339,7 @@ class _DateFixture(_LiteralRoundTripFixture, fixtures.TestBase):
     def test_null(self, connection):
         date_table = self.tables.date_table
 
-        connection.execute(date_table.insert(), {"date_data": None})
+        connection.execute(date_table.insert(), {"id": 1, "date_data": None})
 
         row = connection.execute(select(date_table.c.date_data)).first()
         eq_(row, (None,))
@@ -347,7 +357,7 @@ class _DateFixture(_LiteralRoundTripFixture, fixtures.TestBase):
         date_table = self.tables.date_table
         with config.db.begin() as conn:
             result = conn.execute(
-                date_table.insert(), {"date_data": self.data}
+                date_table.insert(), {"id": 1, "date_data": self.data}
             )
             id_ = result.inserted_primary_key[0]
             stmt = select(date_table.c.id).where(
@@ -457,7 +467,9 @@ class IntegerTest(_LiteralRoundTripFixture, fixtures.TestBase):
 
             metadata.create_all(config.db)
 
-            connection.execute(int_table.insert(), {"integer_data": data})
+            connection.execute(
+                int_table.insert(), {"id": 1, "integer_data": data}
+            )
 
             row = connection.execute(select(int_table.c.integer_data)).first()
 
@@ -816,7 +828,8 @@ class JSONTest(_LiteralRoundTripFixture, fixtures.TablesTest):
         data_table = self.tables.data_table
 
         connection.execute(
-            data_table.insert(), {"name": "row1", "data": data_element}
+            data_table.insert(),
+            {"id": 1, "name": "row1", "data": data_element},
         )
 
         row = connection.execute(select(data_table.c.data)).first()