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

SQLite is a popular lightweight and server less 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 is a powerful programming language that is widely used for various tasks including data processing and management. In this tutorial, we will explore how to create a backup of a SQLite database using Python. We will walk through the process of connecting to a SQLite database, creating a backup file, and verifying that the backup was successful.

This tutorial assumes that you have a basic knowledge of Python and SQLite.

The first step for us is to make sure that SQLite3 is installed on our machine, and to check the same, we can run the command shown below.

sqlite3 

If you get a system similar to the one shown below, then you are good to proceed with the examples shown below.

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> 

Now let's take a few examples to show how you can create backups of a SQLite database.

Example 1: Creating a Simple Database in SQLite

In the first example, we will understand how we can create a simple database in SQLite in python. Consider the code shown below.

This code defines a function called connect_to_database that establishes a connection to an SQLite database named mydatabase.db.

• If the connection is established successfully, the function prints a message to confirm this, and also prints a message to confirm that the database file has been created.

• If an error occurs, the function prints the error message.

The function is called at the end of the code to establish a connection to the database, and then the connection is closed.

# Import the required modules
import sqlite3
from sqlite3 import Error

# Define a function to establish a connection to the database
def connect_to_database():
   try:

      # Connect to the SQLite database
      conn = sqlite3.connect('mydatabase.db')
      
      # Print a message to confirm that the connection was established successfully
      print("Connection established successfully!")
      
      # Print a message to confirm that the database file has been created
      print("'mydatabase.db' created")
      
      # Return the connection object
      return conn
   except Error as e:
   
      # If an error occurs, print the error message
      print(e)

# Call the function to establish a connection to the database
conn = connect_to_database()

# Close the connection to the database
conn.close()

Output

Once you execute this code, it will create a new file named mydatabase.db in the same folder.

Connection established successfully!
'mydatabase.db' created

For reference, now my current directory looks something like this −

.
├── main.py
└── mydatabase.db
0 directories, 2 files 

Example 2: Creating a Table inside the SQLite Database

Now as the next step, let's say that we want to create a table inside our database. Consider the code shown below.

The code creates a connection to an SQLite database named "mydatabase.db" and creates a table named "students" with six columns in the database.

# Import the required modules
import sqlite3
from sqlite3 import Error

# Define a function to establish a connection to the database
def connect_to_database():
   try:
   
      # Connect to the SQLite database named mydatabase.db
      conn = sqlite3.connect('mydatabase.db')
      
      # Return the connection object
      return conn
   except Error as e:

      # If an error occurs, print the error message
      print(e)
      
# Define a function to create a table in the database
def create_table(conn):
   
   # Create a cursor object to execute SQL queries
   cursor = conn.cursor()
   
   # Define the SQL query to create a table named 'students'
   sql_query = '''CREATE TABLE students (
      roll_no INTEGER PRIMARY KEY,
      first_name TEXT,
      last_name TEXT,
      class TEXT,
      stream TEXT,
      address TEXT
   );'''
   
   # Execute the SQL query to create the table
   cursor.execute(sql_query)
   
   # Commit the changes to the database
   conn.commit()
   
   # Print a message to confirm that the table has been created
   print("Table 'students' created successfully") 
   
# Call the function to establish a connection to the database
conn = connect_to_database()

# Call the function to create a table in the database
create_table(conn)

# Close the connection to the database
conn.close() 

Output

On execution, you will get the following output −

Table 'students' created successfully 

Example 3: Creating a Backup of the Database

Now let's focus on the code where we will create a backup of the current database. Consider the code shown below.

import sqlite3
import io
conn = sqlite3.connect('mydatabase.db')

# Open() function
with io.open('mydatabase_dump.sql', 'w') as p:

   # iterdump() function
   for line in conn.iterdump():
      p.write('%s\n' % line)
print(' Backup performed successfully!')
print(' Data Saved as backupdatabase_dump.sql')
conn.close() 

Output

On execution, you will get the following output −

Backup performed successfully!
Data Saved as backupdatabase_dump.sql

Also, a new SQL backup database will be created. Now the directory structure will look like this −

.
├── main.py
├── mydatabase.db
└── mydatabase_dump.sql
0 directories, 3 files 

Conclusion

You can create a backup of an SQLite database using Python by opening a connection to the database, iterating through the data using the iterdump() function, and writing the data to a backup file using the open() function. This process ensures that a copy of the database is saved in the event of data loss or corruption.