时间戳

首页 > 解决方案 > sqlalchemy：获取值不存在的 id

python - sqlalchemy：获取值不存在的 id

问题描述

有一个table带有列的listing_ids，keys如何获取listing_ids（和相应的缺失键）其中list_of_values不存在值的地方keys？

list_of_values = [key2,key3]

桌子

listing_id  keys
424         key1
424         key2
424         key3
523         key1
2433        key2
2433        key1
53          key2
3           key3

我需要得到以下结果：

listing_id  keys_that_does_not_exist
523         key2
523         key3
2433        key3
53          key3
3           key2

我试过了：

ids_without_keys_q = session.query(Table)\
                             .filter(~ exists().where(Table.key.in_(list_of_values))

我在用postgresql

标签： pythonpostgresqlsqlalchemy

解决方案

问题是您期望有多少返回值。PostgreSQL 对生成的数据不是很好，就像这个解决方案使用的那样，所以如果它太慢，只获取所有组合的列表并使用 Python 查找不存在的组合可能会更快。

另外，我在这里根据您的查询假设每个listing_id/key 对有一行，并且您没有将键存储为字符串数组。如果是这种情况，请告诉我，我会修改答案。

  1. 首先，我假设您不想要没有匹配项的 ID，因此您可以像这样构造它，而不是生成一个列表。我使用过滤func.count()掉与所有键匹配的所有列表：
unique_incomplete_listings = session.query(Table.listing_id.label('listing_id'))
  .group_by(Table.listing_id)
  .having(func.count() < 3)
  1. 其次，将其转换为CTE，然后从查询中获取 (listing, key) 的所有可能组合：
from sqlalchemy.dialects.postgresql import array

unique_incomplete_listings = unique_incomplete_listings.cte()
all_potential_matches = session.query(
    unique_incomplete_listings.c.listing_id,
    # this creates a cartesian product of listing_ids to elements in the array
    func.unnest(array(['key1', 'key2', 'key3']))
)
  1. 使用EXCEPT删除您在数据库中找到的任何匹配项
query = all_potential_matches.except_all(
  session.query(Table.listing_id, Table.key)
  # We join here to prevent doing extra work if the listing does have all keys.
  # If this makes the query slower, consider making the CTE a subquery and
  # removing this join instead
  .join(unique_incomplete_listings,
        unique_incomplete_listings.c.listing_id == Table.listing_id)
)

推荐阅读