[Python-checkins] gh-79009: sqlite3.iterdump now correctly handles tables with autoincrement (GH-9621)
miss-islington
webhook-mailer at python.org
Sun Jun 19 19:25:21 EDT 2022
https://github.com/python/cpython/commit/5abe4cbe88ecae2f52aa2b320dfbc7b9d1572a10
commit: 5abe4cbe88ecae2f52aa2b320dfbc7b9d1572a10
branch: 3.11
author: Miss Islington (bot) <31488909+miss-islington at users.noreply.github.com>
committer: miss-islington <31488909+miss-islington at users.noreply.github.com>
date: 2022-06-19T16:25:13-07:00
summary:
gh-79009: sqlite3.iterdump now correctly handles tables with autoincrement (GH-9621)
Co-authored-by: Erlend E. Aasland <erlend.aasland at protonmail.com>
(cherry picked from commit affa9f22cfd1e83a5fb413e5ce2feef9ea1a49ac)
Co-authored-by: itssme <itssme3000 at gmail.com>
files:
A Misc/NEWS.d/next/Library/2018-09-28-22-18-03.bpo-34828.5Zyi_S.rst
M Lib/sqlite3/dump.py
M Lib/test/test_sqlite3/test_dump.py
M Misc/ACKS
diff --git a/Lib/sqlite3/dump.py b/Lib/sqlite3/dump.py
index de9c368be3014..07b9da10b920f 100644
--- a/Lib/sqlite3/dump.py
+++ b/Lib/sqlite3/dump.py
@@ -28,9 +28,16 @@ def _iterdump(connection):
ORDER BY "name"
"""
schema_res = cu.execute(q)
+ sqlite_sequence = []
for table_name, type, sql in schema_res.fetchall():
if table_name == 'sqlite_sequence':
- yield('DELETE FROM "sqlite_sequence";')
+ rows = cu.execute('SELECT * FROM "sqlite_sequence";').fetchall()
+ sqlite_sequence = ['DELETE FROM "sqlite_sequence"']
+ sqlite_sequence += [
+ f'INSERT INTO "sqlite_sequence" VALUES(\'{row[0]}\',{row[1]})'
+ for row in rows
+ ]
+ continue
elif table_name == 'sqlite_stat1':
yield('ANALYZE "sqlite_master";')
elif table_name.startswith('sqlite_'):
@@ -67,4 +74,9 @@ def _iterdump(connection):
for name, type, sql in schema_res.fetchall():
yield('{0};'.format(sql))
+ # gh-79009: Yield statements concerning the sqlite_sequence table at the
+ # end of the transaction.
+ for row in sqlite_sequence:
+ yield('{0};'.format(row))
+
yield('COMMIT;')
diff --git a/Lib/test/test_sqlite3/test_dump.py b/Lib/test/test_sqlite3/test_dump.py
index 1f14c620f0d24..d0c24b9c60e61 100644
--- a/Lib/test/test_sqlite3/test_dump.py
+++ b/Lib/test/test_sqlite3/test_dump.py
@@ -2,6 +2,8 @@
import unittest
import sqlite3 as sqlite
+from .test_dbapi import memory_database
+
class DumpTests(unittest.TestCase):
def setUp(self):
@@ -49,6 +51,51 @@ def test_table_dump(self):
[self.assertEqual(expected_sqls[i], actual_sqls[i])
for i in range(len(expected_sqls))]
+ def test_dump_autoincrement(self):
+ expected = [
+ 'CREATE TABLE "t1" (id integer primary key autoincrement);',
+ 'INSERT INTO "t1" VALUES(NULL);',
+ 'CREATE TABLE "t2" (id integer primary key autoincrement);',
+ ]
+ self.cu.executescript("".join(expected))
+
+ # the NULL value should now be automatically be set to 1
+ expected[1] = expected[1].replace("NULL", "1")
+ expected.insert(0, "BEGIN TRANSACTION;")
+ expected.extend([
+ 'DELETE FROM "sqlite_sequence";',
+ 'INSERT INTO "sqlite_sequence" VALUES(\'t1\',1);',
+ 'COMMIT;',
+ ])
+
+ actual = [stmt for stmt in self.cx.iterdump()]
+ self.assertEqual(expected, actual)
+
+ def test_dump_autoincrement_create_new_db(self):
+ self.cu.execute("BEGIN TRANSACTION")
+ self.cu.execute("CREATE TABLE t1 (id integer primary key autoincrement)")
+ self.cu.execute("CREATE TABLE t2 (id integer primary key autoincrement)")
+ self.cu.executemany("INSERT INTO t1 VALUES(?)", ((None,) for _ in range(9)))
+ self.cu.executemany("INSERT INTO t2 VALUES(?)", ((None,) for _ in range(4)))
+ self.cx.commit()
+
+ with memory_database() as cx2:
+ query = "".join(self.cx.iterdump())
+ cx2.executescript(query)
+ cu2 = cx2.cursor()
+
+ dataset = (
+ ("t1", 9),
+ ("t2", 4),
+ )
+ for table, seq in dataset:
+ with self.subTest(table=table, seq=seq):
+ res = cu2.execute("""
+ SELECT "seq" FROM "sqlite_sequence" WHERE "name" == ?
+ """, (table,))
+ rows = res.fetchall()
+ self.assertEqual(rows[0][0], seq)
+
def test_unorderable_row(self):
# iterdump() should be able to cope with unorderable row types (issue #15545)
class UnorderableRow:
diff --git a/Misc/ACKS b/Misc/ACKS
index d4b728394ecd6..fdcc7ee514d43 100644
--- a/Misc/ACKS
+++ b/Misc/ACKS
@@ -934,6 +934,7 @@ Ron Klatchko
Reid Kleckner
Carsten Klein
Bastian Kleineidam
+Joel Klimont
Bob Kline
Matthias Klose
Jeremy Kloth
diff --git a/Misc/NEWS.d/next/Library/2018-09-28-22-18-03.bpo-34828.5Zyi_S.rst b/Misc/NEWS.d/next/Library/2018-09-28-22-18-03.bpo-34828.5Zyi_S.rst
new file mode 100644
index 0000000000000..b0e10a158b5b1
--- /dev/null
+++ b/Misc/NEWS.d/next/Library/2018-09-28-22-18-03.bpo-34828.5Zyi_S.rst
@@ -0,0 +1 @@
+:meth:`sqlite3.Connection.iterdump` now handles databases that use ``AUTOINCREMENT`` in one or more tables.
More information about the Python-checkins
mailing list