Databases from Python

Part of the free LogicWiz Python course, Module 15: Real-world Python. The examples and practice run in the app.

Two counters wrote to sales.csv at the same time and the file got scrambled: Rs 4,000 of sales vanished. Meher moves Canteen Counter to SQLite, a real database that lives in one file and comes built into Python.

You'll learn to

  • Use `sqlite3` in five steps: connect, cursor, execute, commit, close
  • Insert and read rows with `?` placeholders, `executemany`, `fetchone` and `fetchall`
  • Show why SQL built with f-strings is dangerous (SQL injection), and never do it
  • Use `with conn:` so a group of changes is saved together or not at all

Learn

SQLite in five steps

SQLite is a full SQL database in a single file (or in memory), with no server to install. import sqlite3 and you have it.

  1. conn = sqlite3.connect("canteen.db") opens (or creates) the file. ":memory:" makes a throwaway database in RAM.
  2. cur = conn.cursor() gets a cursor, the thing that runs SQL.
  3. cur.execute(sql, values) runs one statement.
  4. conn.commit() saves your changes. Until then, they are only pending.
  5. conn.close() closes it.

Reading needs no commit: cur.fetchone() gives one row as a tuple, cur.fetchall() gives a list of tuples.

Create, insert, select

import sqlite3

conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute("CREATE TABLE menu (id INTEGER PRIMARY KEY, item TEXT, price INTEGER)")

cur.execute("INSERT INTO menu (item, price) VALUES (?, ?)", ("Samosa", 15))
print("Samosa got id", cur.lastrowid)
cur.executemany(
    "INSERT INTO menu (item, price) VALUES (?, ?)",
    [("Masala Chai", 10), ("Veg Sandwich", 40), ("Cold Coffee", 45)],
)
conn.commit()

cur.execute("SELECT item, price FROM menu WHERE price < ? ORDER BY price", (30,))
print(cur.fetchall())

cur.execute("SELECT COUNT(*), SUM(price) FROM menu")
print(cur.fetchone())
conn.close()

Each ? is a placeholder; the values go in a separate tuple. executemany runs the same statement once per tuple in the list.

SQL injection: why f-strings in SQL are dangerous

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE students (roll TEXT, name TEXT, pin TEXT)")
conn.executemany("INSERT INTO students VALUES (?, ?, ?)",
                 [("101", "Riya", "4821"), ("102", "Kabir", "7790")])

typed = "x' OR '1'='1"          # what a cheeky user types as their PIN

unsafe = f"SELECT name FROM students WHERE roll = '101' AND pin = '{typed}'"
print(unsafe)
print("f-string:", conn.execute(unsafe).fetchall())

safe = "SELECT name FROM students WHERE roll = ? AND pin = ?"
print("? placeholders:", conn.execute(safe, ("101", typed)).fetchall())

With the f-string, the typed text became part of the SQL: OR '1'='1' is always true, so it logs in as everyone. With ?, the whole text is treated as a value, a PIN nobody has, so nothing matches.

Never build SQL with f-strings, + or % on user input. Always use ? placeholders and pass the values separately. It stops SQL injection, and it also handles names like Shankar's Special whose apostrophe would break a hand-built query. Placeholders are for values only; table and column names can't be ?.

A file database, and forgetting commit

import sqlite3

conn = sqlite3.connect("canteen.db")
conn.execute("CREATE TABLE IF NOT EXISTS sales (item TEXT, qty INTEGER, amount INTEGER)")
conn.execute("INSERT INTO sales VALUES (?, ?, ?)", ("Samosa", 2, 30))
conn.commit()                                   # saved to the file
conn.execute("INSERT INTO sales VALUES (?, ?, ?)", ("Chai", 1, 10))
conn.close()                                    # closed WITHOUT commit

conn = sqlite3.connect("canteen.db")
print(conn.execute("SELECT * FROM sales").fetchall())
conn.close()

The Chai sale is gone: closing without commit() throws away pending changes. conn.execute is a shortcut that makes a cursor for you.

with conn: all or nothing

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE wallet (name TEXT, balance INTEGER CHECK (balance >= 0))")
conn.executemany("INSERT INTO wallet VALUES (?, ?)", [("Aarav", 100), ("Canteen", 0)])
conn.commit()

def pay(amount):
    try:
        with conn:      # commits if the block finishes, rolls back on an error
            conn.execute("UPDATE wallet SET balance = balance + ? WHERE name = 'Canteen'", (amount,))
            conn.execute("UPDATE wallet SET balance = balance - ? WHERE name = 'Aarav'", (amount,))
        print("paid", amount)
    except sqlite3.IntegrityError:
        print("payment of", amount, "failed, nothing changed")

pay(40)
pay(500)
print(conn.execute("SELECT * FROM wallet").fetchall())

The Rs 500 payment failed on the second UPDATE, and the first UPDATE was undone too. That is a transaction: money is never added to one side without leaving the other. with conn: does not close the connection.

Reports with GROUP BY, rows by name

import sqlite3

conn = sqlite3.connect(":memory:")
conn.row_factory = sqlite3.Row          # rows act like dicts too
conn.execute("CREATE TABLE sales (day TEXT, item TEXT, qty INTEGER, price INTEGER)")
conn.executemany("INSERT INTO sales VALUES (?, ?, ?, ?)", [
    ("Mon", "Samosa", 20, 15), ("Mon", "Chai", 35, 10),
    ("Tue", "Samosa", 25, 15), ("Tue", "Cold Coffee", 8, 45),
])

query = """SELECT item, SUM(qty) AS sold, SUM(qty * price) AS revenue
           FROM sales GROUP BY item ORDER BY revenue DESC"""
for row in conn.execute(query):
    print(f"{row['item']:<12} {row['sold']:>3} sold  Rs {row['revenue']}")

Let the database do the adding up. You can loop over the cursor directly instead of calling fetchall().

CBSE Class 12 asks the same five steps with MySQL (mysql.connector.connect(...), cursor(), execute(), commit(), fetchone()/fetchall(), rowcount). The only difference: MySQL's placeholder is %s, SQLite's is ?. Board Pack A (lesson A6) drills the MySQL version.

In one line

sqlite3: connect, cursor, execute(sql, (values,)) with ?, commit(), fetch; never build SQL with f-strings; with conn: is all-or-nothing.

Interview corner

What is SQL injection and how do you prevent it in Python?

It happens when user input is pasted into SQL text, so the input can change the query itself (e.g. a PIN of x' OR '1'='1 matches every row). Prevent it with parameterised queries: cur.execute("SELECT * FROM users WHERE name = ?", (name,)) in sqlite3, or %s in MySQL drivers. The driver sends values separately, so they are never parsed as SQL.

What do `commit()` and `rollback()` do?

Changes inside a transaction are pending until commit() makes them permanent; rollback() throws them away. In sqlite3, with conn: commits when the block succeeds and rolls back if it raises, which keeps related updates (like debit and credit) all-or-nothing.

What is the difference between `fetchone()`, `fetchmany(n)` and `fetchall()`?

fetchone() returns the next row as a tuple, or None when there are no more. fetchmany(n) returns a list of up to n rows, and fetchall() returns all remaining rows. For huge results, loop over the cursor or use fetchmany so you don't load everything into memory.

When would you choose SQLite over MySQL or PostgreSQL?

SQLite is a library, not a server: perfect for apps, tests, prototypes, mobile and small sites with few writers. It allows one writer at a time, so busy multi-user systems with many concurrent writes, users and permissions use a server database like PostgreSQL or MySQL.