Zipped dump of a SQLite database with Python

March 5th, 2010 at 12:59 pm

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.

Related posts:

  1. Storing BLOBs in a SQLite DB with Python/pysqlite
  2. Compiling SQLite on Windows
  3. Database n00b
  4. Less copies in Python with the buffer protocol and memoryviews
  5. Setting up Python to work with the serial port

2 Responses to “Zipped dump of a SQLite database with Python”

  1. Floris BruynoogheNo Gravatar Says:

    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)

  2. elibenNo Gravatar Says:

    @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!

Leave a Reply

To post code with preserved formatting, enclose it in `backticks` (even multiple lines)