This Python SQLite tutorial aims to demonstrate how to develop Python database applications with the SQLite database. You will learn how to perform SQLite database operations from Python.
As you all know, SQLite is a C-language library that implements a SQL database engine that is relatively quick, serverless, and self-contained, high-reliable. SQLite is the most commonly used database engine in the test environment (Refer to SQLite Home page).
SQLite comes built-in with most computers and mobile devices, and browsers. Python’s official sqlite3 module helps us to work with the SQLite database.
Python sqlite3 module adheres to Python Database API Specification v2.0 (PEP 249). PEP 249 provides a SQL interface designed to encourage and maintain the similarity between the Python modules used to access databases.
Let see each section now.
Table of contents
- Python SQLite Database Connection
- Create SQLite table from Python
- SQLite Datatypes and it’s corresponding Python types
- Perform SQLite CRUD Operations from Python
- Execute SQL File (scripts) from Python using cursor’s executescript
- Insert/Retrieve digital data in SQLite using Python
- Create Or Redefine SQLite Functions using Python
- Working with SQLite date and timestamp types in Python and vice-versa
- SQLite Database Exceptions
- Change SQLite connection timeout when connecting from Python
- Identify total changes since the SQLite database connection is open
- Take a backup of SQLite database from Python
- Python SQLite Exercise Project
Python SQLite Database Connection
This section lets you know how to connect to SQLite database in Python using the sqlite3 module.
Use the following steps to connect to SQLite
How to Connect to SQLite Database in Python
- Import sqlite3 module
import sqlite3
statement imports the sqlite3 module in the program. Using the classes and methods defined in the sqlite3 module we can communicate with the SQLite database. - Use the connect() method
Use the
connect()
method of theconnector
class with the database name. To establish a connection to SQLite, you need to pass the database name you want to connect. If you specify the database file name that already presents on the disk, it will connect to it. But if your specified SQLite database file doesn’t exist, SQLite creates a new database for you.
This method returns the SQLite Connection Object if the connection is successful. - Use the cursor() method
Use the
cursor()
method of a connection class to create a cursor object to execute SQLite command/queries from Python. - Use the execute() method
The execute() methods run the SQL query and return the result.
- Extract result using fetchall()
Use
cursor.fetchall()
orfetchone()
orfetchmany()
to read query result. - Close cursor and connection objects
use
cursor.clsoe()
andconnection.clsoe()
method to close the cursor and SQLite connections after your work completes - Catch database exception if any that may occur during this connection process.

The following Python program creates and connects to the new database file “SQLite_Python.db” and prints the SQLite version details.
import sqlite3
try:
sqliteConnection = sqlite3.connect('SQLite_Python.db')
cursor = sqliteConnection.cursor()
print("Database created and Successfully Connected to SQLite")
sqlite_select_Query = "select sqlite_version();"
cursor.execute(sqlite_select_Query)
record = cursor.fetchall()
print("SQLite Database Version is: ", record)
cursor.close()
except sqlite3.Error as error:
print("Error while connecting to sqlite", error)
finally:
if sqliteConnection:
sqliteConnection.close()
print("The SQLite connection is closed")
Output
Database created and Successfully Connected to SQLite SQLite Database Version is: [('3.28.0',)] The SQLite connection is closed

Important points while connecting to SQLite
- The connection object is not thread-safe. The sqlite3 module doesn’t allow sharing connections between threads. If you still try to do so, you will get an exception at runtime.
- The
connect()
method accepts various arguments. In our example, we passed the database name argument to connect. - Using a connection object, we can create a cursor object which allows us to execute SQLite command/queries through Python.
- We can create as many cursors as we want from a single connection object. Like a connection object, this cursor object is also not thread-safe. The sqlite3 module doesn’t allow sharing cursors between threads. If you still try to do so, you will get an exception at runtime.
try-except-finally block
: We placed all our code in this block to catch the SQLite database exceptions and errors during this process.- Using the
Error
class, we can handle any database error and exception that may occur while working with SQLite from Python. - The
Error
class helps us to understand the error in detail. It returns an error message and error code. - It is always good practice to close the cursor and connection object once your work gets completed to avoid database issues.
Create SQLite table from Python
This section will learn how to create a table in the SQLite database from Python. Create a table statement is a DDL query. Let see how to execute it from Python.
In this example, we are creating a SqliteDb_developers
table inside the SQLite_Python.db
database.
Steps for create aa table in SQLite from Python: –
- Connect to SQLite using a
sqlite3.connect()
. - Prepare a create table query.
- Execute the query using a
cursor.execute(query)
import sqlite3
try:
sqliteConnection = sqlite3.connect('SQLite_Python.db')
sqlite_create_table_query = '''CREATE TABLE SqliteDb_developers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email text NOT NULL UNIQUE,
joining_date datetime,
salary REAL NOT NULL);'''
cursor = sqliteConnection.cursor()
print("Successfully Connected to SQLite")
cursor.execute(sqlite_create_table_query)
sqliteConnection.commit()
print("SQLite table created")
cursor.close()
except sqlite3.Error as error:
print("Error while creating a sqlite table", error)
finally:
if sqliteConnection:
sqliteConnection.close()
print("sqlite connection is closed")
Output
Successfully Connected to SQLite SQLite table created: the sqlite connection is closed

SQLite Datatypes and it’s corresponding Python types
Before executing SQLite CRUD operations from Python, first understand SQLite data type and their corresponding Python types, which will help us store and read data from the SQLite table.
SQLite database engine has multiple storage classes to store values. Every value stored in an SQLite database has one of the following storage classes or data types.
SQLite DataTypes:
- NULL: – The value is a NULL value.
- INTEGER: – To store the numeric value. The integer stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the number.
- REAL: – The value is a floating-point value, for example, 3.14 value of PI
- TEXT: – The value is a text string, TEXT value stored using the UTF-8, UTF-16BE or UTF-16LE encoding.
- BLOB: – The value is a blob of data, i.e., binary data. It is used to store images and files.
The following Python types get converted to SQLite without any problem. So when you are modifying or reading from the SQLite table by performing CRUD operations, remember this table.
Python Types | SQLite types |
---|---|
None | NULL |
int | INTEGER |
float | REAL |
str | TEXT |
bytes | BLOB |
Perform SQLite CRUD Operations from Python
Most of the time, we need to manipulate the SQLite table’s data from Python. To perform these data manipulations, we execute DML queries, i.e., SQLite Insert, Update, Delete operations from Python.
Now, we know the table and its column details, so let’s move to the crud operations. I have created a separate tutorial on each operation to cover it in detail. Let see each section now.
- Insert data into SQLite Table from Python: Learn how to execute INSERT command from Python to insert records to the SQLite table.
- Read SQLite Table’s data from Python: Learn how to execute SQLite SELECT query from a Python to fetch the table’s rows. Also, I will let you know how to use
fetchall()
,fetchmany()
, andfetchone()
methods of a cursor class to fetch limited rows from the table to enhance the performance. - Update data of SQLite table from Python: Learn how to execute the UPDATE query from Python to modify records of the SQLite table.
- Delete data from SQLite table from Python: Learn how to execute the DELETE query from Python to remove records from the SQLite table.
Execute SQL File (scripts) from Python using cursor’s executescript
SQLite scripts are handy for most of the daily job. SQLite script is a set of SQL commands saved as a file (in .sql
format).
An SQLite script contains one or more SQL operations that you’ll execute from your command line prompt whenever required.
Below are the few common scenarios where we can use SQLite scripts
- Back-Up Multiple Databases at Once.
- Compare Row Counts in Tables From Two Different Databases With the Same Schema.
- Keep all your CREATE TABLE SQL commands in a database script. So you can create database schema on any server.
You can execute your script from the SQLite command line using the .read command, like this:
sqlite> .read mySQLiteScript.sql
For this example, I have created a sample SQLite script that will create two tables.
CREATE TABLE hardware (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
price REAL NOT NULL
);
CREATE TABLE software (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
price REAL NOT NULL
);
Now let’s see how to execute an SQLite script from Python.
import sqlite3
try:
sqliteConnection = sqlite3.connect('SQLite_Python.db')
cursor = sqliteConnection.cursor()
print("Successfully Connected to SQLite")
with open('E:\pynative\Python\photos\sqlite_create_tables.sql', 'r') as sqlite_file:
sql_script = sqlite_file.read()
cursor.executescript(sql_script)
print("SQLite script executed successfully")
cursor.close()
except sqlite3.Error as error:
print("Error while executing sqlite script", error)
finally:
if sqliteConnection:
sqliteConnection.close()
print("sqlite connection is closed")
Output
Successfully Connected to SQLite SQLite script executed successfully sqlite connection is closed

Note: After connecting to SQLite, We read all content of an SQLite script file stored on disk and copied it into a python string variable. Then we called the cursor.executscript(script)
method to execute all SQL statements in one call.
Insert/Retrieve digital data in SQLite using Python
This section will let you know how to insert or save any digital information such as a file, image, video, or song as BLOB data into the SQLite table from Python.
Also, learn how to read a file, image, video, song, or any digital data stored in SQLite using Python.
Read: Python SQLite BLOB to Insert and Retrieve file and images.
Create Or Redefine SQLite Functions using Python
Python sqlite3 module provides us the ability to create and redefine SQL functions from within Python. I have created a separate tutorial to cover it in detail. Please refer to How to create and redefine SQL functions from within Python.
Read: Create Or Redefine SQLite Functions from within Python.
Working with SQLite date and timestamp types in Python and vice-versa
Sometimes we need to insert or read date or DateTime value from an SQLite table. So if you are working with date or timestamp values, then please read working with SQLite DateTime values in Python.
SQLite Database Exceptions
sqlite3.Warning
- A subclass of Exception. And you can ignore it or read and take action if required.
sqlite3.Error
- The base class of the other exceptions in the sqlite3 module. It is a subclass of Exception.
sqlite3.DatabaseError
- This Exception raised for errors that are related to the database.
- Examples: If you try and open a file as a sqlite3 database that is NOT a database file, you will get
sqlite3.DatabaseError: file is encrypted or is not a database
.
sqlite3.IntegrityError
- Subclass of a DatabaseError. You will get this Exception when the relational integrity of the database is affected, e.g., a foreign key check fails.
sqlite3.ProgrammingError
- It is also a subclass of DatabaseError. This exception raised because of programming errors, e.g., creating a table with the same which already exists, syntax error in the SQL queries.
sqlite3.OperationalError
- It is also a subclass of DatabaseError. This error is not in our control. This exception raised for errors that are related to the database’s operation.
- Examples: an accidental disconnect, server down, a timeout occurs, the data source issues. server down
sqlite3.NotSupportedError
- You will get an exception raised when database API was used which is not supported by the database.
- Example: calling the rollback() method on a connection that does not support the transaction. Calling commit after creating table command.
So it will always be advisable to write all your database operation code in the try block so you can catch exceptions in except block if any and take the corrective actions against it.
For example, let’s try to insert data into a table that doesn’t exist in the SQLite database and Print the full exception stack.
import sqlite3
import traceback
import sys
try:
sqliteConnection = sqlite3.connect('SQLite_Python.db')
cursor = sqliteConnection.cursor()
print("Successfully Connected to SQLite")
sqlite_insert_query = """INSERT INTO unknown_table_1
(id, text) VALUES (1, 'Demo Text')"""
count = cursor.execute(sqlite_insert_query)
sqliteConnection.commit()
print("Record inserted successfully into SqliteDb_developers table ", cursor.rowcount)
cursor.close()
except sqlite3.Error as error:
print("Failed to insert data into sqlite table")
print("Exception class is: ", error.__class__)
print("Exception is", error.args)
print('Printing detailed SQLite exception traceback: ')
exc_type, exc_value, exc_tb = sys.exc_info()
print(traceback.format_exception(exc_type, exc_value, exc_tb))
finally:
if (sqliteConnection):
sqliteConnection.close()
print("The SQLite connection is closed")
Output:
Successfully Connected to SQLite Failed to insert data into sqlite table Exception class is: <class 'sqlite3.OperationalError'> Exception is ('no such table: unknown_table_1',) Printing detailed SQLite exception traceback: ['Traceback (most recent call last):\n', ' File "E:/demos/sqlite_demos/sqlite_errors.py", line 13, in <module>\n count = cursor.execute(sqlite_insert_query)\n', 'sqlite3.OperationalError: no such table: unknown_table_1\n'] The SQLite connection is closed
Change SQLite connection timeout when connecting from Python
When multiple connections access an SQLite database and one of the processes performing some data modification operation on the database, the timeout parameter we specify while connecting to the database determines how long the connection should wait for the lock to go away until raising an exception.
The default value for the timeout parameter is 5.0 (five seconds). Whenever you connect to SQLite from Python, and you didn’t get a response within 5 seconds, your program will raise an exception. But if you face a connection timeout issue and want to increase it, you can do this using a timeout argument of a sqlite3.connect()
function.
Let see how to change the connection timeout value.
import sqlite3
def readSqliteTable():
try:
sqliteConnection = sqlite3.connect('SQLite_Python.db', timeout=20)
cursor = sqliteConnection.cursor()
print("Connected to SQLite")
sqlite_select_query = """SELECT count(*) from SqliteDb_developers"""
cursor.execute(sqlite_select_query)
totalRows = cursor.fetchone()
print("Total rows are: ", totalRows)
cursor.close()
except sqlite3.Error as error:
print("Failed to read data from sqlite table", error)
finally:
if sqliteConnection:
sqliteConnection.close()
print("The Sqlite connection is closed")
readSqliteTable()
Output:
Connected to SQLite Total rows are: (2,) The Sqlite connection is closed
Identify total changes since the SQLite database connection is open
For audit or statistics purpose, use the total_changes()
method of a connection class to find the numbers of database rows modified, inserted, or deleted since the database connection was opened .
The connection.total_changes
method returns the total number of database rows that have been affected.
Example
import sqlite3
try:
sqliteConnection = sqlite3.connect('SQLite_Python.db')
cursor = sqliteConnection.cursor()
print("Connected to SQLite")
sqlite_insert_query = """INSERT INTO SqliteDb_developers
(id, name, email, joining_date, salary)
VALUES (4, 'Jos', 'jos@gmail.com', '2019-01-14', 9500);"""
cursor.execute(sqlite_insert_query)
sql_update_query = """Update SqliteDb_developers set salary = 10000 where id = 4"""
cursor.execute(sql_update_query)
sql_delete_query = """DELETE from SqliteDb_developers where id = 4"""
cursor.execute(sql_delete_query)
sqliteConnection.commit()
cursor.close()
except sqlite3.Error as error:
print("Error while working with SQLite", error)
finally:
if sqliteConnection:
print("Total Rows affected since the database connection was opened: ", sqliteConnection.total_changes)
sqliteConnection.close()
print("sqlite connection is closed")
Output:
Connected to SQLite Total Rows affected since the database connection was opened: 3 sqlite connection is closed
Take a backup of SQLite database from Python
Using a connection.backup()
method, you can take the backup of the SQLite database.
connection.backup(target, *, pages=0, progress=None, name="main", sleep=0.250)
This function takes a backup of the SQLite database, and a copy will be written into the argument target
, which must be another Connection instance. By default, or when pages
are either 0 or a negative integer, the entire database is copied in a single step; otherwise, the method performs a loop copying up to pages at a time.
The name
argument specifies the database that you want to copy. The sleep
argument defines the number of seconds to sleep by between successive attempts to backup the remaining pages of a database. The sleep
argument can be specified either as an integer or a floating-point value.
Let see the example to copy an existing database into another.
import sqlite3
def progress(status, remaining, total):
print(f'Copied {total - remaining} of {total} pages...')
try:
# existing DB
sqliteCon = sqlite3.connect('SQLite_Python.db')
# copy into this DB
backupCon = sqlite3.connect('Sqlite_backup.db')
with backupCon:
sqliteCon.backup(backupCon, pages=3, progress=progress)
print("backup successful")
except sqlite3.Error as error:
print("Error while taking backup: ", error)
finally:
if backupCon:
backupCon.close()
sqliteCon.close()
Output:
Copied 3 of 26 pages... Copied 6 of 26 pages... Copied 9 of 26 pages... Copied 12 of 26 pages... Copied 15 of 26 pages... Copied 18 of 26 pages... Copied 21 of 26 pages... Copied 24 of 26 pages... Copied 26 of 26 pages... backup successful

Note:
- After connecting to SQLite, We opened both the databases using two different connections.
- Next, we executed a
connection.backup()
method using a first connection instance. Also, we specified the number of database pages to copy in each iteration.