Python · Module

sqlite3

Python's sqlite3 module provides a DB-API 2.0 interface for working with SQLite databases. It supports file-backed or in-memory databases and can also run as a simple SQLite shell.

How it works

The sqlite3 module provides Python's DB-API 2.0 interface to SQLite. Open a database with connect(), execute SQL through a connection or cursor, bind values with parameters, and use the connection as a context manager for transaction handling.

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)

Parameters

Name	Type	Description
`database`	`str or path-like object`	Database path or special database name such as ":memory:".
`timeout`	`float`	Optional timeout in seconds before an OperationalError is raised when a table is locked.
`cached_statements`	`int`	Optional number of statements cached internally for the connection.

Returns

The sqlite3.connect() function returns a Connection object.

Key points

sqlite3 gives Python code a standard DB-API 2.0 interface for SQLite.
Use connect() to open a database and obtain a Connection object.
Connecting to a filesystem path creates the database file when that path is absent.
Python can launch sqlite3 as a basic command-line SQLite shell.
The module's command-line form accepts optional help/version flags, a filename, and SQL.

Common mistakes

An in-memory database disappears when its connection closes, so it is unsuitable for persistence.

Example: Use sqlite3 with a context-managed connection

import sqlite3

with sqlite3.connect(":memory:") as connection:
    connection.execute("CREATE TABLE items (name TEXT, quantity INTEGER)")
    connection.execute("INSERT INTO items VALUES (?, ?)", ("notebook", 3))
    rows = connection.execute("SELECT name, quantity FROM items").fetchall()

print(rows)

Creates an in-memory database, inserts a parameterized row, queries it, and commits or rolls back automatically when the connection context ends.

When to use it

Embedded file-backed storage

Small applications or scripts need local persistence without a database server.

In-memory test databases

Tests need fast, isolated temporary data.

Command-line SQLite shell

A script needs to inspect or modify SQLite data interactively.

Interview questions

QWhat does sqlite3.connect() provide?

sqlite3.connect() returns a Connection representing the database. SQL can be run with Connection.execute(), which creates and uses a cursor internally; explicit cursors are available when their methods or iteration are useful.

Follow-up: How should values be supplied safely? Use qmark or named placeholders and pass the values separately, rather than formatting them into the SQL string.

QHow does a Connection context manager handle transactions?

A connection context manager commits the transaction if the block completes normally and rolls it back if an exception leaves the block. It does not close the connection, so close it afterward or use an outer resource-management pattern.

Follow-up: What happens to an in-memory database after close()? Its data is lost because the database exists only for the lifetime of the connection.

QWhat problem does sqlite3 solve?

The module follows Python's DB-API 2.0 interface and supports SQLite databases without a separate server process. A database file is created when connecting to a path that does not already exist.

Follow-up: How can sqlite3 be used without creating a Python script? Invoke it with the interpreter's -m switch to provide a simple SQLite shell.

Connection
Cursor
sqlite3.connect
sqlite3.Row
sqlite3.IntegrityError
sqlite3.OperationalError
DB-API 2.0
PEP 249

Source: sqlite3 — DB-API 2.0 interface for SQLite databases — Python. Checked 2026-10-16.