/Lib/sqlite3/dump.py

 1# Mimic the sqlite3 console shell's .dump command
 2# Author: Paul Kippes <kippesp@gmail.com>
 3
 4def _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;')