使用Postgres和SQLAlchemy对数组列进行过滤
使用Postgres和SQLAlchemy对数组列进行过滤
我有一个简单的表格,其中有一个int[]列,我希望能够选择行,其中任何一个数组元素与我拥有的值匹配,但我无法在SQLAlchemy中找到如何做到这一点,而不仅仅使用原始查询,这是我不想要的。
以下是表格("testuser")的模式:
列名 | 类型 | ---------+------------------------+ id | 整数 | name | 字符串(250) | numbers | 整数数组 |
以下是带有示例数据的样子:
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()