Inserting many rows in psycopg3

Question

I used to use execute_values in psycopg2 but it's gone in psycopg3. I tried following the advice in this answer or this github post, but it just doesn't seem to work for my use case. I'm trying to insert multiple values, my SQL is like so:

sql = INSERT INTO activities (type_, key_, a, b, c, d, e)
        VALUES %s
        ON CONFLICT (key_) DO UPDATE
        SET
            a = EXCLUDED.a,
            b = EXCLUDED.b,
            c = EXCLUDED.c,
            d = EXCLUDED.d,
            e = EXCLUDED.e
values = [['type', 'key', None, None, None, None, None]]

But doing cursor.executemany(sql, values) results in {ProgrammingError}the query has 1 placeholder but 7 parameters were passed. I tried many variations with extra parentheses etc. but always it results in some error. For example doing self.cursor.executemany(sql, [values]) results in syntax error near or at "$1": Line 3: VALUES $1.

Answer 1

The values clause should be consist of one %s placeholder for each column being inserted, separated by commas and all within parentheses, like this:

INSERT INTO t (a, b, c) VALUES (%s, %s, %s)

We can produce the desired string with string manipulation:

# Create one placeholder per column inserted.
placeholders = ', '.join(['%s'] * len(values[0]))
# Wrap in parentheses.
values_clause =  f"""({placeholders})"""
# Inject into the query string.
isql = isql % values_clause

with psycopg.connect(dbname='test') as conn, conn.cursor() as cur:
    cur.executemany(isql, values)
    conn.commit()

However psycopg provides tools for composing SQL statements, and it may be safer to use these tools rather than relying on string manipulation if your query building is very dynamic. Using these tools, you would have this (I've added the parentheses into the main query string this time, as there is no benefit in not doing so):

placeholders = sql.SQL(', ').join(sql.Placeholder() * len(values[0]))
isql = sql.SQL("""INSERT INTO t77716028 (type_, key_, a, b, c, d, e)
        VALUES ({placeholders})
        ON CONFLICT (key_) DO UPDATE
        SET
            a = EXCLUDED.a,
            b = EXCLUDED.b,
            c = EXCLUDED.c,
            d = EXCLUDED.d,
            e = EXCLUDED.e""")
isql = isql.format(placeholders=placeholders)

with psycopg.connect(dbname='test') as conn, conn.cursor() as cur:
    print(f'{isql.as_string(conn)=}')
    cur.executemany(isql, values)
    conn.commit()

Answer 2

I would suggest to serialize and pass the arguments as JSON.
SQL and values would channge like this:

INSERT INTO activities (type_, key_, a, b, c, d, e)
VALUES (
  %(arg)s::json->>'type', %(arg)s::json->>'key', 
  %(arg)s::json->>'a', %(arg)s::json->>'b',
  %(arg)s::json->>'c', %(arg)s::json->>'d', %(arg)s::json->>'e'
) 
ON CONFLICT (key_) DO UPDATE SET
  a = EXCLUDED.a, b = EXCLUDED.b, c = EXCLUDED.c, d = EXCLUDED.d, e = EXCLUDED.e;

values = {'arg':json.dumps({'type':'type','key':'key','a':None,'b':None,'c':None,'d':None,'e':None})}

Please note that some type casts may be needed since ->> operator yields text. The same approach can be used successfully in different scenarios. It remains possible to use prepared statements thus checking risk of SQL injection.