How to Manage Data with SQLite in Python

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.

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:

• NULL: A NULL value
• INTEGER: An integer value
• REAL: A floating-point (decimal) value
• TEXT: A text value
• BLOB: A large binary object

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.

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;”)

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:

• We are using triple quotes to surround the SQL queries because triple quotes in Python lets you create string variables that span more than one line. This lets you format the SQL however you want.
• The IF NOT EXISTS part of the SQL queries lets us check if the table exists before creating it. If the table already exists, nothing will happen and the script will move to the next step.
• We use the cursor method execute() to run the SQL query.
• We use the commit method of the connection object to commit this transaction to the database. The changes won’t show in the database until commit is called, but you can also execute multiple queries before you call commit and only call it once at the end. This is useful if you don’t want any changes save if an error happens in any one of the queries. On an error, you can call conn.rollback(), which rolls back all changes to the last commit.
• We set an id column on each table that will work as the primary key of the table. SQLite also has the ability to autoincrement ids if we add the AUTOINCREMENT keyword, but it can affect performance and should be not be used if it’s not needed. We will be setting these ids manually.
• Because SQLite doesn’t allow null values when we define a column as INTEGER PRIMARY KEY, we will always need to set it when creating records or an error will be thrown.
• We added a userid column to the notes table to reference the user who is related to specific notes.

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:

• The executemany() method accepts two arguments: a SQL query with placeholders where the values will be inserted and a list of tuples containing the records being inserted.
• To create a single list of everyone’s notes, we concatenate the note lists using the + operator.
• This method of running SQL queries is safe from SQL injection attacks.

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’)]

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.

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.

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.