Database Connectivity with sqlite3

Almost every real application stores its data in a database. Python talks to relational databases through a common interface called the DB-API 2.0 (PEP 249): you open a connection, create a cursor, execute SQL, fetch results, commit changes, and close the connection. Once you know this pattern with one database, you know it for MySQL, PostgreSQL, SQL Server and Oracle too.

The easiest place to learn it is SQLite, a complete SQL database stored in a single file. The sqlite3 module is part of the standard library, so there is nothing to install and no server to run — which also means every example in this lesson runs right here in the browser.

The DB-API Workflow

conn = sqlite3.connect("shop.db") — open (or create) a database file; ":memory:" creates a temporary in-memory database.
cur = conn.cursor() — a cursor executes statements and holds results. conn.execute() is a shortcut that creates one for you.
cur.execute(sql, params) runs one statement; cur.executemany(sql, rows) runs it for every row.
fetchone(), fetchmany(n) and fetchall() read results — or simply loop over the cursor.
conn.commit() saves changes, conn.rollback() discards them, conn.close() releases the connection.

Placeholders, Not String Formatting

Always pass values separately from the SQL: execute("SELECT * FROM users WHERE id = ?", (user_id,)). sqlite3 uses ? (positional) or :name (named) placeholders; MySQL and PostgreSQL drivers use %s and %(name)s. The driver sends the values safely, which prevents SQL injection and handles quoting and types for you. Note the trailing comma in (user_id,) — parameters must be a sequence, and (user_id) is just a number in brackets.

Rows, Types and Connections as Context Managers

By default rows come back as tuples. Set conn.row_factory = sqlite3.Row to access columns by name as well as by position. SQLite maps Python None, int, float, str and bytes to NULL, INTEGER, REAL, TEXT and BLOB. Using the connection in a with conn: block wraps it in a transaction — commit on success, rollback on an exception — but does not close it; use contextlib.closing() or call close() yourself.

Examples

Connect, create a table, insert and query

Python

import sqlite3

conn = sqlite3.connect(":memory:")        # use "school.db" for a file on disk
cur = conn.cursor()
cur.execute("""
    CREATE TABLE students (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        course TEXT NOT NULL,
        marks INTEGER CHECK (marks BETWEEN 0 AND 100)
    )
""")
cur.execute("INSERT INTO students (name, course, marks) VALUES (?, ?, ?)", ("Asha", "Python", 91))
print("new id:", cur.lastrowid)
conn.commit()

cur.execute("SELECT id, name, course, marks FROM students")
print(cur.fetchall())
print([column[0] for column in cur.description])
conn.close()

Output

new id: 1
[(1, 'Asha', 'Python', 91)]
['id', 'name', 'course', 'marks']

executemany, the fetch methods and sqlite3.Row

Python

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, category TEXT, price REAL)")
conn.executemany("INSERT INTO products (name, category, price) VALUES (?, ?, ?)", [
    ("Pen", "stationery", 20.0),
    ("Notebook", "stationery", 60.0),
    ("Headphones", "electronics", 1499.0),
    ("Mouse", "electronics", 699.0),
    ("Backpack", "bags", 1250.0),
])
conn.commit()

cur = conn.execute("SELECT name, price FROM products ORDER BY price DESC")
print(cur.fetchone())
print(cur.fetchmany(2))
print(cur.fetchall())                      # whatever is left

conn.row_factory = sqlite3.Row             # rows now support row["column"]
query = """SELECT category, COUNT(*) AS items, ROUND(AVG(price), 1) AS avg_price
           FROM products GROUP BY category ORDER BY category"""
for row in conn.execute(query):
    print(row["category"], row["items"], row["avg_price"])
conn.close()

Output

('Headphones', 1499.0)
[('Backpack', 1250.0), ('Mouse', 699.0)]
[('Notebook', 60.0), ('Pen', 20.0)]
bags 1 1250.0
electronics 2 1099.0
stationery 2 40.0

A database file, named placeholders and context managers

Python

import sqlite3
from contextlib import closing

with closing(sqlite3.connect("library.db")) as conn:     # closing() calls conn.close()
    with conn:                                         # commit on success, rollback on error
        conn.execute("DROP TABLE IF EXISTS books")
        conn.execute("CREATE TABLE books (code TEXT PRIMARY KEY, title TEXT, year INTEGER)")
        conn.execute("INSERT INTO books VALUES (:code, :title, :year)",
                     {"code": "BK-001", "title": "Learning Python Basics", "year": 2026})

# A new connection sees the data because it was committed to the file.
with closing(sqlite3.connect("library.db")) as conn:
    print(conn.execute("SELECT title, year FROM books").fetchone())
    print(conn.execute("SELECT COUNT(*) FROM books").fetchone()[0], "book(s) stored")

Output

('Learning Python Basics', 2026)
1 book(s) stored

Common Mistakes

Building SQL with f-strings or + instead of placeholders, opening the door to SQL injection.
Writing (value) instead of (value,) for a single parameter.
Forgetting conn.commit(), so inserts and updates silently disappear when the program ends.
Assuming with conn: closes the connection — it only commits or rolls back.
Calling fetchall() on huge result sets instead of iterating over the cursor.

Key Points to Remember

DB-API pattern: connect → cursor → execute → fetch → commit → close.
sqlite3 is built in; ":memory:" gives a throwaway database, a file name gives a persistent one.
Always use placeholders (? or :name in sqlite3) for values.
fetchone, fetchmany and fetchall read results; cursors are also iterable.
sqlite3.Row gives name-based column access; with conn: manages the transaction.

Practice the examples

Change an input, predict the result, then compare it with the output. Explain why the result changes.

Try Connect, create a table, insert and query in Webnest Codelab Try executemany, the fetch methods and sqlite3.Row in Webnest Codelab Try A database file, named placeholders and context managers in Webnest Codelab