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.
| Term | Meaning |
|---|---|
| Table | A named set of rows that all have the same columns, such as customers |
| Row | One record: one customer, one account |
| Column | One named piece of every row, with a type: name is text, joined is a number |
| Primary key | A column whose value is different for every row, so one row can be picked out exactly. Here it is id |
| Foreign key | A column that holds the primary key of a row in another table. It is how tables are linked |
| SQL | Structured Query Language, the language you use to create tables and to read and change rows |
| Query | One 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.
| Call | Gives you | Use it when |
|---|---|---|
fetchone() | The next row, or None | You expect one row, such as a lookup by id |
fetchall() | A list of all remaining rows | The result is small enough to hold in memory |
for row in cursor | One row at a time | The 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: the idea and ACID in plain words
- Commit and rollback
- A transfer that loses money
- The fix: with connection
- Constraints: a second line of defence
- Two transfers at once
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.
| Letter | Name | In plain words | At the bank |
|---|---|---|---|
| A | Atomicity | All of the changes happen, or none of them | A transfer never takes money out without putting it in |
| C | Consistency | The rules of the database hold before and after | No balance ends up below zero if a rule forbids it |
| I | Isolation | Transactions running at the same time do not see each other's half-finished work | Nobody sees the moment when the money has left one account and not reached the other |
| D | Durability | Once committed, the change survives a crash or power cut | A 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.
| Constraint | Rule |
|---|---|
NOT NULL | The column must have a value |
UNIQUE | No two rows may have the same value in this column |
CHECK (...) | The condition must be true for every row |
PRIMARY KEY | Unique, and identifies the row |
REFERENCES (foreign key) | The value must exist as a key in the other table |
DEFAULT | Not 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, and what it costs
- Engine and models
- Sessions: adding rows
- Querying with select
- Updating and deleting objects
- Relationships and the N+1 problem
- The transfer with the ORM
- Seeing the SQL
- Raw SQL or the ORM, migrations and other databases
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 database | In Python with an ORM |
|---|---|
Table accounts | Class Account |
| Row | One Account object |
Column balance | Attribute account.balance |
Foreign key customer_id | account.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.
| Piece | What 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_populates | Names 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.
| Call | Returns |
|---|---|
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.
| Situation | Reach for |
|---|---|
| A small script, one or two tables, SQLite | sqlite3 and plain SQL |
| An application with many tables: create, read, update, delete | The ORM |
| A report with several joins, groups and totals | Raw SQL, often through text() |
| Changing thousands of rows in one go | One UPDATE statement, raw or with update(), not a loop over objects |
| You are learning how databases work | Raw 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: placeholders, short connections, closed sessions, money types, secrets
- Quick reference
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
| Task | sqlite3 (standard library) | SQLAlchemy ORM (pip install) |
|---|---|---|
| Connect | conn = sqlite3.connect("bank.db") | engine = create_engine("sqlite:///bank.db") |
| Create tables | conn.execute("CREATE TABLE ...") | Base.metadata.create_all(engine) |
| Insert | conn.execute("INSERT ... VALUES (?, ?)", (a, b)) | session.add(Account(...)) |
| Read one by id | conn.execute("SELECT ... WHERE id = ?", (1,)).fetchone() | session.get(Account, 1) |
| Read many | conn.execute("SELECT ...").fetchall() | session.scalars(select(Account).where(...)).all() |
| Update | conn.execute("UPDATE ... SET ... WHERE id = ?", ...) | account.balance = 100 |
| Delete | conn.execute("DELETE FROM ... WHERE id = ?", ...) | session.delete(account) |
| Save | conn.commit() | session.commit() |
| Undo | conn.rollback() | session.rollback() |
| All-or-nothing block | with conn: | with Session(engine) as session, session.begin(): |
| Rows changed | cursor.rowcount | session.execute(update(...)).rowcount |
| Columns by name | conn.row_factory = sqlite3.Row | Attributes on the object |
| Related rows | JOIN ... ON ... | customer.accounts, with selectinload in loops |
| See the SQL | You wrote it | create_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.