Execute Raw SQL In Flask SQLAlchemy

February 13, 2018

If you want to return the result as Models.

from sqlalchemy.sql import text
from app.models import Notification

sql = """
SELECT *
FROM notification
WHERE id > :id
"""

notifications = session.query(Notification).from_statement(text(sql)).params(id=5).all()

NOTE: dict {"id":5} or named parameters id=5 works.

If you want to return the result as tuple/dict.

sql = """
SELECT *
FROM notification
WHERE id > :id
"""

# db is SQLAlchemy object
results = db.engine.execute(text(sql), {"id":5})
for _result in results:
    print(_result['id'])
    print(_result[0])
    print(_result)

You can use session as well.

results = session.execute(text(sql), id=5)
for _result in results:
    print(_result['id'])
This work is licensed under a
Creative Commons Attribution-NonCommercial 4.0 International License.