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())

# Create a zip file and write add the dump into it as
# a new file
zf = zipfile.ZipFile('',
zf.writestr('dump.sql', data)

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 powered by Disqus