如何在Flask-SQLAlchemy应用中执行原始SQL

How do you execute raw SQL in SQLAlchemy?

I have a python web app that runs on flask and interfaces to the database through SQLAlchemy.

I need a way to run the raw SQL. The query involves multiple table joins along with Inline views.

I’ve tried:

connection = db.session.connection()
connection.execute( <sql here> )

But I keep getting gateway errors.

Have you tried:

result = db.engine.execute("<sql here>")

or:

from sqlalchemy import text

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

SQL Alchemy session objects have their own execute method:

result = db.session.execute('SELECT * FROM my_table WHERE my_column = :val', {'val': 5})

All your application queries should be going through a session object, whether they’re raw SQL or not. This ensures that the queries are properly managed by a transaction, which allows multiple queries in the same request to be committed or rolled back as a single unit. Going outside the transaction using the engine or the connection puts you at much greater risk of subtle, possibly hard to detect bugs that can leave you with corrupted data. Each request should be associated with only one transaction, and using db.session will ensure this is the case for your application.

Also take note that execute is designed for parameterized queries. Use parameters, like :val in the example, for any inputs to the query to protect yourself from SQL injection attacks. You can provide the value for these parameters by passing a dict as the second argument, where each key is the name of the parameter as it appears in the query. The exact syntax of the parameter itself may be different depending on your database, but all of the major relational databases support them in some form.

Assuming it’s a SELECT query, this will return an iterable of RowProxy objects.

You can access individual columns with a variety of techniques:

for r in result:
    print(r[0]) # Access by positional index
    print(r['my_column']) # Access by column name as a string
    r_dict = dict(r.items()) # convert to dict keyed by column names

Personally, I prefer to convert the results into namedtuples:

from collections import namedtuple

Record = namedtuple('Record', result.keys())
records = [Record(*r) for r in result.fetchall()]
for r in records:
    print(r.my_column)
    print(r)

If you’re not using the Flask-SQLAlchemy extension, you can still easily use a session:

import sqlalchemy
from sqlalchemy.orm import sessionmaker, scoped_session

engine = sqlalchemy.create_engine('my connection string')
Session = scoped_session(sessionmaker(bind=engine))

s = Session()
result = s.execute('SELECT * FROM my_table WHERE my_column = :val', {'val': 5})

docs: SQL Expression Language Tutorial – Using Text

example:

from sqlalchemy.sql import text

connection = engine.connect()

# recommended
cmd = 'select * from Employees where EmployeeGroup = :group'
employeeGroup = 'Staff'
employees = connection.execute(text(cmd), group = employeeGroup)

# or - wee more difficult to interpret the command
employeeGroup = 'Staff'
employees = connection.execute(
                  text('select * from Employees where EmployeeGroup = :group'), 
                  group = employeeGroup)

# or - notice the requirement to quote 'Staff'
employees = connection.execute(
                  text("select * from Employees where EmployeeGroup = 'Staff'"))


for employee in employees: logger.debug(employee)
# output
(0, 'Tim', 'Gurra', 'Staff', '991-509-9284')
(1, 'Jim', 'Carey', 'Staff', '832-252-1910')
(2, 'Lee', 'Asher', 'Staff', '897-747-1564')
(3, 'Ben', 'Hayes', 'Staff', '584-255-2631')

You can get the results of SELECT SQL queries using from_statement() and text() as shown here. You don’t have to deal with tuples this way. As an example for a class User having the table name users you can try,

from sqlalchemy.sql import text

user = session.query(User).from_statement(
    text("""SELECT * FROM users where name=:name""")
).params(name="ed").all()

return user

result = db.engine.execute(text("<sql here>"))

executes the <sql here> but doesn’t commit it unless you’re on autocommit mode. So, inserts and updates wouldn’t reflect in the database.

To commit after the changes, do

result = db.engine.execute(text("<sql here>").execution_options(autocommit=True))

This is a simplified answer of how to run SQL query from Flask Shell

First, map your module (if your module/app is manage.py in the principal folder and you are in a UNIX Operating system), run:

export FLASK_APP=manage

Run Flask shell

flask shell

Import what we need::

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy(app)
from sqlalchemy import text

Run your query:

result = db.engine.execute(text("<sql here>").execution_options(autocommit=True))

This use the currently database connection which has the application.

Have you tried using connection.execute(text( <sql here> ), <bind params here> ) and bind parameters as described in the docs? This can help solve many parameter formatting and performance problems. Maybe the gateway error is a timeout? Bind parameters tend to make complex queries execute substantially faster.

If you want to avoid tuples, another way is by calling the first, one or all methods:

query = db.engine.execute("SELECT * FROM blogs "
                           "WHERE id = 1 ")

assert query.first().name == "Welcome to my blog"