`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

In this step-by-step tutorial, you'll learn how to connect to different database management systems by using various Python SQL libraries. You'll interact with SQLite, MySQL, and PostgreSQL databases and perform common database queries using a Python application.

basics databases tools

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