sqlite3

sqlite3.connect(database, timeout=5.0, detect_types=0, isolation_level='DEFERRED', check_same_thread=True, factory=sqlite3.Connection, cached_statements=128, uri=False, autocommit=sqlite3.LEGACY_TRANSACTION_CONTROL)

The sqlite3 module provides an interface to SQLite databases. SQLite is a C library that implements a lightweight, disk-based database that doesn’t require a separate server process. The module conforms to the DB-API 2.0 specification (PEP 249), making it straightforward to switch to larger databases like PostgreSQL or Oracle if needed.

sqlite3.connect()

This is the primary function for creating a database connection. It opens a connection to the specified database file, creating the file if it doesn’t exist.

Syntax

sqlite3.connect(database, timeout=5.0, detect_types=0, isolation_level='DEFERRED', check_same_thread=True, factory=sqlite3.Connection, cached_statements=128, uri=False, autocommit=sqlite3.LEGACY_TRANSACTION_CONTROL)

Parameters

Parameter	Type	Default	Description
database	path-like	—	Path to the SQLite database file. Use :memory: for an in-memory database
timeout	float	5.0	Seconds to wait before raising OperationalError when a table is locked
detect_types	int	0	Controls type detection. Set to PARSE_DECLTYPES or PARSE_COLNAMES (or both with |) to enable custom type conversion
isolation_level	str or None	'DEFERRED'	Transaction mode: 'DEFERRED', 'EXCLUSIVE', 'IMMEDIATE', or None to disable autocommit
check_same_thread	bool	True	If True, raises ProgrammingError if the connection is used from a different thread
factory	Connection	sqlite3.Connection	Custom Connection subclass to use
cached_statements	int	128	Number of statements to cache internally
uri	bool	False	If True, interpret database as a URI
autocommit	bool	LEGACY_TRANSACTION_CONTROL	Control transaction behavior (added in Python 3.12)

Returns

Connection object representing the database connection.

Examples

Basic file-based database

import sqlite3

# Create or open a database file
conn = sqlite3.connect('myapp.db')
print(type(conn))
# <class 'sqlite3.Connection'>

# Always close when done
conn.close()

In-memory database

# Useful for testing or temporary data
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
cursor.execute('CREATE TABLE test(x INTEGER)')
cursor.execute('INSERT INTO test VALUES (1)')
conn.commit()

result = cursor.execute('SELECT * FROM test').fetchone()
print(result)
# (1,)

Connection Objects

The Connection object represents a database connection and provides methods for creating cursors and managing transactions.

Connection.cursor()

Creates and returns a Cursor object for executing SQL statements.

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

Connection.commit()

Commits any pending transaction to the database. Changes are not saved until you call this method.

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
cursor.execute('CREATE TABLE users(name TEXT, age INTEGER)')
cursor.execute('INSERT INTO users VALUES (?, ?)', ('Alice', 30))
conn.commit()  # Save the changes

Connection.close()

Closes the database connection. If there are uncommitted changes, they are rolled back (unless autocommit is enabled).

conn = sqlite3.connect('myapp.db')
# ... do stuff ...
conn.close()

Connection.execute() and Connection.executemany()

Convenience methods that create a cursor, execute the statement, and return the cursor.

# Single statement - returns cursor with results
cursor = conn.execute('SELECT * FROM users WHERE age > ?', (25,))

# Multiple statements - returns cursor
cursor = conn.executemany('INSERT INTO users VALUES (?, ?)', [('Bob', 35), ('Carol', 28)])

Cursor Objects

Cursor objects are used to execute SQL statements and fetch results.

Cursor.execute(sql, parameters=())

Executes a single SQL statement. Use parameter substitution to safely bind values.

cursor.execute('CREATE TABLE products(id INTEGER PRIMARY KEY, name TEXT, price REAL)')
cursor.execute('INSERT INTO products (name, price) VALUES (?, ?)', ('Widget', 19.99))

Cursor.executemany(sql, parameters)

Executes a SQL statement multiple times with different parameter sets.

products = [
    ('Gadget', 29.99),
    ('Gizmo', 15.00),
    ('Thingamajig', 9.99),
]
cursor.executemany('INSERT INTO products (name, price) VALUES (?, ?)', products)
conn.commit()

Cursor.fetchone()

Fetches the next row of a query result. Returns a tuple or None if no more rows.

cursor.execute('SELECT name, price FROM products WHERE price < ?', (20,))
row = cursor.fetchone()
while row:
    print(f'{row[0]}: ${row[1]}')
    row = cursor.fetchone()
# Widget: $19.99
# Gizmo: $15.0
# Thingamajig: $9.99

Cursor.fetchall()

Fetches all remaining rows of a query result. Returns a list of tuples.

cursor.execute('SELECT * FROM products')
all_rows = cursor.fetchall()
print(all_rows)
# [(1, 'Widget', 19.99), (2, 'Gizmo', 15.0), (3, 'Thingamajig', 9.99)]

Cursor.fetchmany(size=cursor.arraysize)

Fetches the next set of rows. Default size is the cursor’s arraysize (typically 1).

cursor.execute('SELECT * FROM products')
batch = cursor.fetchmany(2)
print(batch)
# [(1, 'Widget', 19.99), (2, 'Gizmo', 15.0)]

Row Objects and Row Factories

By default, query results are returned as tuples. You can change this behavior using row factories.

Using Row as a Row Factory

The Row class provides dictionary-like access to columns by name.

conn = sqlite3.connect(':memory:')
conn.row_factory = sqlite3.Row

cursor = conn.cursor()
cursor.execute('CREATE TABLE users(name TEXT, age INTEGER)')
cursor.execute('INSERT INTO users VALUES (?, ?)', ('Alice', 30))
cursor.execute('SELECT * FROM users')

row = cursor.fetchone()
print(row['name'], row['age'])
# Alice 30

# Also supports iteration
for column in row:
    print(column)
# Alice
# 30

Custom Row Factory

You can create your own row factory for more control.

def dict_factory(cursor, row):
    columns = [desc[0] for desc in cursor.description]
    return dict(zip(columns, row))

conn = sqlite3.connect(':memory:')
conn.row_factory = dict_factory

cursor = conn.cursor()
cursor.execute('CREATE TABLE users(name TEXT, age INTEGER)')
cursor.execute('INSERT INTO users VALUES (?, ?)', ('Alice', 30))
cursor.execute('SELECT * FROM users')

row = cursor.fetchone()
print(row)
# {'name': 'Alice', 'age': 30}

Common Patterns

Context Manager

Use Python’s with statement to automatically close connections.

with sqlite3.connect('myapp.db') as conn:
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM users')
    users = cursor.fetchall()
# Connection automatically closed and committed

Parameterized Queries

Always use parameter substitution (? for positional, :name for named) to prevent SQL injection.

# Bad - DON'T do this
# cursor.execute(f"SELECT * FROM users WHERE name = '{user_input}'")

# Good - use parameterized queries
cursor.execute('SELECT * FROM users WHERE name = ?', (user_input,))
cursor.execute('SELECT * FROM users WHERE name = :name', {'name': user_input})

Transaction Handling

Changes are only persisted after calling commit(). Use try/except to handle failures.

try:
    cursor.execute('INSERT INTO accounts (id, balance) VALUES (?, ?)', (1, 1000))
    cursor.execute('UPDATE accounts SET balance = balance - ? WHERE id = ?', (100, 1))
    cursor.execute('UPDATE accounts SET balance = balance + ? WHERE id = ?', (100, 2))
    conn.commit()
except Exception as e:
    conn.rollback()
    print(f'Transaction failed: {e}')

Errors

Error	Description
OperationalError	Database locked or other operational issues (e.g., timeout waiting for lock)
ProgrammingError	Invalid SQL or connection used in wrong thread when check_same_thread=True
IntegrityError	Foreign key violation or other constraint failure
DatabaseError	General database error

import sqlite3

try:
    conn = sqlite3.connect(':memory:')
    cursor = conn.cursor()
    cursor.execute('CREATE TABLE t(a INTEGER PRIMARY KEY)')
    cursor.execute('INSERT INTO t VALUES (1)')
    cursor.execute('INSERT INTO t VALUES (1)')  # Duplicate key
except sqlite3.IntegrityError as e:
    print(f'Integrity error: {e}')
finally:
    conn.close()

See Also

• csv
• json
• pickle