Learnings

Python in practice, part 8 of 9

Python Databases

A variable is gone when the program stops. A database keeps data safe on disk, lets you ask questions about it, and protects it when several things happen at once. This page builds a small family bank, first with plain SQL through the standard library module sqlite3, then with the SQLAlchemy ORM. You need to know Python, and nothing about databases.

SQL with sqlite3

Part 1 of 4 · tables, rows and queries with the standard library

SQLite is a complete database that lives in a single file, or in memory. Python ships with the sqlite3 module to talk to it, so everything in this part runs with no installation.

Every example uses the special name :memory:, a database that exists only while the program runs. That keeps each example complete on its own. Pass a file name such as "bank.db" and the same code keeps its data on disk.

What a database is

A relational database stores data in tables. A table looks like a spreadsheet sheet: it has named columns, and each row is one record. The bank on this page starts with a table of customers.

TermMeaning
TableA named set of rows that all have the same columns, such as customers
RowOne record: one customer, one account
ColumnOne named piece of every row, with a type: name is text, joined is a number
Primary keyA column whose value is different for every row, so one row can be picked out exactly. Here it is id
Foreign keyA column that holds the primary key of a row in another table. It is how tables are linked
SQLStructured Query Language, the language you use to create tables and to read and change rows
QueryOne SQL statement sent to the database

Concept: "relational" means the tables relate to each other through keys. An account row does not repeat the customer's name. It stores the customer's id, and the database joins the two when you ask.

Connecting and creating a table

sqlite3.connect() opens a connection, the object every query goes through. CREATE TABLE names the table and lists its columns with their types.

INTEGER PRIMARY KEY makes id the primary key, and SQLite fills it in with 1, 2, 3 and so on. NOT NULL means the column may not be left empty. NULL is the database word for "no value", and Python shows it as None.

Style: SQL keywords are written in capitals by habit, so they stand out from table and column names. The database accepts lower case as well.

Inserting rows

INSERT INTO adds a row. The ? marks are placeholders: the values travel separately, in a tuple, and the database puts them in. executemany() runs the same statement once for each tuple in a list.

Concept: commit() makes the changes permanent. Until you commit, they can still be undone. The transactions part explains this in full.

Placeholders and SQL injection

Never build SQL by gluing text from a user into the query string. If the text contains a quote mark, it stops being a value and becomes part of the SQL. This attack is called SQL injection.

Below, someone types a strange "name" into a search box. The unsafe query returns every customer. The safe query, with a placeholder, treats the whole text as one name and finds nobody.

With worse input the same hole can change or delete data. The rule has no exceptions: values go in through ? placeholders, never through f-strings, + or .format().

Pitfall: the second argument must be a sequence. For one value write (typed,) with the comma. (typed) is just a string, and sqlite3 would treat each character as a separate value.

Selecting, filtering and ordering

SELECT reads rows. WHERE keeps only the rows that match, ORDER BY sorts them, and LIMIT stops after a number of rows.

DESC sorts from high to low. LIKE compares text with a pattern, where % stands for any run of characters. SELECT * returns every column, which is handy while exploring. In real code, name the columns you need.

Pitfall: without ORDER BY the database may return rows in any order. If the order matters, ask for it.

Reading the results

execute() returns a cursor, an object that hands out the result rows. Each row is a tuple. There are three ways to read them.

CallGives youUse it when
fetchone()The next row, or NoneYou expect one row, such as a lookup by id
fetchall()A list of all remaining rowsThe result is small enough to hold in memory
for row in cursorOne row at a timeThe result may be large

Columns by name

row[1] is hard to read and breaks when the column order changes. Set conn.row_factory = sqlite3.Row and every row can be read by column name as well as by position.

Updating and deleting

UPDATE changes rows and DELETE removes them. Both act on every row that matches the WHERE. The cursor's rowcount tells you how many rows were changed.

Pitfall: an UPDATE or DELETE with no WHERE changes the whole table. DELETE FROM customers removes every customer. Write the WHERE first.

A second table and a JOIN

One customer can have several accounts, so accounts get their own table. Each account row holds a customer_id, a foreign key that points at the id of its owner. A JOIN puts rows from both tables side by side wherever the keys match.

The foreign key also protects the data: an account for customer 99 is refused, because there is no customer 99. Hemkesh has no account yet, so he does not appear. A plain JOIN keeps only rows that have a match on both sides.

Money: balances on this page are whole rupees stored as integers, to keep the examples short. Good habits explains why money should never be a float.

Totals with GROUP BY

An aggregate function turns many rows into one value: COUNT, SUM, AVG, MIN, MAX. GROUP BY makes one group per customer and runs the aggregate inside each group.

LEFT JOIN keeps every row of the left table, customers, even when nothing matches on the right. That is why Hemkesh appears here with zero accounts. His sum would be NULL, and COALESCE(..., 0) replaces a NULL with 0. AS total gives the column a name that ORDER BY can use.

Concept: to filter groups after they are totalled, use HAVING, for example HAVING SUM(accounts.balance) > 5000. WHERE filters rows before grouping.

Indexes

Without help, the database finds matching rows by reading the whole table. An index is a sorted lookup structure on one or more columns, like the index at the back of a book. With it, the database jumps straight to the rows.

The bank keeps one row per deposit or withdrawal in a table named ledger. EXPLAIN QUERY PLAN asks SQLite how it would run a query.

SCAN means "read every row". SEARCH ... USING INDEX means "look it up". On ten rows you will not notice. On ten million you will. The exact wording of the plan differs a little between SQLite versions.

Trade-off: an index makes reads faster and writes a little slower, because every insert must update it too. Index the columns you filter or join on often. The primary key is indexed already.

Transactions

Part 2 of 4 · changes that happen completely or not at all

A bank transfer is two changes: take money from one account, add it to another. If the program stops between the two, money is lost. A transaction ties several changes together so that they all happen, or none do. This part still needs only the standard library.

What a transaction is

A transaction is a group of statements that the database treats as one unit of work. You end it in one of two ways. Commit makes all its changes permanent. Rollback throws all of them away, as if nothing had happened.

Databases promise four things about transactions. The first letters spell ACID.

LetterNameIn plain wordsAt the bank
AAtomicityAll of the changes happen, or none of themA transfer never takes money out without putting it in
CConsistencyThe rules of the database hold before and afterNo balance ends up below zero if a rule forbids it
IIsolationTransactions running at the same time do not see each other's half-finished workNobody sees the moment when the money has left one account and not reached the other
DDurabilityOnce committed, the change survives a crash or power cutA confirmed deposit is still there after a restart

Naming: banks also call each deposit or payment a "transaction". To avoid mixing the two meanings, this page calls those rows the ledger, and uses "transaction" only for the database idea.

Commit and rollback

By default, sqlite3 opens a transaction for you just before the first INSERT, UPDATE or DELETE. It stays open until you call commit() or rollback().

Other connections do not see a change until it is committed. That is isolation at work. The next example uses a database file so that two connections can open the same data.

Pitfall: if you close a connection, or the program ends, without committing, the open transaction is rolled back. A forgotten commit() is the usual reason for "my data did not save".

A transfer that loses money

To see why transactions matter, switch them off. isolation_level=None puts the connection in autocommit mode: every statement is saved the moment it runs. The transfer below sends money to an account that does not exist.

The first UPDATE was saved at once. The second one changed nothing, the function raised an error, and the caller was told the transfer failed. But Rishikesh is already 2000 rupees poorer. The same thing happens if the program crashes or the power goes between the two statements.

The fix: with connection

Used in a with statement, a sqlite3 connection wraps the block in a transaction. If the block finishes, it commits. If the block raises an exception, it rolls back, and the exception carries on to the caller.

The failed transfer left no trace: the first UPDATE was rolled back together with the second. The good transfer changed both rows in one step. This is atomicity.

Pitfall: with conn: ends the transaction, not the connection. You still call conn.close() yourself. Context managers are covered in Python Errors and Files.

Constraints

A constraint is a rule written into the table. The database refuses any change that breaks it, whichever program sends the change. Python code should still check first, but a constraint catches the bug that slips past the check.

ConstraintRule
NOT NULLThe column must have a value
UNIQUENo two rows may have the same value in this column
CHECK (...)The condition must be true for every row
PRIMARY KEYUnique, and identifies the row
REFERENCES (foreign key)The value must exist as a key in the other table
DEFAULTNot a check: the value used when none is given

Concept: constraints plus transactions work as a pair. If a CHECK fails on the second statement of a transfer, the exception triggers the rollback, and the first statement is undone as well.

Two transfers at once

A real bank handles many requests at the same moment. Suppose an account holds 1000 rupees and two programs each try to withdraw 800. Each one reads the balance, sees 1000, decides there is enough, and writes back 200. The bank has paid out 1600 from an account that held 1000. This is a race condition, the same "check, then act" bug described for threads in Python Threads and Processes. Here the shared data is a row instead of a variable.

The simplest cure is to make the check and the change one statement. The database runs a single UPDATE as one indivisible step, so the balance cannot change between the test and the subtraction. rowcount then tells you whether it happened.

This example runs the two withdrawals one after the other, so that the output is the same every time. The point is that the pattern stays correct when they arrive together, because the database decides the order and each UPDATE sees the result of the one before it.

Behind this, databases use locks: while one transaction is changing data, others that want the same data must wait. SQLite allows one writer at a time for the whole database file. Server databases such as PostgreSQL and MySQL lock single rows, so writers to different accounts do not wait for each other. How much one transaction may see of another's unfinished or just-committed work is set by its isolation level.

Pitfall: a transaction on its own does not fix "read the balance in Python, then write the new balance". On most databases two transactions can still both read 1000 at their default isolation level. Use the single UPDATE ... AND balance >= ?, or lock the row while you read it (SELECT ... FOR UPDATE on PostgreSQL and MySQL), and keep a CHECK (balance >= 0) constraint as the last guard.

SQLAlchemy ORM

Part 3 of 4 · rows as Python objects

SQLAlchemy is the most widely used database library for Python. It is not in the standard library, so every example in this part needs one install first. The examples use the modern "2.0 style" and were checked with SQLAlchemy 2.0 on Python 3.9.

What an ORM is

An ORM, short for object-relational mapper, connects classes to tables. You write a class for each table. Each row becomes an object, each column an attribute, and the ORM writes the SQL for you.

In the databaseIn Python with an ORM
Table accountsClass Account
RowOne Account object
Column balanceAttribute account.balance
Foreign key customer_idaccount.owner, the Customer object itself
UPDATE accounts SET balance = ...account.balance = ... then commit

What you gain: less repeated SQL, one place that describes each table, normal Python objects to pass around, and the same code on SQLite, PostgreSQL and MySQL. What you pay: one more library to learn, a little speed, and SQL you no longer see. That last cost is real. An innocent-looking loop can run hundreds of queries, as the N+1 chapter shows.

Advice: an ORM does not replace knowing SQL. It writes SQL for you, and you need to be able to read what it wrote. That is why this page started with plain SQL.

Engine and models

The engine knows how to reach the database. It is made once, from a URL. A model is a class that describes one table. Models inherit from a base class that you create from DeclarativeBase.

PieceWhat it does
__tablename__The name of the table in the database
Mapped[int]A type hint that says "this attribute is a column holding an int". A plain type means NOT NULL
Mapped[Optional[int]]The column may be NULL, which Python sees as None
mapped_column(...)Extra details: primary key, foreign key, length, default value
relationship(...)Not a column. A Python-side link that follows the foreign key: customer.accounts is a list, account.owner is one customer
back_populatesNames the attribute on the other class, so both sides stay in step
create_all(engine)Creates any table that does not exist yet. It never changes a table that already exists

Python 3.9: SQLAlchemy reads these type hints while the program runs. Write Optional[int] and List["Account"] from typing. The newer spelling int | None works only on Python 3.10 and later.

Sessions: adding rows

A session is your workspace for one piece of work. You add objects to it, it remembers what is new or changed, and commit() sends everything to the database in one transaction. Used with with, the session is closed for you at the end.

session.get(Account, 3) fetches one object by primary key, or returns None if there is no such row. To keep this and the next few examples short, they use a single Account model that stores the owner's name directly.

Querying with select

select(Account) builds a query from Python expressions. where() filters and order_by() sorts. session.scalars() runs it and gives back Account objects.

CallReturns
session.scalars(query)A result you can loop over, one object per row
.all()A list of all of them
.first()The first one, or None
.one()Exactly one, and an error if there are zero or several
session.scalar(query)A single value, such as a count or a sum
session.execute(query)Rows as tuples, for queries that select several columns

Safety: values such as 5000 and "Malini%" are sent as placeholders, never pasted into the SQL. The ORM protects you from SQL injection as long as you build queries this way.

Updating and deleting objects

To update a row, change the attribute and commit. The session notices the change and writes the UPDATE. To delete a row, pass the object to session.delete().

Pitfall: nothing is saved until commit(). If the with block ends without a commit, the session rolls back and the changes are lost, quietly.

Relationships and the N+1 problem

With a relationship you move between tables by attribute: customer.accounts is the list of that customer's accounts. By default the ORM loads that list only when you first touch it, with one extra query. This is called lazy loading.

Lazy loading is fine for one customer. In a loop over N customers it means 1 query for the customers plus N queries for their accounts. That is the N+1 query problem. The example counts the queries that really reach the database.

Same answer, 6 queries against 2. selectinload(Customer.accounts) tells the ORM to fetch the accounts of all the customers in one second query. With 5 customers the difference is small. With 5000 it is 5001 queries against 2.

Habit: when a loop touches a relationship, load it up front with selectinload. If a page is slow, count the queries before anything else.

The transfer with the ORM

session.begin() is a context manager for one transaction: commit if the block finishes, rollback if it raises. Combined with the session itself on one line, it gives the same all-or-nothing transfer as before.

In both failed transfers, src.balance had already been lowered in Python. The rollback threw that change away, so the database never saw it.

This version reads the balance into Python and writes it back, so it has the race described in Two transfers at once. The ORM can also send the single safe UPDATE. The update() function builds it, and rowcount works as before.

Seeing the SQL

Pass echo=True to create_engine() and SQLAlchemy logs every statement it sends, with the values. It is the quickest way to learn what the ORM does, and to spot an N+1 problem.

This block is not run automatically, because each logged line starts with the current time and some lines include how long a step took, so the output differs on every run. The output below is from a real run, with the timestamps and the table-check lines trimmed.

Everything from this page is visible: the CREATE TABLE, the ? placeholders with the values sent separately, and the BEGIN and COMMIT of each transaction. The final ROLLBACK is harmless. The session was closed after a read, with nothing to save.

Pitfall: switch echo off in production. It is slow, and it writes customer data into the logs.

Raw SQL or the ORM

Both are fine, and many projects use both. SQLAlchemy can also run SQL you wrote yourself, through text(), with named placeholders such as :minimum.

SituationReach for
A small script, one or two tables, SQLitesqlite3 and plain SQL
An application with many tables: create, read, update, deleteThe ORM
A report with several joins, groups and totalsRaw SQL, often through text()
Changing thousands of rows in one goOne UPDATE statement, raw or with update(), not a loop over objects
You are learning how databases workRaw SQL first

Migrations

create_all() creates missing tables, but it never alters an existing one. When a live database needs a new column, you write a migration: a small versioned script that changes the tables step by step. The usual tool with SQLAlchemy is Alembic.

Other databases

SQLite is perfect for learning, tests and small tools. A web application with many users normally runs a database server such as PostgreSQL or MySQL. With SQLAlchemy the models and queries stay the same. You install a driver package for that database and change the URL given to create_engine(). These lines are not runnable here, because they need a real server.

Good habits

Part 4 of 4 · rules that prevent the common disasters

Most database trouble comes from a short list of mistakes. This part states the rules once and ends with a table of everything on the page.

Five habits

1. Never build SQL with string formatting

Values go through placeholders: ? in sqlite3, :name in text(), or ORM expressions. See Placeholders and SQL injection.

2. Keep connections and transactions short

Open, do the work, commit, close. An open transaction can hold locks that make other users wait. Do not keep one open while you wait for a person or call another service.

3. Close what you open

Use with Session(engine) as session: so the session is always closed, even after an error. For sqlite3, call conn.close(), or wrap the connection in contextlib.closing(). Create the engine once for the whole program and reuse it.

4. Store money as integers or Decimal, never float

A float cannot hold most decimal fractions exactly, and the tiny errors add up. Store money as a whole number of the smallest unit, paise for rupees, or use Decimal.

In a table, use an INTEGER column of paise, or the NUMERIC / DECIMAL column type on PostgreSQL and MySQL. The types themselves are covered in Python Data Types.

5. Keep secrets out of the code

A database URL usually contains a password. If it is written in a source file, it ends up in version control, where everyone with access to the code can read it for ever. Read it from an environment variable, a named value that the operating system hands to the program.

Pitfall: a secret that was committed once stays in the history, even after you delete the line. If that happens, change the password.

Quick reference

Tasksqlite3 (standard library)SQLAlchemy ORM (pip install)
Connectconn = sqlite3.connect("bank.db")engine = create_engine("sqlite:///bank.db")
Create tablesconn.execute("CREATE TABLE ...")Base.metadata.create_all(engine)
Insertconn.execute("INSERT ... VALUES (?, ?)", (a, b))session.add(Account(...))
Read one by idconn.execute("SELECT ... WHERE id = ?", (1,)).fetchone()session.get(Account, 1)
Read manyconn.execute("SELECT ...").fetchall()session.scalars(select(Account).where(...)).all()
Updateconn.execute("UPDATE ... SET ... WHERE id = ?", ...)account.balance = 100
Deleteconn.execute("DELETE FROM ... WHERE id = ?", ...)session.delete(account)
Saveconn.commit()session.commit()
Undoconn.rollback()session.rollback()
All-or-nothing blockwith conn:with Session(engine) as session, session.begin():
Rows changedcursor.rowcountsession.execute(update(...)).rowcount
Columns by nameconn.row_factory = sqlite3.RowAttributes on the object
Related rowsJOIN ... ON ...customer.accounts, with selectinload in loops
See the SQLYou wrote itcreate_engine(url, echo=True)

The bank's data is now safe on disk, but only this one program can reach it. The last page of the path puts it behind an HTTP API that any program can call: Python Web APIs.