Python

Python sqlite3 — Tables, Queries, Parameters, and Transactions

A small program often needs a real table long before it needs a database server: rows it can insert, filter, sort, and total with a query instead of a loop. The sqlite3 module in the standard library gives a program exactly that, with no server to run and no package to install. A database is either held in memory or stored in one file, and the module speaks ordinary SQL through a connection and its execute method. This guide builds a small scores table, runs each core operation on it, shows the exact rows each query returns, and ends with the traps that come from missing rows, flexible column types, and queries built by hand.

Written November 13, 2026 · Tested with Python 3.12.3 · 12 min read

By the end of this guide
  • The main program: create a table and read rows back
  • Parameters: passing values with ? marks
  • Update, delete, and rowcount
  • Row: reading columns by name

Written November 13, 2026

Tested with Python 3.12.3

What this guide is

SQLite is a database engine that runs inside the program that uses it. There is no separate server process, no port, and no account. The sqlite3 module connects to a database, sends it SQL statements, and returns the result rows as Python tuples. The special name ':memory:' opens a database that lives only while the connection is open, which suits tests, experiments, and the programs here. A file path opens a database stored in that one file, which is how data survives between runs. The sample data throughout is a small table named scores, with a name, a city, and a numeric score. Small as it is, it shows every behavior that matters, including one that surprises users coming from text files: a score stored in an integer column comes back as an int, not a string.

The main program: create a table and read rows back

The connect function returns a connection. Its execute method runs one SQL statement and returns a cursor, and the cursor methods fetchone, fetchall, and iteration hand back the result rows. A CREATE TABLE statement names the table and each column with its type. INSERT statements add rows, and a SELECT statement reads them back in the order its ORDER BY clause names:

Python
import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE scores (name TEXT, city TEXT, score INTEGER)")
con.execute("INSERT INTO scores VALUES ('ana', 'Toronto', 9)")
con.execute("INSERT INTO scores VALUES ('ben', 'Calgary', 10)")
rows = con.execute("SELECT name, city, score FROM scores ORDER BY name").fetchall()
print(rows)
print(rows[0])
print(type(rows[0][2]))
con.close()

The exact output:

Output
[('ana', 'Toronto', 9), ('ben', 'Calgary', 10)]
('ana', 'Toronto', 9)
<class 'int'>

Each row is a tuple in the column order the SELECT named, and fetchall returns a list of those tuples. Positional access is the default style: row[0] is the name and row[2] is the score. The last line shows the score's type is int, because the engine stores the value under the column's integer type and converts it back on the way out. Nothing here was saved anywhere permanent: the memory database ends when the connection closes, and a second connect to ':memory:' starts from an empty database with no tables at all.

Parameters: passing values with ? marks

Values belong outside the SQL text. A statement that needs a value carries a ? mark where the value goes, and execute takes the values as a second argument, in order. The engine binds each value to its mark as data, never as SQL text. The executemany method runs one statement once per tuple in a list, which is the bulk insert form:

Python
import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE scores (name TEXT, city TEXT, score INTEGER)")
con.executemany("INSERT INTO scores VALUES (?, ?, ?)", [
    ("ana", "Toronto", 9),
    ("ben", "Calgary", 10),
    ("cy", "Toronto", 7),
])
print(con.execute("SELECT COUNT(*) FROM scores").fetchone())
rows = con.execute("SELECT name, score FROM scores WHERE city = ? ORDER BY score DESC", ("Toronto",)).fetchall()
print(rows)
print(con.execute("SELECT name FROM scores WHERE score > ?", (8,)).fetchall())
con.close()

The exact output:

Config
(3,)
[('ana', 9), ('cy', 7)]
[('ana',), ('ben',)]

The count query returns a one-value tuple, (3,), not the bare number 3, because every result is a row. The city query returned the two Toronto rows in score order, highest first, and the score query returned one-value tuples for the two rows above 8. The values argument is a tuple even for a single value, which is why ("Toronto",) carries its trailing comma. Building a query by joining strings instead is both slower to read and unsafe, as the last section shows.

Update, delete, and rowcount

UPDATE changes rows that match its WHERE clause, and DELETE removes them. Both return a cursor whose rowcount attribute states how many rows the statement touched, which is the check that the clause matched what the code expected. Aggregate functions such as AVG, MAX, and MIN compute over the rows that remain:

Python
import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE scores (name TEXT, score INTEGER)")
con.executemany("INSERT INTO scores VALUES (?, ?)", [("ana", 9), ("ben", 10), ("cy", 7)])
cur = con.execute("UPDATE scores SET score = score + 1 WHERE name = ?", ("cy",))
print(cur.rowcount)
cur = con.execute("DELETE FROM scores WHERE score < ?", (9,))
print(cur.rowcount)
print(con.execute("SELECT name, score FROM scores ORDER BY name").fetchall())
print(con.execute("SELECT AVG(score), MAX(score), MIN(score) FROM scores").fetchone())
con.close()

The exact output:

Output
1
1
[('ana', 9), ('ben', 10)]
(9.5, 10, 9)

The update raised the one matching row from 7 to 8, so its rowcount is 1. The delete then removed rows with a score below 9, which is that same row at 8, so its rowcount is also 1. Two rows remain, and the aggregate row reports their average as the float 9.5, their maximum as 10, and their minimum as 9. A rowcount of 0 is the signal to stop and look: the WHERE clause matched nothing, and the program's idea of the table and the table itself disagree.

Row: reading columns by name

Setting the connection's row_factory to sqlite3.Row changes what a query returns. A Row supports the same positional access as a tuple, and it also answers a column lookup by name, a keys method listing the columns, and conversion to a dict:

Python
import sqlite3

con = sqlite3.connect(":memory:")
con.row_factory = sqlite3.Row
con.execute("CREATE TABLE scores (name TEXT, city TEXT, score INTEGER)")
con.execute("INSERT INTO scores VALUES ('ana', 'Toronto', 9)")
row = con.execute("SELECT name, city, score FROM scores").fetchone()
print(row["name"], row["city"], row["score"])
print(row.keys())
print(dict(row))
print(tuple(row))
con.close()

The exact output:

Output
ana Toronto 9
['name', 'city', 'score']
{'name': 'ana', 'city': 'Toronto', 'score': 9}
('ana', 'Toronto', 9)

The name lookups printed the same values the positions hold, keys listed the three column names in SELECT order, and the dict and tuple conversions show the row holds one set of values behind both styles. Name access survives a change in column order that would silently move a positional index to a different column, at the cost of naming the column in every lookup. The factory is set on the connection before the query runs; rows already fetched keep the form they were fetched in.

Transactions: the with block commits or rolls back

A connection groups statements into a transaction. The connection works as a context manager: leaving the with block in the normal way commits the transaction, and leaving it through an exception rolls the transaction back, so none of its statements take effect. A commit on a plain connection saves pending changes to a file database, and close without a commit discards them:

Python
import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE scores (name TEXT, score INTEGER)")
with con:
    con.execute("INSERT INTO scores VALUES ('ana', 9)")
print(con.execute("SELECT COUNT(*) FROM scores").fetchone())
try:
    with con:
        con.execute("INSERT INTO scores VALUES ('ben', 10)")
        raise RuntimeError("stop")
except RuntimeError as error:
    print(type(error).__name__, error)
print(con.execute("SELECT name FROM scores ORDER BY name").fetchall())
con.close()

The exact output:

Output
(1,)
RuntimeError stop
[('ana',)]

The first block committed, so the count is 1. The second block inserted a row and then raised, the with block rolled that insert back, and the final query still shows the first name alone. This all-or-nothing shape is the right one for a group of statements that must land together, such as an insert into two tables that describe one event. A program that skips the block and never calls commit leaves its file database unchanged, which reads as lost work until the missing commit is found.

A file database: data that survives the connection

Passing a file path instead of ':memory:' stores the database in that file. The data is on disk once the transaction commits, and a later connection to the same path sees the same tables and rows. The sqlite_master table is the engine's own record of what the database holds, and a query against it lists the tables:

Python
import sqlite3, pathlib, tempfile, os

folder = tempfile.mkdtemp()
path = pathlib.Path(folder) / "demo.db"
con = sqlite3.connect(path)
con.execute("CREATE TABLE scores (name TEXT, score INTEGER)")
con.execute("INSERT INTO scores VALUES ('ana', 9)")
con.commit()
con.close()
print(path.exists(), path.name)
again = sqlite3.connect(path)
print(again.execute("SELECT name, score FROM scores").fetchall())
print(again.execute("SELECT name FROM sqlite_master WHERE type = 'table'").fetchall())
again.close()
os.unlink(path)
print(path.exists())

The exact output:

Output
True demo.db
[('ana', 9)]
[('scores',)]
False

The file existed after the first connection closed, the second connection read the committed row back, and the sqlite_master query named the one table. The last two lines remove the file and show it gone. One file holding the whole database makes backup and copying simple: copy the file while no connection is writing to it, and the copy is a complete database.

Three traps: no match, flexible types, and hand-built queries

Trap
The first trap is the empty result. fetchone on a query that matches no row returns None, not an empty tuple and not an error, so code that indexes the result without a check fails on the None. The second trap is type flexibility: a column named INTEGER prefers integer storage, but a text value that cannot convert is stored as text anyway, and typeof reports what was actually stored. The third trap is a wrong column name, which is an OperationalError raised at execute time. The same program ends with the reason parameters matter: a hostile name passed as a bound value is stored as plain text, and the table survives:
Python
import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE scores (name TEXT, score INTEGER)")
con.execute("INSERT INTO scores VALUES ('ana', 9)")
print(con.execute("SELECT name FROM scores WHERE name = 'ana'").fetchone())
print(con.execute("SELECT name FROM scores WHERE name = 'nobody'").fetchone())
try:
    con.execute("INSERT INTO scores VALUES ('ana', 'nine')")
    print(con.execute("SELECT score, typeof(score) FROM scores WHERE name = 'ana' AND score = 'nine'").fetchall())
except sqlite3.Error as error:
    print(type(error).__name__, error)
try:
    con.execute("SELECT missing FROM scores")
except sqlite3.Error as error:
    print(type(error).__name__, error)
name = "x'); DROP TABLE scores; --"
con.execute("INSERT INTO scores VALUES (?, ?)", (name, 1))
print(con.execute("SELECT COUNT(*) FROM scores").fetchone())
print(con.execute("SELECT name FROM scores WHERE score = 1").fetchone())
con.close()

The exact output:

Output
('ana',)
None
[('nine', 'text')]
OperationalError no such column: missing
(3,)
("x'); DROP TABLE scores; --",)

The no-match query returned None. The word nine went into the integer column and stayed text, as typeof proves, so code that totals that column must expect mixed storage from outside data. The missing column raised OperationalError with the column named in the message. The hostile string, bound with a ? mark, became an ordinary row: the count rose to 3, the table still answers queries, and the string reads back unchanged as data. Joined into the SQL text by hand, that same string would have ended the insert statement and started a second one.

Which choice for which job

Job Choice Why
Tests and throwaway data ':memory:' No file to clean up
Data kept between runs A file path One file holds the database
One value in a query ? parameter The value stays data
Many rows at once executemany One statement, many tuples
Reading by column name sqlite3.Row Names survive column reordering
A group that lands together with block Commit or full rollback
Checking an update worked rowcount It counts the matched rows

Checklist

  • Before building a query string by hand, stop; put a ? mark in the statement and pass the value as data.
  • Before indexing a fetchone result, check for None; a query with no matching row returns None, not an empty row.
  • Before trusting a numeric column from outside data, check typeof where it matters; a text value can sit in an integer column.
  • Before reporting an update or delete as done, read rowcount; 0 means the WHERE clause matched nothing.
  • Before closing a file database, commit; uncommitted work is discarded, and a with block states the boundary in code.
  • Before re-running a setup script, expect CREATE TABLE to fail on a table that already exists; check sqlite_master or drop the table first.

Sources

  • Python Standard Library, sqlite3 — connect and the ':memory:' name, execute and executemany with ? parameters, fetchone and fetchall, rowcount, sqlite3.Row and row_factory, and the connection as a context manager that commits or rolls back.
  • SQLite, Datatypes In SQLite — column type affinity, the typeof function, and why a text value can be stored in a column declared INTEGER.

Keep Exploring