Python databases with SQLite

A database table is like a smart spreadsheet

A database stores data in tables. Each table has columns (name, class, marks) and rows (one per student). Unlike a CSV file, a database can search millions of rows quickly, keeps data types in check, and lets many programs use the same data safely.

SQLite is a small database that lives in a single file. It comes with Python through the sqlite3 module, so there is nothing to install.

SQL in 5 commands (CRUD)

SQL is the language for talking to databases. CRUD stands for Create, Read, Update, Delete.

UPDATE and DELETE without a WHERE change or delete every row.

Python + SQLite: connect, create, insert, commit

import sqlite3

con = sqlite3.connect("school.db")        # creates the file if it does not exist
cur = con.cursor()
cur.execute("""
    CREATE TABLE IF NOT EXISTS students (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        class INTEGER,
        marks INTEGER
    )
""")
cur.execute("INSERT INTO students (name, class, marks) VALUES (?, ?, ?)", ("Asha", 10, 91))
cur.executemany("INSERT INTO students (name, class, marks) VALUES (?, ?, ?)",
                [("Ravi", 10, 78), ("Meena", 9, 85)])
con.commit()                              # save the changes
con.close()

Changes are not saved until you call commit(). Using the connection in a with block commits for you when the block succeeds (but does not close the connection).

Always use ? placeholders

Never build SQL by joining strings with user input:

name = input("Name: ")
cur.execute(f"SELECT * FROM students WHERE name = '{name}'")      # DANGEROUS
cur.execute("SELECT * FROM students WHERE name = ?", (name,))      # SAFE

If a user types ' OR '1'='1, the first version returns every row. This attack is called SQL injection. With ?, SQLite treats the input purely as a value. Note the comma in (name,): the values must be a tuple.

SELECT with WHERE, ORDER BY, LIMIT and LIKE

cur.execute("SELECT name, marks FROM students WHERE marks >= ? ORDER BY marks DESC LIMIT 3", (60,))
for name, marks in cur.fetchall():
    print(name, marks)

cur.execute("SELECT * FROM students WHERE name LIKE ?", ("A%",))   # names starting with A
row = cur.fetchone()          # the first row, or None

Aggregates and GROUP BY: reports from data

SELECT COUNT(*), AVG(marks), MAX(marks) FROM students;

SELECT class, COUNT(*) AS students, ROUND(AVG(marks), 1) AS average
FROM students
GROUP BY class
ORDER BY class;

GROUP BY puts rows with the same value together and runs COUNT, SUM, AVG, MIN or MAX on each group: one result row per class.

JOIN: combine two related tables

Good databases avoid repeating data. Books and the loans of those books live in separate tables, linked by an id:

CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT);
CREATE TABLE issues (id INTEGER PRIMARY KEY, book_id INTEGER, student TEXT,
                     FOREIGN KEY (book_id) REFERENCES books(id));

SELECT issues.student, books.title
FROM issues
JOIN books ON books.id = issues.book_id;

The JOIN ... ON line says which columns connect the tables.

Example: student database functions

import sqlite3

def add_student(name, marks):
    with sqlite3.connect("school.db") as con:
        con.execute("INSERT INTO students (name, marks) VALUES (?, ?)", (name, marks))

def toppers(n=3):
    with sqlite3.connect("school.db") as con:
        return con.execute(
            "SELECT name, marks FROM students ORDER BY marks DESC LIMIT ?", (n,)).fetchall()

Common mistakes

• Forgetting commit(), so inserts vanish when the program ends.
• Building SQL with f-strings and user input (SQL injection).
• Writing (name) instead of (name,) for a single placeholder value.
• UPDATE or DELETE without WHERE.

Key points

• sqlite3.connect("file.db") opens or creates a database file.
• execute() runs SQL; fetchall() and fetchone() read results; commit() saves.
• Always pass values with ? placeholders.
• GROUP BY builds summaries; JOIN combines related tables.

Practice questions