Executing Raw SQL

Sometimes raw SQL is the simplest solution:

• complex reporting queries
• bulk updates
• vendor-specific SQL features

The ORM is great, but you can mix approaches.

Executing SQL with SQLAlchemy

In modern SQLAlchemy, you typically use text().

python

from sqlalchemy import text
 
with db.engine.connect() as conn:
    result = conn.execute(text("SELECT 1"))
    print(result.scalar())

Parameter binding (important)

Never build SQL by string concatenation.

python

from sqlalchemy import text
 
sql = text("SELECT * FROM users WHERE username = :username")
result = db.session.execute(sql, {"username": "ravi"})
rows = result.fetchall()

This prevents SQL injection.

When to prefer ORM

Use ORM when:

• you’re doing normal CRUD
• you want relationships and model validation

Use raw SQL when:

• query is complex and ORM becomes unreadable
• you need performance tuning (carefully)

Best approach: be pragmatic and keep code understandable.

Raw SQL must be wrapped in text()

raw.py

db.session.execute("SELECT 1")
# ArgumentError: Textual SQL expression 'SELECT 1' should be explicitly declared as text('SELECT 1')
 
db.session.execute(sa.text("SELECT COUNT(*) AS n FROM note")).one()
# (1,)      row.n -> 1

Measured both. The requirement is not bureaucracy: it makes every string that reaches the database visible at the call site, so a reviewer can see exactly where raw SQL is used.

diagram Diagram mermaid

  flowchart TD
  S["a SQL string"] --> Q{"how are values inserted?"}
  Q -->|"f-string or concatenation"| I["the value becomes part of the SQL
INJECTION"]
  Q -->|"bound parameters :name"| B["the value is sent separately
and can never be executed"]

The injection, measured

injection.py

name = "a' OR '1'='1"
 
# parameterised — the value is data
db.session.execute(sa.text("SELECT COUNT(*) FROM note WHERE title = :t"), {"t": name})
# -> 0 rows        the whole string was compared as a literal title
 
# f-string — the value becomes code
db.session.execute(sa.text(f"SELECT COUNT(*) FROM note WHERE title = '{name}'"))
# -> 1 row         the injected OR matched everything

The same input, the same table: 0 rows against 1 row. In a WHERE on a login query that difference is the whole authentication check. Parameters are not an optimisation — they are the boundary between data and code, and an f-string erases it.

There is no safe way to interpolate a value into SQL

Not escaping it yourself, not quoting it, not validating it first. Use bound parameters every time. If part of the query itself must vary — a column to sort by, say — pick it from an allow-list of known-good names rather than passing user input through.

Reading results

results.py

row = db.session.execute(sa.text("SELECT COUNT(*) AS n FROM note")).one()
row.n                # attribute access by label
row[0]               # or by position
 
db.session.execute(sa.text("SELECT * FROM note")).all()       # list of rows
db.session.scalar(sa.text("SELECT COUNT(*) FROM note"))       # a single value

Raw SQL and the session do not automatically agree

stale.py

obj = db.session.scalar(sa.select(Note))     # views = 1
db.session.execute(sa.text("UPDATE note SET views = 99"))
obj.views                                     # 1   <- still the loaded value
db.session.expire(obj)
obj.views                                     # 99

Measured. The session hands back the object it already has; a raw statement it did not issue through the ORM does not invalidate anything. Expire what you touched, or re-query.

When raw SQL is the right answer

• A query the ORM expresses awkwardly — window functions, recursive CTEs, EXPLAIN.
• Bulk work where loading objects would be wasteful.
• Database-specific features with no portable equivalent.

For everything else the ORM gives you parameterisation for free, composes safely, and survives a schema rename. Reach for text() deliberately, not by default.

See it move

sketch Bound parameter against f-string p5.js Pause Restart

A bound parameter is sent separately from the SQL and can never be executed. An f-string makes the value part of the statement.

Check yourself

pch.quizTag pch.quizDefaultTitle

1. db.session.execute('SELECT 1') raises ArgumentError. What does SQLAlchemy require?

   A the statement must be uppercase B the string must be wrapped in sa.text(), which makes every raw SQL site visible at the call C raw SQL is not supported at all D a connection must be opened manually first
   pch.quizShowAnswer

   B — the string must be wrapped in sa.text(), which makes every raw SQL site visible at the call — The measured message asks for text('SELECT 1') explicitly. Requiring the wrapper means a reviewer can grep for exactly where raw SQL enters the codebase.

2. With the input a' OR '1'='1, a parameterised query returned 0 rows and an f-string query returned 1. Why?

   A the parameterised version had a syntax error B a bound parameter is sent separately from the statement and compared as a literal, while the f-string made the input part of the SQL C the f-string version was faster and matched a cached row D the two queries targeted different tables
   pch.quizShowAnswer

   B — a bound parameter is sent separately from the statement and compared as a literal, while the f-string made the input part of the SQL — The quote in the input closed the string literal and the OR became part of the query. On a login check that difference is the entire authentication.

3. Part of a query must vary — the column to ORDER BY comes from the user. What is the correct approach?

   A bind it as a parameter like any value B escape the value and interpolate it C map the input against an allow-list of known column names D validate that it contains no quotes, then interpolate
   pch.quizShowAnswer

   C — map the input against an allow-list of known column names — Bound parameters carry values, not identifiers, so a column name cannot be bound. An allow-list means user input selects from names you wrote rather than supplying any.