psycopg2 prepared delete statement

Question

I am struggling with generating the delete query where parameters for the query is actually a set of values.

So I need to delete rows where parameters are a pair values for example:

delete from table where col1 = %s and col2 = %s

which can be executed in Python like:

cur = conn.cursor()
cur.execute(query, (col1_value, col2_value))

Now I would like to run a query:

delete from table where (col1, col2) in ( (col1_value1, col2_value1), (col1_value2, col2_value2) );

I can generate the queries and values and execute the exact SQL but I can't quite generate prepared statement.

I tried:

delete from table where (col1, col2) in %s

and

delete from table where (col1, col2) in (%s)

But when I try to execute:

cur.execute(query, list_of_col_value_tuples)

or

cur.execute(query, tuple_of_col_value_tuples)

I get an exception that indicates that psycopg2 cannot convert arguments to strings.

Is there any way to use psycopg2 to execute a query like this?

Answer 1

You could dynamically add %s placeholders to your query:

cur = con.cursor()

query = "delete from table where (role, username) in (%s)"
options = [('admin', 'foo'), ('user', 'bar')]

placeholders = '%s,' * len(options)
query = query % placeholders[:-1]  # remove last comma
print(query)
print(cur.mogrify(query, options).decode('utf-8'))

Out:

delete from table where (role, user) in (%s,%s)
delete from table where (role, user) in (('admin', 'foo'),('user', 'bar'))

Alternatively, build the query using psycopg2.sql as answered there.

Answer 2

Actually the resolution is quite easy if carefully constructed.

In the miscellaneous goodies of psycopg2 there is a function execute_values.

While all the examples that are given by psycopg2 deal with inserts as the function basically converts the list of arguments into a VALUES list if the call to delete is formatted like so:

qry = "delete from table where (col1, col2) in (%s)"

The call:

execute_values(cur=cur, qry=qry, argslist=<list of value tuples>)

will make the delete perform exactly as required.