Inserting many rows in psycopg3

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.

Solution

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()