Databases

Problem

A catalog in a file

The store's catalog is a CSV file, as in the files module. While there are a hundred products, it works. Then there are a hundred thousand, and problems show up:

• to find one product, you read the whole file;
• "everything under €5, sorted by price" is a loop, an if and a sort, written by hand;
• two cash registers write to the file at the same time, and one record gets lost or the file gets damaged;
• the power goes out in the middle of a write, and half the file is gone.

That's why the data of almost every real program lives in a database: a program whose job is to keep data, find it fast and never lose it.

The idea

Tables and SQL

A relational database keeps data in tables. A table has columns (name, type) and rows (one for each product). The id column is the primary key: its value is different for each row, so it finds exactly one row.

You talk to the database in SQL. The five most common commands:

SQL

CREATE TABLE products (
    id    INT PRIMARY KEY,
    name  VARCHAR(50) NOT NULL,
    price DECIMAL(8, 2)
);

INSERT INTO products VALUES (1, 'Coffee', 7.50);
SELECT name, price FROM products WHERE price < 5 ORDER BY price;
UPDATE products SET price = 6.90 WHERE id = 1;
DELETE FROM products WHERE id = 3;

• CREATE TABLE makes a table, INSERT adds a row, UPDATE changes rows and DELETE removes them.
• SELECT reads: which columns, from which table, under what condition (WHERE) and in what order (ORDER BY).

You say what you want, and the database decides how to find it fast.

More precisely, if you are curious

• Well-known relational databases are PostgreSQL, MySQL, Oracle and SQL Server. They run as separate servers. Here we use SQLite: a small database that lives in a file or entirely in the program's memory. The sqlite3 module is in Python's standard library. The address ":memory:" is a database in memory: every run starts with an empty one.
• Text in SQL goes in single quotes: 'Coffee'. In PostgreSQL and MySQL, DECIMAL(8, 2) is an exact number with 2 digits after the decimal point, which suits money. SQLite doesn't keep exact decimal numbers: there, 7.50 is an ordinary floating-point number.

See it

sqlite3: connection, query, result

The sqlite3 module follows Python's standard for databases (DB-API, PEP 249): PostgreSQL and MySQL have the same connect, execute, commit and close. The connection is a Connection, and the result of a query is a cursor. The setup function makes the table and adds three products:

def setup(conn):
    conn.execute("CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, price DECIMAL(8, 2))")
    conn.execute("INSERT INTO products VALUES (1, 'Coffee', 7.50), (2, 'Tea', 3.20), (3, 'Chocolate', 2.40)")
    conn.commit()

We read the rows and turn each one into an object of the Product dataclass, with the fields id, name and price:

@dataclass
class Product:
    id: int
    name: str
    price: float

conn = sqlite3.connect(":memory:")
setup(conn)
products = []
for product_id, name, price in conn.execute("SELECT id, name, price FROM products ORDER BY price"):
    products.append(Product(product_id, name, price))
for p in products:
    print(p)
conn.close()

Output

Product(id=3, name='Chocolate', price=2.4)
Product(id=2, name='Tea', price=3.2)
Product(id=1, name='Coffee', price=7.5)

• sqlite3.connect(address) opens the connection. The address says which database: here ":memory:", or a file name like "shop.db".
• conn.execute(sql) sends any command and returns a cursor. A for over the cursor gives you one row at a time, each as a tuple of the columns in the order of the SELECT. Here the tuple is unpacked into three variables.
• Before INSERT, UPDATE and DELETE, sqlite3 starts a transaction by itself, and conn.commit() saves the changes. Without it, changes to a database in a file are lost when it closes.
• conn.close() closes the connection. (with conn: doesn't close it: it makes a transaction, as you'll see below.)
• Errors are exceptions from sqlite3.Error, for example OperationalError for wrong SQL and IntegrityError for a broken table rule. Python has no checked exceptions.

conn = sqlite3.connect(":memory:")
setup(conn)
products = []
for product_id, name, price in conn.execute("SELECT id, name, price FROM products ORDER BY price"):
    products.append(Product(product_id, name, price))
for p in products:
    print(p)
conn.close()

Step by step, in words

1. A connection to the database in memory. setup makes the products table and adds 3 rows.
2. An empty list that the objects will go into.
3. SELECT with ORDER BY price: conn.execute returns a cursor over the rows, cheapest first.
4. The loop takes row 1 as a tuple and unpacks it into three variables, and Product(...) makes an object from them: Chocolate.
5. The loop takes row 2 as a tuple and unpacks it into three variables, and Product(...) makes an object from them: Tea.
6. The loop takes row 3 as a tuple and unpacks it into three variables, and Product(...) makes an object from them: Coffee.
7. The cursor has no more rows and the loop stops.
8. The list holds ordinary objects. Printing shows the dataclass with its fields.

More precisely, if you are curious

• With a real database on a server, the address is different and there's a user and a password. You also need the database's driver as a dependency in the project (for MySQL: the mysql-connector-python package):

import mysql.connector

conn = mysql.connector.connect(host="localhost", port=3306, database="shop", user="shop_app", password="secret")
print("Connected to the database!")
conn.close()

• The password isn't written in the code. It's read from the settings or from an environment variable (os.environ).

• The prices here are float to keep it short. In a real store, money is kept exactly: as DECIMAL in PostgreSQL or MySQL (in Python it arrives as decimal.Decimal), and in SQLite as whole cents.
• With conn.row_factory = sqlite3.Row, you can also read each row by column name: row["name"].

Try it

What will it print?

The products are Coffee €7.50, Tea €3.20 and Chocolate €2.40. What will the program print?

conn = sqlite3.connect(":memory:")
setup(conn)
for row in conn.execute("SELECT name FROM products WHERE price < 5 ORDER BY price DESC"):
    print(row[0])
conn.close()

Answer

Tea
Chocolate

Coffee
Tea
Chocolate

Chocolate
Tea

Check

Problem

The search that shows everything

The website searches for a product by the name the user types. The query is put together with an f-string:

def search(conn, name):
    sql = f"SELECT name, price FROM products WHERE name = '{name}'"
    for found, price in conn.execute(sql):
        print(f"  {found}: €{price}")

conn = sqlite3.connect(":memory:")
setup(conn)
print("Searching: Tea")
search(conn, "Tea")
print("Searching: x' OR '1'='1")
search(conn, "x' OR '1'='1")
conn.close()

Output

Searching: Tea
  Tea: €3.2
Searching: x' OR '1'='1
  Coffee: €7.5
  Tea: €3.2
  Chocolate: €2.4

With "Tea" it works. With x' OR '1'='1 the query becomes ... WHERE name = 'x' OR '1'='1'. The condition is always true, and the whole table comes out. The user's input has become part of the SQL. This is SQL injection, one of the most common attacks on websites. With the same trick, someone can log in without a password or delete a table.

The idea

Parameters with ?

The solution is to send the query and the values separately. The query has ? in place of the values, and you pass them as a tuple, the second argument of execute. (name,) is a tuple with one value: the comma is required.

def search(conn, name):
    sql = "SELECT name, price FROM products WHERE name = ?"
    for found, price in conn.execute(sql, (name,)):
        print(f"  {found}: €{price}")

Output

Searching: Tea
  Tea: €3.2
Searching: x' OR '1'='1

Now x' OR '1'='1 is just text that is searched for as a name. There's no such product, and nothing comes out. Whatever the user types, it can't become SQL.

The rule is simple: a value from the user, from a file or from the network is never glued into SQL. Always use ?.

Try it

Coffee with an apostrophe

The function renames a product. With the name Coffee 'Arabica' the program stops with sqlite3.OperationalError: near "Arabica": syntax error. Which line has the bug?

Pick the line with the bug.

1def rename(conn, product_id, new_name):2 conn.execute(f"UPDATE products SET name = '{new_name}' WHERE id = {product_id}")3 conn.commit()

Check

The idea

Transaction: all or nothing

Ana transfers €150 to Boris. That's two queries: add to Boris, subtract from Ana. The table has the condition CHECK (balance >= 0), so the second query fails: Ana has only €100.

def open_accounts(conn):
    conn.execute("CREATE TABLE accounts (id INT PRIMARY KEY, owner VARCHAR(50), balance INT CONSTRAINT no_debt CHECK (balance >= 0))")
    conn.execute("INSERT INTO accounts VALUES (1, 'Ana', 100), (2, 'Boris', 50)")
    conn.commit()


def move(conn, account_id, amount):
    conn.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", (amount, account_id))


def print_balances(conn):
    for owner, balance in conn.execute("SELECT owner, balance FROM accounts ORDER BY id"):
        print(f"  {owner}: €{balance}")

def transfer(conn, source, target, amount):
    move(conn, target, amount)
    conn.commit()
    move(conn, source, -amount)
    conn.commit()

conn = sqlite3.connect(":memory:")
open_accounts(conn)
try:
    transfer(conn, 1, 2, 150)
except sqlite3.Error:
    print("The transfer of €150 was refused.")
print_balances(conn)
conn.close()

Output

The transfer of €150 was refused.
  Ana: €100
  Boris: €200

The transfer was refused, but Boris has already received €150 out of nothing: every move was saved right away with commit(). The two queries must be a transaction: either both go through, or neither does. In Python, with conn: does this.

def transfer(conn, source, target, amount):
    with conn:
        move(conn, target, amount)
        move(conn, source, -amount)

Output

The transfer of €150 was refused.
  Ana: €100
  Boris: €50

• The first UPDATE starts a transaction: the changes aren't final yet.
• If the with conn: block ends without an error, it calls conn.commit() and saves everything at once.
• If there's an exception inside, it calls conn.rollback() and undoes everything. The exception goes on up, so the caller finds out too.
• open_accounts calls commit() at the end. Without it, creating the accounts would be in the same transaction, and rollback() would undo that too.

More precisely, if you are curious

• The exception for the failed query is an IntegrityError with the message CHECK constraint failed: no_debt. no_debt is the name of the condition: in the table it's balance INT CONSTRAINT no_debt CHECK (balance >= 0).

• Databases promise four things about transactions, known as ACID: all or nothing (atomicity), the data stays correct according to the table rules (consistency), transactions running at the same time don't get in each other's way (isolation), and what's saved isn't lost even if the power goes out (durability).

The idea

ORM: objects instead of rows

With sqlite3, for each class you write SQL and turn the rows into objects by hand. An ORM (object-relational mapping) does this for you: you describe the class, and the library makes the table, the queries and the objects by itself. Python's standard library has no ORM. The best-known one is SQLAlchemy (the sqlalchemy package), and web projects with Django use Django's built-in ORM.

models.py

from sqlalchemy import String, create_engine, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column

class Base(DeclarativeBase):
    pass


class Product(Base):
    __tablename__ = "products"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(50))
    price: Mapped[float]

• A subclass of Base (from DeclarativeBase) is a table, and __tablename__ gives its name.
• Every field with the type Mapped[...] is a column. primary_key=True is the primary key: SQLAlchemy lets the database give it a value.
• mapped_column sets details for the column: here, String(50). A field with only Mapped[float] becomes a column too.
• The constructor is made for you: Product(name="Coffee", price=7.50).

Saving and reading go through the Session:

engine = create_engine("sqlite://")
Base.metadata.create_all(engine)

with Session(engine) as session:
    session.add(Product(name="Coffee", price=7.50))
    session.add(Product(name="Tea", price=3.20))
    session.commit()

    cheap = session.scalars(select(Product).where(Product.price < 5).order_by(Product.price))
    for p in cheap:
        print(f"{p.name}: €{p.price}")

Output (with SQLAlchemy installed)

Tea: €3.2

create_engine("sqlite://") says which database: here, SQLite in memory. create_all makes the tables. session.add adds the object, and commit() saves it in a transaction, as in sqlite3. The query is built with code: select(Product).where(Product.price < 5) talks about the class and its price field, not about the table. session.scalars returns ready objects.

SQLAlchemy is a package, and the site's checker doesn't have it, so this code doesn't run here. The output comes from a run with SQLAlchemy 2.1 installed (pip install sqlalchemy).

More precisely, if you are curious

• A query with select is code, so the conditions are easy to put together from the user's choices. A function that searches by name:

def named(session, name):
    query = select(Product).where(Product.name == name)
    return session.scalars(query).all()

• A transaction that is undone on an error: session.begin() in a with saves everything at the end, and on an exception undoes everything:

with Session(engine) as session, session.begin():
    session.add(Product(name="Chocolate", price=2.40))
    # more changes: on an exception everything is undone, otherwise everything is saved

• With create_engine(address, echo=True), SQLAlchemy prints the SQL it sends. That way you can see it's still SQL underneath.
• In older code you'll see session.query(Product).filter(...). That's the SQLAlchemy 1.x style, and select(...) is from 2.0 on.

Exercise

A store with a database

Solve the tasks in your editor, with sqlite3 and the database ":memory:".1. Catalog. Make a products table and the functions add, set_price, remove and show (list is the name of a built-in type), all with ? except show. Add Coffee (€7.50), Tea (€3.20) and Chocolate (€2.40), print them, change the price of the coffee to €6.90, delete the chocolate and print again:

Output

1. Coffee: €7.5
2. Tea: €3.2
3. Chocolate: €2.4
---
1. Coffee: €6.9
2. Tea: €3.2

2. Search by part of the name. The products are Coffee, Cocoa, Tea and Éclair. A function find(conn, text) finds all products whose name contains the text, ignoring upper and lower case. LOWER in SQLite changes only the letters A to Z, so É stays as it is. Give the database a function of your own: conn.create_function("lower_text", 1, str.lower), then WHERE lower_text(name) LIKE ? with the value f"%{text.lower()}%". Try "CO", "éclair" and an injection attempt:

Output

Searching for "CO":
  Cocoa: €4.8
  Coffee: €7.5
Searching for "éclair":
  Éclair: €2.4
Searching for "' OR '1'='1":
  no products found

3. A transfer that fails halfway. The accounts are Ana (€100) and Boris (€50). transfer(conn, source, target, amount) first subtracts from source, then adds to target. If cursor.rowcount is 0 (no such account), raise ValueError(f"No account {account_id}"). Everything is in with conn:. Transfer €30 from 1 to 2, then €40 from 1 to the account 99, which doesn't exist:

Output

Transferred €30 from 1 to 2
Refused: No account 99
  Ana: €70
  Boris: €80

The second transfer took €40 from Ana before it failed, but with conn (with rollback()) gave it back.

Test

Check yourself

1. What does conn.execute("SELECT ...") return?

A cursor: the rows of the result, which you walk through with forThe number of changed rowsA list of Product objects

2. Why is f"... WHERE name = '{name}'" dangerous?

It is not dangerous if name is a strBecause the text from the user becomes part of the SQL and can change the queryBecause it is slow

3. A transfer is two queries inside with conn:. The first one succeeds, the second one raises an exception. What does with conn do?

It saves the first one and undoes the second oneIt repeats the second queryIt undoes all changes since the start of the transaction, the first one too (rollback)

4. What does an ORM like SQLAlchemy do?

It only makes SQL queries fasterIt replaces the databaseIt turns objects into rows and back, without you writing SQL for everything

Check

Start the lesson over