Question 90475 in PostgreSQL Question posted in
The official documentation can be found here.

Use a list as cursor.execute parameter with dotenv – Postgresql

I am using the dotenv framework to connect my python script to a postgres database.
I have a list of ids and want to delete all the rows containing thoses ids



ids_to_delete = df["food_id"].tolist()


conn = create_conn()        
       with conn.cursor() as cursor:            
         sql = "DELETE FROM food_recommandations.food_categorisation
         WHERE food_categorisation.food_id = %(ids)s "
         cursor.execute(sql, {"ids":ids_to_delete} )
         cursor.close()
         conn.close()

This must delete all the rows containing thoses ids

Tags:

2

Answers


  1. 0

    You can not use = with list because your columns doesn’t have lists stored in it. Single cell contains one integer id. So what you are looking for is SQL in operator

    sql = "DELETE FROM food_recommandations.food_categorisation
         WHERE food_categorisation.food_id in %(ids)s "
cursor.execute(sql, {"ids":tuple(ids_to_delete)} )
    Login or Signup to reply.
  2. 0

    Apparently your obscurification manager (dotenv framework) translates the structure {"ids":tuple(ids_to_delete)} to an array before transmitting to Postgres. That then required a slight alteration in you query. The in expects a delimited list which is close tp the same to you and I is vary different to Postgres. With an array use the predicate = ANY. So the query from @WasiHaider becomes:

    sql = "DELETE FROM food_recommandations.food_categorisation
         WHERE food_categorisation.food_id = ANY %(ids)s "
cursor.execute(sql, {"ids":tuple(ids_to_delete)} )

    Note: Not tested – no data.

    If successful credit to @WasiHaider.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search