Skip to content
Tutorials Center
My learning

Topics

Lesson 29 of 72 ยท Databases

Raw SQL with SQLAlchemy in Flask

Run hand-written SQL in Flask-SQLAlchemy with text() and db.session.execute. Bind parameters safely, read rows as mappings, map results to ORM models, and know when to leave the ORM.

The ORM and select() from Queries and CRUD Views cover most day-to-day needs. But sometimes SQL is simply the better tool: a report with window functions, a recursive query, a bulk update that must be one statement, or a query someone on your team already tuned for PostgreSQL. SQLAlchemy lets you write that SQL yourself and still use the same connection, session and transaction.

In this lesson you will run raw SQL with text(), bind parameters safely, read results as dictionaries, mix raw SQL with ORM models, and decide when each style is the right one. If you want the background on the SQL itself, the SQL tutorial covers it.

text() and db.session.execute

sqlalchemy.text() wraps a SQL string. You run it with db.session.execute(...), which is the same session your ORM code uses, so it takes part in the same transaction and is committed or rolled back with it.

Values go into the statement through named parameters written as :name, supplied in a dictionary. SQLAlchemy sends them to the driver separately from the SQL text, which is what protects you from SQL injection.

Python text(), bound parameters and result rows

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Choose the result shape that suits you:

  • Iterate or call .all(): rows that behave like named tuples (row.name, row[0], tuple unpacking).
  • .mappings(): rows as dict-like objects (row["name"]); dict(row) makes JSON-friendly data.
  • .scalar(), .scalar_one(), .scalar_one_or_none(), .scalars().all(): first column of the first row, exactly one row, or a list of the first column.

Never build SQL with f-strings

The :name parameters are the whole safety story. Concatenating user input into the SQL text hands the attacker the keyboard. Compare the two versions below on the same hostile input:

Python Injection versus a bound parameter

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The unsafe version returns every user's secret. One more rule: parameters can only replace values, not table or column names. To choose a column dynamically, check the user's choice against an allowlist and then insert the allowed name into the SQL yourself (see Web Security):

Python
SORTABLE = {"name", "salary"}
column = request.args.get("sort", "name")
if column not in SORTABLE:
    column = "name"
sql = text(f"SELECT name, salary FROM employee ORDER BY {column}")   # safe: only an allowlisted word

A second trap: lists. WHERE id IN (:ids) does not expand a Python list on its own. Use bindparam("ids", expanding=True):

Python
from sqlalchemy import bindparam
stmt = text("SELECT name FROM employee WHERE id IN :ids").bindparams(bindparam("ids", expanding=True))
db.session.execute(stmt, {"ids": [1, 2, 4]})

Writes, transactions and bulk inserts

INSERT, UPDATE and DELETE run through the same call. Nothing is permanent until db.session.commit(), and rollback() undoes it. Passing a list of dictionaries runs the statement once per item (an executemany), which is the fast way to load many rows.

Python Updates, executemany and rollback

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

That last part is a common surprise: raw SQL changes the database but not the objects the ORM already loaded. Call db.session.refresh(obj) (or expire_all()), or reload them, after raw writes. The reverse is easier: ORM changes are flushed before a raw statement runs, so raw SQL sees them.

Mixing raw SQL with the ORM

When you want entity objects from a hand-written query, give the SQL to select(Model).from_statement(text(...)). The query must return the columns the model needs.

Python from_statement: raw SQL, ORM objects

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

You can go the other way too: str(select(...)) shows the SQL the ORM will send, and app.config["SQLALCHEMY_ECHO"] = True logs every statement, which is a good way to learn what SQL your ORM code produces.

When to use which

flowchart TD
    A["Need data from the database"] --> B{"Can select() express it clearly?"}
    B -- yes --> C["Use the ORM: portable, composable, typed"]
    B -- no --> D{"Report, window function,<br/>CTE, vendor feature,<br/>proven slow ORM query?"}
    D -- yes --> E["Use text() with bound parameters"]
    D -- no --> C

Default to the ORM; reach for raw SQL for a specific reason.

Prefer raw SQL for analytical reports, bulk operations, recursive queries and database-specific features. Prefer the ORM for ordinary CRUD, because it builds queries from pieces (filters, pagination, relationships) and works across PostgreSQL, MySQL and SQLite. Remember that raw SQL ties you to one dialect, that column names in your SQL must match the real table names (Flask-SQLAlchemy uses snake_case: Employee becomes employee), and that it bypasses model-level validation and events, so keep it small, tested and in one place (a queries.py module is common).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. How do you pass a value into a text() statement safely?

    Named parameters are sent to the driver separately from the SQL text, which prevents SQL injection.
  2. 2. Which method gives you rows that behave like dictionaries?

    result.mappings() returns row mappings, so you can use row['name'] or dict(row).
  3. 3. Can a bound parameter replace a column name, as in ORDER BY :column?

    Parameters become quoted values. Dynamic identifiers must come from your own allowlist, never directly from the request.
  4. 4. After a raw UPDATE, an ORM object loaded earlier still shows the old value. What fixes it?

    The session does not know what the raw statement changed. Refreshing reloads the object from the database.

Summary

  • db.session.execute(text("... :name ..."), {"name": value}) runs raw SQL on the same session and transaction as the ORM.
  • Always bind values with named parameters; never format user input into the SQL. Use an allowlist for identifiers and expanding=True for IN lists.
  • Read results as rows, .mappings() dictionaries or scalars; pass a list of dicts for bulk writes.
  • select(Model).from_statement(text(...)) returns real ORM objects from hand-written SQL.
  • Refresh ORM objects after raw writes, and commit or roll back as usual.
  • Default to the ORM; use raw SQL for reports, bulk work and database-specific features.

Next: connect a different kind of database with Flask with MongoDB, or move to server databases in Flask with PostgreSQL and MySQL.