[Python-checkins] cpython (2.7): Fix sqlite3.Connection.iterdump on tables/fields with reserved names or quotes

petri.lehtinen python-checkins at python.org
Sun Feb 12 20:09:40 CET 2012


http://hg.python.org/cpython/rev/60aa7dacb605
changeset:   74891:60aa7dacb605
branch:      2.7
parent:      74888:e0c353a43c7d
user:        Petri Lehtinen <petri at digip.org>
date:        Sun Feb 12 21:03:02 2012 +0200
summary:
  Fix sqlite3.Connection.iterdump on tables/fields with reserved names or quotes

Closes #9750

files:
  Lib/sqlite3/dump.py      |  50 +++++++++++++++------------
  Lib/sqlite3/test/dump.py |   8 ++++
  Misc/ACKS                |   1 +
  Misc/NEWS                |   4 ++
  4 files changed, 41 insertions(+), 22 deletions(-)


diff --git a/Lib/sqlite3/dump.py b/Lib/sqlite3/dump.py
--- a/Lib/sqlite3/dump.py
+++ b/Lib/sqlite3/dump.py
@@ -1,6 +1,12 @@
 # Mimic the sqlite3 console shell's .dump command
 # Author: Paul Kippes <kippesp at gmail.com>
 
+# Every identifier in sql is quoted based on a comment in sqlite
+# documentation "SQLite adds new keywords from time to time when it
+# takes on new features. So to prevent your code from being broken by
+# future enhancements, you should normally quote any identifier that
+# is an English language word, even if you do not have to."
+
 def _iterdump(connection):
     """
     Returns an iterator to the dump of the database in an SQL text format.
@@ -15,49 +21,49 @@
 
     # sqlite_master table contains the SQL CREATE statements for the database.
     q = """
-        SELECT name, type, sql
-        FROM sqlite_master
-            WHERE sql NOT NULL AND
-            type == 'table'
+        SELECT "name", "type", "sql"
+        FROM "sqlite_master"
+            WHERE "sql" NOT NULL AND
+            "type" == 'table'
         """
     schema_res = cu.execute(q)
-    for table_name, type, sql in schema_res.fetchall():
+    for table_name, type, sql in sorted(schema_res.fetchall()):
         if table_name == 'sqlite_sequence':
-            yield('DELETE FROM sqlite_sequence;')
+            yield('DELETE FROM "sqlite_sequence";')
         elif table_name == 'sqlite_stat1':
-            yield('ANALYZE sqlite_master;')
+            yield('ANALYZE "sqlite_master";')
         elif table_name.startswith('sqlite_'):
             continue
         # NOTE: Virtual table support not implemented
         #elif sql.startswith('CREATE VIRTUAL TABLE'):
         #    qtable = table_name.replace("'", "''")
         #    yield("INSERT INTO sqlite_master(type,name,tbl_name,rootpage,sql)"\
-        #        "VALUES('table','%s','%s',0,'%s');" %
+        #        "VALUES('table','{0}','{0}',0,'{1}');".format(
         #        qtable,
-        #        qtable,
-        #        sql.replace("''"))
+        #        sql.replace("''")))
         else:
-            yield('%s;' % sql)
+            yield('{0};'.format(sql))
 
         # Build the insert statement for each row of the current table
-        res = cu.execute("PRAGMA table_info('%s')" % table_name)
+        table_name_ident = table_name.replace('"', '""')
+        res = cu.execute('PRAGMA table_info("{0}")'.format(table_name_ident))
         column_names = [str(table_info[1]) for table_info in res.fetchall()]
-        q = "SELECT 'INSERT INTO \"%(tbl_name)s\" VALUES("
-        q += ",".join(["'||quote(" + col + ")||'" for col in column_names])
-        q += ")' FROM '%(tbl_name)s'"
-        query_res = cu.execute(q % {'tbl_name': table_name})
+        q = """SELECT 'INSERT INTO "{0}" VALUES({1})' FROM "{0}";""".format(
+            table_name_ident,
+            ",".join("""'||quote("{0}")||'""".format(col.replace('"', '""')) for col in column_names))
+        query_res = cu.execute(q)
         for row in query_res:
-            yield("%s;" % row[0])
+            yield("{0};".format(row[0]))
 
     # Now when the type is 'index', 'trigger', or 'view'
     q = """
-        SELECT name, type, sql
-        FROM sqlite_master
-            WHERE sql NOT NULL AND
-            type IN ('index', 'trigger', 'view')
+        SELECT "name", "type", "sql"
+        FROM "sqlite_master"
+            WHERE "sql" NOT NULL AND
+            "type" IN ('index', 'trigger', 'view')
         """
     schema_res = cu.execute(q)
     for name, type, sql in schema_res.fetchall():
-        yield('%s;' % sql)
+        yield('{0};'.format(sql))
 
     yield('COMMIT;')
diff --git a/Lib/sqlite3/test/dump.py b/Lib/sqlite3/test/dump.py
--- a/Lib/sqlite3/test/dump.py
+++ b/Lib/sqlite3/test/dump.py
@@ -13,6 +13,14 @@
 
     def CheckTableDump(self):
         expected_sqls = [
+                """CREATE TABLE "index"("index" blob);"""
+                ,
+                """INSERT INTO "index" VALUES(X'01');"""
+                ,
+                """CREATE TABLE "quoted""table"("quoted""field" text);"""
+                ,
+                """INSERT INTO "quoted""table" VALUES('quoted''value');"""
+                ,
                 "CREATE TABLE t1(id integer primary key, s1 text, " \
                 "t1_i1 integer not null, i2 integer, unique (s1), " \
                 "constraint t1_idx1 unique (i2));"
diff --git a/Misc/ACKS b/Misc/ACKS
--- a/Misc/ACKS
+++ b/Misc/ACKS
@@ -457,6 +457,7 @@
 Pat Knight
 Greg Kochanski
 Damon Kohler
+Marko Kohtala
 Joseph Koshy
 Maksim Kozyarchuk
 Stefan Krah
diff --git a/Misc/NEWS b/Misc/NEWS
--- a/Misc/NEWS
+++ b/Misc/NEWS
@@ -90,6 +90,10 @@
 Library
 -------
 
+- Issue #9750: Fix sqlite3.Connection.iterdump on tables and fields
+  with a name that is a keyword or contains quotes. Patch by Marko
+  Kohtala.
+
 - Issue #13994: Earlier partial revert of Distutils enhancements in 2.7
   has left two versions of customize_compiler, the original in
   distutils.sysconfig and another copy in distutils.ccompiler, with some

-- 
Repository URL: http://hg.python.org/cpython


More information about the Python-checkins mailing list