Every layer of software you use is a translation of a layer below it. An ORM like SQLAlchemy translates Python code into the SQL you wrote by hand last month. SQL itself is translated by the database engine into operations on files. And every file — text, numbers, images, your database — is, at the bottom, the same thing: bits. This article connects three topics that look separate but are really one story: what your data is made of, how it lives in files, and how modern tools let you work with it without touching the lowest layers — provided you understand what those layers are doing.
This article covers three connected topics from the second half of Unit 3: the ORM — what SQLAlchemy is and how it maps onto the SQL you already know; binary representation — bits, bytes, and how integers, text, and images become numbers; and file I/O — reading and writing .txt and .csv files from Python, and why a CSV file is not a database.
| Level | What it covers | When to read it |
|---|---|---|
| Basic idea | What an ORM is; the SQL-to-SQLAlchemy mapping; what a bit and a byte are; how to read and write files in Python | Right now, alongside the Week 18 classes. |
| At a deeper level | When the ORM helps and when raw SQL matters; how text and images become bits; the difference between a CSV file and a database | Once you have written a few SQL/ORM pairs yourself. |
| At the deepest level | Why abstraction layers exist and what they cost; character encoding history; where these ideas appear in professional practice | When the basics feel solid and you want to know why. |
1 What an ORM Is
ORM stands for object-relational mapper. It is a translator. On one side is your Python code, which thinks in objects — a Student with a .name and a .grade. On the other side is the database, which thinks in tables, rows, and SQL. The ORM sits in the middle and converts between the two.
In one sentence: an ORM lets you query and change a database by writing Python instead of SQL — by generating the SQL for you.
The key word is generating. The SQL does not go away. Every SQLAlchemy query you write is turned into a SQL statement — one you could have written yourself — and sent to MySQL exactly as your hand-written queries were. The ORM changes who writes the SQL, not whether SQL happens.
Why bother with a translator at all? Four reasons:
- Python objects, not tuples. Raw SQL results arrive as tuples like
('Ana', 91)— you have to remember which position means what. The ORM gives you aStudentobject wherestudent.gradesays what it is. - Safety from SQL injection. The ORM builds queries with parameters, never by gluing user text into a SQL string. The most famous category of web vulnerability becomes structurally difficult to create.
- Harder to make catastrophic mistakes. There is no single innocent-looking ORM line that quietly means
DELETE FROM students;. Destructive operations are more explicit and deliberate. - Portability. The same SQLAlchemy code can talk to MySQL, PostgreSQL, or SQLite. The ORM generates the right dialect of SQL for whichever database is behind it.
This is why the course taught SQL first. When a SQLAlchemy query misbehaves, the question you must be able to ask is: what SQL is this generating, and is that the SQL I meant? A programmer who never wrote SQL by hand cannot ask that question. They can only copy ORM code and hope. You are in a different position: every ORM query is something you can mentally translate back.
The ORM is an example of an abstraction layer — a tool that hides a lower level so you can work at a higher one. Abstraction is the central strategy of computer science: Python abstracts machine code, SQL abstracts file operations, the ORM abstracts SQL. Each layer trades control for convenience.
Every abstraction has costs. Joel Spolsky named the most important one the law of leaky abstractions: all non-trivial abstractions leak. Sooner or later the lower level shows through — an ORM query is unexpectedly slow, generates strange SQL, or cannot express what you need — and at that moment, the programmer who understands the layer below fixes the problem, while the one who does not is stuck. The purpose of learning SQL before SQLAlchemy is precisely to make you the first kind of programmer.
2 The Reveal: SQL and SQLAlchemy Side by Side
Here are queries you already know in raw SQL, next to their SQLAlchemy equivalents. Read each pair and find the mapping: where did WHERE go? Where is ORDER BY?
| Raw SQL | SQLAlchemy equivalent |
|---|---|
SELECT * FROM students; | db.session.execute(select(Student)).scalars().all() |
SELECT * FROM students WHERE grade > 80; | db.session.execute(select(Student).where(Student.grade > 80)).scalars().all() |
SELECT * FROM students ORDER BY name ASC; | db.session.execute(select(Student).order_by(Student.name)).scalars().all() |
INSERT INTO students (name, grade) VALUES ('Ana', 91); | db.session.add(Student(name='Ana', grade=91)) then db.session.commit() |
The pattern: each SQL clause becomes a Python method. select(Student) is SELECT … FROM. .where(…) is WHERE. .order_by(…) is ORDER BY. Run both versions of any pair and the results are identical — because the ORM sent essentially the same SQL you would have.
Two pieces of the ORM version have no SQL twin, and they are worth understanding rather than memorising:
<strong>.scalars()</strong>— the raw result of a query is rows. When you selected wholeStudentobjects, each row is a one-item wrapper around the object you actually want..scalars()unwraps them, so.all()gives you a clean list ofStudentobjects instead of a list of wrappers.<strong>db.session.commit()</strong>— the session is a workspace.db.session.add(…)stages a change; nothing reaches the database untilcommit(). This means you can stage several changes and save them all at once — or, if something goes wrong, save none of them. Forgettingcommit()is the classic beginner ORM bug: the code runs, no error appears, and the database is unchanged.
Given SQL, write the SQLAlchemy. Given SQLAlchemy, write the SQL. If you can only translate one way, you do not yet own the mapping. The quiz and the mini data project both require producing identical results from both versions — the translation skill is the assessed skill.
When would a professional still write raw SQL? Three common cases: complex multi-table joins where the ORM version becomes harder to read than the SQL it generates; performance tuning, where you need precise control over exactly which query runs; and database-specific features that the ORM cannot express cleanly. Most production codebases are a mixture — ORM for the routine ninety percent, raw SQL for the demanding ten.
You can also make the ORM show its work. SQLAlchemy can log every SQL statement it generates, and simply printing a query object displays the SQL it would send. Professionals use this constantly: when an ORM query behaves oddly, the first move is to look at the generated SQL and check it against the SQL you would have written. That check is only meaningful because you can write the SQL yourself.
3 Binary: Bits, Bytes, and Counting in Base 2
Everything a computer stores — your database rows, this article, a photo — is stored as bits. A bit is a single value that is either 0 or 1. A byte is a group of 8 bits.
With bits, numbers are written in base 2 instead of base 10. In base 10, each column is worth ten times the one to its right (ones, tens, hundreds). In base 2, each column is worth two times the one to its right (ones, twos, fours, eights…).
Place value: 8 4 2 1 Binary: 1 0 1 1 = 8 + 2 + 1 = 11 Binary: 0 1 1 0 = 4 + 2 = 6
That is the whole trick. 1011 in binary is 11 in decimal because there is a 1 in the eights column, the twos column, and the ones column. Counting from 0 to 15 needs 4 bits; one byte (8 bits) can hold any number from 0 to 255.
Why does a byte top out at 255? With 8 bits, the place values are 128, 64, 32, 16, 8, 4, 2, 1. Set them all to 1 and the total is 255. Each additional bit doubles the range — this is why the number of bits matters so much:
| Bits | Distinct values | Enough for… |
|---|---|---|
| 1 | 2 | A yes/no answer; a boolean |
| 4 | 16 | Numbers 0–15 |
| 8 (one byte) | 256 | Numbers 0–255; one character of basic English text |
| 32 | about 4.3 billion | A typical integer; one pixel's full colour |
This doubling explains something you have already seen in the database: why a column has a type and a size. When a table declares a column as a certain integer type, it is declaring how many bits each value gets — and therefore the largest number that column can ever hold.
Why base 2 at all? Because the physical hardware is built from switches that are either on or off — transistors holding a voltage or not. Two reliably distinguishable states are easy to build, cheap, and resistant to noise; ten distinguishable voltage levels would be far more fragile. Binary is not a mathematical preference. It is an engineering consequence of what silicon can do dependably, billions of times per second, for years.
A consequence worth knowing: not everything fits neatly. Negative numbers require a convention (the standard one is called two's complement), and decimal fractions like 0.1 cannot be represented exactly in binary at all — which is why Python sometimes tells you that 0.1 + 0.2 is 0.30000000000000004. That is not a bug in Python. It is base 2 being honest about a number that has no finite binary representation, the same way 1/3 has no finite decimal one.
4 Encoding: How Text and Images Become Bits
Bits store numbers. So how does a computer store the letter A, or a photograph? By encoding: an agreed-upon table that maps things to numbers.
For text, the classic table is ASCII. It assigns a number to each character: A is 65, B is 66, a is 97, the space character is 32. The word "Hi" is stored as the numbers 72 and 105 — two bytes:
H = 72 = 01001000 i = 105 = 01101001
For images, the idea is the same at larger scale. An image is a grid of pixels, and each pixel's colour is stored as numbers — typically three bytes: how much red, how much green, how much blue (0–255 each). The picture is just a very long list of colour numbers plus a note of the grid's width and height.
Encoding explains why file sizes are what they are. Do the arithmetic yourself:
- A sentence of 60 characters in basic ASCII: 60 bytes.
- A page of 3,000 characters: about 3 kilobytes.
- An uncompressed photo of 4,000 × 3,000 pixels at 3 bytes per pixel: 36,000,000 bytes — 36 megabytes for one photo.
That last number is why images dominate storage and network traffic, and why image formats like JPEG and PNG exist: they compress the pixel data, storing it more cleverly than one-number-per-channel-per-pixel. Text is thousands of times cheaper than imagery, which is also why your database stores names and grades directly but real applications store images as files and keep only the file's location in the database.
Encoding also explains a distinction you met in Python's type system: the character "7" and the integer 7 are different things stored differently. The character is the ASCII number 55; the integer is the binary number 00000111. This is why "7" + "7" is "77" but 7 + 7 is 14 — the bits mean what the type says they mean.
ASCII covers 128 characters — enough for English, useless for Polish, Arabic, or Chinese. The modern answer is Unicode, a table that assigns a number to essentially every character in every writing system, and UTF-8, the dominant way of storing Unicode numbers as bytes. UTF-8 is designed so that basic English characters take one byte (identical to ASCII, for compatibility), while other characters take two, three, or four bytes as needed. The Polish letter ł is one character but two bytes.
The deepest point is that meaning is never in the bits — it is in the agreed interpretation. The byte 01001000 is the number 72, the letter H, or one channel of a pixel's colour, depending entirely on what the reader has agreed to treat it as. When a file opens as garbage symbols, nothing in the data is broken; the reader is simply applying the wrong agreement. "Encoding" is the name for those agreements, and file formats are, at bottom, published agreements about what the bits mean and in what order.
5 File I/O: Reading and Writing Files in Python
File I/O (input/output) means your program reading data from files and writing data to them. It is how a program's data survives after the program ends — the simplest form of persistence.
The standard pattern uses with open(…):
# Reading a text file
with open("notes.txt", "r") as f:
for line in f:
print(line.strip())
# Writing a text file
with open("output.txt", "w") as f:
f.write("First line\n")
f.write("Second line\n")The second argument is the mode: "r" to read, "w" to write (which erases any existing content first), "a" to append to the end. The with block closes the file automatically when the block ends — always use it.
A CSV (comma-separated values) file is a text file with a convention: one record per line, fields separated by commas, usually with a header line naming the fields. Python's csv module reads it directly into the data structure you spent Week 16 mastering — a list of dictionaries:
import csv
with open("students.csv", "r") as f:
students = list(csv.DictReader(f))
# students is now a list of dicts:
# [{'name': 'Ana', 'grade': '91'}, {'name': 'Tomek', 'grade': '78'}, ...]
high = [s for s in students if int(s["grade"]) > 80]Note the int(…) in the last line. Everything read from a CSV file arrives as a string — including the grades. The file is text; the numbers in it are characters until you convert them. Forgetting this is the most common CSV bug: comparing "91" > 80 raises an error, and sorting strings puts "100" before "9".
When a task says "read the data, modify it, write it back," write the result to a new file. Opening your source file in "w" mode erases it instantly — before your program has even read it. Keeping the original intact means a bug in your code costs you nothing. This is the file-I/O version of running SELECT before DELETE: protect the data first, act second.
Now the three topics of this article meet. A CSV file looks like a database table — rows, columns, a header. So why did this course spend three weeks on MySQL? Ask what the CSV cannot do:
- No querying. To find students with a grade above 80, your program reads every line of the file, every time. There is no WHERE; there is only the loop.
- No types or constraints. Every value is a string. Nothing stops a row with a grade of "banana", a missing field, or a duplicated id.
- No safe concurrent access. Two programs writing the same CSV at the same time will corrupt it. A database coordinates thousands of simultaneous users.
- No partial updates. Changing one value means rewriting the whole file.
A CSV file is best understood as a table with none of the machinery — data without a database management system around it. It remains genuinely useful as an exchange format: nearly every tool can read and write it, which is why real datasets so often arrive as CSV and are then loaded into a database to be worked with. Reading a CSV into Python and asking a question of it is, in miniature, what the MySQL server does for you at scale — and having now done the job by hand at every layer, you know exactly what the tools above are doing on your behalf.
- Explain in one sentence what an ORM does and why it exists. What does it mean to say the ORM "generates" SQL rather than replacing it?
- Take the query
db.session.execute(select(Student).where(Student.grade > 80)).scalars().all()and write the raw SQL it corresponds to. Which Python method maps to which SQL clause? - Write the SQLAlchemy equivalent of
INSERT INTO students (name, grade) VALUES ('Kasia', 88);. What happens if you forgetdb.session.commit(), and why is this bug hard to notice? - Give two situations where a professional would choose raw SQL over the ORM, and two reasons they would use the ORM for everything else.
- Convert the binary numbers
1010,0111, and1111to decimal, showing the place values. Then convert the decimal number 13 to binary. - A one-byte column can store numbers from 0 to 255. Explain where 255 comes from. How large is the range if you use two bytes instead?
- Using rough arithmetic, explain why one uncompressed photo can be larger than a whole book's worth of text. What does this have to do with encoding?
- You read a CSV of student records and run
if s["grade"] > 80:— Python raises an error. Why? What one change fixes it? - A classmate says: "A CSV file has rows and columns, so it's basically a database." Give three specific things a database does that a CSV file cannot.
- This article claims Python, SQL, and the ORM are all "abstraction layers." Pick one and explain what it hides, what that convenience costs, and why understanding the layer below still matters.