Python sqlite3 Tutorial: SQLite Database CRUD with Examples

By Engr. Henry Akpan

A Real Database in Your Python Program

Text files and CSV files are fine for small amounts of data, but as soon as you need to search, sort, update records safely or link related data together, you need a database. Python comes with one built in: SQLite, a complete SQL database stored in a single file, with no server to install. It is used inside Android apps, web browsers and countless desktop tools. This tutorial shows how to use SQLite from Python with the standard sqlite3 module: connecting, creating tables, inserting data safely with placeholders, querying and fetching rows, updating and deleting, transactions with commit and rollback, accessing columns by name, linking tables with foreign keys and JOINs, and a complete student-records example. It assumes basic SQL from the tutorial on SQL basics. All outputs shown were produced by running the code on Python 3.13.

Connecting and Creating a Table

sqlite3.connect() opens a database file, creating it if it does not exist. (Use the special name ":memory:" for a temporary database held in RAM.) A cursor runs SQL statements.

import sqlite3

conn = sqlite3.connect("school.db")
cur = conn.cursor()

cur.execute("""
    CREATE TABLE IF NOT EXISTS students (
        id      INTEGER PRIMARY KEY AUTOINCREMENT,
        name    TEXT NOT NULL,
        dept    TEXT NOT NULL,
        level   INTEGER CHECK (level IN (100, 200, 300, 400)),
        cgpa    REAL
    )
""")
conn.commit()
print("Table ready")

Output:

Table ready

IF NOT EXISTS means the script can run again without an error. The CHECK constraint makes the database itself reject an invalid level.

Inserting Data Safely with Placeholders

Always pass values with ? placeholders and a tuple of values. Never build SQL by pasting user input into a string with an f-string or +.

cur.execute(
    "INSERT INTO students (name, dept, level, cgpa) VALUES (?, ?, ?, ?)",
    ("Ada Obi", "Computer Science", 200, 4.52),
)
print("New id:", cur.lastrowid)

more = [
    ("Bayo Adeyemi", "Physics", 300, 3.85),
    ("Chidi Eze", "Computer Science", 300, 3.10),
    ("Dami Okafor", "Mathematics", 100, 4.75),
    ("Efe Ighodaro", "Physics", 200, 2.95),
]
cur.executemany(
    "INSERT INTO students (name, dept, level, cgpa) VALUES (?, ?, ?, ?)", more
)
conn.commit()
print("Rows inserted:", cur.rowcount)

Output:

New id: 1
Rows inserted: 4

executemany() runs the same statement for every tuple in the list. lastrowid gives the id of the last inserted row, and rowcount the number of rows affected.

Why placeholders matter: SQL injection

Imagine a search box where a user types a name. If you paste it into the SQL directly, a malicious user can change the meaning of the query:

user_input = "x' OR '1'='1"

unsafe = f"SELECT name FROM students WHERE name = '{user_input}'"
print(len(cur.execute(unsafe).fetchall()), "rows leaked")

safe = cur.execute("SELECT name FROM students WHERE name = ?", (user_input,))
print(len(safe.fetchall()), "rows returned")

Output:

5 rows leaked
0 rows returned

The unsafe query became WHERE name = 'x' OR '1'='1', which is true for every row. With a placeholder, the input is treated purely as data. This attack, called SQL injection, is one of the most common security holes on the web. Note the trailing comma in (user_input,), which makes a one-item tuple.

Querying and Fetching Rows

After a SELECT, fetch results with fetchone() (the next row, or None), fetchall() (a list of all rows), or by looping over the cursor directly. Each row is a tuple.

cur.execute("SELECT name, cgpa FROM students WHERE cgpa >= ? ORDER BY cgpa DESC", (3.5,))
for name, cgpa in cur:
    print(f"{name:15} {cgpa:.2f}")

row = cur.execute("SELECT COUNT(*), ROUND(AVG(cgpa), 2) FROM students").fetchone()
print("Students:", row[0], "Average CGPA:", row[1])

missing = cur.execute("SELECT * FROM students WHERE id = ?", (99,)).fetchone()
print(missing)

Output:

Dami Okafor     4.75
Ada Obi         4.52
Bayo Adeyemi    3.85
Students: 5 Average CGPA: 3.83
None

Always check for None before using the result of fetchone().

Accessing Columns by Name

Setting row_factory to sqlite3.Row lets you use column names instead of positions, which makes code clearer and safer when columns are added later:

conn.row_factory = sqlite3.Row
cur = conn.cursor()
row = cur.execute("SELECT * FROM students WHERE name = ?", ("Ada Obi",)).fetchone()
print(row["name"], row["dept"], row["level"])
print(row.keys())
print(dict(row))

Output:

Ada Obi Computer Science 200
['id', 'name', 'dept', 'level', 'cgpa']
{'id': 1, 'name': 'Ada Obi', 'dept': 'Computer Science', 'level': 200, 'cgpa': 4.52}

Updating and Deleting

cur.execute("UPDATE students SET level = level + 100 WHERE level < 400")
print("Promoted:", cur.rowcount)

cur.execute("DELETE FROM students WHERE name = ?", ("Efe Ighodaro",))
print("Deleted:", cur.rowcount)
conn.commit()

for r in cur.execute("SELECT name, level FROM students ORDER BY id"):
    print(r["name"], r["level"])

Output:

Promoted: 5
Deleted: 1
Ada Obi 300
Bayo Adeyemi 400
Chidi Eze 400
Dami Okafor 200

Be careful: an UPDATE or DELETE without a WHERE clause affects every row.

Transactions: commit and rollback

Changes are not saved permanently until you call conn.commit(). If something goes wrong part-way through a group of changes, conn.rollback() undoes everything since the last commit, so the database is never left half-updated. Using the connection in a with block does this automatically: it commits if the block succeeds and rolls back if an exception occurs.

try:
    with conn:
        conn.execute("UPDATE students SET cgpa = 5.0 WHERE name = ?", ("Chidi Eze",))
        conn.execute(
            "INSERT INTO students (name, dept, level, cgpa) VALUES (?, ?, ?, ?)",
            ("Fola Bello", "Physics", 250, 3.0),   # 250 breaks the CHECK rule
        )
except sqlite3.IntegrityError as e:
    print("Rolled back:", e)

print(conn.execute("SELECT cgpa FROM students WHERE name = 'Chidi Eze'").fetchone()[0])

Output:

Rolled back: CHECK constraint failed: level IN (100, 200, 300, 400)
3.1

The invalid insert failed, so the update in the same block was undone too: Chidi's CGPA is still 3.1. This all-or-nothing behaviour is essential for things like bank transfers and course registration. (Note that with conn: does not close the connection; it only manages the transaction.)

Related Tables, Foreign Keys and JOIN

Real data is spread over several linked tables. Here a results table refers to students by id. SQLite only enforces foreign keys after PRAGMA foreign_keys = ON.

conn.execute("PRAGMA foreign_keys = ON")
conn.execute("""
    CREATE TABLE IF NOT EXISTS results (
        student_id INTEGER REFERENCES students(id),
        course     TEXT,
        score      INTEGER
    )
""")
with conn:
    conn.executemany("INSERT INTO results VALUES (?, ?, ?)", [
        (1, "CSC 201", 78), (1, "MTH 201", 65),
        (2, "PHY 301", 58), (3, "CSC 201", 49), (4, "MTH 101", 91),
    ])

query = """
    SELECT s.name, r.course, r.score
    FROM results AS r
    JOIN students AS s ON s.id = r.student_id
    WHERE r.score >= ?
    ORDER BY r.score DESC
"""
for r in conn.execute(query, (60,)):
    print(f"{r['name']:13} {r['course']} {r['score']}")

try:
    conn.execute("INSERT INTO results VALUES (?, ?, ?)", (42, "CSC 101", 70))
except sqlite3.IntegrityError as e:
    print("Error:", e)

Output:

Dami Okafor   MTH 101 91
Ada Obi       CSC 201 78
Ada Obi       MTH 201 65
Error: FOREIGN KEY constraint failed

The database refuses a result for student 42, who does not exist. The tutorial on advanced SQL covers subqueries, common table expressions and window functions, all of which work in SQLite.

Putting It Together: A Small Data Layer

In a real program, wrap the SQL in small functions so the rest of the code never deals with cursors directly:

def add_student(conn, name, dept, level, cgpa):
    with conn:
        cur = conn.execute(
            "INSERT INTO students (name, dept, level, cgpa) VALUES (?, ?, ?, ?)",
            (name, dept, level, cgpa),
        )
    return cur.lastrowid

def top_students(conn, dept, limit=3):
    rows = conn.execute(
        "SELECT name, cgpa FROM students WHERE dept = ? ORDER BY cgpa DESC LIMIT ?",
        (dept, limit),
    )
    return [(r["name"], r["cgpa"]) for r in rows]

new_id = add_student(conn, "Gift Nwosu", "Computer Science", 100, 4.1)
print(new_id)
print(top_students(conn, "Computer Science"))
conn.close()

Output:

6
[('Ada Obi', 4.52), ('Gift Nwosu', 4.1), ('Chidi Eze', 3.1)]

Notice that the new id is 6, not 5: AUTOINCREMENT never reuses the id of the deleted student. Always close() the connection when you are finished.

Tips for Using sqlite3

  • Always use placeholders for values. Placeholders cannot be used for table or column names; validate those against a fixed list instead.
  • Commit after changes, or use with conn: blocks.
  • Use sqlite3.Row for readable column access.
  • Turn on foreign keys with PRAGMA foreign_keys = ON for every new connection.
  • Explore your database with the free "DB Browser for SQLite" tool or the sqlite3 command-line shell.
  • For a multi-user web application with heavy traffic, move to a server database such as PostgreSQL or MySQL. The SQL you learn here carries over almost unchanged.

Python sqlite3: Practice Exercises

  1. Create a books table with id, title, author and year, and insert five books with executemany().
  2. Write a function that returns all books by a given author, using a placeholder.
  3. Add a loans table that references books, and list the titles currently on loan with a JOIN.
  4. Show that a transaction rolls back when one of two inserts fails.

Conclusion

Python's sqlite3 module gives you a full SQL database in a single file. Connect with sqlite3.connect(), run statements with execute() and executemany(), and always pass values through ? placeholders to prevent SQL injection. Fetch rows with fetchone(), fetchall() or a loop, use sqlite3.Row for named columns, and group changes in transactions with with conn: so they either all succeed or all roll back. With foreign keys and JOINs, you can model real, linked data in just a few lines of Python.

Related Tutorials