How to Use SQLite in Python: sqlite3 Examples You Can Run
A sqlite3 cheat sheet with runnable examples for queries, transactions, UPSERT and RETURNING, dates, backups and the errors you'll hit first.
Checked against the official docs on Oct 23, 2026.

Python ships with the sqlite3 module, so you can use SQLite with no installs: open a database with sqlite3.connect("app.db"), run SQL through execute() with ? placeholders for every value, call commit() to save, and close() when you're done. The examples below cover that cycle plus transactions, upserts, dates and the mistakes that cause most errors.
sqlite3 cheat sheet
Everything here comes from the official sqlite3 module documentation:
| Task | Code |
|---|---|
| Open or create a file database | con = sqlite3.connect("app.db") |
| Temporary database in memory | con = sqlite3.connect(":memory:") |
| Run one statement | con.execute(sql, params) |
| Insert many rows | con.executemany(sql, list_of_tuples) |
| Run a multi-statement script | con.executescript(sql_script) |
| Positional placeholders | "... WHERE id = ?" with (42,) |
| Named placeholders | "... WHERE id = :id" with {"id": 42} |
First row, or None |
cur.fetchone() |
| All remaining rows | cur.fetchall() |
| Next batch of rows | cur.fetchmany(100) |
| ID of the last inserted row | cur.lastrowid |
| Rows changed by the last statement | cur.rowcount |
| Rows readable by column name | con.row_factory = sqlite3.Row |
| Save or discard changes | con.commit(), con.rollback() |
| Commit or roll back automatically | with con: ... |
| Close the connection | con.close() |
| SQLite library version | sqlite3.sqlite_version |
Create a table, insert and query
This first example uses an in-memory database, so nothing is written to disk. Swap ":memory:" for a filename like "app.db" and the same code creates a real database file, or opens it if it already exists.
import sqlite3
con = sqlite3.connect(":memory:")
con.row_factory = sqlite3.Row # rows behave like tuples and dicts
con.execute("""
CREATE TABLE users (
id INTEGER PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
plan TEXT NOT NULL DEFAULT 'free'
)
""")
cur = con.execute("INSERT INTO users (email, name) VALUES (?, ?)", ("ada@example.com", "Ada"))
print("new id:", cur.lastrowid)
people = [("grace@example.com", "Grace", "pro"), ("linus@example.com", "Linus", "free")]
con.executemany("INSERT INTO users (email, name, plan) VALUES (?, ?, ?)", people)
con.commit()
row = con.execute("SELECT * FROM users WHERE email = :email", {"email": "grace@example.com"}).fetchone()
print(row["name"], row["plan"], row.keys())
for r in con.execute("SELECT id, name FROM users ORDER BY name"):
print(r["id"], r["name"])
cur = con.execute("UPDATE users SET plan = ? WHERE plan = ?", ("pro", "free"))
print("rows updated:", cur.rowcount)
con.commit()
print("count:", con.execute("SELECT COUNT(*) FROM users").fetchone()[0])
con.close()A few details worth knowing:
con.execute()is a shortcut that creates a cursor for you and returns it, so you rarely needcon.cursor()by hand.- A cursor is iterable, which is the memory-friendly way to walk a large result set.
fetchall()loads every row at once. sqlite3.Rowlets you read columns by name (row["plan"]) or index (row[0]), androw.keys()lists the column names. Without a row factory you get plain tuples.lastrowidis only updated after a successfulINSERTorREPLACErun withexecute(), androwcountreports changed rows forINSERT,UPDATE,DELETEandREPLACE, and-1for other statements.
Always use placeholders
Never build SQL with f-strings or % formatting. The docs are explicit that placeholders are how you avoid SQL injection: their example shows the input ' OR TRUE; -- rewriting a WHERE clause built with string formatting. Use ? with a sequence, or :name with a dict.
Two traps catch almost everyone. First, a single parameter still needs a tuple: (42,), not (42). Passing a bare string like ("Ada") makes each character a separate parameter, which fails with "Incorrect number of bindings supplied." Second, named placeholders need a dict; since Python 3.14, passing a sequence with named placeholders raises ProgrammingError. Placeholders work for values only, not table or column names.
Transactions: commit, rollback and closing
Two facts from the docs explain most "my data disappeared" reports. Changes aren't saved until a commit, and the connection's context manager commits or rolls back, but in the docs' own words it "neither implicitly opens a new transaction nor closes the connection." For closing, they suggest contextlib.closing(). This example writes a small file database in the working folder:
import sqlite3
from contextlib import closing
with closing(sqlite3.connect("shop.db")) as con:
con.execute("DROP TABLE IF EXISTS stock")
con.execute("CREATE TABLE stock (sku TEXT PRIMARY KEY, qty INTEGER NOT NULL CHECK (qty >= 0))")
with con: # commits on success, rolls back on an exception
con.execute("INSERT INTO stock VALUES (?, ?)", ("A1", 5))
try:
with con:
con.execute("UPDATE stock SET qty = qty + 10 WHERE sku = ?", ("A1",))
con.execute("INSERT INTO stock VALUES (?, ?)", ("A1", 1)) # duplicate key
except sqlite3.IntegrityError as err:
print("rolled back:", err)
print("qty after rollback:", con.execute("SELECT qty FROM stock").fetchone()[0])
# UPSERT: insert, or update the existing row on conflict
with con:
for sku, qty in [("A1", 3), ("B2", 7)]:
con.execute(
"INSERT INTO stock (sku, qty) VALUES (?, ?) "
"ON CONFLICT(sku) DO UPDATE SET qty = qty + excluded.qty",
(sku, qty),
)
print(con.execute("SELECT * FROM stock ORDER BY sku").fetchall())
# RETURNING hands back the affected rows
with con:
sold = con.execute("UPDATE stock SET qty = qty - 1 WHERE sku = 'B2' RETURNING sku, qty").fetchall()
print("after sale:", sold)
print("connection closed by closing():", True)The failed block shows why with con: is worth using: the UPDATE that added 10 ran successfully, but the duplicate insert raised IntegrityError, so both were rolled back together and the quantity stayed at 5.
Python 3.12 added an autocommit setting that controls all of this more explicitly:
autocommit value |
Behavior |
|---|---|
sqlite3.LEGACY_TRANSACTION_CONTROL |
Current default. Pre-3.12 behavior: a transaction opens implicitly before INSERT, UPDATE, DELETE or REPLACE, shaped by isolation_level |
False |
The docs' recommended value: a transaction is always open, and you commit() or rollback() it |
True |
SQLite's own autocommit mode: each statement saves immediately, and commit()/rollback() do nothing |
The docs note that the default will change to False in a future Python release, so new code that passes autocommit=False and commits explicitly won't need changes later. With autocommit=False, closing a connection rolls back any pending transaction, so commit first.
Upsert and RETURNING
Both features are SQLite SQL rather than Python, and both appear in the example above. The SQLite UPSERT documentation explains that ON CONFLICT(...) DO UPDATE turns an insert that would violate a uniqueness constraint into an update, and that the special excluded. prefix refers to the value you tried to insert (plain column names refer to the existing row). DO NOTHING skips the row instead. UPSERT arrived in SQLite 3.24.0. The RETURNING clause, available since SQLite 3.35.0, makes INSERT, UPDATE and DELETE return the affected rows, which saves a second SELECT; the SQLite docs warn that its rows come back in arbitrary order. Check your library version with sqlite3.sqlite_version before relying on either.
Dates, Python functions and backups
The datetime adapters that used to be applied automatically are deprecated since Python 3.12, and the docs point you to their adapter and converter recipes instead. Registering your own pair takes two lines and makes the conversion explicit:
import sqlite3
from contextlib import closing
from datetime import datetime, timezone
# Explicit adapter and converter (the built-in ones are deprecated since 3.12)
sqlite3.register_adapter(datetime, lambda dt: dt.isoformat())
sqlite3.register_converter("timestamp", lambda b: datetime.fromisoformat(b.decode()))
con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
con.execute("CREATE TABLE events (name TEXT, at TIMESTAMP)")
with con:
con.execute("INSERT INTO events VALUES (?, ?)", ("deploy", datetime(2026, 10, 23, 9, 30, tzinfo=timezone.utc)))
name, at = con.execute("SELECT name, at FROM events").fetchone()
print(name, repr(at))
# Call a Python function from SQL
con.create_function("slugify", 1, lambda s: s.lower().replace(" ", "-"), deterministic=True)
print(con.execute("SELECT slugify(?)", ("Hello SQLite World",)).fetchone()[0])
# Rows as plain dicts, on one cursor only
cur = con.cursor()
cur.row_factory = lambda c, row: {col[0]: val for col, val in zip(c.description, row)}
print(cur.execute("SELECT name, 1 + 1 AS two FROM events").fetchall())
# Copy the database to a file (commit first), then dump it as SQL
with closing(sqlite3.connect("events-backup.db")) as dst:
con.backup(dst)
print("rows in backup:", dst.execute("SELECT COUNT(*) FROM events").fetchone()[0])
for line in con.iterdump():
print(line)
con.close()How it works: register_adapter() tells sqlite3 how to store a Python type, register_converter() how to turn stored bytes back into one, and detect_types=sqlite3.PARSE_DECLTYPES applies the converter based on the first word of the declared column type (TIMESTAMP here). Converters always receive bytes, hence the .decode(). Storing ISO 8601 text keeps the timezone offset intact, and those strings sort chronologically when every value uses the same offset.
create_function() registers a Python callable you can use inside SQL, and backup() copies a live database to another connection. Commit before calling backup(): with an uncommitted write still open on the source connection, the copy can't complete. iterdump() produces the SQL needed to recreate the database, which is handy for a quick text export.
Insert lots of rows and read big results
Bulk work is where small habits pay off. This pattern loads a CSV file into a table and reads the result back in batches:
import csv
import sqlite3
from contextlib import closing
def read_rows(path):
with open(path, newline="", encoding="utf-8") as f:
for rec in csv.DictReader(f):
yield rec["email"], rec["name"]
with closing(sqlite3.connect("app.db", autocommit=False)) as con:
con.execute("CREATE TABLE IF NOT EXISTS users (email TEXT PRIMARY KEY, name TEXT)")
con.execute("CREATE INDEX IF NOT EXISTS users_name ON users (name)")
con.executemany("INSERT OR IGNORE INTO users VALUES (?, ?)", read_rows("users.csv"))
con.commit()
cur = con.execute("SELECT email, name FROM users ORDER BY name")
while batch := cur.fetchmany(500):
for email, name in batch:
print(email, name)Why it's written this way:
executemany()runs the statement once for every item it's given, and it accepts any iterable, so a generator streams the CSV without loading it all into memory.- Every row goes in under a single transaction with one
commit()at the end, instead of a commit per row. INSERT OR IGNOREskips rows that would break the primary key, so a duplicate email doesn't abort the whole load.- The index on
namespeeds up theORDER BYand any lookups by name. fetchmany(500)reads in fixed-size batches and returns an empty list when nothing is left, which ends the loop.
The autocommit=False argument needs Python 3.12 or newer; drop it on older versions and the legacy default still opens the transaction for you.
Use sqlite3 safely with threads
By default a connection refuses to be used from any thread other than the one that created it, because check_same_thread is True. The simplest correct design is one connection per thread or per task. You can pass check_same_thread=False to share one, but the docs warn that write operations may then need to be serialized by you to avoid data corruption, so put a lock around writes if you go that way. When several connections write to the same file, the timeout argument decides how long one waits for another's lock before raising "database is locked."
Explore a database from the command line
Since Python 3.12 the module has its own shell, so you can inspect a file without installing the separate sqlite3 program:
python -m sqlite3 shop.db
python -m sqlite3 shop.db "SELECT * FROM stock"
python -m sqlite3 -vThe first form opens an interactive prompt (type .quit or press Ctrl-D to leave), the second runs one statement and exits, and -v prints the SQLite library version. In Python 3.15 the shell gained Tab completion for SQL keywords and for table, column and other schema names, plus colored prompts and errors, according to What's New in Python 3.15.
Common sqlite3 errors and fixes
| Error message | Cause | Fix |
|---|---|---|
Incorrect number of bindings supplied |
Parameter count doesn't match the ? count, often ("x") instead of ("x",) |
Pass a tuple with a trailing comma |
no such table: users |
Wrong working directory or path, or a new :memory: connection |
Use an absolute path; each :memory: connection is a separate database |
database is locked |
Another connection holds a write transaction longer than timeout (default 5 seconds) |
Commit sooner, keep transactions short, or raise timeout |
You can only execute one statement at a time. |
Several statements passed to execute() |
Use executescript() for scripts |
SQLite objects created in a thread can only be used in that same thread |
A connection shared across threads with check_same_thread=True |
Open one connection per thread |
Cannot operate on a closed database. |
Query after close(), often outside a closing() block |
Keep work inside the block or reopen |
UNIQUE constraint failed |
Duplicate key, raised as IntegrityError |
Catch it, or use an UPSERT |
unable to open database file |
The folder in the path doesn't exist or isn't writable | Create the folder or fix permissions |
All of these are subclasses of sqlite3.Error, with OperationalError, IntegrityError and ProgrammingError being the ones you'll see most. Since Python 3.11, errors from the SQLite library also carry sqlite_errorcode and sqlite_errorname attributes, such as SQLITE_BUSY for a locked database, which are easier to branch on than message text.
Turning plain-English questions into queries is a natural next step: try the SQL query from plain English prompt. More quick references live under cheat sheets.
Quick answers
Do I need to install anything to use SQLite in Python?
No. The sqlite3 module is part of the standard library, so import sqlite3 works in a normal Python installation. A database is just a file that sqlite3.connect() creates on first use.
Where is my SQLite database file created?
A relative name like app.db is resolved against the current working directory of the process, not the folder of your script. Use an absolute path, for example built from pathlib.Path(file).parent, if the script runs from different places.
Should I keep one connection open or reconnect for every query?
In a script or single-threaded app, open one connection and reuse it; each connection keeps a cache of prepared statements (cached_statements defaults to 128). In threaded code, give each thread its own connection.
How do I list the tables in a SQLite database from Python?
Query the schema table: con.execute("SELECT name FROM sqlite_master WHERE type = 'table'").fetchall(). From a terminal, python -m sqlite3 app.db opens a shell where you can run the same query.
Official sources
Next guide