Python & Databases
Lesson 4 of 5
4Python & Databases: from MySQLdb to an ORM
The big pictureHow a Python program talks to a database: first the direct way (a driver sends SQL and receives rows), then the object way, where a table becomes a class, a row becomes an object, and the library writes the SQL for you.
What the picture shows: The code sends the query and its values separately through the driver, and rows come back to Python. A change is not permanent until you call commit.
Map of this part
The concepts in order, click any one to jump to it:
Two ways into the same idea
Your program and the database speak two different languages. The first way is to send it letters in its own language (SQL) through a postman (the driver). The second way is to hire an interpreter (an ORM): you work with ordinary Python objects and it writes the letters for you.
A driver follows DB-API, like sqlite3: a connection, a cursor, then execute with ? placeholders instead of string building, which prevents SQL injection. Changes are not saved until commit. SQLAlchemy ORM maps a table to a class and a row to an object, with an Engine, a Session, models and queries; flush sends without committing.
Required Fundamentals
This part is the bridge between two worlds you have learned separately, so make sure you are comfortable in both:
Classes and objects (the Classes and objects lesson)
You will turn a table into a class, so you need __init__, attributes, __repr__, and the idea that each object has its own independent state.
SQL and JOIN (the SQL CRUD and SQL joins lessons)
The library writes SQL for you, but you must be able to read it to confirm it is what you meant. Without SELECT … WHERE you cannot judge what the ORM is doing behind the curtain.
Table design (the Database design lesson)
Primary and foreign keys and one-to-many relationships: these exact ideas become lines in a class definition (ForeignKey and relationship).
Exceptions (the Exceptions lesson)
Connecting to a database can fail (wrong password, server down). try/except/finally is what guarantees the connection is closed.
Core Concepts
The big idea: storage (a table in a database) and usage (an object in Python) are different worlds. Something has to translate between them, and there are two choices: translate by hand or let a library do it.
1The driver and the DB-API
A driver is a library that speaks one server’s protocol. For MySQL the common one is mysqlclient, imported as MySQLdb. Every Python driver follows one convention called DB-API 2.0 (document PEP 249), so the style is the same everywhere: connect(), then cursor(), then execute(), then fetch…(), then commit(), then close().
| Object | Role | Key calls |
|---|---|---|
| Connection | An open channel to the server; it owns the current transaction. | commit() rollback() close() |
| Cursor | The tool that runs statements and walks through their results. | execute() executemany() fetchone() fetchall() close() |
| Parameters | Values passed separately from the statement text and slotted into placeholders. | execute(sql, (v1, v2)) |
2Placeholders, not string-building: what stops SQL injection
The most dangerous mistake at this layer is building the statement by gluing user text into it. Any input containing a quote can “escape” the value and write SQL of its own. The fix is to write the statement with placeholders (%s in MySQLdb, ? in sqlite3) and pass the values as a second argument; the driver is the one that escapes them safely.
| Driver | Placeholder style | Note |
|---|---|---|
MySQLdb | %s | Used for every type (text and numbers). Do not quote the %s. A literal percent sign is written %%. |
sqlite3 | ? | A different style for the same idea; never copy a statement between drivers without checking. |
"… WHERE name = '%s'" % user_input (Python’s % formatting operator) believing it is the same placeholder. The difference is fatal: that glues the text before it reaches the driver. The right form is cur.execute("… WHERE name = %s", (user_input,)) with a comma between the two arguments, and a single value needs a trailing comma to be a tuple.
3Transactions: nothing is saved until commit
The driver opens a transaction implicitly, so INSERT, UPDATE and DELETE stay pending and invisible to other connections until you call commit(). If something fails in the middle, rollback() undoes everything since the last commit. A bare SELECT does not need a commit.
4Why an ORM? The mismatch
Rows arrive from the driver as raw tuples like (1, 'Flat White', 1400), so you are the one who must remember that index 2 is the price, write SQL for every operation, and convert rows to objects. An ORM (Object-Relational Mapper) removes that repetitive work by defining one map:
| In the database | In Python |
|---|---|
Table drinks | Class Drink |
| A row | An instance of the class |
| A column | An attribute on the class |
| A foreign key | relationship: a list or a related object |
INSERT / UPDATE / DELETE | Modify the object, or session.add / session.delete, then commit |
5The four building blocks of SQLAlchemy
Engine
The starting point: created once from a URL like mysql+mysqldb://user:pass@host/db; it manages a connection pool so you do not open a new connection per operation.
Base and mapped classes
A base class from declarative_base(); every class inheriting it that sets __tablename__ and columns becomes “mapped” to a table. Base.metadata.create_all(engine) creates only the tables that are missing.
Session
Your “conversation” with the database: it tracks the objects you loaded or added, keeps an identity map (one object per primary key), and postpones writing changes until a flush.
Query
session.query(Drink).filter(…).order_by(…).all() builds SQL from Python expressions; .first(), .one() and .get() cover the other cases.
6flush vs. commit
flush sends the pending SQL to the database inside the transaction (so a new object receives its id) but does not make it permanent. commit flushes first and then finalizes the transaction. The session also auto-flushes before any query, which is why you see your uncommitted changes in later queries within the same session.
with Session(engine) as session: and Python closes it automatically at the end of the block. After commit the objects are “expired” and reloaded on first access; this default is deliberate.
How Python Talks to the Database, Step by Step
Three pictures summarize everything: where each layer sits, what happens when you save an object, and the states an object passes through.
Diagram 29, The access layers: the dotted path is the direct way with no ORM, the full path is what happens with an ORM, in both cases the driver is what actually talks to the server.
Diagram 30, The journey of add then commit: no SQL is sent at add; everything goes out together at commit (or at an earlier flush).
Diagram 31, The object lifecycle with the session: errors like “DetachedInstanceError” usually mean you touched an object after its session was closed.
- Create the engine once at program start, define the classes, and call
create_allif you want the tables created. - Open a session for each short unit of work (a request or a script), not one global session forever.
- Create objects and
addthem, or load them with a query, then change their attributes like any Python object. - Call
commit()once when the unit is complete, orrollback()on any error. - Close the session (the
withblock does it for you) and the connection returns to the pool.
Worked Example: from Direct SQL to Classes
We stay in our coffee shop (the Database design lesson) with two tables: baristas and drinks (each drink is made by one barista). So the examples run on any machine without a server we use in-memory SQLite; moving to MySQL changes only the connection line.
Step 1, The direct way (DB-API)
import sqlite3
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("CREATE TABLE drinks (id INTEGER PRIMARY KEY, name TEXT NOT NULL, price_halalas INTEGER NOT NULL)")
cur.execute("INSERT INTO drinks (name, price_halalas) VALUES (?, ?)", ("Flat White", 1400))
cur.execute("INSERT INTO drinks (name, price_halalas) VALUES (?, ?)", ("Cortado", 1200))
con.commit() # without it the changes are not saved
cur.execute("SELECT name FROM drinks WHERE name = ?", ("Cortado",))
print(cur.fetchall()) # [('Cortado',)]
con.close()With MySQL the structure is the same; only the connection and the placeholder style (%s) change. Note that the password comes from an environment variable, not from the code:
import os
import MySQLdb
conn = MySQLdb.connect(host="localhost", port=3306, user="cafe_user",
passwd=os.environ["CAFE_DB_PASSWORD"], db="cafe", charset="utf8mb4")
cur = conn.cursor()
try:
cur.execute("INSERT INTO drinks (name, price_halalas) VALUES (%s, %s)", ("V60", 1800))
conn.commit()
except MySQLdb.Error:
conn.rollback()
raise
finally:
cur.close()
conn.close()password and database, while many pages (including project references) use passwd and db. Check your installed version. The charset argument must be passed by keyword.
Step 2, Why placeholders? Try the injection yourself
evil = "x' OR '1'='1"
# safe: the value is treated as one string
cur.execute("SELECT name FROM drinks WHERE name = ?", (evil,))
print(cur.fetchall()) # []
# dangerous: gluing text changes the meaning of the statement
bad = "SELECT name FROM drinks WHERE name = '%s'" % evil
print(bad) # SELECT name FROM drinks WHERE name = 'x' OR '1'='1'
cur.execute(bad)
print(cur.fetchall()) # [('Flat White',), ('Cortado',)] every row!Step 3, The same world with an ORM: one map
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import declarative_base, relationship, Session
Base = declarative_base()
class Barista(Base):
__tablename__ = "baristas"
id = Column(Integer, primary_key=True)
name = Column(String(60), nullable=False)
drinks = relationship("Drink", back_populates="barista", order_by="Drink.id")
class Drink(Base):
__tablename__ = "drinks"
id = Column(Integer, primary_key=True)
name = Column(String(60), nullable=False)
price_halalas = Column(Integer, nullable=False)
barista_id = Column(Integer, ForeignKey("baristas.id"))
barista = relationship("Barista", back_populates="drinks")
def __repr__(self):
return f"Drink({self.name!r}, {self.price_halalas})"
# SQLite for trying it out. With MySQL: "mysql+mysqldb://user:pass@localhost/cafe?charset=utf8mb4"
engine = create_engine("sqlite://", pool_pre_ping=True)
Base.metadata.create_all(engine)Step 4, Add objects and watch the lifecycle
from sqlalchemy import inspect
with Session(engine) as s:
d = Drink(name="Flat White", price_halalas=1400)
print(inspect(d).transient) # True a new object no session knows
s.add(d)
print(inspect(d).pending) # True waiting to be written
s.flush()
print(inspect(d).persistent, d.id) # True 1 it received its key
s.commit()
with Session(engine) as s:
s.add_all([
Barista(name="Noura", drinks=[Drink(name="Cortado", price_halalas=1200),
Drink(name="Mocha", price_halalas=1600)]),
Barista(name="Sultan", drinks=[Drink(name="V60", price_halalas=1800)]),
])
s.commit() # baristas + their drinks in one transaction; foreign keys filled automaticallyStep 5, Querying and updating
with Session(engine) as s:
cheap = (s.query(Drink)
.filter(Drink.price_halalas < 1500)
.order_by(Drink.price_halalas)
.all())
print(cheap) # [Drink('Cortado', 1200), Drink('Flat White', 1400)]
d = s.get(Drink, 1) # lookup by primary key
d.price_halalas = 1500 # changing the attribute is enough; no hand-written UPDATE
s.commit()
print(s.query(Drink).filter_by(name="Flat White").one().price_halalas) # 1500Step 6, The N+1 trap: count the queries
A relationship is loaded lazily by default: barista.drinks is not read until you first touch it. In a loop over all baristas that means one query for the baristas + one query per barista. We counted queries with an engine event listener (before_cursor_execute) and got:
| Approach | Code | Queries (two baristas) |
|---|---|---|
| Lazy (default) | for b in s.query(Barista): b.drinks | 3 |
| Eager load with JOIN | s.query(Barista).options(joinedload(Barista.drinks)) | 1 |
from sqlalchemy.orm import joinedload
for b in s.query(Barista).options(joinedload(Barista.drinks)).order_by(Barista.id):
print(b.name, [x.name for x in b.drinks])
# Noura ['Cortado', 'Mocha']
# Sultan ['V60']create_engine(url, echo=True) so it prints every SQL statement. If you see the same statement repeated dozens of times with different values, that is N+1. Always read what the ORM generates: it “writes SQL for you”, it does not “excuse you from understanding it”.
Myth vs. Reality
| The myth | The reality |
|---|---|
| “With an ORM I do not need to learn SQL.” | The ORM generates SQL and you are responsible for its quality. Without understanding JOIN and WHERE you will not notice N+1 or a slow query. |
| “commit protects against injection.” | Injection is about how the statement was built, not how it was saved. Placeholders (which the ORM uses automatically) are the protection, and the risk remains in any raw SQL you build by gluing text. |
| “An ORM makes the code fully independent of the database type.” | It gets you very close (often just a URL change), but types, constraints and some functions differ between engines, so test on the real one. |
| “One global session is easier and faster.” | A session is not safe to share across threads, and objects and open transactions pile up in it. A short session per unit of work is better. |
Where Is This Used in Practice?
Web applications
Frameworks such as Flask and Django reach the database through an ORM: each request opens a short session, reads and writes, then closes it. You will see this come together in the Frameworks track that follows this one.
Testing
Changing the URL to sqlite:// gives each test an in-memory database: no server, no leftover data.
Switching storage
Writing business logic against Python objects makes it easier to swap where data is stored later (file → database), the idea behind the storage models you will meet in design patterns.
Scripts and reports
A short MySQLdb script is sometimes all a report or a data migration needs; pick the lightest tool that fits the size of the job.
Try it yourself
Why is cur.execute("... WHERE name = %s", (name,)) better than gluing the name into the text?
The value is sent separately and is never read as a command, even if the user types a quote or SQL.
Quick Review Flashcards
%s for every type, with the values passed as a tuple in a second argument.Check Your Understanding
1. Why do we not write cur.execute("SELECT * FROM drinks WHERE id = " + user_id) even if user_id is a number?
Because the value arrives from outside as text and can carry 1 OR 1=1 or worse. It must be passed as a parameter so the driver escapes it: cur.execute("… WHERE id = %s", (user_id,)).
2. After session.add(d) then print(d.id) you get None. Why, and how do you get the number before commit?
Because the object is pending and the INSERT has not been sent. Call session.flush(): the INSERT runs inside the transaction and the object receives its id.
3. (Spot the bug) A script loads 500 baristas then prints each one’s drinks and takes a minute. What is your first hypothesis?
N+1: 501 queries because of lazy loading. Load the drinks together with the baristas (joinedload) to make it one query. Verify with echo=True before assuming.
4. Why open the session with a with block?
Because closing is automatic even when an exception occurs, so no connections or transactions are left hanging.
5. (Decision) You want drink names to be unique. Where does the rule go: in the class or in the database?
In the database, through a constraint (unique=True in the column definition creates UNIQUE), because a database constraint protects against any application or script that writes to it; then handle IntegrityError in the code.
Resources for this part
Sorted by kind: start with the official docs when you want the precise reference, or with an article or video when you want the explanation first.
Resources for this lesson
Grouped by type. Start with the official docs for precision, or an article or video for the explanation.
Official docs3
Done with this lesson?
Sign in to save your progress.