Backing up sqlite3 database in Python 2.5

Question

I'm working on a project that needs to support both Python 2.5 and 2.6, and uses sqlite3. I'd like to be able to backup the database from within the program.

It seems to be that there are two ways to do this: create a new database in sqlite3 and move all the data, or simply copy the database file on the drive.

My instinct (and part of what I'd like to test here) is that it's safer to copy the data within the system since it will make sure I don't try to copy a file in an unstable state. While I could protect against many basic errors, protecting against all possible mistakes in copying the file would be a challenge.

However, since I'm supporting python 2.5 I don't have iterdump() at my disposal, creating the backup within the program would be time consuming (making the file copy tempting).

What are the pros and cons of copying the file directly? Is there an easy way in 2.5 to copy all the database in a manner similar to 2.6's iterdump()?

Answer 1

Copying a SQLite3 file that's currently in use may work 99% of the time, but it's not safe, as I learned the hard way. I mean, you could actually copy the DB file itself, but you have to be absolutely sure that all file handles to the DB file are closed, otherwise you risk precisely the situation you're concerned about.

Here's two options:

1. Dump using a system call to the command line

This is great, because it dumps the database to a file with a set of SQL statements that will rebuild it for you from scratch, AND compresses it using gzip in one step. You then backup a copy of the resulting .gz file and you're done.

2. Dump using the sqlite3 command line shell

Answer 2

I'm taking a shot in the dark, not knowing Python but being familiar with SQLite3.

Pros

• Safe backup, as SQLite databases are single files
• Backward compatible for your missing iterdump() function.

Cons

• Database size might prohibit duplication.