Psycopg2 execute values -the query contains more than one '%s' placeholder-CodePudding

I'm trying to use psycopg2 execute_values for faster execute and keep getting errors so i guess i'm not writing the sql statement correct.

That's what i try:

  sql = """UPDATE table SET value1 = %s, value2 = %s, value3 = %s WHERE id = %s"""
for feat in features:
    value1=feat.value
    value2=feat.value
    value3=feat.value


    parameters = [
                value1,
                value2,
                value3,
               
            ]
    all_parameters.append(parameters)



execute_values(cur,sql,all_parameters)

i tried using VALUES as well:

 sql="""UPDATE table SET (value1,value2,value3) VALUES (%s) WHERE id=%s"""

This one gave me mostly syntax errors for VALUES

i'm keep getting : "the query contains more than one '%s' placeholder" or "not all values formatted during string formatting" ," Syntax error in ___"

How should i write it?

CodePudding user response：

Your statement is fine, but you should be using cursor execute() here:

sql = """UPDATE table
         SET value1 = %s, value2 = %s, value3 = %s, value4 = %s,
             value5 = %s, value6 = %s, value7 = %s, value8 = %s, value9 = %s
         WHERE id = %s"""

parameters = (value1, value2, value3, value4, value5, value6, value7, value8, value9, id,)
        
cur.execute(sql, parameters)

execute_values() is intended to be used for inserts with a VALUES clause.

CodePudding user response：

execute_values only supplies one "item", so only takes one %s. But that item is a double composite (a list of tuples). This syntax is natural for INSERT, but is a bit odd for UPDATE. So you need to write the statement a bit oddly.

sql = """UPDATE tableb SET value1 = v1, value2 = v2, value3 = v3 
         from (values %s) f(id, v1, v2, v3)
         WHERE tableb.id = f.id"""
for feat in features:
    parameters = [
                feat.id,
                feat.value1,
                feat.value2,
                feat.value3,
                 ]
    all_parameters.append(parameters)
execute_values(cur,sql,all_parameters)

Note how you need to assign aliases for the "table" and "columns" of the values construct right after the (values %s), and then reference them by those aliases elsewhere.