CRUD with SQLite

You know how to store data in Python lists and dictionaries — but as soon as you close your script, that data vanishes. When you need persistent storage that survives restarts, scales from a single file to a production database, and can be queried with SQL, you need a proper database. SQLite, Python's built-in battery, gives you a full SQL engine without installing a server. In this lesson, you'll implement a basic CRUD application with SQLite — Create, Read, Update, and Delete records — turning ephemeral Python data into a reusable, queryable store.

The problem this lesson solves

Every real-world Python application eventually must save data between runs. File-based storage (JSON, CSV) works for small datasets but breaks down when you need concurrent writes, complex queries, or data consistency. Without a database, you reinvent the wheel: manual file locking, ad‑hoc search logic, and fragile update routines. The pain is immediate: your to‑do list app forgets tasks after a reboot, your inventory script corrupts on a crash, and your analytics program takes minutes to scan a CSV.

SQLite solves all this with zero configuration. It lives in a single .db file, uses the same SQL you'd run on PostgreSQL or MySQL, and integrates directly into Python via the sqlite3 module. By the end of this lesson, you will implement a basic CRUD application with SQLite that can serve as the foundation for everything from a personal budgeting tool to a lightweight CRM.

Core concept / mental model

Think of SQLite as a file‑based relational database engine. Instead of a separate server process, your Python program is the database server. The .db file acts like a spreadsheet with multiple sheets — except each “sheet” is a table with rigid columns, data types, and constraints.

Database = File + Schema + Cursor

• Connection: A sqlite3.connect('filename.db') opens (or creates) a single database file. The connection manages the transaction state.
• Cursor: A cursor is your pointer for executing SQL statements and fetching results. Think of it as a robot that walks through the table answering your SQL queries row by row.
• Schema: You define the structure — table names, column types, constraints (like NOT NULL or PRIMARY KEY) — using CREATE TABLE statements.

The CRUD acronym maps directly to SQL commands

Pro tip: SQLite works transactionally by default — every INSERT, UPDATE, or DELETE must be followed by a commit() or rollback(). Forgetting commit() is the #1 cause of “lost data” in beginner SQLite apps.

How it works step by step

Step 1: Connect to the database (or create it)

import sqlite3

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

If library.db doesn't exist, SQLite creates it silently. The cursor is now ready to execute SQL.

Step 2: Create a table (the schema)

cursor.execute('''
    CREATE TABLE IF NOT EXISTS books (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        author TEXT NOT NULL,
        year INTEGER
    )
''')
conn.commit()

• IF NOT EXISTS prevents errors if the table already exists.
• AUTOINCREMENT on id makes it a unique, auto‑generated integer key.
• TEXT NOT NULL ensures we never insert a book without a title/author.

Step 3: Create (INSERT) a record

def add_book(conn, title, author, year):
    cursor = conn.cursor()
    cursor.execute('''
        INSERT INTO books (title, author, year)
        VALUES (?, ?, ?)
    ''', (title, author, year))
    conn.commit()
    return cursor.lastrowid  # id of new record

Why ? placeholders? Never use f‑strings or + for SQL values — that invites SQL injection. The ? placeholders safely escape the values for you.

Step 4: Read (SELECT) records

def get_all_books(conn):
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM books')
    rows = cursor.fetchall()
    return rows  # list of tuples

def get_book_by_id(conn, book_id):
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM books WHERE id = ?', (book_id,))
    return cursor.fetchone()  # single tuple or None

• fetchall() returns a list of tuples, one tuple per row.
• fetchone() returns one tuple (or None if no match).

Step 5: Update a record

def update_book_year(conn, book_id, new_year):
    cursor = conn.cursor()
    cursor.execute('''
        UPDATE books SET year = ? WHERE id = ?
    ''', (new_year, book_id))
    conn.commit()
    return cursor.rowcount  # number of rows changed

Step 6: Delete a record

def delete_book(conn, book_id):
    cursor = conn.cursor()
    cursor.execute('DELETE FROM books WHERE id = ?', (book_id,))
    conn.commit()
    return cursor.rowcount

Step 7: Close the connection

conn.close()

Always close the connection when you're done — especially in scripts that run repeatedly.

Hands-on walkthrough

Now let's wire everything together into a complete, runnable script. Copy this into a file called library_crud.py and run it with python library_crud.py.

import sqlite3

DB_FILE = 'library.db'

def init_db():
    conn = sqlite3.connect(DB_FILE)
    cursor = conn.cursor()
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS books (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            author TEXT NOT NULL,
            year INTEGER
        )
    ''')
    conn.commit()
    return conn

def add_book(conn, title, author, year):
    cursor = conn.cursor()
    cursor.execute(
        'INSERT INTO books (title, author, year) VALUES (?, ?, ?)',
        (title, author, year)
    )
    conn.commit()
    return cursor.lastrowid

def get_all_books(conn):
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM books')
    return cursor.fetchall()

def update_book_year(conn, book_id, new_year):
    cursor = conn.cursor()
    cursor.execute(
        'UPDATE books SET year = ? WHERE id = ?',
        (new_year, book_id)
    )
    conn.commit()
    return cursor.rowcount

def delete_book(conn, book_id):
    cursor = conn.cursor()
    cursor.execute('DELETE FROM books WHERE id = ?', (book_id,))
    conn.commit()
    return cursor.rowcount

def display_books(books):
    if not books:
        print('📚 No books in library.')
        return
    for book in books:
        print(f'{book[0]:>3}: {book[1]} by {book[2]} ({book[3]})')

def main():
    conn = init_db()

    # CREATE sample books
    id1 = add_book(conn, 'The Pragmatic Programmer', 'Andy Hunt', 1999)
    id2 = add_book(conn, 'Clean Code', 'Robert C. Martin', 2008)
    add_book(conn, 'Python Crash Course', 'Eric Matthes', 2019)
    print('✅ Added 3 books.')

    # READ all books
    print('\n--- All Books ---')
    display_books(get_all_books(conn))

    # UPDATE first book's year
    updated = update_book_year(conn, id1, 2020)
    print(f'\n✅ Updated {updated} book(s).')

    # READ again to confirm
    print('\n--- After Update ---')
    display_books(get_all_books(conn))

    # DELETE second book
    deleted = delete_book(conn, id2)
    print(f'\n✅ Deleted {deleted} book(s).')

    # READ final state
    print('\n--- Final Library ---')
    display_books(get_all_books(conn))

    conn.close()

if __name__ == '__main__':
    main()

Expected output:

✅ Added 3 books.

--- All Books ---
  1: The Pragmatic Programmer by Andy Hunt (1999)
  2: Clean Code by Robert C. Martin (2008)
  3: Python Crash Course by Eric Matthes (2019)

✅ Updated 1 book(s).

--- After Update ---
  1: The Pragmatic Programmer by Andy Hunt (2020)
  2: Clean Code by Robert C. Martin (2008)
  3: Python Crash Course by Eric Matthes (2019)

✅ Deleted 1 book(s).

--- Final Library ---
  1: The Pragmatic Programmer by Andy Hunt (2020)
  3: Python Crash Course by Eric Matthes (2019)

Query with filters (bonus reading pattern)

def search_books_by_author(conn, author_name):
    cursor = conn.cursor()
    cursor.execute(
        'SELECT * FROM books WHERE author LIKE ?',
        (f'%{author_name}%',)
    )
    return cursor.fetchall()

Use LIKE with % wildcards for flexible searching. The % in the Python string says “match anything before/after the search term.”

Compare options / when to choose what

Choosing a storage layer for a Python app involves trade‑offs. Here's a comparison of SQLite with common alternatives:

When to choose SQLite? When your app needs persistent, queryable storage with minimal setup — perfect for prototypes, desktop apps, mobile apps (iOS/Android), and tools that run on a single machine. For high‑concurrency web servers or multi‑user environments, switch to PostgreSQL or MySQL.

Troubleshooting & edge cases

“File is not a database” error

sqlite3.DatabaseError: file is not a database

• Cause: The file exists but isn't a valid SQLite database (e.g., you opened a PDF or a JSON file).
• Fix: Delete the file or use a different name. Check the file's origin.

“No such table” after creating a table

• Cause: You created the table in one connection, then opened a different connection (or a new script run) without calling init_db() again, or you never committed the CREATE TABLE statement.
• Fix: Always call init_db() (or CREATE TABLE IF NOT EXISTS) at the start of every script/process that uses the same .db file. Ensure you conn.commit() after table creation.

Data disappears after program ends

cursor.execute('INSERT INTO ... VALUES (?, ?)', ('A', 'B'))
# No conn.commit() -- data not written

• Fix: Always call conn.commit() after INSERT, UPDATE, or DELETE. Use with conn: as a context manager to auto‑commit:

with conn:
    conn.execute('INSERT ...', values)

SQL injection via unsafe string formatting

# DANGEROUS
cursor.execute(f"SELECT * FROM books WHERE title = '{title}'")

• Fix: Use ? placeholders. If a user inputs ' OR 1=1 -- , the placeholder treats it as a literal string, not a SQL command.

Concurrent write locks

• Problem: Two Python processes try to write to the same .db file simultaneously.
• Symptom: sqlite3.OperationalError: database is locked
• Fix: Use a single‑process writer or switch to a client‑server database (PostgreSQL, MySQL). For light concurrency, you can set a timeout:

conn = sqlite3.connect('library.db', timeout=10)  # wait up to 10 seconds

What you learned & what's next

You now know how to implement a basic CRUD application with SQLite in Python. You connected to a database, created a schema, inserted records, queried them with SELECT, updated records, and deleted them — all with parameterised queries to prevent SQL injection. You also learned to compare SQLite with other storage options and handle common pitfalls like forgotten commits and locked databases.

Learning objectives met: - ✅ Explain the core idea behind a basic CRUD application with SQLite - ✅ Complete a practical exercise that performs all four CRUD operations

Next step: In the next lesson, you'll level up by adding foreign key relationships and JOINs — connecting multiple tables to model real‑world data like users, orders, and products. You'll also learn about indexing to speed up queries on large datasets.