Runnable SQLite Python examples using the sqlite3 module

Python ships with SQLite support in the standard library, so you need no install. Import sqlite3, call sqlite3.connect() to get a connection, create a cursor, run SQL with cur.execute(), and call con.commit() after any write. That's the whole loop.

The examples below are short and self-contained. The runnable ones use an in-memory database, so they leave nothing on disk. Swap ":memory:" for a file name like "tutorial.db" when you want the data to persist.

SQLite Python example: create, insert, query#

Here's the smallest useful program. It opens a database that lives only in memory, creates a table, inserts rows, and reads them back.

import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()

cur.execute("CREATE TABLE movie(title, year, score)")
cur.execute("""
    INSERT INTO movie VALUES
    ('Monty Python and the Holy Grail', 1975, 8.2),
    ('And Now for Something Completely Different', 1971, 7.5)
""")
con.commit()

res = cur.execute("SELECT title, year FROM movie ORDER BY year")
print(res.fetchall())

con.close()

A few things to notice:

• sqlite3.connect("tutorial.db") creates the file in the current working directory if it doesn't exist. ":memory:" creates a database that exists only in memory.
• SQLite's flexible typing means column types are optional. CREATE TABLE movie(title, year, score) is valid as written.
• execute() returns the cursor, so you can chain .fetchall() or .fetchone() onto it, or loop over it directly.

To check that a table exists, query the built-in sqlite_master table. fetchone() returns None when nothing matches:

res = cur.execute("SELECT name FROM sqlite_master WHERE name='spam'")
print(res.fetchone() is None)  # True if there's no table named spam

Python SQLite tutorial: parameters and executemany#

The first example used SQL literals. In real code your values come from variables, and you should bind them with placeholders instead of building strings. The Python docs say to always use placeholders rather than string formatting to avoid SQL injection attacks.

The module's paramstyle is "qmark", so ? is the standard marker. The named style (:name) is also supported.

import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("CREATE TABLE movie(title, year, score)")

# One row, positional placeholders
cur.execute("INSERT INTO movie VALUES(?, ?, ?)", ("Monty Python's Life of Brian", 1979, 8.0))

# Many rows at once
data = [
    ("Monty Python Live at the Hollywood Bowl", 1982, 7.9),
    ("Monty Python's The Meaning of Life", 1983, 7.5),
]
cur.executemany("INSERT INTO movie VALUES(?, ?, ?)", data)
con.commit()

# Named placeholders with a dict
row = cur.execute(
    "SELECT title, score FROM movie WHERE year = :year",
    {"year": 1982},
).fetchone()
print(row)

# A hostile-looking value is just data when bound
evil = "x'; DROP TABLE movie; --"
print(cur.execute("SELECT count(*) FROM movie WHERE title = ?", (evil,)).fetchone())
print(cur.execute("SELECT count(*) FROM movie").fetchone())

con.close()

Placeholders bind values only. They can't stand in for table or column names. If you need a dynamic identifier, validate it against a fixed list in your own code before putting it in the SQL string.

One detail that trips people up: a single parameter still has to be a sequence, so write (evil,) with the trailing comma, not (evil).

Transactions and commit#

This is the part that surprises people coming from other drivers. According to the docs, an INSERT statement implicitly opens a transaction, and that transaction has to be committed before the changes are saved in the database. Call con.commit() on the connection, not the cursor.

The module has two transaction-handling modes:

isolation_level has no effect unless autocommit is left at LEGACY_TRANSACTION_CONTROL, which is the default today.

My advice: leave both at their defaults while you're learning, and commit explicitly after each logical unit of work. The docs' how-to guides include a section on using the connection as a context manager, which is worth reading once you've got the basics down. The "Transaction control" explanation covers the details of both modes.

A related setting is timeout. If another connection has a transaction open that modifies a table, that table is locked until the transaction is committed. Your connection waits up to timeout seconds (default 5.0) and then raises OperationalError. Keep write transactions short.

Fetching results and row factories#

There are three ways to read rows from a cursor:

• fetchone() returns the next row, or None when there are no more.
• fetchall() returns a list of every remaining row.
• Iterating over the cursor gives you one row at a time, which is the better habit for large result sets.

By default each row is a tuple, so SELECT year, title gives you (1971, 'And Now for Something Completely Different'). Unpacking works well for small queries:

import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("CREATE TABLE movie(title, year, score)")
cur.executemany(
    "INSERT INTO movie VALUES(?, ?, ?)",
    [
        ("Monty Python and the Holy Grail", 1975, 8.2),
        ("Monty Python's Life of Brian", 1979, 8.0),
        ("Monty Python's The Meaning of Life", 1983, 7.5),
    ],
)
con.commit()

for year, title in cur.execute("SELECT year, title FROM movie ORDER BY year"):
    print(year, title)

title, year = cur.execute(
    "SELECT title, year FROM movie ORDER BY score DESC"
).fetchone()
print(f"Highest scoring: {title!r}, released in {year}")

con.close()

Tuples get awkward once a query returns more than a few columns, because you're reading values by position. That's what row factories are for. They let you control the type of object each row is returned as, such as something you can index by column name. The official how-to guide "How to create and use row factories" in the sqlite3 documentation walks through it. I'd read that guide before writing your own.

Custom Python types with adapters and converters#

SQLite natively understands a small set of types. For anything else, such as your own class, register an adapter (Python to SQLite) and a converter (SQLite to Python).

• sqlite3.register_adapter(type, adapter) takes a callable that receives your object and must return a value SQLite natively understands.
• sqlite3.register_converter(typename, converter) takes a callable that receives a bytes object and returns your Python object. The type name is matched case-insensitively.
• Converters only run if you pass detect_types to connect(). sqlite3.PARSE_DECLTYPES looks up the converter using the first word of the declared column type.

import sqlite3

class Point:
    def __init__(self, x, y):
        self.x, self.y = x, y
    def __repr__(self):
        return f"Point({self.x}, {self.y})"

def adapt_point(p):
    return f"{p.x};{p.y}"

def convert_point(s):
    x, y = s.split(b";")
    return Point(float(x), float(y))

sqlite3.register_adapter(Point, adapt_point)
sqlite3.register_converter("point", convert_point)

con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
con.execute("CREATE TABLE test(p point)")
con.execute("INSERT INTO test(p) VALUES(?)", (Point(4.0, -3.2),))
con.commit()

print(con.execute("SELECT p FROM test").fetchone()[0])
con.close()

The column is declared as point, so the converter registered under "point" runs when the value is read. There's one catch with computed columns. The docs say generated fields such as MAX(p) come back as str under PARSE_DECLTYPES. Use PARSE_COLNAMES for those, with a column alias like "p [point]". The two flags can be combined with |.

Common mistakes with sqlite3#

Forgetting to commit. The write succeeds on your connection and vanishes when you close it. Call con.commit() after inserts, updates, and deletes.

Formatting values into SQL. An f-string or % in a query is an injection hole. Bind values with ? or :name instead.

Passing a bare value as the parameter. execute(sql, (evil)) is just execute(sql, evil), and a string is treated as a sequence of characters. Use (evil,).

Using one connection across threads. By default check_same_thread=True, and using the connection from another thread raises ProgrammingError. Setting it to False lets you share the connection, but the docs warn that write operations may need to be serialized by you to avoid data corruption. Opening one connection per thread is the simpler path.

Expecting typed values from sqlite_master style queries. Without detect_types, nothing gets converted. Declared types alone don't change what Python gives you.

Hitting OperationalError on a locked table. Another connection holds an open write transaction. Commit sooner, or raise timeout if the wait is legitimate.

Relying on positional arguments in new code. As of Python 3.15, every parameter of connect() except database is keyword-only. Write sqlite3.connect("app.db", timeout=10.0).

Where to find a SQLite Python tutorial, PDF, or GitHub examples#

The most reliable source is the tutorial in the official sqlite3 docs, which builds the same Monty Python movie database used above. It's versioned with each Python release, so you can pick the one matching your interpreter. If you want a PDF, print the docs page to PDF from your browser, and you'll have a copy that matches your version exactly.

For GitHub examples, treat them with some care. Check that they use placeholders and call commit(). Older snippets sometimes build SQL with string formatting, and you shouldn't copy that.

Quick reference#

Before you ship, check these:

• Every value in a query is bound with ? or :name.
• Every write path ends in commit().
• Single parameters have a trailing comma in the tuple.
• Each thread has its own connection, or you've handled serialization yourself.
• The connection is closed when you're done.

Quick answers