Skip to content
Python Central Hub

Python sqlite3 — Database Basics

sqlite3sqlite3 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()
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::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 / methodPurpose
sqlite3.connect(path)sqlite3.connect(path)Open (or create) a database.
conn.cursor()conn.cursor()Create a cursor to run statements.
cur.execute(sql, params)cur.execute(sql, params)Run one SQL statement.
cur.executemany(sql, seq)cur.executemany(sql, seq)Run a statement for many rows.
cur.fetchone()cur.fetchone() / fetchall()fetchall()Get one row / all rows.
conn.commit()conn.commit()Save pending changes.
conn.close()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()
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()
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()
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_factoryrow_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()
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: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()
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()

Common pitfalls

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

Practice Exercises

Exercise 1 – Create and insert

Exercise 2 – Parameterized filter

Exercise 3 – Insert many and fetch all

Summary

  • sqlite3sqlite3 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.Rowsqlite3.Row for dict-like rows and with conn:with conn: for automatic commit/rollback.

If this helped you, consider buying me a coffee ☕

Buy me a coffee

Was this page helpful?

Let us know how we did