python - 如何从输入列表/数组创建单列 SQL 表？

Question

我想知道如何将列表/集合/元组从 python(通过 psycopg2)作为单列表传递给 postgres 查询。例如，如果列表是 ['Alice', 'Bob']，我希望表格是:

| Temp  |
+-------+
| Alice |
| Bob   |

如果有人在阅读下面的部分后有其他建议来实现我的结果，那也很好。

背景

我有一个包含三列的 SQL 表:

ID | Members | Group
---+---------+----------
1  | Alice   | 1
2  | Alice   | 1
3  | Bob     | 1
4  | Charlie | 1
5  | Alice   | 2
6  | Bob     | 2
7  | Alice   | 3
8  | Bob     | 4
9  | Charlie | 3

我想要一个包含特定成员组合的组表。请注意，一个成员可能在一个组中有多个项目(例如 ID 1 和 2)。

对于 ['Alice'] 的输入，我想知道她在哪个组中(存在)以及只包含她(唯一)的组，如下所示:

Group | Type
------+--------
1     | present
2     | present
3     | present

对于 ['Alice', 'Bob'] 的输入:

Group | Type
------+--------
1     | present
2     | unique

从阅读看来我正在寻找描述的关系划分 here ，为此我需要做原始问题所要求的，因为输入是从用 python 处理的 Web 表单中获取的。同样，也欢迎使用替代解决方案。

Answer 1

您需要在创建成员计数的位置创建一个子查询，然后使用 GROUP BY 语句执行简单的除数查询，但针对 IN static_set 子句而不是针对另一张 table 。因为这是 python，所以您已经知道静态集的大小。

我假设您已经有一个数据库游标，并且该表名为 GroupMembers:

MEMBERSHIP_QUERY = '''
    SELECT gm.group, mc.memberscount = %(len)s AS type
      FROM groupmembers gm
      JOIN (SELECT "group", COUNT(DISTINCT members) as memberscount 
              FROM groupmembers
             GROUP BY "group") mc
      ON gm.group = mc.group
    WHERE gm.members IN %(set)s
    GROUP BY gm.group, mc.memberscount
    HAVING COUNT(DISTINCT gm.members) = %(len)s;
'''

def membership(members):
    # obtain a cursor
    for row in cursor.execute(MEMBERSHIP_QUERY, dict(len=len(members), set=members)):
        yield dict(group=row[0], type=row[1])

因此无需使用 TEMP 表来执行此查询。

如果您确实需要一个 TEMP 表用于其他目的，使用 .executemany() 插入一组行是最简单的:

members = ['Alice', 'Bob']
cursor.execute('CREATE TEMP TABLE tmp_members (member CHAR(255)) ON COMMIT DROP;')
cursor.executemany('INSERT INTO tmp_members VALUES (%s);',
    [(name,) for name in members])

请注意，.executemany() 需要一系列序列；每个条目都是一系列行数据，在这种情况下，每个条目只包含一个名称。我生成一个单项元组列表来填充表格。

或者，您也可以使用一系列映射并使用 %(name)s 参数语法(因此行数据序列变为 [dict(name=name) for name in成员]).