Querying SQLite in Python: A Beginner's Guide

SQLite is a lightweight, file-based database engine that works perfectly with Python. It requires no separate server setup. This makes it ideal for small to medium applications.

In this guide, we'll explore how to query SQLite databases in Python. We'll cover connecting to databases, executing queries, and retrieving results.

Getting Started with SQLite in Python

Python includes the built-in sqlite3 module. No additional installation is required for basic usage.

Let's start by creating a simple database connection:

import sqlite3

# Connect to SQLite database (or create it)
conn = sqlite3.connect('example.db')

# Create a cursor object
cursor = conn.cursor()

print("Connection established successfully")

Connection established successfully

The connect() function creates a connection to the database file. If the file doesn't exist, SQLite creates it automatically.

Creating Tables and Inserting Data

Before querying data, we need a table with some records. Here's how to create a table and insert sample data:

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Create table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        age INTEGER,
        city TEXT
    )
''')

# Insert sample data
users_data = [
    (1, 'Alice', 25, 'New York'),
    (2, 'Bob', 30, 'Los Angeles'),
    (3, 'Charlie', 35, 'Chicago'),
    (4, 'Diana', 28, 'Houston')
]

cursor.executemany('INSERT INTO users VALUES (?, ?, ?, ?)', users_data)

# Commit changes
conn.commit()
print("Table created and data inserted successfully")

Table created and data inserted successfully

Executing Basic SELECT Queries

The most common operation is retrieving data using SELECT statements. Let's fetch all records from our users table:

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Execute SELECT query
cursor.execute('SELECT * FROM users')

# Fetch all results
results = cursor.fetchall()

# Display results
for row in results:
    print(row)

(1, 'Alice', 25, 'New York')
(2, 'Bob', 30, 'Los Angeles')
(3, 'Charlie', 35, 'Chicago')
(4, 'Diana', 28, 'Houston')

The fetchall() method retrieves all matching rows. Each row comes back as a tuple.

Filtering Results with WHERE Clause

Often you need specific records. Use WHERE clauses to filter data:

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Query with WHERE clause
cursor.execute('SELECT name, age FROM users WHERE age > 28')

results = cursor.fetchall()

for row in results:
    print(f"Name: {row[0]}, Age: {row[1]}")

Name: Bob, Age: 30
Name: Charlie, Age: 35
Name: Diana, Age: 28

Using Parameterized Queries

Never concatenate user input directly into SQL strings. This creates SQL injection vulnerabilities.

Use parameterized queries instead:

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Safe parameterized query
min_age = 30
cursor.execute('SELECT * FROM users WHERE age >= ?', (min_age,))

results = cursor.fetchall()

for row in results:
    print(row)

(2, 'Bob', 30, 'Los Angeles')
(3, 'Charlie', 35, 'Chicago')

Question marks (?) act as placeholders. The actual values are passed as a tuple in the second parameter.

Retrieving Single Rows

When you expect only one result, use the fetchone() method:

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Find user by ID
user_id = 2
cursor.execute('SELECT name, city FROM users WHERE id = ?', (user_id,))

result = cursor.fetchone()

if result:
    print(f"User found: {result[0]} from {result[1]}")
else:
    print("User not found")

User found: Bob from Los Angeles

For more details on retrieving single rows, check our comprehensive fetchone guide.

Sorting Results with ORDER BY

You can sort query results using ORDER BY:

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Sort by age in descending order
cursor.execute('SELECT name, age FROM users ORDER BY age DESC')

results = cursor.fetchall()

for row in results:
    print(f"{row[0]}: {row[1]} years old")

Charlie: 35 years old
Bob: 30 years old
Diana: 28 years old
Alice: 25 years old

Aggregation Functions

SQLite supports aggregation functions like COUNT, AVG, and SUM:

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Count total users
cursor.execute('SELECT COUNT(*) FROM users')
total_users = cursor.fetchone()[0]
print(f"Total users: {total_users}")

# Calculate average age
cursor.execute('SELECT AVG(age) FROM users')
avg_age = cursor.fetchone()[0]
print(f"Average age: {avg_age:.1f}")

# Sum of ages (just for demonstration)
cursor.execute('SELECT SUM(age) FROM users')
total_age = cursor.fetchone()[0]
print(f"Sum of ages: {total_age}")

Total users: 4
Average age: 29.5
Sum of ages: 118

Joining Tables

Real-world databases often contain multiple related tables. Here's how to join them:

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Create orders table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS orders (
        order_id INTEGER PRIMARY KEY,
        user_id INTEGER,
        product TEXT,
        amount REAL,
        FOREIGN KEY (user_id) REFERENCES users(id)
    )
''')

# Insert order data
orders_data = [
    (101, 1, 'Laptop', 999.99),
    (102, 1, 'Mouse', 25.50),
    (103, 2, 'Keyboard', 75.00),
    (104, 3, 'Monitor', 299.99)
]

cursor.executemany('INSERT INTO orders VALUES (?, ?, ?, ?)', orders_data)
conn.commit()

# Join users and orders
cursor.execute('''
    SELECT u.name, o.product, o.amount 
    FROM users u 
    JOIN orders o ON u.id = o.user_id
    ORDER BY u.name
''')

results = cursor.fetchall()

for row in results:
    print(f"{row[0]} ordered {row[1]} for ${row[2]}")

Alice ordered Laptop for $999.99
Alice ordered Mouse for $25.5
Bob ordered Keyboard for $75.0
Charlie ordered Monitor for $299.99

Updating Records

To modify existing data, use UPDATE statements:

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Update user's age
new_age = 26
user_name = 'Alice'
cursor.execute('UPDATE users SET age = ? WHERE name = ?', (new_age, user_name))

# Verify update
cursor.execute('SELECT name, age FROM users WHERE name = ?', (user_name,))
result = cursor.fetchone()
print(f"Updated record: {result}")

conn.commit()

Updated record: ('Alice', 26)

Deleting Records

Remove records carefully using DELETE statements:

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Delete specific user
user_id_to_delete = 4
cursor.execute('DELETE FROM users WHERE id = ?', (user_id_to_delete,))

# Verify deletion
cursor.execute('SELECT COUNT(*) FROM users')
remaining_count = cursor.fetchone()[0]
print(f"Remaining users: {remaining_count}")

conn.commit()

Remaining users: 3

Proper Resource Management

Always close your database connections. Use context managers for automatic cleanup:

import sqlite3

# Using context manager for automatic cleanup
with sqlite3.connect('example.db') as conn:
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM users')
    results = cursor.fetchall()
    
    for row in results:
        print(row)

# Connection automatically closed after 'with' block
print("Connection closed automatically")

(1, 'Alice', 26, 'New York')
(2, 'Bob', 30, 'Los Angeles')
(3, 'Charlie', 35, 'Chicago')
Connection closed automatically

Conclusion

Querying SQLite databases in Python is straightforward once you understand the core concepts. Key takeaways include:

• Use the built-in sqlite3 module
• Always use parameterized queries to prevent SQL injection
• Close connections properly using context managers
• Leverage aggregation functions for data analysis
• Use JOINs to work with related data across tables

For Flask web applications, SQLite integrates seamlessly. Learn more in our Flask SQLite integration guide.

Practice these techniques with different datasets. Soon, querying SQLite in Python will become second nature.