`sqlite3`

The Python sqlite3 module provides an interface for interacting with SQLite databases, which are lightweight, serverless, and self-contained. This module allows you to effortlessly create, manage, and query SQLite databases from Python code.

Here’s a quick example:

>>> import sqlite3

>>> with sqlite3.connect(":memory:") as connection:
...     cursor = connection.cursor()
...     cursor.execute(
...     "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)"
...     )
...     cursor.execute(
...         "INSERT INTO users (name) VALUES ('Alice')"
...     )
...     connection.commit()
...

Key Features

Connects to SQLite databases stored in files or in-memory
Executes SQL queries and fetches results
Supports transactions and provides a context manager interface
Controls transactions through the Connection.autocommit attribute, the recommended approach since Python 3.12
Allows for the creation of custom SQLite functions in Python
Supports dictionary-like row access with sqlite3.Row
Doubles as a command-line SQLite shell via python -m sqlite3 (Python 3.12+)

Frequently Used Classes and Functions

Object	Type	Description
`sqlite3.connect()`	Function	Connects to an SQLite database
`sqlite3.Connection`	Class	Represents a connection to the database
`sqlite3.Cursor`	Class	Facilitates query execution and result retrieval
`sqlite3.Row`	Class	Provides dictionary-like access to query results
`sqlite3.Error`	Class	Provides a base class for all SQLite exceptions
`sqlite3.Cursor.executemany()`	Method	Executes the same SQL command for multiple sets of parameters

Examples

Connecting to an SQLite database and creating a table:

>>> import sqlite3

>>> connection = sqlite3.connect("example.db")
>>> cursor = connection.cursor()
>>> cursor.execute(
...     "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)"
... )
>>> connection.commit()

Inserting data into a table:

>>> cursor.execute(
...     "INSERT INTO users (name) VALUES (?)", ("Bob",)
... )
>>> connection.commit()

Querying data from a table:

>>> cursor.execute("SELECT * FROM users")
>>> cursor.fetchall()
[(1, 'Bob')]

Handling errors and transactions:

>>> try:
...     connection = sqlite3.connect("example.db")
...     cursor = connection.cursor()
...     cursor.execute(
...         "INSERT INTO users (id, name) VALUES (?, ?)", (1, "Frank")
...     )
...     connection.commit()
... except sqlite3.Error as e:
...     print("An error occurred:", e)
...     connection.rollback()
...
An error occurred: UNIQUE constraint failed: users.id

Step through a transaction below and watch where a staged row lives: it sits in your connection until .commit() writes it to the file, or disappears when .rollback() discards it.

Using executemany() to insert multiple rows:

>>> users = [("Carol",), ("Dave",), ("Eve",)]
>>> cursor.executemany(
...     "INSERT INTO users (name) VALUES (?)", users
... )
>>> connection.commit()

>>> cursor.execute("SELECT * FROM users")
>>> cursor.fetchall()
[(1, 'Bob'), (2, 'Carol'), (3, 'Dave'), (4, 'Eve')]

>>> connection.close()

Common Use Cases

Storing application data in a local database
Prototyping and testing SQL queries
Creating lightweight, portable data storage solutions

Real-World Example

Suppose you want to manage a contact list. You can use the sqlite3 module to create a database, add contacts, and retrieve them quickly:

import sqlite3
from contextlib import closing

def init_db(connection):
    cursor = connection.cursor()
    cursor.execute(
        "CREATE TABLE IF NOT EXISTS contacts ("
        "id INTEGER PRIMARY KEY, name TEXT, email TEXT)"
    )
    connection.commit()

def add_contacts(connection):
    cursor = connection.cursor()
    cursor.execute(
        "INSERT INTO contacts (name, email) VALUES (?, ?)",
        ("Alice", "alice@example.com")
    )
    cursor.execute(
        "INSERT INTO contacts (name, email) VALUES (?, ?)",
        ("Bob", "bob@example.com")
    )
    connection.commit()

def display_contacts(connection):
    cursor = connection.cursor()
    cursor.execute("SELECT * FROM contacts")
    print(cursor.fetchall())

def main():
    with closing(sqlite3.connect("contacts.db")) as connection:
        init_db(connection)
        add_contacts(connection)
        display_contacts(connection)

if __name__ == "__main__":
    main()

This example demonstrates how the sqlite3 module can be used to store and manage contact information in a local SQLite database.

Tutorial

Introduction to Python SQL Libraries

Connect to SQLite, MySQL, and PostgreSQL databases with Python SQL libraries, then run common queries from a single Python application.

basics databases tools

For additional information on related topics, take a look at the following resources:

Build a Contact Book App With Python, Textual, and SQLite (Tutorial)
Data Management With Python, SQLite, and SQLAlchemy (Tutorial)
SQLite and SQLAlchemy in Python: Move Your Data Beyond Flat Files (Course)
Preventing SQL Injection Attacks With Python (Tutorial)
Introduction to Python SQL Libraries (Quiz)
Data Management With Python, SQLite, and SQLAlchemy (Quiz)

Have a question about this? Mentor AI can show you examples, compare related terms, and point you to tutorials.

By Leodanis Pozo Ramos • Updated Sept. 21, 2026

Python Standard Library Share Feedback

sqlite3