Suppose you manage some data in a SQLite DB within a Python application. How can you dump the DB into a SQL dump file? Better yet, how can you directly create a zipped dump file (dumps tend to be big, and since they’re SQL code, can be compressed very nicely).
Here’s the code:
import sqlite3, sys, zipfile dbname = sys.argv if len(sys.argv) > 1 else 'testdb.db' # Open the db and dump all its data into the 'data' buffer con = sqlite3.connect(dbname) data = '\n'.join(con.iterdump()) con.close() # Create a zip file and write add the dump into it as # a new file zf = zipfile.ZipFile('dump.zip', mode='w', compression=zipfile.ZIP_DEFLATED) zf.writestr('dump.sql', data) zf.close()
It will work with Python 2.6 and later, since the iterdump method of sqlite3 is only available since that version.
Note that the .zip file is created on the fly from a buffer, without a real dump.sql file being created on the disk.