3 回答

TA貢獻1998條經(jīng)驗 獲得超6個贊
你有沒有嘗試過:
result = db.engine.execute("<sql here>")
要么:
from sqlalchemy import text
sql = text('select name from penguins')
result = db.engine.execute(sql)
names = [row[0] for row in result]
print names

TA貢獻1942條經(jīng)驗 獲得超3個贊
SQL Alchemy會話對象有自己的execute方法:
result = db.session.execute('SELECT * FROM my_table WHERE my_column = :val', {'val': 5})
您的所有查詢都應(yīng)該通過會話對象,無論它們是否是原始SQL。這可確保查詢由事務(wù)正確管理,從而允許將同一請求中的多個查詢作為單個單元提交或回滾。使用引擎或連接走出事務(wù)會使您面臨更大的微妙風險,可能很難檢測到可能導致數(shù)據(jù)損壞的錯誤。每個請求應(yīng)僅與一個事務(wù)相關(guān)聯(lián),并且使用db.session將確保您的應(yīng)用程序的情況。
假設(shè)它是一個SELECT查詢,這將返回一個可迭代的RowProxy對象。
您可以使用各種技術(shù)訪問各個列:
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
就個人而言,我更喜歡將結(jié)果轉(zhuǎn)換為namedtuples:
from collections import namedtuple
Record = namedtuple('Record', result.keys())
records = [Record(*r) for r in result.fetchall()]
for r in records:
print(r)
如果您沒有使用Flask-SQLAlchemy擴展,您仍然可以輕松使用會話:
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})

TA貢獻1780條經(jīng)驗 獲得超4個贊
docs:SQL表達式語言教程 - 使用文本
例:
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, u'Tim', u'Gurra', u'Staff', u'991-509-9284')
(1, u'Jim', u'Carey', u'Staff', u'832-252-1910')
(2, u'Lee', u'Asher', u'Staff', u'897-747-1564')
(3, u'Ben', u'Hayes', u'Staff', u'584-255-2631')
添加回答
舉報