Perform CRUD with SQLite

Your web app needs persistent data, but wiring up a full database server can feel like overkill. You've built endpoints that return hardcoded JSON, and now you're staring at the requirement: store and retrieve real user data. Enter SQLite — the embedded database that's already built into Python. In this lesson, you'll learn to perform CRUD operations with SQLite — Create, Read, Update, Delete — using the sqlite3 module. You'll move from static responses to a dynamic API backed by a real database, without installing a single dependency.

The problem this lesson solves

Every meaningful web application needs persistence. Data must survive server restarts, be queryable, and support concurrent access. Setting up PostgreSQL or MySQL for a small project can be heavy: you need a server process, credentials, and configuration. For many applications — prototypes, internal tools, or even production apps with modest traffic — that's unnecessary complexity.

SQLite solves this by giving you a full-featured SQL database contained in a single file. No server to run, no configuration to manage. Python's standard library includes sqlite3, so you can start persisting data in minutes. However, naive use of SQLite in a web context comes with pitfalls: connection management, injection vulnerabilities, and transaction mishandling. This lesson guides you through perform CRUD operations with SQLite correctly, so your API is both functional and robust.

By the end, you'll be able to:

• Create a database schema and connect to it from Python
• Insert (Create) and query (Read) data safely
• Update existing records and delete them when needed
• Avoid common SQLite mistakes that break web apps in production

Core concept / mental model

Think of SQLite as a library rather than a server. When you access a regular database, you're a client talking to a remote process. With SQLite, your Python program is the database engine. The entire database lives in a .db file on your disk, and sqlite3 gives you a connection to that file.

Everything in SQLite revolves around these core objects:

• Database file: The single file that stores all tables and data.
• Connection: The object that represents your session with the database. You create it with sqlite3.connect().
• Cursor: A handle used to execute SQL statements and fetch results. You get it from the connection.
• Transactions: SQLite groups operations into transactions for atomicity — either all changes happen, or none do.

Mental model: The database file is your persistent memory. The connection is your current conversation with that memory. Each SQL statement is a question or command you send through the conversation, and the cursor carries back the answers.

The four CRUD operations map cleanly to SQL commands:

But there's a catch: a single execute() call is not enough. You must commit changes to make them permanent, and you must close connections to free resources. Forgetting either leads to silent data loss or locked files.

How it works step by step

Every CRUD operation follows the same four-step pattern:

1. Connect to the database using sqlite3.connect(). This creates the file if it doesn't exist.
2. Create a cursor with conn.cursor().
3. Execute a SQL statement with cursor.execute(). Use parameterized queries to prevent SQL injection.
4. Commit and close — commit for write operations, close to release the connection.

Let's break down each CRUD operation.

Create: Insert data

To add a new record, use an INSERT statement. Always use placeholders (?) to pass values safely:

import sqlite3

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

cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE NOT NULL
    )
''')

cursor.execute(
    'INSERT INTO users (name, email) VALUES (?, ?)',
    ('Alice', 'alice@example.com')
)

conn.commit()
print('User inserted with id:', cursor.lastrowid)
conn.close()

Output:

User inserted with id: 1

Read: Query data

Retrieving data uses SELECT. You can fetch all rows at once with fetchall() or one row at a time with fetchone():

import sqlite3

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

cursor.execute('SELECT * FROM users')
users = cursor.fetchall()
for user in users:
    print(user)

conn.close()

Output (assuming the previous insert):

(1, 'Alice', 'alice@example.com')

Update: Modify data

Updates use UPDATE with a WHERE clause to target specific rows. Without WHERE, you'd overwrite every row!

import sqlite3

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

cursor.execute(
    'UPDATE users SET email = ? WHERE name = ?',
    ('alice@newdomain.com', 'Alice')
)

if cursor.rowcount == 0:
    print('No rows updated')
else:
    print('Updated', cursor.rowcount, 'row(s)')
conn.commit()
conn.close()

Delete: Remove data

DELETE also relies on WHERE to avoid wiping the entire table:

import sqlite3

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

cursor.execute('DELETE FROM users WHERE id = ?', (1,))
print('Deleted', cursor.rowcount, 'row(s)')
conn.commit()
conn.close()

Hands-on walkthrough

Now let's apply CRUD in a realistic web API context. We'll build a simple user management script with functions for each operation, and we'll make sure connections are always properly closed using a context manager.

Step 1: Define the database helper functions

import sqlite3
from contextlib import closing

DATABASE = 'app.db'

def get_connection():
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row  # access columns by name
    return conn

def create_user(name, email):
    with closing(get_connection()) as conn, conn:
        cursor = conn.cursor()
        cursor.execute(
            'INSERT INTO users (name, email) VALUES (?, ?)',
            (name, email)
        )
        return cursor.lastrowid

def list_users():
    with closing(get_connection()) as conn:
        cursor = conn.cursor()
        cursor.execute('SELECT * FROM users')
        return [dict(row) for row in cursor.fetchall()]

def update_email(user_id, new_email):
    with closing(get_connection()) as conn, conn:
        cursor = conn.cursor()
        cursor.execute(
            'UPDATE users SET email = ? WHERE id = ?',
            (new_email, user_id)
        )
        return cursor.rowcount

def delete_user(user_id):
    with closing(get_connection()) as conn, conn:
        cursor = conn.cursor()
        cursor.execute('DELETE FROM users WHERE id = ?', (user_id,))
        return cursor.rowcount

Pro tip: with conn: automatically commits if the block succeeds and rolls back on exception. Combined with closing(), you handle both commit and close cleanly.

Step 2: Demo the full CRUD cycle

# demo.py
if __name__ == '__main__':
    # Create
    new_id = create_user('Bob', 'bob@example.com')
    print(f'Created user with id {new_id}')

    # Read
    print('Users:', list_users())

    # Update
    rows = update_email(new_id, 'bob@newdomain.com')
    print(f'Updated {rows} row(s)')

    # Delete
    rows = delete_user(new_id)
    print(f'Deleted {rows} row(s)')

    print('Final users:', list_users())

Output:

Created user with id 1
Users: [{'id': 1, 'name': 'Bob', 'email': 'bob@example.com'}]
Updated 1 row(s)
Deleted 1 row(s)
Final users: []

Step 3: Handle duplicate emails gracefully

In a real API you'd catch sqlite3.IntegrityError to return a 409 Conflict:

try:
    create_user('Alice', 'alice@example.com')
except sqlite3.IntegrityError as e:
    print('Error:', e)  # UNIQUE constraint failed: users.email

Compare options / when to choose what

SQLite vs. client-server databases is a common early decision. Here's a practical comparison:

When to choose SQLite: - Early development or internal tools - Applications with low write concurrency - Embedded or mobile apps

When to choose a server database: - Multiple processes writing simultaneously - Need for user permissions and network access - Expected to scale beyond single-server storage

Alternatives within Python: you could use psycopg for PostgreSQL or SQLAlchemy as an ORM, but the sqlite3 module is always available and perfect for learning CRUD fundamentals.

Troubleshooting & edge cases

Even with SQLite, things can go wrong. Here are common pitfalls and their fixes.

sqlite3.OperationalError: no such table

You're querying a table that doesn't exist. Usually the table creation script ran on a different database file, or you forgot to run it. Verify the file path — it may have been created in a different working directory.

sqlite3.IntegrityError: UNIQUE constraint failed

You attempted to insert a duplicate value in a column with UNIQUE or PRIMARY KEY. This is common with email or username fields. Catch the error and handle it appropriately (e.g., return a 409 response).

sqlite3.ProgrammingError: Cannot operate on a closed database

You closed the connection too early, often because you closed within a loop or a try block before fetching results. Always fetch all needed rows before closing.

Changes not persisting

You executed INSERT or UPDATE but the data vanishes after restart. You forgot to call conn.commit(). Always commit for write operations, or use with conn: to commit automatically.

database is locked

SQLite locks the entire database for writes. This happens if another connection has an open transaction. Shorten transactions, use with conn: to auto-commit, and consider a timeout: sqlite3.connect(DB, timeout=10).

What you learned & what's next

You now know how to perform CRUD operations with SQLite — from connecting to a file-based database through creating, reading, updating, and deleting records. You learned the importance of parameterized queries to prevent SQL injection, and you know how to handle common errors like integrity violations and lock conflicts.

In the next lesson, you'll build on this foundation to integrate SQLite into a FastAPI application, replacing in-memory data with real persistence. You'll create endpoints that accept user input and use these CRUD functions to respond with real data. With SQLite under your belt, you're ready to build APIs that survive restarts and serve meaningful, dynamic content.

Keep this CRUD pattern in your toolkit — every database-backed API you write from here on will follow the same connect-execute-commit-close rhythm.