5 Best Ways to Create a Backup of a SQLite Database Using Python

by

💡 Problem Formulation: Databases are critical components of many applications, and ensuring that data is protected against loss is paramount. Specifically, SQLite—a lightweight, file-based database engine—widely used in applications, requires an efficient method for creating backups. This article focuses on using Python to backup an SQLite database, transforming an active database file into a secure backup file that can be stored and retrieved when needed.

Method 1: Using the sqlite3 Backup API

The sqlite3 module in Python provides a straightforward API for backing up SQLite databases. The sqlite3.connect() method opens the database and the backup() function creates a backup efficiently. This method allows for incremental backup and gives the user control over the backup process.

Here’s an example:

import sqlite3

def backup_db(source, target):
    con = sqlite3.connect(source)
    bck = sqlite3.connect(target)
    with bck:
        con.backup(bck)
    bck.close()
    con.close()

backup_db('example.db', 'backup.db')

Output: A backup file named ‘backup.db’ is created.

This code snippet defines a function backup_db() which takes two arguments: the source database file and the target backup file. It connects to both databases and uses the backup() method of the connection object to create the backup. After the process is complete, both connections are closed safely.

Method 2: Copying the Database File

A simple file copy of the SQLite database file is a quick and effective method for creating a backup. Python’s built-in shutil module provides a copyfile() function that can be used to make a copy of the database file on disk.

Here’s an example:

import shutil

def copy_db(source, target):
    shutil.copyfile(source, target)

copy_db('example.db', 'backup.db')

Output: A duplicate of ‘example.db’ is made as ‘backup.db’.

In this example, the copy_db() function is used to copy the SQLite database file to a backup location. The shutil.copyfile() method takes the source file and the destination file as its arguments and creates a copy of the file.

Method 3: Dumping to SQL File

For a text-based version of the database, sqlite3 can be used to dump the database contents to a SQL file. This can be done using Python subprocesses to call the SQLite command line utility, allowing the creation of a script that can rebuild the database from scratch.

Here’s an example:

import subprocess

def dump_db_to_sql(source, sql_backup_file):
    subprocess.call(['sqlite3', source, '.dump'], stdout=open(sql_backup_file, 'w'))

dump_db_to_sql('example.db', 'backup.sql')

Output: A SQL file named ‘backup.sql’ containing all the database statements.

The code makes use of the subprocess.call() function to run the SQLite command-line utility, outputting the entire database to a file. This SQL file can later be executed to recreate the database.

Method 4: Using a Python Script to Dump Data

You can write your own Python script to connect to the SQLite database and extract all the data and schema to save it into a SQL file. This is a more manual approach but gives flexibility for processing data.

Here’s an example:

import sqlite3

def dump_to_sql(source, sql_backup_file):
    con = sqlite3.connect(source)
    with open(sql_backup_file, 'w') as f:
        for line in con.iterdump():
            f.write('%s\n' % line)

    con.close()

dump_to_sql('example.db', 'backup.sql')

Output: A SQL file named ‘backup.sql’ containing all the database statements.

This example creates a custom function dump_to_sql() to write the SQLite database to a SQL file, using the iterdump() method of the connection object to retrieve the database dump as an iterable.

Bonus One-Liner Method 5: Using pathlib and shutil

For those who prefer a one-liner approach and like to work with modern Python file paths, the combination of pathlib.Path and shutil.copy() provides an extremely concise way to back up a SQLite database.

Here’s an example:

from pathlib import Path
import shutil

shutil.copy(Path('example.db'), Path('backup.db'))

Output: ‘example.db’ is copied to ‘backup.db’.

This one-liner uses Python’s pathlib to work with paths as objects, making the copy process very readable and straightforward.

Summary/Discussion

  • Method 1: sqlite3 Backup API. Offers a reliable and incremental backup. May not be as quick as a simple file copy for small databases.
  • Method 2: Copying Database File. Fast and easy, great for small to medium databases but might not work with a locked database file.
  • Method 3: Dumping to SQL File. Creates a versatile SQL text-file backup, which is ideal for versioning but is slower for large databases.
  • Method 4: Custom Python Script Dump. Flexible and scriptable, allowing for custom pre-processing, but requires more coding effort.
  • Bonus Method 5: pathlib and shutil. Quick one-liner suitable for scripts and small applications, with the modern touch of pathlib.