I want to do the bulk delete in postgres using python and my where clause has multiple columns to fetch the correct record.
Ex: For One records i ran this delete products where company_id='123' and product_id=1;
I tried this for multiple records but getting this error
the query contains more than one '%s' placeholder
query = delete products where company_id='%s' and product_id=%s;
values =[(1,2),(3,4)]
psycopg2.extras.execute_values(self.cursor, delete_query, values)
I see a couple of issues with the snippet you shared
delete from <tablename> where ...you can either execute multiple queries to delete the records or you can pass a list of values to compare against the compound field (company_id, product_id) & use execute_values
assuming company_id is text & your values list contains strings
stmt = "delete from products where company_id = %s and product_id = %s"
cur.execute('begin')
try:
for cid, pid in values:
cur.execute(stmt, (cid, pid))
cur.execute('commit')
# do other things
except:
cur.execute('rollback')
# do other things to handle this exception
from postgresql.extras import execute_values
stmt = "delete from products where (company_id, product_id) IN (%s)"
execute_values(cur, stmt, values)
The psycopg2.extras documentation page contains lots of helpful functions, including documentations for execute_values
You are sending each %s a tuple, instead of a single value.
This is how it is supposed to be used:
Reference: https://www.psycopg.org/docs/extras.html?highlight=uuid
>>> execute_values(cur,
... """UPDATE test SET v1 = data.v1 FROM (VALUES %s) AS data (id, v1)
... WHERE test.id = data.id""",
... [(1, 20), (4, 50)])
So in your case it needs to be something along these lines. See how they reference data.
... """DELETE products USING (VALUES %s) AS data (companyId, productId)
... WHERE company_id = data.companyId and product_id = data.productId """,
... [(1, 20), (4, 50)])
