How do I create a one-column SQL table from an input list/array?

Question

I would like to know how to pass a list/set/tuple from python (via psycopg2) to a postgres query as a one-column table. For example, if the list is ['Alice', 'Bob'], I want the table to be:

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

If anybody has alternate suggestions to achieve my result after reading the section below, that would be fine as well.

Background

I have an SQL table which has three columns of interest:

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

I want a table of groups with certain combinations of members. Note that a member may have multiple items in a group (e.g. IDs 1 and 2).

For an input of ['Alice'] I would want which groups she is in (present) and which contain only her (unique), as below:

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

For an input of ['Alice', 'Bob']:

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

From reading it looks like I am looking for relational division as described here, for which I need to do what the original question asks as the input is taken from a web form processed in python. Again, alternative solutions are also welcome.

Note that for 'Alice', the first table should be present for all three groups; 'Charlie' is also in group 3. — Martijn Pieters
– Martijn Pieters, Commented Aug 20, 2012 at 20:11

Martijn Pieters · Accepted Answer · 2012-08-21 08:47:23Z

3

You need to make a subquery where you create member counts, then do a simple divisor query with a GROUP BY statement, but against a IN static_set clause instead of against another table. Because this is python, you already know the size of the static set.

I'll assume you already have a database cursor, and the table is called 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])

There is thus no need to use a TEMP table to execute this query.

If you do need a TEMP table for other purposes, inserting a set of rows is easiest with .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])

Note that .executemany() expects a sequence of sequences; each entry is a sequence of row data, which in this case only holds one name each. I generate a list of single-item tuples to fill the table.

Alternatively, you can use a sequence of mappings too and use the %(name)s parameter syntax (so the row data sequence becomes [dict(name=name) for name in members]).

edited Aug 21, 2012 at 8:47

answered Aug 20, 2012 at 16:39

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

kai Over a year ago

This has two problems for me. The GROUP BY on the last line raises an error since the '=' on line 1 is not an aggregate function. Using SELECT DISTINCT instead does not give the correct result for some cases. I have updated the table with a further entry to demonstrate - the query would not work for ['Alice', 'Bob'] any more as group 3 has 2 members, but they are the wrong ones.

kai Over a year ago

This makes sense, thanks for the help. Would you be able to answer the original question as well, for completeness? Otherwise if nobody else does I'll accept this as the correct answer.

Collectives™ on Stack Overflow

How do I create a one-column SQL table from an input list/array?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related