0

I want to write the following SQL query efficiently with SQLAlchemy.

UPDATE item_recs
    SET deleted_at = :deleted_at 
WHERE project_id = :project_id
    AND item_id IN (1602,1603,1604)

I have a corrosponding SQLAlchemy model ItemRec that has item_id, project_id, and deleted_at as columns. It also has an id which is an auto-incremented primary key.

Also, I am running the above query with session.execute() method in SQLAlchemy. Right now I am just joining the item_ids in the IN clause and concatenating it with the rest of the query. Is there a way to build this IN clause with placeholders like :project_id instead of using string concatenation?

Pritam Barhate
  • 765
  • 7
  • 22

1 Answers1

0

you can do

"UPDATE item_recs SET deleted_at = :deleted_at 
WHERE project_id = :project_id
AND item_id IN :item_ids"

and then use a tuple as the value for item_ids

params = {"item_ids": (1602,1603,1604)}

of course, you'll use the params as the argument in the session.execute(sql, params) along with the values for project_id and deleted_at in it as well

c8999c 3f964f64
  • 1,430
  • 1
  • 12
  • 25
  • This solution may or may not work, depending on the DBAPI layer being used. For example, even though it may work for PostgreSQL and MySQL, it does not work for Microsoft SQL Server or SQLite. – Gord Thompson Jan 25 '21 at 14:42