Working with SQLite from Python

So far, you have reached for Python's sqlite3 module whenever an example needed it, opening a connection and running a statement to make things work. This page treats that module deliberately: how to open the database, pass values safely, read the rows that come back, and make writes succeed or fail cleanly.

Open a connection to the database

Python talks to SQLite through a connection object. The familiar pattern from earlier pages opens weather.db, gives that open connection the name conn, and lets your code run SQL through it:

import sqlite3

with sqlite3.connect("weather.db") as conn:
    conn.execute("SELECT 1")

A with block doesn't close the database connection, which is fine for short scripts. To close the database explicitly, you can call conn.close() when you are done. We will show you the tricks for that later on this page, in Transaction handling is not connection cleanup.

":memory:"

For tests and throwaway demos, SQLite also supports the special name ":memory:". Instead of opening a database file on disk, SQLite creates a temporary database in RAM. You still use the same sqlite3 API, but the data disappears as soon as the connection closes. That makes ":memory:" a clean way to test real SQL without creating or modifying a database file. Chapter 16 uses this pattern in the Spotify project's test suite.

conn = sqlite3.connect(":memory:")

Send SQL through the connection

The connection object has an execute() method. You pass it a SQL statement, and SQLite runs that statement against the database.

import sqlite3

with sqlite3.connect("weather.db") as conn:
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS weather_readings (
            id           INTEGER PRIMARY KEY,
            location     TEXT NOT NULL,
            temperature  REAL,
            conditions   TEXT,
            humidity     INTEGER,
            wind_speed   REAL,
            recorded_at  DATETIME DEFAULT CURRENT_TIMESTAMP
        )
        """
    )

The SQL still looks like SQL. The Python part is the delivery mechanism: conn.execute() sends the statement to SQLite.

For statements such as CREATE TABLE, the main result is that the database changes. For SELECT statements, conn.execute() returns a cursor you can read rows from.

A cursor lets you read query results

A cursor is the object SQLite gives back when a query may produce rows. You can think of it as the result stream for a SELECT query.

import sqlite3

with sqlite3.connect("weather.db") as conn:
    cursor = conn.execute(
        """
        SELECT location, temperature, conditions
        FROM weather_readings
        ORDER BY recorded_at DESC
        """
    )

    for row in cursor:
        print(row)

You do not need to turn every result into a list. If the query could return many rows, looping over the cursor is often the cleanest shape. SQLite gives you each row as the loop advances.

Fetch the shape you expect

Different queries imply different result shapes. A query for one latest reading should not be handled the same way as a query for a full history table.

• Loop over the cursor when you want to process rows one at a time.
• Use fetchone() when you expect one row, or no row.
• Use fetchall() only when the result set is small enough to load into memory.

For a cache lookup, fetchone() is usually the right fit because the query asks for one latest row for one location.

import sqlite3

location = "Dublin"

with sqlite3.connect("weather.db") as conn:
    row = conn.execute(
        """
        SELECT location, temperature, conditions, recorded_at
        FROM weather_readings
        WHERE location = ?
        ORDER BY recorded_at DESC
        LIMIT 1
        """,
        (location,),
    ).fetchone()

if row is None:
    print("No reading found.")
else:
    print(row)

fetchone() returns a single row, or None if the query found nothing. That None case matters in real application code: an empty cache is not a crash, it is a normal outcome your program should handle.

Values go in as parameters

When a value comes from Python, do not glue it into the SQL string. Keep the SQL structure in the SQL string, and pass the changing values separately as parameters.

import sqlite3

location = "Dublin"

with sqlite3.connect("weather.db") as conn:
    # Avoid this pattern.
    sql = f"SELECT * FROM weather_readings WHERE location = '{location}'"
    row = conn.execute(sql).fetchone()

That version mixes SQL code and data. It is easy to break with quotes, awkward to read, and unsafe when values come from users, APIs, files, or other external systems.

The safe version uses a ? placeholder in the SQL and passes the Python values as a tuple.

import sqlite3

location = "Dublin"

with sqlite3.connect("weather.db") as conn:
    row = conn.execute(
        """
        SELECT location, temperature, conditions, recorded_at
        FROM weather_readings
        WHERE location = ?
        ORDER BY recorded_at DESC
        LIMIT 1
        """,
        (location,),
    ).fetchone()

The comma in (location,) matters. It tells Python this is a one-value tuple, not just parentheses around a string.

You can also use named placeholders. This is useful when a query has several values and you want each one to be explicit.

import sqlite3

reading = {
    "location": "Dublin",
    "temperature": 12.5,
    "conditions": "cloudy",
    "humidity": 75,
    "wind_speed": 4.5,
}

with sqlite3.connect("weather.db") as conn:
    conn.execute(
        """
        INSERT INTO weather_readings
            (location, temperature, conditions, humidity, wind_speed)
        VALUES
            (:location, :temperature, :conditions, :humidity, :wind_speed)
        """,
        reading,
    )

The principle is the same in both forms: SQL describes the operation; parameters carry the values. That prevents SQL injection and avoids broken string-built SQL.

Writes happen inside transactions

A transaction is a group of database changes that should succeed or fail together. If the transaction succeeds, you commit it. If something goes wrong, you roll it back.

• commit() saves the transaction and makes the changes permanent.
• rollback() cancels the transaction and undoes changes that have not been committed.
• The with sqlite3.connect(...) as conn: block you have already been using is itself a transaction. It commits if the block finishes normally and rolls back if an exception is raised, so for the simple writes you have seen so far, you do not call commit() or rollback() yourself.

import sqlite3

with sqlite3.connect("weather.db") as conn:
    conn.execute(
        """
        INSERT INTO weather_readings (location, temperature, conditions)
        VALUES (?, ?, ?)
        """,
        ("Dublin", 12.5, "cloudy"),
    )
    conn.execute(
        """
        INSERT INTO weather_readings (location, temperature, conditions)
        VALUES (?, ?, ?)
        """,
        ("London", 15.2, "rain"),
    )
# Either both rows are saved, or neither is.

This is the safest beginner pattern for writes: the with block is the transaction. If it finishes normally, SQLite commits. If an exception is raised, SQLite rolls the transaction back. That is why every write example in this chapter has done its work inside one of these blocks.

Transaction handling is not connection cleanup

One detail many SQLite tutorials blur: using a SQLite connection in a with block handles the transaction, not the connection's lifetime. If the block finishes successfully, SQLite commits the transaction. If an exception happens inside the block, SQLite rolls the transaction back. But the connection itself is not closed automatically.

The earlier pages in this chapter used the bare form on purpose; it keeps small examples small and for short scripts, that is usually fine because the Python process exits immediately. The operating system reclaims the connection when the process ends. However, in reusable application code, connection cleanup should be explicit.

The most explicit way to close a connection reliably is try/finally. It makes the connection's lifetime clear and gives you a place to add logging or error handling around the close.

import sqlite3

conn = sqlite3.connect("weather.db")

try:
    conn.execute(
        """
        SELECT location, temperature
        FROM weather_readings
        LIMIT 1
        """
    )
finally:
    conn.close()

That works, but Python has a more idiomatic alternative: contextlib.closing(). It gives you a context manager whose only job is to call conn.close() when the outer block ends. The result is the same as try/finally, with less boilerplate, and the cleanup runs even if the block exits via an unexpected exception.

from contextlib import closing
import sqlite3

with closing(sqlite3.connect("weather.db")) as conn:
    with conn:
        conn.execute(
            """
            INSERT INTO weather_readings (location, temperature, conditions)
            VALUES (?, ?, ?)
            """,
            ("Dublin", 12.5, "cloudy"),
        )

Wrapping the connect call in closing(...) changes what the outer with block is responsible for. The outer block now uses closing's context manager, whose job is to close the connection when the block ends.

That is why the example has an inner with conn: block. The inner block brings back SQLite's transaction handling: commit if the database work succeeds, roll back if an exception happens. Now the two responsibilities are visible separately. closing(...) handles connection cleanup. with conn: handles the transaction.

sqlite3.Row lets you read columns by name

By default, SQLite result rows behave like tuples. That works, but it can make application code fragile because you have to remember column positions.

import sqlite3

with sqlite3.connect("weather.db") as conn:
    row = conn.execute(
        """
        SELECT location, temperature, conditions
        FROM weather_readings
        ORDER BY recorded_at DESC
        LIMIT 1
        """
    ).fetchone()

print(row[0])  # location
print(row[1])  # temperature
print(row[2])  # conditions

The comments help, but the code is still tied to column order. If the query changes, row[0] and row[1] may no longer mean what you think they mean.

Set conn.row_factory = sqlite3.Row, and SQLite lets you read columns by name.

import sqlite3

with sqlite3.connect("weather.db") as conn:
    conn.row_factory = sqlite3.Row

    row = conn.execute(
        """
        SELECT location, temperature, conditions
        FROM weather_readings
        ORDER BY recorded_at DESC
        LIMIT 1
        """
    ).fetchone()

print(row["location"])
print(row["temperature"])
print(row["conditions"])

This is much nicer for application code and for the cache class you will build later. The row now reads like the data it represents, not like a mystery tuple.

Expected database errors have names

Not every database error should be treated the same way. SQLite gives common failure modes specific exception names, which makes error handling clearer.

• sqlite3.IntegrityError usually means a constraint failed: a duplicate primary key, a missing NOT NULL value, or a foreign key problem.
• sqlite3.OperationalError usually means SQLite could not run the operation: a missing table, a locked database, a syntax problem, or another setup/runtime issue.

import sqlite3

try:
    with sqlite3.connect("weather.db") as conn:
        with conn:
            conn.execute(
                """
                INSERT INTO weather_readings (location, temperature, conditions)
                VALUES (?, ?, ?)
                """,
                (None, 12.5, "cloudy"),
            )
except sqlite3.IntegrityError as exc:
    print(f"The row broke a database rule: {exc}")
except sqlite3.OperationalError as exc:
    print(f"SQLite could not run the operation: {exc}")

For a small script, printing the error may be enough. In an application, these exceptions become part of your boundary: catch the database problem, add context, and return a useful application-level error instead of letting a raw database traceback leak everywhere.

The final read pattern

The pieces now fit together. For a read operation, open the connection, set the row factory, run parameterised SQL, fetch the expected shape, handle the empty result, and close the connection deliberately.

from contextlib import closing
import sqlite3

DB_PATH = "weather.db"

location = "Dublin"

with closing(sqlite3.connect(DB_PATH)) as conn:
    conn.row_factory = sqlite3.Row

    row = conn.execute(
        """
        SELECT location, temperature, conditions, recorded_at
        FROM weather_readings
        WHERE location = ?
        ORDER BY recorded_at DESC
        LIMIT 1
        """,
        (location,),
    ).fetchone()

if row is None:
    print("No cached reading found.")
else:
    print(row["location"])
    print(row["temperature"])
    print(row["conditions"])
    print(row["recorded_at"])

This is the shape you want when the database is answering a question. There is no write transaction because the code is only reading.

The final write pattern

For a write operation, add a transaction block inside the open connection. That makes the database change deliberate and gives SQLite a clean way to commit or roll back.

from contextlib import closing
import sqlite3

DB_PATH = "weather.db"

reading = {
    "location": "Dublin",
    "temperature": 12.5,
    "conditions": "cloudy",
}

with closing(sqlite3.connect(DB_PATH)) as conn:
    with conn:
        conn.execute(
            """
            INSERT INTO weather_readings (location, temperature, conditions)
            VALUES (:location, :temperature, :conditions)
            """,
            reading,
        )

This pattern is small, but it carries a lot of good habits: deliberate connection cleanup, transaction-aware writes, parameterised values, and SQL that stays readable.

Where this leaves the project

You now have the Python side of the database boundary. The earlier pages taught the SQL shapes: INSERT for storing rows and SELECT for asking questions. This page adds the application pattern that lets Python use those statements safely.

• The connection is the handle. It is how Python talks to the SQLite file.
• conn.execute() sends SQL. For queries, the returned cursor lets you read rows.
• Parameters carry values. Do not build SQL by gluing strings together.
• Transactions protect writes. A write should commit completely or roll back cleanly.
• Cleanup is separate. with conn: handles transaction behaviour; closing() handles connection closing.
• sqlite3.Row improves application code. Reading row["temperature"] is safer than guessing what row[1] means.

The next step is to wrap these habits in a small cache class, so the rest of your application can ask for weather data without repeating connection, transaction, and SQL details everywhere.