- Trending Categories
- Data Structure
- Operating System
- MS Excel
- C Programming
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
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()
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()
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()
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
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.
Kickstart Your Career
Get certified by completing the courseGet Started