How To Use an SQLite Database With Python [Step-By-Step]

In this Python tutorial, we will learn how to connect to an SQLite database and how to perform CRUD (Create Read Update Delete) operations using Python.

To interact with an SQLite database in Python you have to connect to the database, create a cursor and use the execute() function on the cursor. This allows you to execute all the CRUD operations you need. Remember to commit() your changes and to close() your database connection once you are done with it.

We will go through each one of the steps mentioned above so you can use this knowledge in your Python applications.

Let’s learn how to use SQLite with Python!

How to Connect to an SQLite3 Database Using Python

The first step to connect to an SQLite database in Python is to import the module sqlite3 which is part of Python since version 2.5 so you do not need to install it if you are using Python 3 (and you should).

This module provides an interface for interacting with SQLite databases that is compliant with the Database API Specification 2.0.

import sqlite3

To read data from an SQLite database you have to connect to it using the connect() function of the sqlite3 module. You have to pass the filename of the SQLite database to the connect() function.

The filename of the database we will use in this example is flight_booking.db.

So far we haven’t created a database file, so what happens if we execute the following Python statement?

conn = sqlite3.connect('flight_booking.db')

When we execute the code to connect to the SQLite database, Python automatically creates the database file in the local directory if it doesn’t already exist.

From the shell below you can see that an empty SQLite database file called flight_booking.db has been created.

(python-env) # ls -ltr flight_booking.db 
-rw-r--r--  1 codefathertech  codefathertech  0 Mar 18 09:52 flight_booking.db

What’s next?

How to Create a Table in SQLite Using Python

The database we created is empty so we will create a table in that database.

To execute SQL statements with SQLite and retrieve the results of the statements using Python you have to create a cursor object. You can create the cursor once you have a connection object using the cursor() factory.

cursor = conn.cursor()

Using Python’s dir() function let’s see what are the methods of the cursor object.

print(dir(cursor))

[output]
['__class__', '__delattr__', '__dir__', '__doc__', '__eq__', '__format__', '__ge__', '__getattribute__', '__gt__', '__hash__', '__init__', '__init_subclass__', '__iter__', '__le__', '__lt__', '__ne__', '__new__', '__next__', '__reduce__', '__reduce_ex__', '__repr__', '__setattr__', '__sizeof__', '__str__', '__subclasshook__', 'arraysize', 'close', 'connection', 'description', 'execute', 'executemany', 'executescript', 'fetchall', 'fetchmany', 'fetchone', 'lastrowid', 'row_factory', 'rowcount', 'setinputsizes', 'setoutputsize']

You can see that the cursor object provides the execute() method which is the one we will use to execute an SQL statement to create the first table in our database.

Let’s run the first SQL command against our SQLite database.

Pass a “CREATE TABLE” SQL statement to cursor.execute(). Assume we are creating a Python application to book flights and we need a table called flights.

cursor.execute('''
    CREATE TABLE flights(
        flight_number TEXT PRIMARY KEY NOT NULL,
        origin TEXT NOT NULL,
        destination TEXT NOT NULL
    )
''')

As you can see we are setting the flight_number as the primary key of the table.

The sqlite Python module provides the function commit() that is needed to commit pending transactions to the database.

conn.commit()

After executing the CREATE TABLE statement and the commit, we want to verify that the table exists in the database.

To explore the structure of an SQLite database you can use the sqlite3 client. The command to access the database is “sqlite3 <database_filename>”. To see the tables in the database use the command “.tables”.

(python-env) # sqlite3 flight_booking.db
SQLite version 3.32.3 2020-06-18 14:16:19
Enter ".help" for usage hints.
sqlite> .tables
flights
sqlite> .schema flights
CREATE TABLE flights(
        flight_number TEXT PRIMARY KEY NOT NULL,
        origin TEXT NOT NULL,
        destination TEXT NOT NULL
    );

The table exists!

Let’s try to execute again the Python code we have written so far. When you execute the code you will see the following error:

Traceback (most recent call last):
  File "sqlite_tutorial.py", line 5, in <module>
    cursor.execute('''
sqlite3.OperationalError: table flights already exists

The error “table already exists” is caused by the fact that we are trying to recreate a table that is already present in the database.

To avoid this error you can update the “CREATE TABLE” SQL statement as shown below.

cursor.execute('''
    CREATE TABLE IF NOT EXISTS flights(
        flight_number TEXT PRIMARY KEY NOT NULL,
        origin TEXT NOT NULL,
        destination TEXT NOT NULL
    )
''')

After adding “IF NOT EXISTS” to the “CREATE TABLE” SQL statement, execute the Python code again and confirm that the error we have seen before doesn’t occur again.

How to Insert A Single Record Into an SQLite Database With Python

Now that we have a table in the database, the next step is to insert data into the table.

To insert data into an SQLite table you use cursor.execute() and pass to it an “INSERT INTO” SQL statement. You can insert a single record or multiple records into a table.

Below you can see how to insert a single record into the table we have created.

cursor.execute("INSERT INTO flights (flight_number, origin, destination) VALUES (?, ?, ?)", ("AZ12345", "Rome", "Paris"))
conn.commit()

Use sqlite3 to confirm we can see the record in the table.

sqlite> .header ON
sqlite> select * from flights;
flight_number|origin|destination
AZ12345|Rome|Paris

We can see the new record. We use “.header ON” to also display the column names as part of the SELECT SQL query.

If you try to execute the “INSERT INTO” statement above you get the following error.

Traceback (most recent call last):
  File "sqlite_tutorial.py", line 14, in <module>
    cursor.execute("INSERT INTO flights (flight_number, origin, destination) VALUES (?, ?, ?)", ("AZ12345", "Rome", "Paris"))
sqlite3.IntegrityError: UNIQUE constraint failed: flights.flight_number

Why do you get this error?

This error is caused by the fact that we have set the flight_number column as the primary key and hence it has to be unique. That’s why SQLite prevents us from inserting another record with the same flight_number.

That’s a good way to prevent errors that otherwise could be caused by having multiple records with the same flight_number.

How to Insert Multiple Records Into an SQLite Database With Python

If you are just starting to create your Python application you might want to populate the database with enough data to do a good level of testing while developing the code.

This means you will have to insert multiple records into the table we have created.

To insert multiple records into an SQLite table using Python you can define a list of tuples where every tuple is the data for a single database record. Then use the function cursor.executemany() and pass the list of tuples to it.

Below we define the list of tuples data and then we use it to insert multiple rows into the table.

data = [
    ("BD36457", "New York", "Los Angeles"),
    ("SZ37486", "Milan", "London"),
    ("BT34567", "Oslo", "Copenhagen"),
    ("RS25690", "Madrid", "Turin")
]

cursor.executemany("INSERT INTO flights (flight_number, origin, destination) VALUES (?, ?, ?)", data)
conn.commit()

After inserting the data you can see the data stored in SQLite. The new rows are visible in the table using the sqlite3 client.

sqlite> select * from flights;
AZ12345|Rome|Paris
BD36457|New York|Los Angeles
SZ37486|Milan|London
BT34567|Oslo|Copenhagen
RS25690|Madrid|Turin

Looks good, we have inserted multiple rows into our SQLite database table.

How to Create a Query to Read Data From SQLite in Python

Now that we have inserted data into an SQLite database using Python, the next step is to learn how to access data.

How do we fetch data from an SQLite database and convert it into data structures we can use in a Python program?

To read database rows from an SQLite table using Python you can use the function cursor.execute() to execute a “SELECT FROM” SQL statement. Then use the function cursor.fetchall() to get back the data as a list.

cursor.execute("SELECT * FROM flights")
flights = cursor.fetchall()

After executing the “SELECT FROM” SQL statement we use cursor.fetchall() to get the data back as a list. With a print() statement, we can confirm that the data type received is a list.

print(type(flights))

[output]
<class 'list'>

And now let’s have a look at the content of this Python list.

print("Flights: ", flights)

[output]
Flights:  [('AZ12345', 'Rome', 'Paris'), ('BD36457', 'New York', 'Los Angeles'), ('SZ37486', 'Milan', 'London'), ('BT34567', 'Oslo', 'Copenhagen'), ('RS25690', 'Madrid', 'Turin')]

As you can see we got back a list of tuples.

Nice one, now we can use it the way we want in our Python application.

And now, let’s apply a condition to our SELECT statement.

cursor.execute("SELECT * FROM flights WHERE origin = ?", ("Rome",))
flights = cursor.fetchall()
print("Flights from Rome: ", flights)

Here is the data we get back from the SELECT.

Flights:  [('AZ12345', 'Rome', 'Paris')]

It’s correct!

How to Update a Row in an SQLite Table using Python

Let’s continue exploring the sqlite library.

We have created data in our database table, now let’s see how to update that data.

Assume the data for the flight from New York is incorrect and that the flight is supposed to have Boston as the origin.

Let’s fetch the data for the flight we want to update.

cursor.execute("SELECT * FROM flights WHERE origin = ?", ("New York",))
flights = cursor.fetchall()
print("Flights from New York: ", flights)

[output]
Flights from New York:  [('BD36457', 'New York', 'Los Angeles')]

Now execute an UPDATE SQL statement to change the origin of this flight from New York to Boston.

cursor.execute("UPDATE flights SET origin = ? WHERE flight_number = ?", ("Boston", "BD36457"))
conn.commit()

And then confirm the record has been updated by fetching flights from New York and Boston.

cursor.execute("SELECT * FROM flights WHERE origin = ?", ("New York",))
flights_from_new_york = cursor.fetchall()

cursor.execute("SELECT * FROM flights WHERE origin = ?", ("Boston",))
flights_from_boston = cursor.fetchall()

print("Flights from New York: ", flights_from_new_york)
print("Flights from New York: ", flights_from_boston)

The output is:

Flights from New York:  []
Flights from New York:  [('BD36457', 'Boston', 'Los Angeles')]

This confirms that we have updated the database record correctly.

How to Delete an SQLite Record From a Table in Python

And what if one of the records we have inserted into our table is incorrect?

How can we delete that SQLite record?

In a similar way to what we have done before, we can pass a DELETE SQL statement to cursor.execute().

cursor.execute("DELETE FROM flights WHERE flight_number = ?", ("BT34567",))
conn.commit()

After deleting the record above based on the flight_number, we can use a SELECT statement to verify that the record is not present anymore in the table.

cursor.execute("SELECT * FROM flights")
flights = cursor.fetchall()
print("Flights: ", flights)

[output]
Flights:  [('AZ12345', 'Rome', 'Paris'), ('BD36457', 'Boston', 'Los Angeles'), ('SZ37486', 'Milan', 'London'), ('RS25690', 'Madrid', 'Turin')]

The database record has been deleted successfully.

How Do I Close An SQLite Connection with Python?

To close the connection to your SQLite database you can call the following Python statement that uses the connection close() function. The function close() is not called automatically, you have to remember to call it manually.

conn.close()

It’s important to remember to commit all your changes using the function commit() before closing the database connection. If you don’t do that you will lose pending changes.

This concludes our tutorial about the Python sqlite3 module.

Wrapping Up This Python SQLite Tutorial

After going through this Python sqlite tutorial you know how to work with the SQLite database and to execute CRUD operations to:

• Insert a record into a table (Create).
• Fetch records from a table (Read).
• Update a record in a table (Update).
• Delete a record from a table (Delete).

Now it’s time to use this knowledge to create a Python application that uses backend data storage.

Related article: one of the Python types we have talked about in this tutorial is the tuple. Get familiar with this data type by going through this CodeFatherTech tutorial about Python tuples.