Passing nested tuple to VALUES in psycopg3

Question

I'm trying to updates some psycopg2 code to psycopg3. I'm trying to do a selection based on a set of values passed from Python (joining with an existing table). Without the join, a simplified example is:

with connection.cursor() as cur:
    sql = "WITH sources (a,b,c) AS (VALUES %s) SELECT a,b+c FROM sources;"
    data = (('hi',2,0), ('ho',5,2))
    cur.execute(sql, (data,) )
    print(cur.fetchone());

I get an error

ProgrammingError: syntax error at or near "'("(hi,2,0)","(ho,5,2)")'"
LINE 1: WITH sources (a,b,c) AS (VALUES '("(hi,2,0)","(ho,5,2)")') S...

The psycopg2 code used extras.execute_values instead, which is not available in psycopg3.

Is there a way to pass the values for an intermediate table using psycopg3?

Either use postgresql `jsonb` functions to handle `json.dumps(data)` or use `executemany()` as shown here: https://github.com/psycopg/psycopg/issues/114#issuecomment-1553278669 — Mike Organek, Jul 18 '23 at 14:11
Thanks, @MikeOrganek. executemany() wasn't appropriate for my query, but the json approach worked using `jsonb_to_recordset()`. — xioxox, Jul 18 '23 at 17:54

Adrian Klaver · Accepted Answer · 2023-07-18T22:14:30.010

One way to do it:

import psycopg

con = psycopg.connect("dbname=test host=localhost  user=postgres")

with con.cursor() as cur:
    rs = []
    sql = "SELECT %s, %s + %s"
    data = [('hi',2,0), ('ho',5,2)]
    cur.executemany(sql, data, returning=True )
    while True:
        rs.append(cur.fetchone())
        if not cur.nextset():
            break
    print(rs)

[('hi', 2), ('ho', 7)]

From here psycopg cursor classes:

executemany( ... )

Note

Using the usual fetchone(), fetchall(), you will be able to read the records returned by the first query executed only. In order to read the results of the following queries you can call nextset() to move to the following result set.

A typical use case for executemany(returning=True) might be to insert a bunch of records and to retrieve the primary keys inserted, taken from a PostgreSQL sequence. In order to do so, you may execute a query such as INSERT INTO table VALUES (...) RETURNING id. Because every INSERT is guaranteed to insert exactly a single record, you can obtain the list of the new ids using a pattern such as:

cur.executemany(query, records)
ids = []
while True:
    ids.append(cur.fetchone()[0])
    if not cur.nextset():
        break

Warning

More explicitly, fetchall() alone will not return all the values returned! You must iterate on the results using nextset().

UPDATE

data_combined = [y for x in data for y in x]

data_combined
['hi', 2, 0, 'ho', 5, 2]


qry = sql.Composed(
    [sql.SQL("select a, b + c from ( "), sql.SQL('VALUES '), 
     sql.SQL(",").join(
        sql.SQL("({})").format(sql.SQL(',').join(sql.Placeholder() 
        * len(data[0]))) * len(data)), 
     sql.SQL(") as t(a, b, c)")])

print(qry.as_string(con))
select a, b + c from ( VALUES (%s,%s,%s),(%s,%s,%s)) as t(a, b, c)

cur.execute(qry, data_combined)

cur.fetchall()
[('hi', 2), ('ho', 7)]

Used sql.Composed to build up a query with a variable number of VALUES and placeholders. Combined the tuple of tuples into a flat list and passed it to the query.

Thanks. This is a good answer. It's not that useful for my use case, as I need to do a single query, not multiple ones (my example was too simplified). — xioxox, Jul 18 '23 at 19:40
Update your question with example that is closer to what you want. — Adrian Klaver, Jul 18 '23 at 20:32

Passing nested tuple to VALUES in psycopg3

1 Answers1