Introduction

Flask-SQLAlchemy lets you use the ORM for most queries, but sometimes raw SQL is the better tool. That happens when you need vendor-specific features, a query that is awkward in ORM form, or a hand-tuned statement for reporting or migration work. The main rule is to execute raw SQL through SQLAlchemy’s session or connection APIs and keep parameter binding explicit.

Use text() with db.session.execute

The standard pattern is to wrap the SQL string in sqlalchemy.text and pass parameters separately.

1from flask import Flask
2from flask_sqlalchemy import SQLAlchemy
3from sqlalchemy import text
4
5app = Flask(__name__)
6app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///example.db"
7db = SQLAlchemy(app)
8
9with app.app_context():
10    result = db.session.execute(
11        text("SELECT id, name FROM user WHERE id > :min_id"),
12        {"min_id": 1},
13    )
14
15    for row in result:
16        print(row.id, row.name)

This is the basic answer for raw SELECT execution in a Flask-SQLAlchemy application.

Use Bound Parameters, Not String Interpolation

Even when the SQL itself is handwritten, values should still be passed as parameters.

Bad pattern:

user_id = 5
sql = f"SELECT id, name FROM user WHERE id = {user_id}"

Good pattern:

1user_id = 5
2result = db.session.execute(
3    text("SELECT id, name FROM user WHERE id = :user_id"),
4    {"user_id": user_id},
5)

This matters for both security and correctness. Parameters handle quoting, escaping, and data typing more reliably than string formatting.

Modifying Data Requires a Commit

If the SQL changes the database, the session still needs to commit the transaction.

1from sqlalchemy import text
2
3with app.app_context():
4    db.session.execute(
5        text("UPDATE user SET name = :name WHERE id = :user_id"),
6        {"name": "Ava", "user_id": 1},
7    )
8    db.session.commit()

This works for INSERT, UPDATE, and DELETE just as it would for ORM-managed changes.

Use Result Mappings When You Want Dictionary-Like Access

Depending on the SQLAlchemy version and result style, rows can behave like tuple-like objects. If you want mapping-style access by column name, use .mappings().

1with app.app_context():
2    result = db.session.execute(
3        text("SELECT id, name FROM user")
4    ).mappings()
5
6    for row in result:
7        print(row["id"], row["name"])

This is often clearer for ad hoc reporting code or when the column list is dynamic.

Use a Connection for Lower-Level Work

Session execution is the normal application path, but lower-level work can also use an engine connection directly.

1from sqlalchemy import text
2
3with app.app_context():
4    with db.engine.connect() as connection:
5        result = connection.execute(
6            text("SELECT COUNT(*) AS total FROM user")
7        )
8        print(result.scalar())

This style is useful when you want direct connection semantics or are working outside normal ORM transaction flows.

Raw SQL and ORM Can Coexist

Using raw SQL does not mean abandoning Flask-SQLAlchemy. The practical balance is usually:

• ORM for common CRUD and model-centric queries
• raw SQL for specialized or database-specific work

That balance keeps most of the application readable while still letting you drop lower when the ORM is not the best fit.

Keep Raw SQL Local and Intentional

Raw SQL becomes hard to maintain if it spreads everywhere. A good pattern is to keep it:

• near the repository or service layer that owns the query
• parameterized and clearly named
• isolated from presentation code

That way the application still has one obvious place to inspect when a particular hand-written query changes.

Common Pitfalls

• Building raw SQL with string interpolation instead of bound parameters.
• Forgetting to commit after INSERT, UPDATE, or DELETE.
• Mixing connection-level and session-level transaction handling without understanding the boundaries.
• Using raw SQL everywhere when the ORM would be simpler for ordinary queries.
• Assuming row access style will always look the same without checking whether .mappings() is needed.

Summary

• In Flask-SQLAlchemy, raw SQL is usually executed through db.session.execute(text(...), params).
• Always bind parameters instead of formatting values directly into SQL strings.
• Data-changing statements still need db.session.commit().
• Use .mappings() when dictionary-like row access is clearer.
• Raw SQL is a useful tool, but it works best when kept intentional and localized.