Sentry Answers>Flask>

Execute raw SQL in Flask-SQLAlchemy application

Execute raw SQL in Flask-SQLAlchemy application

David Y.

—

The Problem

When using Flask with SQLAlchemy, how can we execute a raw SQL statement?

The Solution

SQLAlchemy provides powerful object-relational mapping (ORM) that allows one to use data from SQL databases as Python objects. Most common operations can be accomplished without needing to use custom SQL statements, and avoiding custom SQL makes our code more readable and reduces the chances that SQL injection vulnerabilities will be introduced.

However, the ORM may still be insufficient for representing particularly complex SQL queries. For this reason, SQLAlchemy provides a mechanism for executing raw SQL queries: the TextClause object, created using the text() function. Instances of this object contain SQL statements and can be passed to the execute() method in the same way as normal ORM operations. Example code:

Click to Copy
from sqlalchemy import text

query = text("SELECT name, price FROM products")
result = db.engine.execute(query)

It is possible to achieve the same result by providing a raw string as a parameter for execute(), but the TextClause object provides some additional functionality, such as parameter binding:

Click to Copy
from sqlalchemy import text

query = text("SELECT name, price FROM products WHERE category=:product_category")
result = db.engine.execute(query, product_category="Fruit")

Parameter binding allows us to reuse the same SQL query with different values and mitigates the risks of SQL injection when working with untrusted data.

  • SentryFlask Error Monitoring
  • Syntax.fmListen to the Syntax Podcast
  • Community SeriesIdentify, Trace, and Fix Endpoint Regression Issues
  • Syntax.fm logo
    Listen to the Syntax Podcast

    Tasty treats for web developers brought to you by Sentry. Get tips and tricks from Wes Bos and Scott Tolinski.

    SEE EPISODES

Considered “not bad” by 4 million developers and more than 100,000 organizations worldwide, Sentry provides code-level observability to many of the world’s best-known companies like Disney, Peloton, Cloudflare, Eventbrite, Slack, Supercell, and Rockstar Games. Each month we process billions of exceptions from the most popular products on the internet.

learn more about sentryjoin our discord
© 2024 • Sentry is a registered Trademark
of Functional Software, Inc.