Safe Queries and When to Reach for an ORM
Placeholders, named columns, and the framework question
In this lesson
Two habits separate SQL that survives real data from SQL that doesn’t. The first is never letting a value become part of your query text. The second is not depending on column order. This lesson covers both, then steps back to the question you will eventually face: should you be writing SQL at all?
Explain it like I’m 5
A placeholder is the difference between handing the librarian a slip with a title written on it, and letting a stranger rewrite the library’s catalog.
What actually goes wrong without placeholders
Formatting a value into a query works perfectly until a value contains a character SQL treats as special. The classic is an apostrophe, and plenty of real names have one.
Because the query is just text, an apostrophe in the data closes the string early. The database then tries to parse the rest of the name as SQL keywords and fails. That is the harmless version. The dangerous version is when the value was chosen deliberately, and the fragment it injects is valid SQL that does something you did not intend, which is the family of bugs called SQL injection.
name = "O'Brien"
# Broken: the apostrophe ends the string early.
bad = f"SELECT * FROM members WHERE name = '{name}'"
print(bad)
# SELECT * FROM members WHERE name = 'O'Brien'
# ^ the database sees the string end here
SELECT * FROM members WHERE name = 'O'Brien'
Placeholders keep data out of the query
Pass ? in the SQL and the value separately. This is a parameterized query. The database receives the query and the data as two different things, so a value can never be read as an instruction, no matter what characters it contains.
Note what you do not do: no escaping, no quoting, no sanitizing. Those are attempts to make mixing safe. Placeholders stop the mixing.
import sqlite3
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE members (name TEXT, city TEXT)")
conn.executemany("INSERT INTO members VALUES (?, ?)", [
("O'Brien", "Dublin"),
("Chen", "Lisbon"),
])
conn.commit()
row = conn.execute(
"SELECT name, city FROM members WHERE name = ?", ("O'Brien",)
).fetchone()
print(row)
("O'Brien", 'Dublin')
Read columns by name, not by position
Rows arrive as tuples, so row[0] is the first selected column. That is fine for two columns and miserable for eight, and it breaks silently the day someone reorders the SELECT.
Set conn.row_factory = sqlite3.Row and rows can be indexed by column name instead. It still behaves like a tuple, so existing positional code keeps working.
import sqlite3
conn = sqlite3.connect(":memory:")
conn.row_factory = sqlite3.Row # ask for named access
conn.execute("CREATE TABLE members (name TEXT, city TEXT)")
conn.execute("INSERT INTO members VALUES (?, ?)", ("Chen", "Lisbon"))
conn.commit()
row = conn.execute("SELECT name, city FROM members").fetchone()
print(row["name"], "from", row["city"])
print("still works positionally:", row[0])
Chen from Lisbon still works positionally: Chen
Two fixes in one. Turn on sqlite3.Row, then rewrite the unsafe query to use a placeholder, and print the name and city by column name.
import sqlite3
conn = sqlite3.connect(":memory:")
# TODO: turn on named column access
conn.execute("CREATE TABLE members (name TEXT, city TEXT)")
conn.executemany("INSERT INTO members VALUES (?, ?)", [
("O'Brien", "Dublin"), ("Chen", "Lisbon"),
])
conn.commit()
wanted = "O'Brien"
# TODO: rewrite this to use a ? placeholder
row = conn.execute(f"SELECT name, city FROM members WHERE name = '{wanted}'").fetchone()
print(row["name"], "from", row["city"])Add conn.row_factory = sqlite3.Row right after connecting. Then replace the f-string with a plain string containing ?, passing (wanted,) as the second argument — trailing comma included.
import sqlite3
conn = sqlite3.connect(":memory:")
conn.row_factory = sqlite3.Row
conn.execute("CREATE TABLE members (name TEXT, city TEXT)")
conn.executemany("INSERT INTO members VALUES (?, ?)", [
("O'Brien", "Dublin"), ("Chen", "Lisbon"),
])
conn.commit()
wanted = "O'Brien"
row = conn.execute(
"SELECT name, city FROM members WHERE name = ?", (wanted,)
).fetchone()
print(row["name"], "from", row["city"])What an ORM does, and whether you need one
An ORM (object-relational mapper) lets you describe your tables as Python classes and query them with Python instead of SQL. You saw the idea at the end of Unit 7: define a Book class, and the framework creates the table and turns method calls into queries.
The trade is straightforward. Less SQL, more framework. You stop writing queries and start learning a particular library’s way of expressing them. For a large application with many tables and relationships that is a clear win. For a script with one table it is a lot of machinery for very little.
- Stay with
sqlite3when you have a handful of tables, want no dependencies, or want to see exactly what runs. - Reach for an ORM when you have many related tables, schema changes to manage over time, or a web framework that already includes one.
Either way, the SQL you have just learned stays useful. An ORM generates SQL, and when a query is slow or wrong, reading it is how you find out why.
Common mistake: Using an f-string for ‘just this one’ query
The value is internal and obviously safe, so the shortcut feels harmless.
Use a placeholder anyway. Values become user-supplied over time, and a habit you apply selectively is not a habit.
Common mistake: Trying to escape quotes manually instead of using placeholders
Doubling apostrophes fixes the immediate error, so it looks like the solution.
Hand-escaping is a losing game with many edge cases. Placeholders remove the problem rather than patching it.
Common mistake: Depending on column position after changing the SELECT
row[2] keeps running after a reorder; it just returns the wrong column.
Use sqlite3.Row and index by name. Wrong-column bugs are silent, which makes them expensive.
Common mistake: Assuming an ORM means you never need SQL
It hides queries so thoroughly that they stop feeling real.
Learn to see the generated SQL. When something is slow, that is the only level at which the problem is visible.
Why does f-string formatting a name like O'Brien into SQL break?
The query is text, so a quote inside the data terminates the string. Placeholders keep the value out of the text entirely.
What does conn.row_factory = sqlite3.Row give you?
It changes how rows are returned so row["city"] works, without breaking row[0].
When is raw sqlite3 the better choice over an ORM?
ORMs pay off with scale and relationships. For a small script they add a framework to learn for little benefit.
Mini exercise (medium)
Write find_members(conn, city) that returns a list of member names in the given city, sorted alphabetically. Use a placeholder for the city and read the name by column name, not position.
Write it out. Finish the snippet below and press Run. It works straight away in your browser.
import sqlite3
conn = sqlite3.connect(":memory:")
conn.row_factory = sqlite3.Row
conn.execute("CREATE TABLE members (name TEXT, city TEXT)")
conn.executemany("INSERT INTO members VALUES (?, ?)", [
("O'Brien", "Dublin"), ("Chen", "Lisbon"), ("Alvarez", "Dublin"),
])
conn.commit()
def find_members(conn, city):
return [] # TODO: names in that city, alphabetical, via a placeholder
print(find_members(conn, "Dublin"))Set conn.row_factory = sqlite3.Row before querying. Then a list comprehension over the cursor gives you the names in one line.
import sqlite3
conn = sqlite3.connect(":memory:")
conn.row_factory = sqlite3.Row
conn.execute("CREATE TABLE members (name TEXT, city TEXT)")
conn.executemany("INSERT INTO members VALUES (?, ?)", [
("O'Brien", "Dublin"), ("Chen", "Lisbon"), ("Alvarez", "Dublin"),
])
conn.commit()
def find_members(conn, city):
cur = conn.execute(
"SELECT name FROM members WHERE city = ? ORDER BY name", (city,)
)
return [row["name"] for row in cur]
print(find_members(conn, "Dublin"))