Python ships with a working SQL database built in. The sqlite3 module wraps the SQLite engine, which is a small C library that stores a whole database in a single file (or in memory). No server to install, no credentials to set up, no network port to open. That makes it the easiest way to learn SQL from Python and a strong pick for a long list of small but real applications. This lesson covers when SQLite is the right fit, how to open and use a connection, what cursors and row factories do, how to handle transactions, and where SQLite's quirks differ from heavier databases.

SQLite isn't a stripped-down database. It is a fully ACID-compliant SQL engine that happens to run inside the application process instead of as a separate server. That design fits a specific shape of workload and is a bad fit for a different one.

SQLite fits when:

It is the wrong tool when:

The rule of thumb: if the data fits on one machine and one process reaches it, SQLite is fine. The moment a second process needs to write, switch to PostgreSQL or MySQL. The Python code from this lesson carries over with small changes, so the upgrade isn't expensive when needed.

A SQLite connection is created with sqlite3.connect(path). The path is the filename for an on-disk database; the string ":memory:" makes a brand-new in-memory database that vanishes when the connection closes.

If shop.db doesn't exist, SQLite creates an empty file in the current working directory. If it exists, the connection opens the existing database. Either way, the call returns a Connection object that holds the state of the session: the open transaction, the row factory, and a few configuration knobs covered later in this lesson.

For tests and scratch work, the in-memory variant is the friendliest:

Each sqlite3.connect(":memory:") call gives back a fresh, isolated database. Two :memory: connections inside the same process see two unrelated databases. That property makes :memory: ideal for tests: each test gets a clean slate, and there's no cleanup to do.

A connection should be closed when no longer needed, either by calling conn.close() explicitly or by using it as a context manager. The context-manager form doesn't close the connection; it commits or rolls back the active transaction.

sqlite3.connect(path) does not raise when the file is missing. It creates a new file. To open an existing database and fail when it isn't there, check os.path.exists(path) first or pass f"file:{path}?mode=rw" with uri=True.

The connection itself doesn't run SQL. A cursor runs statements on its behalf. A cursor is a small object that tracks one statement at a time and the rows produced by it.

A few details to know about this pattern.

First, execute(sql, params=()) runs one SQL statement. The optional second argument is a tuple (or dict) of parameter values for any placeholders in the SQL. Parameter substitution is the safe, escaping-aware way to plug values in.

Second, after a SELECT, the cursor itself becomes iterable. for row in cur walks the result set one row at a time. This is the memory-friendly way to read a large result, because the database hands rows over as needed instead of all at once.

Third, the row is a tuple. The first item is the first column in the SELECT, the second is the second, and so on. Row factories allow row["name"] instead of row[1].

For one-off statements, execute can also be called directly on the connection. SQLite creates a cursor, runs the statement, and returns the cursor.

The connection-level execute is fine for short scripts. For anything longer, opening an explicit cursor keeps the code self-explanatory and allows reusing the same cursor for related statements.

SQLite uses the qmark placeholder style. Every ? in the SQL is filled in, in order, from the tuple passed as the second argument to execute.

Three placeholders, three values, in the same order. A common error: a single-value tuple has to include the trailing comma, or Python parses the parentheses as a plain group, not a tuple.

The second form raises sqlite3.ProgrammingError: parameters are of unsupported type because (101) is an integer, not a one-tuple. A list works fine for readability: [101].

SQLite also accepts named parameters with the :name syntax. The second argument becomes a dict.

Named parameters get useful when the SQL has many placeholders or when the values come from a dict. Most code uses positional ? for short queries and switches to :name when the query gets long.

Never interpolate values into the SQL string with f-strings or %. That's how SQL injection bugs happen. The placeholder form sends values as data; the f-string form sends them as code.

The wrong form is a classic SQL injection: the value contains SQL syntax that ends the string and runs an extra statement. The right form treats the entire value as a single string literal, and the database stores or compares it as one piece of data.

Inserting one row at a time in a loop works, but every execute carries some overhead. With many rows to insert at once, executemany(sql, rows) is the better tool. It takes the same SQL once and a list of parameter tuples, and runs the statement once per tuple in one round trip to the engine.

executemany keeps the per-row overhead low and lets the engine batch the writes. The performance difference is small at four rows and noticeable at four thousand.

Inserting 100,000 rows one at a time with execute in a loop can take many seconds because every call is its own statement preparation and commit. The same insert with executemany inside one transaction often finishes in under a second. The two big levers are (1) executemany, and (2) commit at the end, not after each row.

executemany only works for statements that don't return rows (INSERT, UPDATE, DELETE, REPLACE). It can't be used to bulk-SELECT.

After executing a SELECT, the cursor holds a pointer into the result set. Rows are read from it with one of three methods.

fetchone() returns the next row (or None when the result is exhausted). Use it when the query is expected to return at most one row, or when walking results one at a time.

fetchall() returns every remaining row as a list. It's convenient for small result sets but loads everything into memory at once.

fetchmany(size) returns up to size rows at a time. It strikes a balance between the two: rows arrive in chunks, holding only one batch in memory at a time. Useful when the result is too big for fetchall but fewer round trips than fetchone are desired.

The cursor itself is also iterable, as shown earlier. Iterating the cursor reads one row at a time and is functionally equivalent to a fetchone loop. For large result sets, iterating (or fetchmany) fits best. For small result sets, fetchall is fine and easier to read.

fetchall() on a million-row result will pull every row into a Python list before the code sees the first one. That's the worst memory case. Switching to iteration (for row in cur) or fetchmany(1000) keeps memory roughly constant.

By default, each row is a tuple. That's compact, but indexing by position (row[1] for the name) reads worse than indexing by column (row["name"]). The Connection.row_factory attribute controls how each row is returned, and sqlite3.Row is a built-in factory that allows column access by name or by position.

sqlite3.Row is lightweight (close to a tuple in cost), supports case-insensitive name lookup, and converts cleanly to a dict when needed. Setting it once on the connection applies to every cursor created from that connection.

For more control, set row_factory to any callable that takes (cursor, row_tuple) and returns any type.

cursor.description is a sequence of column metadata for the last executed SELECT. The first element of each metadata tuple is the column name, which is used to build the dict. Python 3.12 added sqlite3.Connection.create_row_factory registration, but the manual form above works on every supported Python version.

A class factory is also common: turn each row into a domain object.

The factory pattern is how lightweight ORMs build their first layer. For most applications, sqlite3.Row is enough and easier to maintain than a custom factory.

SQLite's type system is unusual. Most databases store each column as exactly the type declared at create time. SQLite uses type affinity: the column declaration is a hint, not a strict rule, and any column can hold any type unless explicitly forbidden.

The five storage classes SQLite recognizes:

A column declared as INTEGER stores numbers efficiently but doesn't prevent inserting a string. DECIMAL(10,2) is parsed and accepted, but it really has NUMERIC affinity, which is roughly "store the most compact form." That looseness is convenient in scripts and a source of subtle bugs in larger apps.

The second row holds strings even though the columns claim to be INTEGER and REAL. SQLite's typeof() function shows the actual storage class. For strict typing, add STRICT to the table definition (SQLite 3.37+):

With STRICT, mismatched types raise an error on insert. For new tables, STRICT is usually preferable; it makes the schema mean what it says.

Two other type notes. SQLite has no native BOOLEAN type: it stores booleans as 0 and 1 integers. And it has no native DATETIME type: dates and times are typically stored as ISO 8601 strings ("2026-05-22T14:30:00") or as Unix timestamps. Python's sqlite3 module supports detect_types=sqlite3.PARSE_DECLTYPES for automatic conversion, but the convention since Python 3.12 is to handle dates explicitly because the built-in conversions had edge cases.

Storing dates as ISO 8601 strings keeps the database portable and readable, and datetime.fromisoformat rebuilds the Python object cleanly on the way back.

SQLite is transactional. Every statement runs inside a transaction, and changes only become visible to other readers (and only become durable) when the transaction commits. The sqlite3 module's behavior around when transactions begin and end is the source of more confusion than any other part of the API, so it deserves a careful look.

The default isolation_level on a sqlite3.Connection is "" (empty string), which means the module opens an implicit transaction the first time a data-modifying statement runs. The transaction stays open until conn.commit() or conn.rollback() is called. Pure SELECTs don't start a transaction.

The rollback threw away the keyboard insert. The first two survive because they were committed before the rollback. That's the basic flow.

The cleaner way to write the same logic is to use the connection as a context manager. It commits on normal exit and rolls back if an exception escapes the block.

The with conn: form is the idiomatic way to wrap a unit of work. It removes the need to remember conn.commit() and guarantees a rollback on any error.

One quirk to know: sqlite3 historically had isolation_level="" and intercepted DDL statements like CREATE TABLE to commit any open transaction before running them. Python 3.12 added autocommit as the modern, explicit alternative.

With autocommit=False, transactions begin explicitly (conn.execute("BEGIN")) or implicitly on the first statement, and commit only on an explicit call. autocommit=True flushes every statement on its own. autocommit=sqlite3.LEGACY_TRANSACTION_CONTROL is the old default.

For new code on Python 3.12+, prefer autocommit=False and the with conn: pattern. On older Python, the same pattern still works with the legacy default; the difference is mostly in how DDL interacts with transactions.

SQLite uses file-level locking. Only one writer can be active at a time. By default, a write lock blocks for up to 5 seconds before raising sqlite3.OperationalError: database is locked. The timeout is configurable: sqlite3.connect(path, timeout=30) waits 30 seconds. For a single-writer app, the default is plenty. For workloads with multiple writers, switch to PostgreSQL.

SQLite supports many concurrent readers, but only one writer at a time. The default journal mode (rollback journal) serializes writes through a single file lock. For applications that need overlapping reads and writes (a web service that reads on most requests and writes occasionally), the Write-Ahead Logging (WAL) journal mode is a meaningful improvement.

With WAL on, readers don't block the writer and the writer doesn't block readers. A single writer is still the limit, but the writer no longer blocks readers while it's running. The setting persists in the database file, so it only needs to be enabled once per database.

A few more pragmas to know on a fresh connection:

Foreign keys are easy to miss. SQLite parses FOREIGN KEY clauses but ignores them at write time unless foreign_keys is ON, and the pragma applies per connection. Set it as soon as the connection opens, before doing any work.

A SQLite database is a single file on disk. It can be copied like any other file, but only when no writer is active. The safer approach is the backup API, which produces a consistent snapshot even with an open writer.

source.backup(target) copies the database page by page into the target connection's database. It can copy to an in-memory database too, which is one way to load a small on-disk database into memory for fast tests.

For text-format dumps (for inspection or for version control), conn.iterdump() yields the SQL statements needed to recreate the database.

The resulting shop.sql is plain text and can be loaded into any SQLite database with the sqlite3 CLI or replayed in Python.

A bigger picture pulls the pieces together: open a connection, set sensible pragmas, create a schema, insert data with executemany, query with sqlite3.Row so columns are accessible by name, and wrap the writes in a transaction so an error rolls everything back.

executescript runs several semicolon-separated statements at once, which is handy for schema setup. INSERT OR REPLACE makes the seed function idempotent: running it twice doesn't fail on duplicate primary keys. The with conn: block wraps both inserts in a single transaction, so the database moves from "empty schema" to "fully seeded" in one atomic step. The list_in_stock function reads through sqlite3.Row, so the format string accesses columns by name.

The end-to-end shape from this lesson:

A small helper to open the connection (with pragmas and a row factory) is the difference between scripts that read clearly and scripts that scatter setup across every file. Most codebases that use SQLite end up with something close to the open_connection function above.