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:

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:

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.

Loved by over 4 million developers and more than 90,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.

Share on Twitter
Bookmark this page
Ask a questionJoin the discussion

Related Answers

A better experience for your users. An easier life for your developers.

© 2024 • Sentry is a registered Trademark
of Functional Software, Inc.