/Lib/sqlite3/dump.py
Python | 63 lines | 50 code | 3 blank | 10 comment | 0 complexity | 43482efd14f6f500b0aa2b9b927d3587 MD5 | raw file
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;')