Inserting rows

Adding a row to SQLite starts with INSERT, but not every insert means the same thing. Sometimes you are adding a setup row. Sometimes you are saving values that came from Python. Sometimes you are loading many rows at once. In cache-style code, you may not want another row at all; you may want the latest value to replace the old one.

Different ways to insert data in SQLite

In SQLite, INSERT is the SQL statement you use to add rows to a table. There are several variations. Some append new rows, some skip duplicates, some replace old data, and some update an existing row when a conflict happens. The right choice depends on what kind of data you are storing.

We will look at these variations in order of increasing complexity. But the basic idea is simple: choose the table, choose the columns, and provide the values.

Basic INSERT

The most common form adds one new row to a table.

INSERT INTO weather_readings (location, temperature, conditions)
VALUES ('Dublin', 12.5, 'cloudy');

This is the normal append pattern. Use it when every row is meant to be a new record: logs, history, events, audit trails, or anything where old entries should be preserved.

INSERT with selected columns

You don't have to provide every column. You can name only the columns you want to fill. SQLite will use default values for columns with defaults, and NULL for columns that allow missing values.

INSERT INTO weather_readings (location, temperature)
VALUES ('Dublin', 12.5);

This is usually the safest style to teach because it makes the insert explicit. A reader can see exactly which value goes into which column.

INSERT without column names

SQLite also lets you insert values without naming the columns, but this is fragile. Without column names you must supply a value for every column in the table's exact order, including the id and recorded_at you would normally let SQLite fill in. Miss one, or get the order wrong, and the row either fails or lands values in the wrong columns. For real code, prefer named columns.

INSERT INTO weather_readings
VALUES (1, 'Dublin', 12.5, 'cloudy', 75, 4.5, '2026-05-28 10:00:00');

Multi-row INSERT

A single INSERT statement can add several rows at once.

INSERT INTO weather_readings (location, temperature, conditions)
VALUES
    ('Dublin', 12.5, 'cloudy'),
    ('Cork', 14.1, 'rain'),
    ('Galway', 13.2, 'windy');

This is useful for seed data, examples, setup scripts, and small batches of test data.

Handling conflicts: when a row already exists

The forms so far always add a new row. Sometimes a new row would collide with an existing one, and you want SQLite to do something other than fail. These forms only matter when the table has a uniqueness constraint to conflict against, such as a PRIMARY KEY or UNIQUE column. The cache table built later on this page keys on location (one row per city), so a second Dublin insert conflicts with the first.

SQLite offers four ways to resolve that conflict:

• INSERT OR IGNORE skips the new row and keeps the old one. It does not fail loudly; it simply does nothing, which can hide problems.
• INSERT OR REPLACE deletes the conflicting row and inserts the new one in its place. Simple, but because it is a delete-then-insert, anything tied to the old record (an auto-generated ID, a timestamp, a related row) is lost.
• ON CONFLICT(col) DO UPDATE, the modern upsert, keeps the existing row and updates only the columns you name. This is the precise, production-friendly form.
• ON CONFLICT(col) DO NOTHING is the explicit version of OR IGNORE: it names the one conflict you expect instead of swallowing every constraint error.

The two that matter for a cache are INSERT OR REPLACE and ON CONFLICT DO UPDATE. Rather than dwell on the SQL in the abstract, the Python upsert section later on this page runs both against a real demo database, so you can see exactly what each one does to the stored row.

One further form is worth knowing exists: INSERT INTO ... SELECT copies rows produced by a query from one table into another, which is handy for archiving old rows or building summary tables. The weather project does not need it.

The main teaching distinction

The most important distinction is not the SQL syntax. It is the purpose of the table.

• Logs append. Every reading is a historical record, so each insert creates a new row.
• Caches update. The table stores the latest value for something, so a new reading may replace or update an existing row.

-- Logging: keep every reading
INSERT INTO weather_readings (location, temperature, conditions)
VALUES ('Dublin', 12.5, 'cloudy');

-- Caching: keep one latest reading per location
INSERT INTO weather_cache (location, temperature, conditions, fetched_at)
VALUES ('Dublin', 12.5, 'cloudy', CURRENT_TIMESTAMP)
ON CONFLICT(location) DO UPDATE SET
    temperature = excluded.temperature,
    conditions = excluded.conditions,
    fetched_at = excluded.fetched_at;

That mental model keeps the choice simple. If the table is a history, append. If the table is a latest-value cache, update the existing row when the key already exists.

Running INSERT from Python

The SQL patterns above are the database side of the story. In a Python API project, you usually send those statements through sqlite3, using placeholders for any values that came from Python.

The examples in this section use this chapter's real weather_readings table.

Parameterised inserts for API data

When the values come from Python, use placeholders instead. This version replaces the hardcoded values with ? markers: each ? gets filled in by the matching value from a tuple Python passes alongside the SQL, and SQLite treats every passed value as data, never as code.

Save the full version as insert_demo.py:

import sqlite3

# A single weather reading, as you'd get back from the API.
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 (?, ?, ?, ?, ?)
        """,
        (
            reading["location"],
            reading["temperature"],
            reading["conditions"],
            reading["humidity"],
            reading["wind_speed"],
        ),
    )

That's the safe pattern for values from APIs, users, files, or any other dynamic source. The sqlite3 page later in this chapter covers parameterised queries in more depth.

In the script above, it's worth noticing what's not in the INSERT: the id and recorded_at columns. The INTEGER PRIMARY KEY lets SQLite choose the id, and the DEFAULT CURRENT_TIMESTAMP handles recorded_at, where SQLite stamps the row with the current UTC time at insertion.

Letting the database fill recorded_at is what makes later freshness checks work: the stored value lines up with datetime('now', ...) for lexical comparison.

Batching rows with executemany()

A single INSERT per row works, but if you already have a list of rows, executemany() is clearer and avoids writing the same execute() call over and over:

readings = [
    ("Dublin", 12.5, "cloudy", 75, 4.5),
    ("London", 15.2, "rain", 85, 6.2),
    ("Paris", 18.0, "sunny", 60, 3.1),
]

with sqlite3.connect("weather.db") as conn:
    conn.executemany(
        """
        INSERT INTO weather_readings
            (location, temperature, conditions, humidity, wind_speed)
        VALUES (?, ?, ?, ?, ?)
        """,
        readings,
    )
    print(f"Inserted {len(readings)} readings.")

That's three rows handled through one call, and the savings grow as the list does. Reach for executemany() any time you already have the rows in a list; for the weather cache you will not need it (one reading per call), but for any "ingest a CSV / batch a day's worth of records" job it is the right tool.

When repeated inserts create repeated rows

What happens if you INSERT a Dublin reading, then INSERT another one a few minutes later? Nothing dramatic: SQLite stores both. The weather_readings schema has no constraint that says "Dublin is unique," so the database has no reason to refuse the second row. The auto-incremented id keeps every row distinct as far as SQLite is concerned, so the second insert lands as another row instead of replacing the first.

You can see this directly. The script below clears out any previous Dublin rows for a tidy demo, inserts two new readings back to back, then peeks at the rows for Dublin (a small forward look at SELECT; the next page covers it properly):

import sqlite3

with sqlite3.connect("weather.db") as conn:
    conn.execute("DELETE FROM weather_readings WHERE location = ?", ("Dublin",))

    conn.execute(
        "INSERT INTO weather_readings (location, temperature) VALUES (?, ?)",
        ("Dublin", 12.5),
    )
    conn.execute(
        "INSERT INTO weather_readings (location, temperature) VALUES (?, ?)",
        ("Dublin", 13.2),
    )

    # Peek at what's in the table for Dublin.
    rows = conn.execute(
        "SELECT location, temperature "
        "FROM weather_readings WHERE location = ? "
        "ORDER BY id",
        ("Dublin",),
    ).fetchall()
    for row in rows:
        print(row)

Running it shows two rows for Dublin, with separate id values behind them (even though this quick peek does not display those IDs).

('Dublin', 12.5)
('Dublin', 13.2)

For a log of readings that's the right behaviour: every fetch becomes a new row in the historical record. For a cache it isn't: you want the second reading to replace the first, not sit alongside it. That's the gap the next section closes.

Upserts in action

Earlier, you saw how an upsert can keep one latest row per location. Now you'll run that idea from Python in a separate demo database, weather_cache_demo.db, so the main weather.db file stays focused on the historical weather_readings table.

A cache needs one row per key. For this demo, location is the key: a second Dublin reading should refresh the Dublin row instead of appending another historical reading.

An upsert works here. SQLite inserts when the key is new and updates or replaces when the key already exists.

Create the cache schema

Save this cache-table schema as weather_cache_demo_schema.sql.

It has a uniqueness constraint (a PRIMARY KEY or UNIQUE) on the column that defines "same row"; otherwise SQLite has nothing to detect a conflict on. For a cache keyed by location, that's location:

CREATE TABLE IF NOT EXISTS weather_cache (
    location     TEXT PRIMARY KEY,
    temperature  REAL,
    conditions   TEXT,
    fetched_at   DATETIME DEFAULT CURRENT_TIMESTAMP
);

With location as the primary key, two rows for the same location can't coexist. INSERT OR REPLACE can detect the conflict and rewrite the row. The name sounds like an update, but REPLACE is literal: SQLite removes the conflicting row and inserts a new one.

Create a table in a demo database

Run the schema file once against weather_cache_demo.db before trying the upsert examples. Save this script as create_weather_cache_demo_db.py:

import sqlite3

with open("weather_cache_demo_schema.sql", encoding="utf-8") as f:
    schema = f.read()

with sqlite3.connect("weather_cache_demo.db") as conn:
    conn.executescript(schema)

print("Created weather_cache_demo.db with the weather_cache table.")

Run the helper from the project root:

python create_weather_cache_demo_db.py

You should now see a new weather_cache_demo.db file with a weather_cache table inside, ready for the upsert demos below.

Form 1: replace the whole row with INSERT OR REPLACE

import sqlite3

# If a Dublin row exists, delete it and insert this one in its place.
with sqlite3.connect("weather_cache_demo.db") as conn:
    conn.execute(
        """
        INSERT OR REPLACE INTO weather_cache
            (location, temperature, conditions)
        VALUES (?, ?, ?)
        """,
        ("Dublin", 12.5, "cloudy"),
    )

Whatever was in the Dublin row before is gone; the new row sits where the old one did. fetched_at is stamped fresh because, from SQLite's perspective, this is a brand-new row, and the column's DEFAULT CURRENT_TIMESTAMP applies the way it would for any insert.

Form 2: update selected columns with ON CONFLICT

Sometimes replacing the entire row is too destructive. You may want to preserve certain columns while updating only the fields that changed: a created_at timestamp, a manually edited note, or an internal counter that should keep climbing rather than reset.

That's what ON CONFLICT ... DO UPDATE is for. Instead of deleting the old row and inserting a new one, SQLite keeps the existing row and updates only the columns listed in the SET clause.

import sqlite3

# If a Dublin row exists, keep it and update only these three columns.
# `excluded.X` is the value Python passed for column X.
with sqlite3.connect("weather_cache_demo.db") as conn:
    conn.execute(
        """
        INSERT INTO weather_cache (location, temperature, conditions)
        VALUES (?, ?, ?)
        ON CONFLICT(location) DO UPDATE SET
            temperature = excluded.temperature,
            conditions  = excluded.conditions,
            fetched_at  = CURRENT_TIMESTAMP
        """,
        ("Dublin", 13.2, "overcast"),
    )

The Dublin row keeps its identity here: SQLite walks down the DO UPDATE list and changes those three columns in place. Any column you don't mention survives untouched, which is why fetched_at needs an explicit = CURRENT_TIMESTAMP. Without it, the existing timestamp would stick around even though the row's data has changed.

For a simple cache where every column refreshes together, the simpler form is fine. But for rows containing historical data (a created_at timestamp, a hit counter, or anything you don't want overwritten on every refresh), the surgical form gives you precise control over what changes and what survives.

Both forms also avoid the subtle risk in a manual check-then-insert approach. From SQLite's perspective, each upsert is one atomic operation, with no gap between "check" and "insert" for another writer to interfere.

Where this leaves the project

The main weather_readings table in this chapter takes the append path: every successful fetch becomes a new row so history and simple trends remain possible. The upsert pattern is still worth knowing because it is the shape you would use for a one-row-per-location cache, where freshness matters more than keeping every reading.