Getting Started 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 embedding within applications. 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.
Setting Up the Environment
Before we dive into working with SQLite databases, let’s set up our environment. First, create a dedicated virtual environment for your project and activate it using the built-in venv module:
$ python3 -m venv v1
$ source v1/bin/activate
Next, install the Faker library using pip to generate synthetic records:
$ pip3 install Faker
The sqlite3 module is built into the Python standard library, so you don’t need to install it. With Faker installed and a recent version of Python, you’re good to go!
Setting up the environment
Connecting to an SQLite Database
In the project directory, create a Python script and get started. To interact with the database, we need to establish a connection with the database. We can use the connect() function from the sqlite3 module to connect to a sample database example.db:
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’ll create a database cursor that will help us run queries. The cursor object has methods to execute queries and fetch the results of the query. It works very similarly to a file handler.
Creating Database Tables
Now let’s create a customers table with the required fields in the database. We’ll create a cursor object and run a CREATE TABLE statement to create the table:
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()
Performing CRUD Operations
Let’s perform some basic CRUD operations on the database table.
Inserting Records
Now we’ll insert some records into the customers table. We’ll use Faker to generate synthetic records. To keep the outputs readable, I’ve inserted only 10 records. But you may insert as many records as you’d like.
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()
Reading and Updating Records
Now that we’ve inserted records into the table, let’s run a query to read in all the records. Notice how we use the execute() method to run queries and the fetchall() method on the cursor to retrieve the results of the query.
Because we’ve stored the results of the previous query in all_customers, let’s also run an UPDATE query to update the num_orders corresponding to the id 1.
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()
Deleting Records
To delete a customer with a specific customer ID, let’s run 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()
print(f"Customer with ID {cid_to_delete} deleted successfully.")
cursor.close()
Filtering Records Using the WHERE Clause
Let’s say we want to fetch records of customers who’ve made fewer than 10 orders, say to run targeted campaigns and the like. For this, we run a SELECT query with the WHERE clause specifying the condition for filtering (in this case the number of orders).
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.")
