Executing Raw SQL in Flask-SQLAlchemy

When working with Flask-SQLAlchemy, you may encounter a scenario where you need to execute raw SQL queries. These queries could involve complex table joins or inline views. The initial approach demonstrated in the question using connection.execute() may trigger gateway errors. To address this issue, let's explore the recommended approaches in SQLAlchemy versions 1.x and 2.0.

SQLAlchemy 2.0

To execute raw SQL in SQLAlchemy 2.0, you can leverage the engine.connect() method:

with engine.connect() as connection:
    result = connection.execute(text('SELECT * FROM your_table'))
    # Perform operations on the result

By utilizing the engine.connect() context manager, you ensure that the connection is established and automatically closed upon exiting the context.

SQLAlchemy 1.x

In SQLAlchemy 1.x, you can execute raw SQL using the db.engine.execute() method in conjunction with the text() function:

from sqlalchemy import text

sql = text('select name from penguins')
result = db.engine.execute(sql)
names = [row[0] for row in result]
print(names)

Note that the db.engine.execute() method executes queries "connectionless," meaning it does not establish a database session. This method is deprecated in SQLAlchemy 2.0 and should be avoided for consistency and best practices.