Python Sqlalchemy、原始查询和参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29208847/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sqlalchemy, raw query and parameters
提问by Drachenfels
I am trying to perform raw sql query using sqlalchemy and wondering what is a 'proper' way to do it.
我正在尝试使用 sqlalchemy 执行原始 sql 查询，并想知道什么是“正确”的方法。
My query looks as follows (for now):
我的查询如下（目前）：
db.my_session.execute(
"""UPDATE client SET musicVol = {}, messageVol = {}""".format(
music_volume, message_volume))
What I don't like is string formatting and lack of any parameter handling (hello to quotation marks in music_volume :-D).
我不喜欢的是字符串格式和缺少任何参数处理（你好，music_volume 中的引号：-D）。
I tried to follow this answer:
我试图按照这个答案：
How to execute raw SQL in SQLAlchemy-flask app
如何在 SQLAlchemy-flask 应用程序中执行原始 SQL
And after applying what I read, my snippet looks as follows:
应用我阅读的内容后，我的代码片段如下所示：
db.my_session.execute(
"UPDATE client SET musicVol = :mv , messageVol = :ml", mv=music_volume, ml=message_volume)
However I am getting error that mv and ml is not recognized parameter.
但是我收到错误，mv 和 ml 不是识别参数。
If I change my snippet into this, it works:
如果我将我的代码段更改为这个，它会起作用：
db.my_session.execute(
"UPDATE client SET musicVol = :mv , messageVol = :ml", {mv: music_volume, ml: message_volume})
Lastly my_session is initiated like that in a file called db.py:
最后 my_session 是在一个名为 db.py 的文件中启动的：
engi = sqlalchemy.create_engine(
'mysql://{user}:{passwd}@{host}/{db}'.format(
host=settings.HOST,
user=settings.USER,
passwd=settings.PASS,
db=settings.DB_NAME), execution_options={
'autocommit': True,
})
my_session = sqlalchemy.orm.scoped_session(sqlalchemy.orm.sessionmaker(bind=engi), scopefunc=os.getpid)
sqlalchemy.orm.scoped_session.configure(my_session, autocommit=True)
What I would like to know is why answer linked above and this part of documentation:
我想知道的是为什么上面链接的答案和这部分文档：
http://docs.sqlalchemy.org/en/rel_0_9/core/tutorial.html#using-text
http://docs.sqlalchemy.org/en/rel_0_9/core/tutorial.html#using-text
Are showing slightly different solution to what actually is working for me.
对实际对我有用的解决方案略有不同。
Also if my approach is one to go.
另外，如果我的方法是一种方法。
采纳答案by mpiskore
Both mvand mlwill not be recognized, since you haven't defined them as variables.
双方mv并ml不会被认可，因为你还没有他们定义为变量。
The second argument of executestatement is a dictionary, and all the elements of your plain query "UPDATE client SET musicVol = :mv , messageVol = :ml"escaped with a colon are being searched for in this dictionary's keys. The executemethod did not found a key 'mv'nor 'ml'in this dictionary, therefore an error is raised.
executestatement的第二个参数是一个字典，"UPDATE client SET musicVol = :mv , messageVol = :ml"在这个字典的键中搜索用冒号转义的普通查询的所有元素。该execute方法'mv'未'ml'在此字典中找到键，因此引发错误。
This is the correct version:
这是正确的版本：
db.my_session.execute(
"UPDATE client SET musicVol = :mv, messageVol = :ml",
{'mv': music_volume, 'ml': message_volume}
)
