It’s surprisingly complicated to collect a bunch of row IDs and then delete the rows en bloc in SQL. I have found a nice way with a recent version of Python’s Postgres driver psycopg3.
An obvious approach would be1:
ids_to_delete = [25, 3, 80, 42] cursor.execute("DELETE from tbl WHERE id IN %s;", (ids_to_delete,))
However, that doesn’t work. You can’t pass a sequence to a
WHERE IN clause.
A cursory Google search is unhelpful and leads to solutions that either are prone to SQL injection or plainly break the moment the identifiers aren’t integers (but, say, UUIDs).
The best approach I’ve found is utilizing psycopg3’s new
Cursor.copy() context manager together with a temporary table:
cursor.execute("CREATE TEMP TABLE to_delete (id int);") with cursor.copy("COPY to_delete (id) FROM STDIN;") as copy: for id_ in ids_to_delete: copy.write_row((id_,)) cursor.execute("""\ DELETE FROM tbl WHERE id IN (SELECT id FROM to_delete);""")
- You create one big batch file that you then load into the temporary table
to_deletewith one command.
- You refer to that table using an
INclause of your
- The temporary table is automatically cleaned up for you.
Of course, that workflow is useful for many other problems, too!