Zipped dump of a SQLite database with Python
March 5th, 2010 at 12:59 pmSuppose 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.
Related posts:
March 5th, 2010 at 17:40
Your snippet will build a string with the entire dump entirely in memory. If it’s too big for an unzipped file surely it’s also too big to be in memory.
I don’t think you can using zipefile but using gzip you can compress on the fly:
zf = gzip.open('dump.txt.gz', mode='w')for line in con.iterdump():
zf.write(line)
zf.write('\n')
zf.close()
(indentation messed up, .write()s are indended obviously)
March 6th, 2010 at 09:03
@Floris,
I did not actually mean that it’s too big for the disk – just that it can save a messy step of writing a temporary file and then zipping it.
However, your comment is still insightful – I didn’t know this about
gzip– thanks!