Working with SQLite in Python Using the sqlite3 Module

SQLite is a full SQL database that lives in a single file, with no server to install or manage. Python ships with the sqlite3 module in the standard library, so you can create tables, run joins, and use transactions without installing a thing. It's a great fit for command-line tools, desktop apps, prototypes, test fixtures, caches, and plenty of small production websites.

The module is easy to start with but has a few behaviors that surprise people: how transactions are committed, what the with statement does and doesn't do, how dates are stored, and why foreign keys aren't enforced by default. This guide covers the practical path from creating a database to querying it safely, plus those gotchas.

Examples use Python 3.13. Some SQL features mentioned depend on the version of the SQLite library your Python is linked against, which you can check with sqlite3.sqlite_version.

Creating a Database and Schema

sqlite3.connect() opens a database file, creating it if it doesn't exist:

# setup_db.py
import sqlite3

SCHEMA = """
CREATE TABLE IF NOT EXISTS authors (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
) STRICT;

CREATE TABLE IF NOT EXISTS books (
    id        INTEGER PRIMARY KEY,
    title     TEXT NOT NULL,
    author_id INTEGER NOT NULL REFERENCES authors(id),
    year      INTEGER,
    rating    REAL CHECK (rating BETWEEN 0 AND 5)
) STRICT;

CREATE INDEX IF NOT EXISTS idx_books_author ON books(author_id);
"""

con = sqlite3.connect("library.db")
con.executescript(SCHEMA)
con.close()
print("Schema created")

A few details in that schema matter:

• INTEGER PRIMARY KEY makes id an alias for SQLite's internal row ID. It auto-assigns increasing values when you insert without one.
• STRICT tables (SQLite 3.37+) enforce column types. Without it, SQLite uses "type affinity" and will happily store the text 'nineteen' in an INTEGER column. Strict tables reject that with an error, which is what most Python developers expect.
• CHECK constraints and NOT NULL let the database guard your data, not just your application code.
• executescript() runs several statements separated by semicolons. Use it for schema files. For everything else, use execute(), which runs exactly one statement.

Pass ":memory:" instead of a filename for a temporary database that lives in RAM and disappears when the connection closes. That's ideal for tests.

Inserting Data Safely

# insert_data.py
import sqlite3

con = sqlite3.connect("library.db")
con.execute("PRAGMA foreign_keys = ON")

with con:
    cur = con.execute("INSERT INTO authors (name) VALUES (?)", ("Ursula K. Le Guin",))
    le_guin_id = cur.lastrowid
    cur = con.execute("INSERT INTO authors (name) VALUES (?)", ("Octavia E. Butler",))
    butler_id = cur.lastrowid

    books = [
        ("A Wizard of Earthsea", le_guin_id, 1968, 4.5),
        ("The Left Hand of Darkness", le_guin_id, 1969, 4.7),
        ("The Dispossessed", le_guin_id, 1974, 4.6),
        ("Kindred", butler_id, 1979, 4.8),
        ("Parable of the Sower", butler_id, 1993, None),
    ]
    con.executemany(
        "INSERT INTO books (title, author_id, year, rating) VALUES (?, ?, ?, ?)", books
    )

print(le_guin_id, butler_id)
print(con.execute("SELECT count(*) FROM books").fetchone())
con.close()

1 2
(5,)

Always Use Placeholders

The ? marks are placeholders, and the values come from the tuple in the second argument. The sqlite3 module passes them to SQLite separately from the SQL text, so a value can never be interpreted as SQL. Compare:

# Never do this: the title is pasted into the SQL itself
con.execute(f"SELECT * FROM books WHERE title = '{title}'")

# Do this: the title is passed as a parameter
con.execute("SELECT * FROM books WHERE title = ?", (title,))

With the f-string version, a title like Kindred'; DROP TABLE books; -- changes the meaning of your query. With placeholders, it's just an odd string that matches no rows. Placeholders also handle quoting, so names like O'Brien work without escaping.

Note the trailing comma in (title,). Parameters must be a sequence, and (title) is just title in parentheses. Passing a bare value fails:

con.execute("SELECT * FROM books WHERE id = ?", 3)

sqlite3.ProgrammingError: parameters are of unsupported type

Placeholders only work for values, not for table or column names. If those need to be dynamic, check them against an allow-list in Python before building the SQL.

Useful Insert Tools

• cur.lastrowid gives the id of the row just inserted, handy for inserting related rows.
• executemany() runs one statement for every tuple in an iterable, and it's much faster than looping over execute().
• PRAGMA foreign_keys = ON: SQLite does not enforce REFERENCES constraints unless you turn this on, and the setting is per connection. Run it every time you connect.

Querying Data

Fetching Rows

execute() returns a cursor, and you pull rows from it with the fetch methods:

import sqlite3

con = sqlite3.connect("library.db")
cur = con.execute("SELECT id, title FROM books ORDER BY id")
print(cur.fetchone())
print(cur.fetchmany(2))
print(cur.fetchall())

(1, 'A Wizard of Earthsea')
[(2, 'The Left Hand of Darkness'), (3, 'The Dispossessed')]
[(4, 'Kindred'), (5, 'Parable of the Sower')]

fetchone() returns the next row or None when there are no more. fetchmany(n) returns up to n rows, and fetchall() returns everything left. For large results, iterate the cursor directly so rows are fetched as you go instead of all at once:

for book_id, title in con.execute("SELECT id, title FROM books WHERE author_id = ?", (2,)):
    print(book_id, title)

4 Kindred
5 Parable of the Sower

SQL NULL comes back as Python None, and aggregate functions work as you'd expect. avg() and count(column) skip nulls, while count(*) counts every row:

print(con.execute("SELECT avg(rating), count(rating), count(*) FROM books").fetchone())

(4.65, 4, 5)

Named Parameters and sqlite3.Row

By default, rows are plain tuples, so you access columns by position. Set row_factory to sqlite3.Row to access them by name too:

# query_data.py
import sqlite3

con = sqlite3.connect("library.db")
con.row_factory = sqlite3.Row

rows = con.execute(
    """
    SELECT b.title, a.name AS author, b.year, b.rating
    FROM books AS b
    JOIN authors AS a ON a.id = b.author_id
    WHERE b.year < :before
    ORDER BY b.year
    """,
    {"before": 1975},
).fetchall()

for row in rows:
    print(f"{row['year']}  {row['title']:<26} {row['author']}")

print(rows[0].keys())
print(dict(rows[0]))
con.close()

1968  A Wizard of Earthsea       Ursula K. Le Guin
1969  The Left Hand of Darkness  Ursula K. Le Guin
1974  The Dispossessed           Ursula K. Le Guin
['title', 'author', 'year', 'rating']
{'title': 'A Wizard of Earthsea', 'author': 'Ursula K. Le Guin', 'year': 1968, 'rating': 4.5}

This example also uses a named placeholder, :before, with a dict of parameters. Named placeholders are easier to read when a query has several parameters or uses the same value twice.

sqlite3.Row objects support both row["title"] and row[0], unpacking, keys(), and conversion with dict(row), which is convenient for returning JSON from a web handler.

IN Clauses

A placeholder stands for one value, so WHERE id IN (?) with a list doesn't work. Generate one placeholder per item:

ids = [1, 3, 4]
placeholders = ", ".join("?" * len(ids))
rows = con.execute(
    f"SELECT title FROM books WHERE id IN ({placeholders}) ORDER BY id", ids
).fetchall()
print(rows)

[('A Wizard of Earthsea',), ('The Dispossessed',), ('Kindred',)]

The f-string only inserts question marks, never the values themselves, so this is still safe.

Transactions: How Commits Actually Work

This is the part of sqlite3 that trips people up the most.

By default, the module opens a transaction implicitly before INSERT, UPDATE, and DELETE statements. Nothing is saved to disk until you call con.commit(). If you close the connection or your program exits without committing, those changes are rolled back and lost.

The cleanest way to manage this is to use the connection as a context manager:

# transaction.py
import sqlite3

con = sqlite3.connect("library.db")
con.execute("PRAGMA foreign_keys = ON")

try:
    with con:
        con.execute("UPDATE books SET rating = 4.9 WHERE title = ?", ("Kindred",))
        con.execute("INSERT INTO books (title, author_id) VALUES (?, ?)", ("Orphan", 99))
except sqlite3.IntegrityError as exc:
    print("Rolled back:", exc)

print(con.execute("SELECT rating FROM books WHERE title = 'Kindred'").fetchone())
con.close()

Rolled back: FOREIGN KEY constraint failed
(4.8,)

The with con: block commits if the block finishes normally and rolls back if an exception escapes it. Here the second statement references an author that doesn't exist, so the whole block is rolled back, and Kindred's rating is still 4.8. That all-or-nothing behavior is exactly what you want for operations like transferring stock between warehouses.

Important: with con: does not close the connection. It only manages the transaction. You still need con.close(), or contextlib.closing:

import sqlite3
from contextlib import closing

with closing(sqlite3.connect("library.db")) as con, con:
    print(con.execute("SELECT count(*) FROM books").fetchone())

The first context manager closes the connection; the second (con itself) handles the transaction. Python 3.13 emits a ResourceWarning when a connection is garbage-collected without being closed, which helps you catch leaks during development (run with -W always to see it).

The autocommit Attribute

Python 3.12 added an autocommit parameter that makes transaction behavior explicit and closer to the database standard (PEP 249):

con = sqlite3.connect("library.db", autocommit=False)

With autocommit=False, a transaction is always open, starting from the moment you connect: every statement runs inside it, and you call commit() or rollback() to finish it (a new one starts right after). With autocommit=True, every statement is committed immediately, and you manage transactions yourself with explicit BEGIN and COMMIT SQL. The default (sqlite3.LEGACY_TRANSACTION_CONTROL) keeps the older implicit behavior described above, for backward compatibility.

For new code, autocommit=False with with con: blocks is the most predictable combination. Whichever you choose, use it consistently across a project.

Constraint Errors

Constraint violations raise sqlite3.IntegrityError. Each message tells you which rule failed:

UNIQUE constraint failed: authors.name
CHECK constraint failed: rating BETWEEN 0 AND 5
cannot store TEXT value in INTEGER column books.year

The last one comes from the STRICT table. To insert a row unless it already exists, let SQLite handle the conflict with an upsert instead of catching the error:

with con:
    con.execute(
        "INSERT INTO authors (name) VALUES (?) ON CONFLICT(name) DO NOTHING",
        ("Octavia E. Butler",),
    )

ON CONFLICT(name) DO UPDATE SET ... updates the existing row instead. After an UPDATE or DELETE, cursor.rowcount tells you how many rows were affected.

Dates and Times

SQLite has no dedicated date type. Dates are usually stored as ISO 8601 text ('2026-10-03T09:30:00+00:00'), which sorts correctly and works with SQLite's date functions.

Older versions of the sqlite3 module converted date and datetime objects automatically, but those default adapters are deprecated as of Python 3.12 and emit a DeprecationWarning. The recommended approach is to register your own:

# dates.py
import sqlite3
from datetime import UTC, datetime


def adapt_datetime(value: datetime) -> str:
    return value.isoformat()


def convert_timestamp(raw: bytes) -> datetime:
    return datetime.fromisoformat(raw.decode())


sqlite3.register_adapter(datetime, adapt_datetime)
sqlite3.register_converter("timestamp", convert_timestamp)

con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
con.execute("CREATE TABLE loans (book_id INTEGER, borrowed_at timestamp)")
con.execute(
    "INSERT INTO loans VALUES (?, ?)", (1, datetime(2026, 10, 3, 9, 30, tzinfo=UTC))
)

borrowed_at = con.execute("SELECT borrowed_at FROM loans").fetchone()[0]
print(type(borrowed_at).__name__, borrowed_at)
con.close()

datetime 2026-10-03 09:30:00+00:00

The adapter converts a Python datetime to text on the way in. The converter turns the stored bytes back into a datetime on the way out, and detect_types=sqlite3.PARSE_DECLTYPES tells the module to apply converters based on the column's declared type (timestamp). Store times in UTC with an explicit offset, and convert to local time only for display. Note that STRICT tables only allow the core type names (INTEGER, REAL, TEXT, BLOB, ANY), so this declared-type trick works only in regular tables; in a strict table, declare the column TEXT and convert in your code.

JSON in SQLite

SQLite has built-in JSON functions, so you can store a JSON document in a TEXT column and query inside it:

import json
import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE events (id INTEGER PRIMARY KEY, payload TEXT)")
con.execute(
    "INSERT INTO events (payload) VALUES (?)",
    (json.dumps({"type": "loan", "book": 4, "tags": ["new"]}),),
)
print(con.execute("SELECT payload ->> '$.type', json_extract(payload, '$.book') FROM events").fetchone())
con.close()

('loan', 4)

json_extract() is available in any modern SQLite build, and the ->> operator needs SQLite 3.38 or newer. This works well for flexible attributes that don't deserve their own columns. For data you filter on constantly, real columns are still faster and clearer.

Concurrency and Performance Settings

SQLite allows many readers but only one writer at a time. In the default rollback-journal mode, a write also blocks readers. Two pragmas make a big difference for apps with more than one connection, such as a web app:

con = sqlite3.connect("library.db", timeout=5.0)
con.execute("PRAGMA journal_mode = WAL")

• WAL (write-ahead logging) mode lets readers keep reading while a write is in progress. The setting is persistent: it's stored in the database file, so you only need to set it once. You'll see extra -wal and -shm files next to the database; that's normal.
• timeout (default 5 seconds) is how long a connection waits for a lock before raising sqlite3.OperationalError: database is locked. Keeping write transactions short is the real fix for lock errors.

A few other performance habits:

• Batch writes inside one transaction. Thousands of inserts in a single with con: block are dramatically faster than committing each one, because every commit waits for the disk.
• Add indexes for columns you filter or join on, as the schema did for books.author_id. Prefix a query with EXPLAIN QUERY PLAN to see whether an index is used.
• Connections can't be shared between threads by default. Give each thread its own connection.

Other Handy Features

Custom SQL functions. Register a Python function and call it from SQL:

con.create_function("slugify", 1, lambda s: s.lower().replace(" ", "-"), deterministic=True)
print(con.execute("SELECT slugify(title) FROM books WHERE id = 2").fetchone())

('the-left-hand-of-darkness',)

Online backups. Connection.backup() copies a live database safely, even while it's in use:

import sqlite3

source = sqlite3.connect("library.db")
target = sqlite3.connect("library-backup.db")
with target:
    source.backup(target)
target.close()
source.close()

SQL dumps. con.iterdump() yields the SQL statements needed to recreate the database, useful for exporting to text.

A built-in shell. Since Python 3.12 you can open an interactive SQLite prompt with nothing but Python installed:

python -m sqlite3 library.db

pandas. pd.read_sql_query("SELECT ...", con, params=(...)) loads query results straight into a DataFrame, and df.to_sql() writes one back.

When to Reach for Something Else

SQLite handles more than most people expect: databases of many gigabytes and read-heavy web apps with plenty of traffic. Consider a client-server database like PostgreSQL when you need many concurrent writers, multiple application servers writing to the same data over a network, or fine-grained user permissions. If you'd rather work with Python classes than SQL strings, an ORM such as SQLAlchemy works on top of SQLite too; see how to use Python with databases for a broader overview of the options.

Conclusion

The sqlite3 module gives you a real SQL database with zero setup. Use placeholders for every value, turn on PRAGMA foreign_keys on each connection, consider STRICT tables, and manage transactions with with con: blocks while remembering that they commit but don't close. Register your own date adapters instead of relying on the deprecated defaults, switch to WAL mode when several connections share a database, and batch writes into transactions for speed.

With those habits in place, SQLite is a dependable choice for far more projects than its "lightweight" reputation suggests.