`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
Allows for the creation of custom SQLite functions in Python
Supports dictionary-like row access with sqlite3.Row

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.executemany()`	Function	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(
...     "INSERT INTO users (name) VALUES (?)", ("Bob",)
... )
>>> 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 (name) VALUES (?)", (None,)
...     )
...     connection.commit()
>>> except sqlite3.Error as e:
...     print("An error occurred:", e)
...     connection.rollback()
...

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

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 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.

sqlite3