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[1] 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.


Comments

comments powered by Disqus