SQLite is a small, fast, full-featured relational database engine that is the most used relational database system in the world that comes with Python in the form of the Python SQLite. You don’t need a database server or a connection to a remote database to use SQLite. SQLite uses a simple file to store data, or you could keep the whole database in memory.

SQLite comes bundled with Python, which means you don’t have to install third-party modules to add database capabilities to your Python program, just use the built-in database engine. In this article, we’ll look at the Python SQLite3 module and show you how to create, connect to, and manipulate data in an SQLite database from Python.

Person in front of computer in office setting

SQLite data types

SQLite uses a relational database structure. This means you will use SQL to add, update, and access the data in the database. The data is stored in tables that consist of columns, and the data in these columns must be of a consistent type. Here are the data types that SQLite supports:

If you’re familiar with other relational databases, then you may notice that the data types in SQLite are limited. For example, there is no date or varchar type. This may mean SQLite won’t fit some database needs, but it’s still useful for many applications.

2022 Complete Python Bootcamp From Zero to Hero in Python

Last Updated March 2021

  • 155 lectures
  • All Levels
4.6 (422,586)

Learn Python like a Professional Start from the basics and go all the way to creating your own applications and games | By Jose Portilla

Explore Course

Getting started with Python SQLite

While SQLite is built into Python, you still have to import the sqlite3 module into your Python file, and here’s how you would do that:

import sqlite3

Creating and connecting to a SQLite database

With Python SQLite, creating and connecting to a database is one and the same. If the database file exists, Python will connect to it. If it doesn’t exist, then it will create the database file and connect to it.

Here is an example:

# Import the sqlite3 module
import sqlite3

# Create a database and connect to it with the connect function
# The conn variable will be used to interact with the database
conn = sqlite3.connect(‘db.sqlite3’)

If you run the code above, it will create a database file in the folder you run it in. You can also connect to an SQLite database by using the full path to the file if your database is not located in the same folder, like this:

conn = sqlite3.connect(‘/some/other/folder/db.sqlite3’)

We set this connection object to the variable conn, which we will be using in the remaining steps.

If you don’t need a static database file, you can also create and use an SQL database that exists only in memory and will be gone once the program stops. Here is how you would create and connect to an in-memory SQLite database:

conn = sqlite3.connect(“:memory:”)

Creating a cursor object with Python SQLite

Once finished creating the connection object, you will now need to create a cursor object to query the database with SQL. Here is the full script:

# Import the sqlite3 module
import sqlite3

# Create the connection
conn = sqlite3.connect(‘db.sqlite3’)

# Create the cursor
cursor = conn.cursor()

Now that we have a cursor object, we can use the execute method of the object that executes the SQL on the database. Make sure to wrap the SQL query in quotes. You can use single or double quotes.

cursor.execute(“SELECT * FROM my_table;”)

Top courses in Python

Python for Data Science and Machine Learning Bootcamp
Jose Portilla
4.7 (116,723)
Bestseller
Machine Learning A-Z™: Hands-On Python & R In Data Science
Kirill Eremenko, Hadelin de Ponteves, Ligency I Team, Ligency Team
4.6 (159,895)
Programming Numerical Methods in Python
Murad Elarbi
4.6 (670)
Bestseller

More Python Courses

Creating a table in a SQLite database

Now that we have a cursor object and can execute SQL, we can create tables to hold our data. For this example, we will be creating two tables so that we can use JOIN in our SQL queries later on. Here is how you create a table with Python SQLite:

# Create the users table
cursor.execute(“””CREATE TABLE IF NOT EXISTS users(
  id INTEGER PRIMARY KEY,
  firstname TEXT,
  lastname TEXT);
“””)
conn.commit()

# Create the notes table
cursor.execute(“””CREATE TABLE IF NOT EXISTS notes(
  id INTEGER PRIMARY KEY,
  userid INTEGER,
  note TEXT);
“””)
conn.commit()

Here are some things you should notice about the code above:

Adding data to SQLite tables

Now that we have some tables to work with, we can add data to the tables. We will continue to use the execute method of the cursor object to execute the SQL queries and the commit method of the connection objects to commit the changes to the database. If we simply want to add one user who has one note, we can use code like this:

# Add a single user
cursor.execute(“””INSERT INTO users(id, firstname, lastname)
  VALUES(1, ‘John’, ‘Doe’);
“””)
conn.commit()

# Add a single note
cursor.execute(“””INSERT INTO notes(id, userid, note)
  VALUES(1, 1, ‘This is a note’);
  “””)
conn.commit()

If we want to add multiple records at once, this is an easier way to do it:

# Multiple users
all_users = [(2, ‘Bob’, ‘Doe’), (3, ‘Jane’, ‘Doe’), (4, ‘Jack’, ‘Doe’)];

# Add multiple users
cursor.executemany(“””INSERT INTO users(id, firstname, lastname)
  VALUES(?, ?, ?);”””, all_users)
conn.commit()

# Multiple notes
bobs_notes = [(2, 2, ‘This is a second note’), (3, 2, ‘This is a third note’)];
janes_notes = [(4, 3, ‘This is a fourth note’), (5, 3, ‘This is a fifth note’)];
jacks_notes = [(6, 4, ‘This is a sixth note’), (7, 4, ‘This is a seventh note’)];
all_notes = bobs_notes + janes_notes + jacks_notes;

# Add multiple notes
cursor.executemany(“””INSERT INTO notes(id, userid, note)
  VALUES(?, ?, ?);”””, all_notes)
conn.commit()

Here is what you need to know about the code above:

It’s also good to note that you can use a similar method for adding records with the execute() method except it only will insert one record at a time. Here is an example:

# Add a single user
user = (1, ‘John’, ‘Doe’)
cursor.execute(“””INSERT INTO users(id, firstname, lastname)
  VALUES(?, ?, ?);
“””, user)
conn.commit()

We will be using this method of executing queries from now on, since it’s a good practice to prevent SQL injection.

Reading data from SQLite

You can fetch data a few different ways using Python SQLite.

Using fetchall()

The fetchall() method returns every record that results from your SQL query as a list of tuples. Here’s an example that queries all the users:

# Fetches all users
users = cursor.execute(“””SELECT * FROM users”””).fetchall()
print(users)

And here are the results of this code:

[(1, ‘John’, ‘Doe’), (2, ‘Bob’, ‘Doe’), (3, ‘Jane’, ‘Doe’), (4, ‘Jack’, ‘Doe’)]

We can also limit an SQL query with a WHERE clause to get only the text of Jack’s notes and return all those records.

# Get Jack’s notes
jacks_notes = cursor.execute(“””SELECT note FROM notes WHERE userid = ?”””, (4,)).fetchall()
print(jacks_notes)

Here is the result of executing this code:

[(‘This is a sixth note’,), (‘This is a seventh note’,)]

Using fetchmany()

The fetchmany() method is similar to the fetchall() method but accepts an integer argument that designates the count of records you want to fetch. Here is the same users query where we only fetch the first two user records returned from this SQL statement:

# Fetches first two users
users = cursor.execute(“””SELECT * FROM users”””).fetchmany(2)
print(users)

And here are the records this code returned:

[(1, ‘John’, ‘Doe’), (2, ‘Bob’, ‘Doe’)]

Using fetchone()

The Python SQLite fetching() method is similar to using SELECT TOP 1 in Microsoft SQL Server and will return the first record from a query. Here is an example:

# Fetch one user
user = cursor.execute(“””SELECT * FROM users”””).fetchone()
print(user)

And here is what this script prints:

(1, ‘John’, ‘Doe

Also note that when you use fetchone() you get one tuple, not a list of one tuple.

Joining tables

You don’t have to stick to simple SQL queries in SQLite. After all, the point of a relational database is relating different sets of data to each other. We created a users table and then referenced each user with the userid in the notes table in order to relate notes to the user that owns them. Now it’s time to put that relationship to work.

Let’s say we want every note from the notes table along with the first and last name of the user that owns the note. Here is how we could retrieve that data from the database:

# Get notes with the user’s name
notes = cursor.execute(“””
  SELECT u.firstname, u.lastname, n.note FROM users AS u
  INNER JOIN notes AS n
  ON u.id = n.userid”””).fetchall()
print(notes)

Notice that we used the AS keyword in our query. This sets an alias for the name of the table in SQLite, so we don’t have to type the whole name everywhere we use it. Here is the result of this query:

[(‘John’, ‘Doe’, ‘This is a note’), (‘Bob’, ‘Doe’, ‘This is a second note’), (‘Bob’, ‘Doe’, ‘This is a third note’), (‘Jane’, ‘Doe’, ‘This is a fourth note’), (‘Jane’, ‘Doe’, ‘This is a fifth note’), (‘Jack’, ‘Doe’, ‘This is a seventh note’)]

Featured courses in Web Development

More Web Development Courses

Updating data in an SQLite database

You can also update the data in an SQLite database by using an update statement in your SQL query. In this example, Jack wants to change the content of one of his notes:

# Update Jack’s note
updated_note = ‘This is an updated note’
note_id = 6
cursor.execute(“””UPDATE notes SET note = ? WHERE id = ?;”””, (updated_note, note_id))
conn.commit()

And if we take a look at the database, we can see the note has been updated.

Updating data in an SQLite database

Deleting data with Python SQLite

To delete data in your database, the process is the same. Just add a delete statement to your SQL query, execute the query, and commit the changes. Here is an example:

# Delete one of Jack’s notes
note_id = 6
cursor.execute(“””DELETE FROM notes WHERE id = ?;”””, (note_id, ))
conn.commit()

When we take a look at the database, the note with an id of 6 will be gone.

Deleting data with Python SQLite

Closing the database connection

You may have noticed that we have dealt with two Python SQLite objects in our code over and over: a connection and a cursor. It’s good practice to close these objects when we no longer need them, and here is an easy way to do it automatically using a with statement and the closing method from the Python contextlib:

from contextlib import closing

with closing(sqlite3.connect(‘db.sqlite3’)) as conn:
    with closing(conn.cursor()) as cursor:
      # Here is where we execute all of our queries
        users = cursor.execute(‘SELECT * from users’).fetchall()
        print(users)

# Once we are out of the with block both the cursor and connection will be closed

The SQLite module comes built-in to Python and is a powerful way to manipulate, store, and retrieve data for your Python applications. To learn more about Python and what it can do, read What is Python. To compare Python to Perl, another programming language used for similar purposes, check out Perl vs Python. And if you are ready to build more Python skills, Udemy has Python courses that will let you learn at your own pace online.

Page Last Updated: March 2022