Merge "Fix get_columns sqlite reflection rejecting tables with WITHOUT_ROWID and/or STRICT for generated column case" into main

zzzeek · Gerrit Code Review · commit 8bb8c1a6d643 · 2025-09-15T21:55:45.000Z
diff --git a/doc/build/changelog/unreleased_20/12864.rst b/doc/build/changelog/unreleased_20/12864.rst
@@ -0,0 +1,10 @@
+.. change::
+    :tags: bug, sqlite
+    :tickets: 12864
+
+    Fixed issue where SQLite table reflection would fail for tables using
+    ``WITHOUT ROWID`` and/or ``STRICT`` table options when the table contained
+    generated columns. The regular expression used to parse ``CREATE TABLE``
+    statements for generated column detection has been updated to properly
+    handle these SQLite table options that appear after the column definitions.
+    Pull request courtesy Tip ten Brink.
diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py
@@ -2377,7 +2377,10 @@ def get_columns(self, connection, table_name, schema=None, **kw):
                 )
                 # remove create table
                 match = re.match(
-                    r"create table .*?\((.*)\)$",
+                    (
+                        r"create table .*?\((.*)\)"
+                        r"(?:\s*,?\s*(?:WITHOUT\s+ROWID|STRICT))*$"
+                    ),
                     tablesql.strip(),
                     re.DOTALL | re.IGNORECASE,
                 )
diff --git a/test/dialect/test_sqlite.py b/test/dialect/test_sqlite.py
@@ -3980,6 +3980,25 @@ def setup_test_class(cls):
                 x INTEGER GENERATED ALWAYS AS (INSTR(s, ",")) STORED,
                 y INTEGER GENERATED ALWAYS AS (INSTR(x, ",")) STORED
             );""",
+            """CREATE TABLE test9 (
+                id INTEGER PRIMARY KEY,
+                s VARCHAR,
+                x VARCHAR GENERATED ALWAYS AS (s || 'x')
+            ) WITHOUT ROWID;""",
+            """CREATE TABLE test10 (
+                s TEXT,
+                x TEXT GENERATED ALWAYS AS (s || 'x')
+            ) STRICT;""",
+            """CREATE TABLE test11 (
+                id INTEGER PRIMARY KEY,
+                s TEXT,
+                x TEXT GENERATED ALWAYS AS (s || 'x')
+            ) STRICT, WITHOUT ROWID;""",
+            """CREATE TABLE test12 (
+                id INTEGER PRIMARY KEY,
+                s TEXT,
+                x TEXT GENERATED ALWAYS AS (s || 'x')
+            ) WITHOUT ROWID, STRICT;""",
         ]
 
         with testing.db.begin() as conn:
@@ -4013,6 +4032,10 @@ def teardown_test_class(cls):
             "x": {"text": 'INSTR(s, ",")', "stored": True},
             "y": {"text": 'INSTR(x, ",")', "stored": True},
         },
+        "test9": {"x": {"text": "s || 'x'", "stored": False}},
+        "test10": {"x": {"text": "s || 'x'", "stored": False}},
+        "test11": {"x": {"text": "s || 'x'", "stored": False}},
+        "test12": {"x": {"text": "s || 'x'", "stored": False}},
     }
 
     def test_reflection(self, connection):

Original file line number	Diff line number	Diff line change
`@@ -2377,7 +2377,10 @@ def get_columns(self, connection, table_name, schema=None, **kw):`
`2377`	`2377`	`)`
`2378`	`2378`	`# remove create table`
`2379`	`2379`	`match = re.match(`
`2380`		`- r"create table .?\((.)\)$",`
	`2380`	`+ (`
	`2381`	`+ r"create table .?\((.)\)"`
	`2382`	`+ r"(?:\s,?\s(?:WITHOUT\s+ROWID\|STRICT))*$"`
	`2383`	`+ ),`
`2381`	`2384`	`tablesql.strip(),`
`2382`	`2385`	`re.DOTALL \| re.IGNORECASE,`
`2383`	`2386`	`)`