The sqlite3 module provides a DB-API 2.0 interface to SQLite, a self-contained, serverless SQL database engine. The entire database is stored in a single file (or in memory). No separate server process or installation is required — SQLite ships with Python.
A real database in one file
What you will learn: connecting to a SQLite database, creating tables, inserting and reading rows, and the critical role of commit().
How to read this tab: SQLite needs no server — the database is just a file. Run the examples and inspect the .db file that appears.
sqlite3 is a complete SQL database built into Python — no server to install, no configuration. The whole database lives in one file on disk. It is the same engine that runs inside phones, browsers, and countless apps. For local storage and prototypes, it is ideal.
You must commit() to save. INSERT, UPDATE, and DELETE live in a transaction until conn.commit(). Close or exit without committing and the changes vanish. This enables rollback-on-error, but catches beginners constantly. Or use with conn: to auto-commit.
Connecting and running a query
The workflow: connect to a database file (created if it does not exist), get a cursor, execute SQL, fetch results, and commit changes.
import sqlite3
# Connect — creates the file if it doesn't exist
conn = sqlite3.connect("app.db")
cursor = conn.cursor()
# Create a table
cursor.execute("""
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE,
age INTEGER
)
""")
# Insert a row
cursor.execute(
"INSERT INTO users (name, email, age) VALUES (?, ?, ?)",
("Priya", "priya@example.com", 30)
)
conn.commit() # save changes to disk — REQUIRED
conn.close() # always close when doneChanges (INSERT, UPDATE, DELETE) live in a transaction until you call conn.commit(). If you close the connection or the program exits without committing, the changes are lost. This is a feature — it lets you roll back on error — but it catches beginners constantly.
Iterate the cursor for large results. fetchall() loads every row into memory; iterating the cursor directly (for row in cursor) fetches one row at a time, which matters for big query results. fetchone() returns a single row or None.
Reading data back
import sqlite3
conn = sqlite3.connect("app.db")
cursor = conn.cursor()
# Fetch ALL matching rows as a list of tuples
cursor.execute("SELECT name, age FROM users")
rows = cursor.fetchall()
for name, age in rows:
print(f"{name} is {age}")
# Fetch ONE row (or None if no rows)
cursor.execute("SELECT * FROM users WHERE name = ?", ("Priya",))
row = cursor.fetchone()
print(row) # (1, 'Priya', 'priya@example.com', 30)
# Iterate the cursor directly — memory-efficient for large results
cursor.execute("SELECT name FROM users")
for row in cursor: # fetches one row at a time
print(row[0])
# Using the connection as a context manager auto-commits
with sqlite3.connect("app.db") as conn:
conn.execute("INSERT INTO users (name) VALUES (?)", ("Arjun",))
# commits automatically if no exception; rolls back on error
conn.close()✅ Beginner tab complete
- I can connect, get a cursor, execute SQL, and close
- I know I must commit() to save changes to disk
- I can fetchall() and fetchone() to read results
- I know connect() creates the file if it does not exist
Safe parameters, rows, and transactions
What you will learn: parameterised queries to prevent SQL injection, the Row factory for named column access, and transactions that all-succeed or all-fail.
How to read this tab: The ? placeholder rule is the most important security lesson here — never build SQL with f-strings.
Always use ? placeholders, never f-strings. Building SQL with f-strings opens an injection hole — a crafted input like '; DROP TABLE users; -- can destroy your data. execute("WHERE name = ?", (value,)) lets the driver escape the value safely. And remember a single value needs a trailing comma: (value,).
Safe parameters — never use string formatting
The single most important rule in sqlite3 (and all SQL): pass values as parameters with ? placeholders. Never build SQL with f-strings or + — that opens an SQL injection vulnerability.
import sqlite3
conn = sqlite3.connect("app.db")
cursor = conn.cursor()
user_input = "Priya"
# WRONG — SQL injection vulnerability. NEVER do this.
# cursor.execute(f"SELECT * FROM users WHERE name = '{user_input}'")
# If user_input were "'; DROP TABLE users; --" your table is gone.
# RIGHT — parameterised query. The ? is a placeholder.
cursor.execute("SELECT * FROM users WHERE name = ?", (user_input,))
# The driver safely escapes the value — injection is impossible.
# Multiple parameters — order matches the ? positions
cursor.execute(
"SELECT * FROM users WHERE age > ? AND age < ?",
(18, 65)
)
# Named parameters — clearer for many values
cursor.execute(
"INSERT INTO users (name, email, age) VALUES (:name, :email, :age)",
{"name": "Rohit", "email": "rohit@example.com", "age": 28}
)
# executemany — insert many rows efficiently
users = [("Sara", "sara@x.com", 25), ("Ken", "ken@x.com", 40)]
cursor.executemany(
"INSERT INTO users (name, email, age) VALUES (?, ?, ?)", users
)
conn.commit()cursor.execute("... WHERE name = ?", (name,)) — note the trailing comma making (name,) a tuple. (name) without the comma is just name in parentheses, not a tuple, and raises an error. This is the most common sqlite3 mistake.
sqlite3.Row lets you access columns by name. Set conn.row_factory = sqlite3.Row and read row["email"] instead of row[2] — far clearer and robust against column reordering. Wrap multi-step changes in with conn: so they commit together or roll back together.
Row factories and transactions
import sqlite3
conn = sqlite3.connect("app.db")
# Row factory — access columns by NAME instead of index
conn.row_factory = sqlite3.Row
cursor = conn.cursor()
cursor.execute("SELECT * FROM users WHERE name = ?", ("Priya",))
row = cursor.fetchone()
print(row["name"], row["email"]) # by column name — much clearer
print(row.keys()) # ['id', 'name', 'email', 'age']
# Transactions — group operations so they all succeed or all fail
try:
with conn: # 'with conn' is a transaction
conn.execute("UPDATE users SET age = age + 1 WHERE name = ?", ("Priya",))
conn.execute("INSERT INTO users (name) VALUES (?)", ("Maya",))
# Both committed together if no exception
except sqlite3.IntegrityError as e:
print(f"Transaction rolled back: {e}")
# Neither change was applied — the database is unchanged
# An in-memory database — fast, temporary, gone when closed
mem = sqlite3.connect(":memory:") # great for tests and caching
conn.close()? in the SQL, and do not use f-strings. "WHERE name = ?" is correct; "WHERE name = '?'" and f"WHERE name = {x}" are both wrong (the first looks for a literal question mark, the second is an injection hole).with conn: which commits automatically.✅ Intermediate tab complete
- I always use ? placeholders, never f-strings, for values
- I remember a single value still needs a tuple: (value,)
- I can use sqlite3.Row to access columns by name
- I can use with conn: for automatic transaction commit/rollback
Type adapters, PRAGMAs, and scaling limits
What you will learn: registering type adapters/converters, isolation levels, essential PRAGMAs (foreign_keys, WAL), the backup API, and when to move to a server database.
How to read this tab: Read when building a real application — foreign keys are OFF by default, which surprises everyone.
Type adapters, isolation levels, and when to move to a server database
SQLite stores values using a dynamic type system with five storage classes (NULL, INTEGER, REAL, TEXT, BLOB). Python's sqlite3 maps these to native types automatically, and you can register adapters and converters to store richer types like datetime or custom objects transparently. The detect_types parameter enables this conversion on the way back out.
import sqlite3, datetime
# Register an adapter (Python -> SQLite) and converter (SQLite -> Python)
def adapt_datetime(dt):
return dt.isoformat()
def convert_datetime(b):
return datetime.datetime.fromisoformat(b.decode())
sqlite3.register_adapter(datetime.datetime, adapt_datetime)
sqlite3.register_converter("datetime", convert_datetime)
conn = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
conn.execute("CREATE TABLE events (when_ datetime)")
conn.execute("INSERT INTO events VALUES (?)", (datetime.datetime.now(),))
row = conn.execute("SELECT when_ FROM events").fetchone()
print(type(row[0])) # <class 'datetime.datetime'> — converted back
# Isolation levels control automatic transaction behaviour
# isolation_level=None gives autocommit mode (each statement commits)
auto = sqlite3.connect(":memory:", isolation_level=None)
# Explicit transaction control with autocommit
auto.execute("BEGIN")
# ... multiple statements ...
auto.execute("COMMIT")
# PRAGMA statements configure the database engine
conn.execute("PRAGMA foreign_keys = ON") # enforce foreign keys (off by default!)
conn.execute("PRAGMA journal_mode = WAL") # write-ahead logging — better concurrency
# Backup API — copy a live database safely
src = sqlite3.connect("app.db")
dst = sqlite3.connect("backup.db")
src.backup(dst) # atomic, consistent backup even of a live DB
src.close(); dst.close()A critical default to know: SQLite does not enforce foreign-key constraints unless you turn them on with PRAGMA foreign_keys = ON for each connection. SQLite excels for embedded use, single-writer workloads, local application storage, testing, and prototypes — its single-file simplicity is a genuine strength. It reaches its limits with high write concurrency (it permits only one writer at a time) and very large multi-user deployments, where a client-server database like PostgreSQL or MySQL becomes appropriate. Because Python's sqlite3 follows the DB-API 2.0 standard, the same cursor/execute/fetch patterns transfer almost unchanged to those databases through their respective drivers.
✅ Expert tab complete
- I know foreign keys are OFF unless I enable PRAGMA foreign_keys = ON
- I can register adapters/converters for custom types
- I know SQLite suits single-writer/embedded use; Postgres/MySQL for high concurrency
- I know sqlite3 follows DB-API 2.0, so patterns transfer to other databases