0

I am doing a sequence of psycopg2 executions with execute_values. It works fine for UPDATE and INSERT statements, but not for DELETE statements.

For example, when I run:

execute_values(db, "DELETE FROM table1 WHERE column1 = %s", [(1,), (2,), (3,)])

I get:

psycopg2.errors.SyntaxError: syntax error at or near ","
LINE 1: DELETE FROM table1 WHERE column1 = (1),(2),(3)                                            
                                              ^

However when I run an UPDATE or INSERT with similar code, it works fine, for example:

execute_values(db, "INSERT INTO table1 (column1, column2, column3) VALUES %s", 
[(1, foo, bar), (2, foo, bar), (3, foo, bar)])

The docs imply execute_values should work fine with a DELETE statement but don't say so explicitly. However, I don't understand otherwise why I am only getting this error with DELETE statements.

marduk
  • 51
  • 6
  • `"DELETE FROM table1 WHERE column1 = some_scalar_value_of_the_correct_type;"` – wildplasser Jul 20 '21 at 13:30
  • @wildplasser I don't understand, can you explain more? It seems to me I am using a list of tuples as my argument but psycopg2 is interpreting it as a single argument of comma separated parenthesized integers. – marduk Jul 20 '21 at 13:36
  • No, that is Python/psycopg at wotk, exploding the list into the the argument while substituting it. You can only compare a columns to a single value. Maybe you actually want `WHERE column1 IN (1,2,3,...)` ? – wildplasser Jul 20 '21 at 13:42
  • @wildplasser execute_values requires a sequence of sequences as execute_values' third argument. Singular values therefore must be given as a tuple within a list. According to the documentation, each tuple in the list should be executed as the value/s for a single iteration of the SQL statement. Therefore, the above DELETE statement should execute as one value per column per iteration. – marduk Jul 20 '21 at 14:11
  • 1
    No that is not how it works. If you want to do that you need `executemany`. `execute_value` creates a `VALUES list` and as @wildplasser notes to use that for `DELETE` requires the `IN` notation. – Adrian Klaver Jul 20 '21 at 14:35
  • https://stackoverflow.com/q/28117576/905902 – wildplasser Jul 20 '21 at 14:50
  • Should have added to my previous comment, you can also use `execute_batch`. – Adrian Klaver Jul 20 '21 at 15:13

1 Answers1

0

An example of how to make it work:

select * from animals ;
 id  | cond  | animal 
-----+-------+--------
  30 | false | false
  50 | false | false
  16 | false | false
  60 | false | false
 200 | false | false
   1 | false | false

import psycopg2
from psycopg2.extras import execute_values
con = psycopg2.connect(dbname="test", host='localhost', user='postgres')
cur = con.cursor()
delete_sql = "delete from animals where id in (%s)"
id_vals = [(30,), (50,), (60,)]
execute_values(cur, delete_sql, id_vals)
--Query as it reached database
delete from animals where id in ((30),(50),(60))
con.commit()

select * from animals ;
 id  | cond  | animal 
-----+-------+--------
  16 | false | false
 200 | false | false
   1 | false | false

Per my comment DELETE needs to operate on multiple values using an IN that points at the VALUES list that you construct using the list of tuples.

Adrian Klaver
  • 15,886
  • 2
  • 17
  • 28
  • Thank you. I am very confused. Why would an UPDATE or INSERT work without an IN statement but a DELETE statement requires one? Also, according to your solution, does execute_values become superfluous? – marduk Jul 20 '21 at 15:13
  • Because `execute_values` produces a [VALUES](https://www.postgresql.org/docs/current/queries-values.html) list and your `INSERT` is expecting one; `VALUES %s`. Also I'm guessing your `UPDATE` is doing something that like : `(col1_name), (col2_name) = %s` where `%s` resolves to `(val1), (val2)` with the values list supplying the values. It is not superfluous as `execute_values` allows you to multiple deletes in a single operation. FYI, you could simplify further by doing: `id_vals =[30, 50, 60]`. – Adrian Klaver Jul 20 '21 at 15:30
  • Let me back up on the superfluous comment. Yes you could use just an `execute` using tuple adaptation per here [Tuple](https://www.psycopg.org/docs/usage.html#adapt-tuple), where you provide the ids as a Python tuple `(30, 50, 60)` and a SQL string of `delete from some_table where id in %s`. – Adrian Klaver Jul 20 '21 at 15:37
  • @marduk : You are confused. Please study the SQL syntax. You can compare a column to a *scalar* value. Or you can check if it occurs **in** a list of values. There are two different operators for these: `=` and `IN(...)` – wildplasser Jul 20 '21 at 16:15