SQLAlchemy，原始查询和参数 [英] Sqlalchemy, raw query and parameters

问题描述

我正在尝试使用sqlalchemy执行原始sql查询，并且想知道什么是正确"的方法.

I am trying to perform raw sql query using sqlalchemy and wondering what is a 'proper' way to do it.

我的查询如下(目前):

My query looks as follows (for now):

db.my_session.execute(
    """UPDATE client SET musicVol = {}, messageVol = {}""".format(
    music_volume, message_volume))

我不喜欢字符串格式和缺少任何参数处理(你好，music_volume中的引号是:-D).

What I don't like is string formatting and lack of any parameter handling (hello to quotation marks in music_volume :-D).

我试图遵循以下答案:

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

应用我阅读的内容后，我的代码段如下所示:

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)

但是我收到错误，指出mv和ml无法识别为参数.

However I am getting error that mv and ml is not recognized parameter.

如果我将代码段更改为此，它会起作用:

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})

最近，my_session的启动类似于在名为db.py的文件中进行的操作:

Lastly my_session is initiated like that in a file called 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

对于我实际使用的解决方案显示出略有不同的解决方案.

Are showing slightly different solution to what actually is working for me.

如果我的方法是可行的话.

Also if my approach is one to go.

推荐答案

mv和ml都不会被识别，因为您尚未将它们定义为变量.

Both mv and ml will not be recognized, since you haven't defined them as variables.

execute语句的第二个参数是字典，并且在此字典的键中搜索用冒号转义的普通查询"UPDATE client SET musicVol = :mv , messageVol = :ml"的所有元素. execute方法在此字典中找不到键'mv'或'ml'，因此会引发错误.

The second argument of execute statement 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 execute method did not found a key 'mv' nor 'ml' in this dictionary, therefore an error is raised.

这是正确的版本:

db.my_session.execute(
    "UPDATE client SET musicVol = :mv, messageVol = :ml",
    {'mv': music_volume, 'ml': message_volume}
)

这篇关于SQLAlchemy，原始查询和参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！