Lib/sqlite3/dump.py

   1 # Mimic the sqlite3 console shell's .dump command
   2 # Author: Paul Kippes <kippesp@gmail.com>
   3
   4 def _iterdump(connection):
   5     """
   6     Returns an iterator to the dump of the database in an SQL text format.
   7
   8     Used to produce an SQL dump of the database.  Useful to save an in-memory
   9     database for later restoration.  This function should not be called
  10     directly but instead called from the Connection method, iterdump().
  11     """
  12
  13     cu = connection.cursor()
  14     yield('BEGIN TRANSACTION;')
  15
  16     # sqlite_master table contains the SQL CREATE statements for the database.
  17     q = """
  18         SELECT name, type, sql
  19         FROM sqlite_master
  20             WHERE sql NOT NULL AND
  21             type == 'table'
  22         """
  23     schema_res = cu.execute(q)
  24     for table_name, type, sql in schema_res.fetchall():
  25         if table_name == 'sqlite_sequence':
  26             yield('DELETE FROM sqlite_sequence;')
  27         elif table_name == 'sqlite_stat1':
  28             yield('ANALYZE sqlite_master;')
  29         elif table_name.startswith('sqlite_'):
  30             continue
  31         # NOTE: Virtual table support not implemented
  32         #elif sql.startswith('CREATE VIRTUAL TABLE'):
  33         #    qtable = table_name.replace("'", "''")
  34         #    yield("INSERT INTO sqlite_master(type,name,tbl_name,rootpage,sql)"\
  35         #        "VALUES('table','%s','%s',0,'%s');" %
  36         #        qtable,
  37         #        qtable,
  38         #        sql.replace("''"))
  39         else:
  40             yield('%s;' % sql)
  41
  42         # Build the insert statement for each row of the current table
  43         res = cu.execute("PRAGMA table_info('%s')" % table_name)
  44         column_names = [str(table_info[1]) for table_info in res.fetchall()]
  45         q = "SELECT 'INSERT INTO \"%(tbl_name)s\" VALUES("
  46         q += ",".join(["'||quote(" + col + ")||'" for col in column_names])
  47         q += ")' FROM '%(tbl_name)s'"
  48         query_res = cu.execute(q % {'tbl_name': table_name})
  49         for row in query_res:
  50             yield("%s;" % row[0])
  51
  52     # Now when the type is 'index', 'trigger', or 'view'
  53     q = """
  54         SELECT name, type, sql
  55         FROM sqlite_master
  56             WHERE sql NOT NULL AND
  57             type IN ('index', 'trigger', 'view')
  58         """
  59     schema_res = cu.execute(q)
  60     for name, type, sql in schema_res.fetchall():
  61         yield('%s;' % sql)
  62
  63     yield('COMMIT;')