Skip to content

Commit a973b4b

Browse files
felixxmerlend-aasland
authored and
fsc-eriker
committed
pythongh-91602: Add iterdump() support for filtering database objects (python#114501)
Add optional 'filter' parameter to iterdump() that allows a "LIKE" pattern for filtering database objects to dump. Co-authored-by: Erlend E. Aasland <erlend@python.org>
1 parent 755e692 commit a973b4b

11 files changed

Lines changed: 176 additions & 17 deletions

File tree

‎Doc/library/sqlite3.rst‎

Lines changed: 10 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1137,12 +1137,19 @@ Connection objects
11371137

11381138
.. _Loading an Extension: https://www.sqlite.org/loadext.html#loading_an_extension_
11391139

1140-
.. method:: iterdump
1140+
.. method:: iterdump(*, filter=None)
11411141

11421142
Return an :term:`iterator` to dump the database as SQL source code.
11431143
Useful when saving an in-memory database for later restoration.
11441144
Similar to the ``.dump`` command in the :program:`sqlite3` shell.
11451145

1146+
:param filter:
1147+
1148+
An optional ``LIKE`` pattern for database objects to dump, e.g. ``prefix_%``.
1149+
If ``None`` (the default), all database objects will be included.
1150+
1151+
:type filter: str | None
1152+
11461153
Example:
11471154

11481155
.. testcode::
@@ -1158,6 +1165,8 @@ Connection objects
11581165

11591166
:ref:`sqlite3-howto-encoding`
11601167

1168+
.. versionchanged:: 3.13
1169+
Added the *filter* parameter.
11611170

11621171
.. method:: backup(target, *, pages=-1, progress=None, name="main", sleep=0.250)
11631172

‎Doc/whatsnew/3.13.rst‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -438,6 +438,10 @@ sqlite3
438438
object is not :meth:`closed <sqlite3.Connection.close>` explicitly.
439439
(Contributed by Erlend E. Aasland in :gh:`105539`.)
440440

441+
* Add *filter* keyword-only parameter to :meth:`sqlite3.Connection.iterdump`
442+
for filtering database objects to dump.
443+
(Contributed by Mariusz Felisiak in :gh:`91602`.)
444+
441445
subprocess
442446
----------
443447

‎Include/internal/pycore_global_objects_fini_generated.h‎

Lines changed: 1 addition & 0 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎Include/internal/pycore_global_strings.h‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -429,6 +429,7 @@ struct _Py_global_strings {
429429
STRUCT_FOR_ID(fileno)
430430
STRUCT_FOR_ID(filepath)
431431
STRUCT_FOR_ID(fillvalue)
432+
STRUCT_FOR_ID(filter)
432433
STRUCT_FOR_ID(filters)
433434
STRUCT_FOR_ID(final)
434435
STRUCT_FOR_ID(find_class)

‎Include/internal/pycore_runtime_init_generated.h‎

Lines changed: 1 addition & 0 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎Include/internal/pycore_unicodeobject_generated.h‎

Lines changed: 3 additions & 0 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎Lib/sqlite3/dump.py‎

Lines changed: 14 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -15,7 +15,7 @@ def _quote_value(value):
1515
return "'{0}'".format(value.replace("'", "''"))
1616

1717

18-
def _iterdump(connection):
18+
def _iterdump(connection, *, filter=None):
1919
"""
2020
Returns an iterator to the dump of the database in an SQL text format.
2121
@@ -32,15 +32,23 @@ def _iterdump(connection):
3232
yield('PRAGMA foreign_keys=OFF;')
3333
yield('BEGIN TRANSACTION;')
3434

35+
if filter:
36+
# Return database objects which match the filter pattern.
37+
filter_name_clause = 'AND "name" LIKE ?'
38+
params = [filter]
39+
else:
40+
filter_name_clause = ""
41+
params = []
3542
# sqlite_master table contains the SQL CREATE statements for the database.
36-
q = """
43+
q = f"""
3744
SELECT "name", "type", "sql"
3845
FROM "sqlite_master"
3946
WHERE "sql" NOT NULL AND
4047
"type" == 'table'
48+
{filter_name_clause}
4149
ORDER BY "name"
4250
"""
43-
schema_res = cu.execute(q)
51+
schema_res = cu.execute(q, params)
4452
sqlite_sequence = []
4553
for table_name, type, sql in schema_res.fetchall():
4654
if table_name == 'sqlite_sequence':
@@ -82,13 +90,14 @@ def _iterdump(connection):
8290
yield("{0};".format(row[0]))
8391

8492
# Now when the type is 'index', 'trigger', or 'view'
85-
q = """
93+
q = f"""
8694
SELECT "name", "type", "sql"
8795
FROM "sqlite_master"
8896
WHERE "sql" NOT NULL AND
8997
"type" IN ('index', 'trigger', 'view')
98+
{filter_name_clause}
9099
"""
91-
schema_res = cu.execute(q)
100+
schema_res = cu.execute(q, params)
92101
for name, type, sql in schema_res.fetchall():
93102
yield('{0};'.format(sql))
94103

‎Lib/test/test_sqlite3/test_dump.py‎

Lines changed: 70 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -54,6 +54,76 @@ def test_table_dump(self):
5454
[self.assertEqual(expected_sqls[i], actual_sqls[i])
5555
for i in range(len(expected_sqls))]
5656

57+
def test_table_dump_filter(self):
58+
all_table_sqls = [
59+
"""CREATE TABLE "some_table_2" ("id_1" INTEGER);""",
60+
"""INSERT INTO "some_table_2" VALUES(3);""",
61+
"""INSERT INTO "some_table_2" VALUES(4);""",
62+
"""CREATE TABLE "test_table_1" ("id_2" INTEGER);""",
63+
"""INSERT INTO "test_table_1" VALUES(1);""",
64+
"""INSERT INTO "test_table_1" VALUES(2);""",
65+
]
66+
all_views_sqls = [
67+
"""CREATE VIEW "view_1" AS SELECT * FROM "some_table_2";""",
68+
"""CREATE VIEW "view_2" AS SELECT * FROM "test_table_1";""",
69+
]
70+
# Create database structure.
71+
for sql in [*all_table_sqls, *all_views_sqls]:
72+
self.cu.execute(sql)
73+
# %_table_% matches all tables.
74+
dump_sqls = list(self.cx.iterdump(filter="%_table_%"))
75+
self.assertEqual(
76+
dump_sqls,
77+
["BEGIN TRANSACTION;", *all_table_sqls, "COMMIT;"],
78+
)
79+
# view_% matches all views.
80+
dump_sqls = list(self.cx.iterdump(filter="view_%"))
81+
self.assertEqual(
82+
dump_sqls,
83+
["BEGIN TRANSACTION;", *all_views_sqls, "COMMIT;"],
84+
)
85+
# %_1 matches tables and views with the _1 suffix.
86+
dump_sqls = list(self.cx.iterdump(filter="%_1"))
87+
self.assertEqual(
88+
dump_sqls,
89+
[
90+
"BEGIN TRANSACTION;",
91+
"""CREATE TABLE "test_table_1" ("id_2" INTEGER);""",
92+
"""INSERT INTO "test_table_1" VALUES(1);""",
93+
"""INSERT INTO "test_table_1" VALUES(2);""",
94+
"""CREATE VIEW "view_1" AS SELECT * FROM "some_table_2";""",
95+
"COMMIT;"
96+
],
97+
)
98+
# some_% matches some_table_2.
99+
dump_sqls = list(self.cx.iterdump(filter="some_%"))
100+
self.assertEqual(
101+
dump_sqls,
102+
[
103+
"BEGIN TRANSACTION;",
104+
"""CREATE TABLE "some_table_2" ("id_1" INTEGER);""",
105+
"""INSERT INTO "some_table_2" VALUES(3);""",
106+
"""INSERT INTO "some_table_2" VALUES(4);""",
107+
"COMMIT;"
108+
],
109+
)
110+
# Only single object.
111+
dump_sqls = list(self.cx.iterdump(filter="view_2"))
112+
self.assertEqual(
113+
dump_sqls,
114+
[
115+
"BEGIN TRANSACTION;",
116+
"""CREATE VIEW "view_2" AS SELECT * FROM "test_table_1";""",
117+
"COMMIT;"
118+
],
119+
)
120+
# % matches all objects.
121+
dump_sqls = list(self.cx.iterdump(filter="%"))
122+
self.assertEqual(
123+
dump_sqls,
124+
["BEGIN TRANSACTION;", *all_table_sqls, *all_views_sqls, "COMMIT;"],
125+
)
126+
57127
def test_dump_autoincrement(self):
58128
expected = [
59129
'CREATE TABLE "t1" (id integer primary key autoincrement);',

‎Misc/NEWS.d/next/Library/2024-01-24-20-51-49.gh-issue-91602.8fOH8l.rst‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,3 @@
1+
Add *filter* keyword-only parameter to
2+
:meth:`sqlite3.Connection.iterdump` for filtering database objects to dump.
3+
Patch by Mariusz Felisiak.

‎Modules/_sqlite/clinic/connection.c.h‎

Lines changed: 53 additions & 7 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

0 commit comments

Comments
 (0)