SQLAlchemy WHERE IN单个值(原始SQL) [英] SQLAlchemy WHERE IN single value (raw SQL)

问题描述

在执行针对多个值进行检查的原始SQL时，我在使用SQLAlchemy时遇到了麻烦.

I'm having trouble with SQLAlchemy when doing a raw SQL which checks against multiple values.

my_sess.execute(
        "SELECT * FROM table WHERE `key`='rating' AND uid IN :uids",
        params=dict(uids=some_list)
    ).fetchall()

此查询有2种情况，一种有效，一种无效.如果为some_list = [1]，则会抛出SQL错误，提示我在)附近有语法错误.但是，如果some_list = [1, 2]，则查询将成功执行.

There are 2 scenarios for this query, one that works and one that doesn't. If some_list = [1], it throws me an SQL error that I have a syntax error near ). But if some_list = [1, 2], the query executes successfully.

为什么会发生这种情况?

Any reason why this would happen?

推荐答案

否，SQL参数只能处理 scalar 值.您将必须在此处生成SQL.如果您需要原始SQL，请使用:

No, SQL parameters only ever deal with scalar values. You'll have to generate the SQL here; if you need raw SQL, use:

statement = "SELECT * FROM table WHERE `key`='rating' AND uid IN ({})".format(
    ', '.join([':i{}'.format(i) for i in range(len(some_list))]))

my_sess.execute(
        statement, 
        params={'i{}'.format(i): v for i, v in enumerate(some_list)})
    ).fetchall()

例如生成足够的参数以字符串格式保存some_list中的所有值，然后生成匹配的参数以填充它们.

e.g. generate enough parameters to hold all values in some_list with string formatting, then generate matching parameters to fill them.

最好还是使用 literal_column()对象为您完成所有生成工作:

Better still would be to use a literal_column() object to do all the generating for you:

from sqlalchemy.sql import literal_column

uid_in = literal_column('uid').in_(some_list)
statement = "SELECT * FROM able WHERE `key`='rating' AND {}".format(uid_in)

my_sess.execute(
        statement, 
        params={'uid_{}'.format(i): v for i, v in enumerate(some_list)})
    ).fetchall()

但是随后您也许可以使用sqlalchemy.sql.expression模块生成整个语句，因为这将使支持多种数据库方言变得更加容易.

but then you perhaps could just generate the whole statement using the `sqlalchemy.sql.expression module, as this would make supporting multiple database dialects much easier.

此外，uid_in对象已经保存了对绑定参数正确值的引用.而不是像上面的str.format()动作那样将其转换为字符串，SQLAlchemy将具有实际的对象以及相关的参数，并且您将不再需要生成params字典 .

Moreover, the uid_in object already holds references to the right values for the bind parameters; instead of turning it into a string as we do with the str.format() action above, SQLAlchemy would have the actual object plus the associated parameters and you would no longer have to generate the params dictionary either.

以下应能工作:

from sqlalchemy.sql import table, literal_column, select

tbl = table('table')
key_clause = literal_column('key') == 'rating'
uid_clause = literal_column('uid').in_(some_list)
my_sess.execute(select('*', key_clause & uid_clause, [tbl]))

其中 sqlalchemy.sql.select() 带有列规范(此处硬编码为*)，where子句(使用&从两个子句生成以生成SQL AND子句)和可选列表；在这里，您的 sqlalchemy.sql.table() 值

where the sqlalchemy.sql.select() takes a column spec (here hard-coded to *), a where clause (generated from the two clauses with & to generate a SQL AND clause) and a list of selectables; here your one sqlalchemy.sql.table() value.

快速演示:

>>> from sqlalchemy.sql import table, literal_column, select
>>> some_list = ['foo', 'bar']
>>> tbl = table('table')
>>> key_clause = literal_column('key') == 'rating'
>>> uid_clause = literal_column('uid').in_(some_list)
>>> print select('*', key_clause & uid_clause, [tbl])
SELECT * 
FROM "table" 
WHERE key = :key_1 AND uid IN (:uid_1, :uid_2)

但是从所有这些生成的实际对象树也包含绑定参数的实际值，因此my_sess.execute()可以直接访问它们.

but the actual object tree generated from all this contains the actual values for the bind parameters too, so my_sess.execute() can access these directly.

这篇关于SQLAlchemy WHERE IN单个值(原始SQL)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！