在 SQLAlchemy 查询中搜索集合 [英] Search a Set in SQLAlchemy query

问题描述

我在完全不同的服务器上有 2 个数据库，两者之间没有关系.两个数据库中有一列具有相同的数据.我需要根据该数据进行匹配，以便我可以在一个视图中从两个数据库中获取其他信息.

I have 2 databases in completely different servers with no relationship between the two. There's one column in both databases that has identical data. I need to match based on that data so I can grab other info from both databases in one View.

这是我的 views.py

@view_config(route_name='cis', renderer='templates/cis.pt')
def cis(request):
    db1 = cis_db.query(site.site_id).join(site_tag).filter(site_tag.tag_id.like(202)).all()
    db2 = DBSession.query(A_School.cis_site_id).all()
    sites = set(db1).intersection(db2)
    return {
        'newsites': cis_db.query(site_tag).filter(site_tag.tag_id.like(202)).count(),
        'schools': DBSession.query(table).all(),
        'test': DBSession.query(table.cis_site_id.like(sites)).all(),
        }

我呈现的页面返回此错误:

My rendered page returns this error:

ProgrammingError: (ProgrammingError) ('Invalid parameter type.  param-index=0 param-type=set', 'HY105')

后面的 sql 代码中有正确的数字.我认为问题在于 sites 中返回的内容.以下是该错误后页面立即返回的内容:

The sql code that follows has the correct numbers in it. I think the problem lies in what's returned in sites. Here's what the page returns immediately after that error:

u'SELECT [A_School].cis_site_id LIKE ? AS anon_1 
FROM [A_School]' (set([(1,), (2,), (3,), (4,), (5,)]),)

所以返回的数据看起来是正确的，但我认为领先的 set 正在抛弃在 .like 部分具有 sites 的查询.不确定如何使其正常工作.

So the data returned looks correct but the leading set I think is throwing off the query that has sites in the .like section. Not sure how to get this working correctly.

推荐答案

你应该使用 in_(...) 而不是 like(sites)?
实际上，in_([s[0] for s in sites]) 是为了从网站集中解开元组.

You should use in_(...) instead of like(sites)?
Actually, in_([s[0] for s in sites]) in order to unwrap the tuples from the sites set.

这篇关于在 SQLAlchemy 查询中搜索集合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！