使用Postgres和SQLAlchemy过滤数组列

Question

我有一个简单的表格，其中包含 int[] 类型的列，我想能够选择其中任意一个数组元素与我拥有的值相匹配的行，但我无法在不使用原始查询的情况下使用 SQLAlchemy 实现这一点。

以下是该表格("testuser")的架构：

 Column  |          Type          |
---------+------------------------+
 id      | integer                |
 name    | character varying(250) |
 numbers | integer[]              |

以下是使用示例数据的样子：

 id |  name   |    numbers
----+---------+---------------
  1 | David   | {25,33,42,55}
  2 | Salazar | {11,33,7,19}
  3 | Belinda | {32,6,20,23}
  4 | Casey   | {19,20,27,8}
  5 | Kathie  | {25,31,10,40}
  6 | Dianne  | {25,20,40,39}
  7 | Cortez  | {1,20,18,38}

这里有一条SQL语句可以生成我想要的结果；我希望能够在Python中完成这个操作，而不是简单地编写原始查询（25只是一个示例）。

scrape=# select * from testuser where 25 = ANY(numbers);
 id |  name  |    numbers
----+--------+---------------
  5 | Kathie | {25,31,10,40}
  6 | Dianne | {25,20,40,39}
  1 | David  | {25,33,42,55}
(3 rows)

我发现另一种编写方式:

scrape=# select * from testuser where numbers @> ARRAY[25];
 id |  name  |    numbers
----+--------+---------------
  5 | Kathie | {25,31,10,40}
  6 | Dianne | {25,20,40,39}
  1 | David  | {25,33,42,55}
(3 rows)

以下是我用来生成表格的Python代码：

from sqlalchemy import Column, Integer, String
from sqlalchemy.dialects import postgresql
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class TestUser(Base):
    __tablename__ = 'testuser'
    id = Column(Integer, primary_key=True)
    name = Column(String(250))
    numbers = Column(postgresql.ARRAY(Integer))

engine = create_engine('postgresql://postgres:{pw}@localhost:5432/scrape'.format(pw=POSTGRES_PASSWORD))

Base.metadata.create_all(engine)

DBSession = sessionmaker(bind=engine)
session = DBSession()

testcases = [{"numbers": [25, 33, 42, 55], "name": "David"}, {"numbers": [11, 33, 7, 19 ], "name":     "Salazar"}, {"numbers": [32, 6, 20, 23 ], "name": "Belinda"}, {"numbers": [19, 20, 27, 8 ], "name": "Casey"},     {"numbers": [25, 31, 10, 40 ], "name": "Kathie"}, {"numbers": [25, 20, 40, 39 ], "name": "Dianne"},     {"numbers": [1, 20, 18, 38 ], "name": "Cortez"} ]

for t in testcases:
    session.add(TestUser(name=t['name'], numbers=t['numbers']))
session.commit()

- profesor_tortuga

Answer 1

所以你想要使用Postgres 数组比较器。

query = session.query(TestUser).filter(TestUser.numbers.contains([some_int])).all()

或者

query = session.query(TestUser).filter(TestUser.numbers.any(25)).all()

- ACV

---

出现了一个错误，但你指出了我的正确方向。需要是 [some_int] （包含与数组的比较）或者是 any(some_int)。可以使用以下代码来实现：session.query(TestUser).filter(TestUser.numbers.any(25)).all()。谢谢！ - profesor_tortuga

当您使用ARRAY(db.String)而不是db.Integer时，会出现问题，您需要进行强制转换TestUser.numbers.contains(cast([some_int], ARRAY(String)))。 - Virako

如果列是可空的，并且可能有一个 null 而不是数组，这个解决方案是否有效？ - Sergey P. aka azure

Answer 2

该死的SQLAlchemy，花了我一些时间才弄清楚正确的方法是：

TelegramUser.query.filter(TelegramUser.selected_exchanges.contains(f"{{{platform_name}}}")).all()

其中 platform_name 是一个 str，而 selected_exchanges 列定义如下：

from sqlalchemy.dialects import postgresql as pg

selected_exchanges = db.Column(pg.ARRAY(db.String, dimensions=1), nullable=True)

- scythargon

---

正确的方法在接受答案中。手动编写SQL存在错误的风险，并且是不必要的。contains([platform_name]) 可以起作用，而且更安全。 - Ilja Everilä