使用Postgres和SQLAlchemy使用Array列进行过滤

问题描述

我有一个带有int []列的简单表，并且我希望能够选择其数组元素中的任何一个与我具有的值匹配的行，而我无法弄清楚如何使用SQLAlchemy

I have a simple table with an int[] column, and I'd like to be able to select rows where any one of their array elements matches a value I have, and I cannot figure out how to do this using SQLAlchemy without just using a raw query, which I do not want to do.

这是表的架构（ testuser）：

Here is the schema for the table ("testuser"):

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

以下是示例数据的样子：

Here is what it looks like with sample data:

 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为例）。

Here is an SQL statement that generates what I want; I want to do this all in Python without simply writing the raw query (25 is just used as an example).

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)

我发现的另一种方式编写它：

Another way I found to write it:

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我用来生成表的代码：

Here is the Python code I used to generate the table:

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

推荐答案

因此您想使用Postgres 。

So you want to use the Postgres Array Comparator.

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

或

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

这篇关于使用Postgres和SQLAlchemy使用Array列进行过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！