在sqlachemy中的hading子句中使用标签

问题描述

我正在尝试在带有sqlalchemy的hading子句中使用标签，但是在使它工作时遇到了问题.我正在尝试这样的事情:

I'm trying to use a label in a having clause with sqlalchemy, but I'm having problems getting it working. I'm trying something like this:

qry = db.session.query(
         Foo.id,
         Foo.name,
         (Foo.max_stuff - func.sum(Bar.stuff)).label('rstuff')
).join(Bar) \
 .group_by(Foo.id) \
 .having('rstuff' >= "some_data_passed_in_by_customer")

for res in qry.all(): 
    print res

但是得到错误:

sqlalchemy.exc.ArgumentError: having() argument must be of type sqlalchemy.sql.ClauseElement or string

如果我尝试将hading语句更改为:

If I try changing the having statement to:

.having('rstuff >= "some_data_passed_in_by_customer"')

然后它似乎可以正常工作，但是我想那会导致sql注入漏洞.我可以通过原始sql来做到这一点，但是如果可以的话，我想避免这种情况.有什么想法吗?

Then it seems to work alright, but I'm guess that would lead to a sql injection vulnerability. I could do this via raw sql, but I would like to avoid that if I could. Any ideas?

推荐答案

尝试以下两个版本中的任何一个(虽然不确定第二个版本是否适用于MySQL):

Try any of the two versions below (not sure the second one works on MySQL though):

rstuff = (Foo.max_stuff - db.func.sum(Bar.stuff))
qry = (
    db.session.query(
        Foo.id,
        Foo.name,
        rstuff.label('rstuff')
    )
    .join(Bar)
    .group_by(Foo.id)

    # version-1: probably universal, but repeats the expression
    .having(rstuff >= 3)
    # version-2: might depend on the RMDBS engine
    # .having(db.literal_column('rstuff') >= 3)
)

这篇关于在sqlachemy中的hading子句中使用标签的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！