Databases with SQLite

SQLite is a full relational database that lives in a single file on disk — there's no separate server process to install or run. Python ships a built-in sqlite3 module, so you get a real SQL database with zero extra installation. It's the natural choice for small applications, embedded storage, prototypes, and anywhere you need structured, queryable persistence without the operational overhead of running a database server.

Connecting to a database

sqlite3.connect() opens (and creates, if it doesn't exist yet) a database file. A Connection gives you a Cursor, which is what actually executes SQL statements.

connect.py

Python

import sqlite3

conn = sqlite3.connect("app.db")
cursor = conn.cursor()

Creating a table

create_table.py

Python

cursor.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE NOT NULL
    )
""")
conn.commit()

Inserting, reading, updating, deleting

All four basic operations go through cursor.execute() with a SQL string. Reads use .fetchone() for a single row or .fetchall() for every matching row.

crud.py

Python

# INSERT
cursor.execute(
    "INSERT INTO users (name, email) VALUES (?, ?)",
    ("Ada Lovelace", "ada@example.com"),
)
conn.commit()

# SELECT
cursor.execute("SELECT id, name, email FROM users WHERE name = ?", ("Ada Lovelace",))
row = cursor.fetchone()
print(row)   # (1, 'Ada Lovelace', 'ada@example.com')

cursor.execute("SELECT id, name FROM users")
for row in cursor.fetchall():
    print(row)

# UPDATE
cursor.execute(
    "UPDATE users SET email = ? WHERE id = ?",
    ("ada.lovelace@example.com", 1),
)
conn.commit()

# DELETE
cursor.execute("DELETE FROM users WHERE id = ?", (1,))
conn.commit()

Parameterized queries: the ? placeholder

Every example above passes values as a separate tuple argument to .execute() rather than gluing them into the SQL string. This is not a style preference — it is the difference between a database that is safe from injection and one that isn't.

Never build SQL with string formatting or f-strings

If you interpolate user input directly into a SQL string, anyone who controls that input can inject their own SQL and read, modify, or delete data they shouldn't have access to.

bad_vs_good.py

Python

# BAD — vulnerable to SQL injection.
# If name = "'; DROP TABLE users; --", this executes attacker-controlled SQL.
name = get_user_input()
cursor.execute(f"SELECT * FROM users WHERE name = '{name}'")

# GOOD — the driver treats the value as data, never as SQL syntax.
cursor.execute("SELECT * FROM users WHERE name = ?", (name,))

? placeholders work for every value, not just strings

Numbers, dates, and booleans should all be passed through `?` placeholders too. Let `sqlite3` handle the type conversion — don't format numbers into the SQL string either.

Committing transactions

Changes made with INSERT, UPDATE, or DELETE are not saved to disk until you call conn.commit(). If your program exits or crashes before committing, those changes are lost. Plain SELECT queries don't need a commit since they don't modify anything.

Using `with` for automatic handling

Wrapping a connection in a with block automatically commits on success or rolls back on an exception — but note it does not close the connection for you, so it's common to nest a with for the transaction inside a connection you close yourself (or also use with sqlite3.connect(...) to guarantee closing).

with_block.py

Python

import sqlite3

with sqlite3.connect("app.db") as conn:
    conn.execute(
        "INSERT INTO users (name, email) VALUES (?, ?)",
        ("Grace Hopper", "grace@example.com"),
    )
    # commits automatically here if no exception was raised;
    # rolls back automatically if one was

SQLite vs a client-server database

Situation	SQLite	PostgreSQL / MySQL
Single application, single process	Great fit	Overkill
Embedded / local storage (desktop apps, mobile, scripts)	Great fit	Not designed for this
Prototyping and small tools	Great fit	Slower to set up
Multiple services or processes writing concurrently	Struggles — file-level locking limits concurrent writers	Built for this
Needs to scale across machines / large datasets	Not designed for this	Built for this

Rule of thumb

Reach for SQLite when your data lives with one application. Reach for PostgreSQL or MySQL as soon as more than one process needs to write to the same data at the same time, or the database needs to live on its own server independent of any single app.