How to Create a Backup of a SQLite Database Using Python?

SQLite is a popular lightweight and serverless database management system used in many applications. It is known for its ease of use, small footprint, and portability. However, just like any other database, it is important to have a backup of your SQLite database to protect against data loss.

Python provides built-in support for SQLite through the sqlite3 module, making it easy to create database backups. In this tutorial, we will explore how to create a backup of a SQLite database using Python's iterdump() method.

Prerequisites

Before starting, ensure SQLite3 is installed on your system. You can check this by running the following command in your terminal ?

sqlite3

If SQLite is installed, you should see output similar to this ?

SQLite version 3.39.5 2022-10-14 20:58:05
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>

Creating a Sample SQLite Database

First, let's create a simple SQLite database with a table to demonstrate the backup process ?

import sqlite3
from sqlite3 import Error

def create_sample_database():
    try:
        # Connect to SQLite database (creates if doesn't exist)
        conn = sqlite3.connect('mydatabase.db')
        cursor = conn.cursor()
        
        # Create a students table
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS students (
            roll_no INTEGER PRIMARY KEY,
            first_name TEXT,
            last_name TEXT,
            class TEXT,
            stream TEXT,
            address TEXT
        )
        ''')
        
        # Insert sample data
        students_data = [
            (1, 'John', 'Doe', '12th', 'Science', 'New York'),
            (2, 'Jane', 'Smith', '11th', 'Commerce', 'California'),
            (3, 'Mike', 'Johnson', '12th', 'Arts', 'Texas')
        ]
        
        cursor.executemany('''
        INSERT OR REPLACE INTO students 
        (roll_no, first_name, last_name, class, stream, address) 
        VALUES (?, ?, ?, ?, ?, ?)
        ''', students_data)
        
        conn.commit()
        print("Database and table created successfully!")
        print("Sample data inserted!")
        
        return conn
        
    except Error as e:
        print(f"Error creating database: {e}")
        return None

# Create the sample database
conn = create_sample_database()
if conn:
    conn.close()

Database and table created successfully!
Sample data inserted!

Method 1: Using iterdump() for SQL Backup

The iterdump() method creates a SQL dump of the entire database, which can be used to restore the database later ?

import sqlite3
import io

def create_sql_backup():
    try:
        # Connect to the source database
        conn = sqlite3.connect('mydatabase.db')
        
        # Create SQL backup file
        with io.open('mydatabase_backup.sql', 'w') as backup_file:
            # Use iterdump() to get SQL commands
            for line in conn.iterdump():
                backup_file.write(f'{line}\n')
        
        print("SQL backup created successfully!")
        print("Backup saved as 'mydatabase_backup.sql'")
        
        conn.close()
        
    except Exception as e:
        print(f"Error creating backup: {e}")

# Create the backup
create_sql_backup()

SQL backup created successfully!
Backup saved as 'mydatabase_backup.sql'

Method 2: Using backup() for Binary Copy

For a more efficient backup, you can use the backup() method which creates a binary copy of the database ?

import sqlite3

def create_binary_backup():
    try:
        # Connect to source and backup databases
        source_conn = sqlite3.connect('mydatabase.db')
        backup_conn = sqlite3.connect('mydatabase_binary_backup.db')
        
        # Create binary backup
        source_conn.backup(backup_conn)
        
        print("Binary backup created successfully!")
        print("Backup saved as 'mydatabase_binary_backup.db'")
        
        # Close connections
        backup_conn.close()
        source_conn.close()
        
    except Exception as e:
        print(f"Error creating binary backup: {e}")

# Create binary backup
create_binary_backup()

Binary backup created successfully!
Backup saved as 'mydatabase_binary_backup.db'

Verifying the Backup

Let's verify that our backup works by reading data from the backup database ?

import sqlite3

def verify_backup():
    try:
        # Connect to the backup database
        conn = sqlite3.connect('mydatabase_binary_backup.db')
        cursor = conn.cursor()
        
        # Query the students table
        cursor.execute("SELECT * FROM students")
        rows = cursor.fetchall()
        
        print("Data from backup database:")
        print("Roll No | First Name | Last Name | Class | Stream | Address")
        print("-" * 60)
        
        for row in rows:
            print(f"{row[0]:7} | {row[1]:10} | {row[2]:9} | {row[3]:5} | {row[4]:7} | {row[5]}")
        
        conn.close()
        
    except Exception as e:
        print(f"Error verifying backup: {e}")

# Verify the backup
verify_backup()

Data from backup database:
Roll No | First Name | Last Name | Class | Stream | Address
------------------------------------------------------------
      1 | John       | Doe       | 12th  | Science | New York
      2 | Jane       | Smith     | 11th  | Commerce| California
      3 | Mike       | Johnson   | 12th  | Arts    | Texas

Comparison of Backup Methods

Conclusion

Creating backups of SQLite databases in Python is straightforward using either iterdump() for SQL dumps or backup() for binary copies. The backup() method is more efficient for large databases, while iterdump() creates human-readable SQL files that can be easily inspected and modified.