postgres delete query for batch using python-CodePudding

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)

CodePudding user response：

I see a couple of issues with the snippet you shared

the delete syntax in postgresql is delete from <tablename> where ...
company_id seems to be a string but in values its expressed as an integer.

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

multiple queries:

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

one query execute_values

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

CodePudding user response：

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