A guide to working with SQLite databases in Python

SQLite is a lightweight, serverless relational database management system (RDBMS) that is widely used due to its simplicity and ease of integration into applications.

So whether you're building a small application, managing data locally, or prototyping a project, SQLite provides a convenient solution for storing and querying structured data. In this tutorial, you will learn how to work with SQLite databases from Python using the built-in software. sqlite3 module.

In particular, you will learn how to connect to an SQLite database from Python and perform basic CRUD operations. Let us begin.

Set up the environment

As a first step, create a dedicated virtual environment for your project (in the project directory) and activate it. You can do it using the built-in venv module like this:

$ python3 -m venv v1
$ source v1/bin/activate

In this tutorial, we will use faker to generate synthetic records. So install it using pip:

The sqlite3 module is built into the Python standard library, so there is no need to install it. So if you've installed Faker and are using a recent version of Python, you're good to go!

Connecting to a SQLite database

In the project directory, create a Python script and get started. As a first step to interact with the database, we must establish a connection with the database.

To connect to a sample database example.db, you can use the connect() It works from the sqlite3 module like this:

conn = sqlite3.connect(‘example.db’)

If the database already exists, it connects to it. Otherwise, it creates the database in the working directory.

After connecting to the database, we will create a database cursor that will help us execute queries. The cursor object has methods to execute queries and obtain the results of the query. It works very similar to a file handler.

Database Cursor | Image by author

It is often useful to use the connection as a context manager in a with statement like this:

import sqlite3

# Connect to the db
with sqlite3.connect('example.db') as conn:
    # create db cursor
    # run queries
    # commit changes

This way you don't have to worry about closing the connection object. The connection is automatically closed when execution exits the with block. However, we will explicitly close cursor objects in this tutorial.

Create database tables

Now let's create a customers table with the required fields in the database. To do this, we first create a cursor object. We then execute a CREATE TABLE statement and pass the query string to the execute() method called on the cursor object:

import sqlite3

# Connect to the db
with sqlite3.connect('example.db') as conn:
	cursor = conn.cursor()

	# Create customers table
	cursor.execute('''
    	CREATE TABLE IF NOT EXISTS customers (
        	id INTEGER PRIMARY KEY,
        	first_name TEXT NOT NULL,
        	last_name TEXT NOT NULL,
        	email TEXT UNIQUE NOT NULL,
        	phone TEXT,
        	num_orders INTEGER
    	);
	''')
	conn.commit()
	print("Customers table created successfully.")
	cursor.close()

When you run the script, you should see the following output:

Output >>>
Customers table created successfully.

Perform CRUD operations

Let's perform some basic CRUD operations on the database table. If you want, you can create separate scripts for each operation.

Insert records

Now we will insert some records into the customers table. We will use Faker to generate synthetic logs. To make the results readable, I have inserted only 10 records. But you can insert as many records as you want.

import sqlite3
import random
from faker import Faker

# Initialize Faker object
fake = Faker()
Faker.seed(24)

# Connect to the db
with sqlite3.connect('example.db') as conn:
	cursor = conn.cursor()

	# Insert customer records
	num_records = 10
	for _ in range(num_records):
    	    first_name = fake.first_name()
    	    last_name = fake.last_name()
    	    email = fake.email()
    	    phone = fake.phone_number()
    	    num_orders = random.randint(0,100)

    	cursor.execute('''
        	INSERT INTO customers (first_name, last_name, email, phone, num_orders)
        	VALUES (?, ?, ?, ?, ?)
    	''', (first_name, last_name, email, phone, num_orders))
	print(f"{num_records} customer records inserted successfully.")
	conn.commit()
	cursor.close()

Notice how we use parameterized queries: instead of hardcoding the values ​​in the INSERT statement, we use ? placeholders and pass a tuple of values.

When you run the script you should see:

Output >>>
10 customer records inserted successfully.

Reading and updating records

Now that we have inserted records into the table, let's run a query to read all the records. Notice how we use the execute() method to execute queries and the fetchall() method on the cursor to retrieve the results of the query.

Since we have stored the results of the previous query in `all_customers`, let's also run an UPDATE query to update the num_orders corresponding to id 1. Here is the code snippet:

import sqlite3

# Connect to the db
with sqlite3.connect('example.db') as conn:
	cursor = conn.cursor()

	# Fetch and display all customers
	cursor.execute('SELECT id, first_name, last_name, email, num_orders FROM customers')
	all_customers = cursor.fetchall()
	print("All Customers:")
	for customer in all_customers:
    	    print(customer)

	# Update num_orders for a specific customer
	if all_customers:
    	    customer_id = all_customers(0)(0)  # Take the ID of the first customer
    	    new_num_orders = all_customers(0)(4) + 1  # Increment num_orders by 1
    	cursor.execute('''
        	UPDATE customers
        	SET num_orders = ?
        	WHERE id = ?
    	''', (new_num_orders, customer_id))
    	print(f"Orders updated for customer ID {customer_id}: now has {new_num_orders} orders.")
    
	conn.commit()
	cursor.close()

This outputs both the logs and the message after the update query:

Output >>>

All Customers:
(1, 'Jennifer', 'Franco', '[email protected]', 54)
(2, 'Grace', 'King', '[email protected]', 43)
(3, 'Lori', 'Braun', '[email protected]', 99)
(4, 'Wendy', 'Hubbard', '[email protected]', 11)
(5, 'Morgan', 'Wright', '[email protected]', 4)
(6, 'Juan', 'Watson', '[email protected]', 51)
(7, 'Randy', 'Smith', '[email protected]', 32)
(8, 'Jimmy', 'Johnson', '[email protected]', 64)
(9, 'Gina', 'Ellison', '[email protected]', 85)
(10, 'Cory', 'Joyce', '[email protected]', 41)
Orders updated for customer ID 1: now has 55 orders.

Delete records

To delete a customer with a specific customer ID, let's execute a DELETE statement as shown:

import sqlite3

# Specify the customer ID of the customer to delete
cid_to_delete = 3  

with sqlite3.connect('example.db') as conn:
	cursor = conn.cursor()

	# Execute DELETE statement to remove the customer with the specified ID
	cursor.execute('''
    	DELETE FROM customers
    	WHERE id = ?
	''', (cid_to_delete,))
        
	conn.commit()
        f"Customer with ID {cid_to_delete} deleted successfully.")
	cursor.close()          

This produces:

Customer with ID 3 deleted successfully.

Filter records using the WHERE clause

Image by author

Let's say we want to retrieve records from customers who have placed less than 10 orders, for example, to run specific campaigns and the like. To do this, we execute a SELECT query with the WHERE clause specifying the condition for filtering (in this case the number of orders). Here's how you can achieve this:

import sqlite3

# Define the threshold for the number of orders
order_threshold = 10

with sqlite3.connect('example.db') as conn:
	cursor = conn.cursor()

	# Fetch customers with less than 10 orders
	cursor.execute('''
    	SELECT id, first_name, last_name, email, num_orders
    	FROM customers
    	WHERE num_orders < ?
	''', (order_threshold,))

	# Fetch all matching customers
	filtered_customers = cursor.fetchall()

	# Display filtered customers
	if filtered_customers:
    	    print("Customers with less than 10 orders:")
    	    for customer in filtered_customers:
        	        print(customer)
	else:
    	    print("No customers found with less than 10 orders.")

And here is the result:

Output >>>
Customers with less than 10 orders:
(5, 'Morgan', 'Wright', '[email protected]', 4)

Ending

And that's a wrap! This was a guide to getting started using SQLite with Python. I hope you found it useful. You can find all the code. on GitHub. In the next part, we will look at how to run joins and subqueries, manage transactions in SQLite, and more. Until then, happy coding!

If you are interested in learning how database indexes work, read How to Speed ​​Up SQL Queries Using Indexes (Python Edition).

twitter.com/balawc27″ rel=”noopener”>Bala Priya C. is a developer and technical writer from India. He enjoys working at the intersection of mathematics, programming, data science, and content creation. His areas of interest and expertise include DevOps, data science, and natural language processing. He likes to read, write, code and drink coffee! Currently, he is working to learn and share his knowledge with the developer community by creating tutorials, how-to guides, opinion pieces, and more. Bala also creates engaging resource descriptions and coding tutorials.