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?
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.