Python sqlite3 — Database Basics

sqlite3 is Python’s built-in interface to SQLite, a tiny, serverless SQL database stored in a single file (or in memory). No setup, no server — perfect for scripts, prototypes, tests, and small apps.

quickstart.py

import sqlite3
 
conn = sqlite3.connect(":memory:")     # database that lives in RAM
cur = conn.cursor()
cur.execute("CREATE TABLE users (id INTEGER, name TEXT)")
cur.execute("INSERT INTO users VALUES (1, 'Ada')")
conn.commit()
 
cur.execute("SELECT * FROM users")
print(cur.fetchall())                  # [(1, 'Ada')]
conn.close()

The workflow

Every sqlite3 program follows the same shape:

1. Connect to a database file (or :memory:).
2. Get a cursor to run SQL.
3. execute statements.
4. commit changes that modify data.
5. fetch results from queries.
6. close the connection.

Object / method	Purpose
sqlite3.connect(path)	Open (or create) a database.
conn.cursor()	Create a cursor to run statements.
cur.execute(sql, params)	Run one SQL statement.
cur.executemany(sql, seq)	Run a statement for many rows.
cur.fetchone() / fetchall()	Get one row / all rows.
conn.commit()	Save pending changes.
conn.close()	Close the connection.

Creating tables and inserting

create_insert.py

import sqlite3
 
conn = sqlite3.connect("app.db")       # creates app.db if missing
cur = conn.cursor()
 
cur.execute("""
    CREATE TABLE IF NOT EXISTS books (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        year INTEGER
    )
""")
 
cur.execute("INSERT INTO books (title, year) VALUES (?, ?)", ("Dune", 1965))
conn.commit()
print("Inserted row id:", cur.lastrowid)
conn.close()

Parameterized queries — never use string formatting

Always pass values with ? placeholders. Building SQL with f-strings or + opens the door to SQL injection and quoting bugs.

parameters.py

import sqlite3
 
conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute("CREATE TABLE users (name TEXT, age INTEGER)")
 
# SAFE: values are passed separately
name = "Ada'; DROP TABLE users; --"     # malicious input
cur.execute("INSERT INTO users VALUES (?, ?)", (name, 30))
 
# Insert many rows at once
rows = [("Bob", 25), ("Cara", 41)]
cur.executemany("INSERT INTO users VALUES (?, ?)", rows)
conn.commit()
 
cur.execute("SELECT * FROM users WHERE age > ?", (28,))
print(cur.fetchall())   # [("Ada'; DROP TABLE users; --", 30), ('Cara', 41)]
conn.close()

Rule: the SQL string stays constant; data goes through ? parameters.

Reading results

reading.py

import sqlite3
 
conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute("CREATE TABLE nums (n INTEGER)")
cur.executemany("INSERT INTO nums VALUES (?)", [(i,) for i in range(5)])
conn.commit()
 
cur.execute("SELECT n FROM nums")
print(cur.fetchone())     # (0,)  -> first row only
print(cur.fetchall())     # [(1,), (2,), (3,), (4,)]  -> remaining rows
 
# A cursor is iterable — stream rows without loading all at once
cur.execute("SELECT n FROM nums")
for (value,) in cur:
    print(value, end=" ")  # 0 1 2 3 4
conn.close()

Rows as dictionaries

By default rows are tuples. Set a row_factory to access columns by name.

row_factory.py

import sqlite3
 
conn = sqlite3.connect(":memory:")
conn.row_factory = sqlite3.Row         # rows behave like dicts
cur = conn.cursor()
cur.execute("CREATE TABLE p (id INTEGER, name TEXT)")
cur.execute("INSERT INTO p VALUES (1, 'Ada')")
 
row = cur.execute("SELECT * FROM p").fetchone()
print(row["name"])    # Ada
print(row["id"])      # 1
conn.close()

Connections as context managers

Using with conn: automatically commits on success or rolls back on error.

context_manager.py

import sqlite3
 
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE t (x INTEGER)")
 
with conn:                              # commits automatically
    conn.execute("INSERT INTO t VALUES (?)", (10,))
 
print(conn.execute("SELECT * FROM t").fetchall())  # [(10,)]
conn.close()

sketch What an index is worth: 2,181x on 200,000 rows p5.js Pause Restart

The same query, the same data, run before and after adding one index. Without it SQLite reads every row and compares -- the query plan literally says SCAN. With it, the plan says SEARCH USING INDEX and the work becomes a lookup in a sorted structure. Measured 18.106 ms against 0.0083 ms. The gap grows with the table: a scan is linear in rows, an index lookup is logarithmic, so this is the difference between a query that survives growth and one that does not.

Common pitfalls

• Forgetting commit() — changes vanish when the program ends. (A with conn: block handles this.)
• String-built SQL — always parameterize with ? to stay safe.
• A single value tuple needs a comma — (28,), not (28).
• :memory: databases disappear when the connection closes — fine for tests, not for persistence.

Practice Exercises

Exercise 1 – Create and insert

pch.exerciseTag pch.exerciseTitle

Exercise 2 – Parameterized filter

pch.exerciseTag pch.exerciseTitle

Exercise 3 – Insert many and fetch all

pch.exerciseTag pch.exerciseTitle

Summary

• sqlite3 is a zero-setup SQL database built into Python.
• Follow connect → cursor → execute → commit → fetch → close.
• Always parameterize values with ? to avoid SQL injection.
• Use sqlite3.Row for dict-like rows and with conn: for automatic commit/rollback.