SQLAlchemy 从表中不存在的列表中查找 ID

Question

我有一个包含一些记录的表(每条记录都有一个id - 主键(。现在我需要从指定的列表/集中选择表中不存在的所有 id。我正在使用postgres数据库和sqlalchemy作为ORM。请建议必须执行此类查询。

Answer 1

对于非常大的集合来说可能效率不高，但演示流程很简单：

my_list = [1, 2, 3, 4, 5]
missing_from_table = []
for id in my_list:
result = session.query(Model.id).get(id)  # result is a tuple
if not result:
missing_from_table.append(id)
print(f'The following ids are not in the table: {missing_from_table}')

另一种选择是：

my_list = [1, 2, 3, 4, 5]
all_ids = [r.id for r in session.query(Model.id).all()]
missing_from_table = [id for id in my_list if id not in all_ids]

Answer 2

这是一个完全在数据库中运行的选项。它绕过了ORM，但仍利用SQLAlchemy的便利性进行会话和对象映射。

from sqlalchemy import text
my_list = [1, 2, 3, 4, 5, 6]
query = text("""
SELECT array_agg(id) 
FROM unnest(:my_list) id 
WHERE id NOT IN (
SELECT 
id 
FROM 
insert-table-name-here
)
""")
# Belows assumes session is a defined SQLAlchemy database session
missing_ids = session.execute(query, {'my_list': my_list}).scalar()
print(f'The following ids from my_list are missing in table: {missing_ids}')

Answer 3

此选项使用 SQLAlchemy，非常有效。

from sqlalchemy.sql import Values, select, column
new_items_values = Values(column("id"), name="new_items").data(
items_ids
)
query = (
select(new_items_values.c.id)
.outerjoin(
Model,
Model.id == new_items_values.c.id,
)
.where(Model.id == None)
)
# assumes `session` is already defined
missing_ids = set(
session.execute(
query,
{"items_ids": items_ids},
)
or []
) # a list of unary tuples, e.g. [('id1',), ('id4',)]

这将生成如下 SQL：

select new_items.id
from (
values ('id1'),('id2'),('id3'), ('id4')
) as new_items(id)
left join model on model.id = new_items.id
where model.id is null;

(我用WHERE id NOT IN将其与下面的@Matt Graham的解决方案进行了比较;这个解决方案需要20ms;另一个在我的数据库上花了几分钟的时间。