Use a list as cursor.execute parameter with dotenv-CodePudding

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

CodePudding user response：

Thank it help a lot already but when I apply your answear to my script i get this error

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

'''

SyntaxError: syntax error at or near "ARRAY" LINE 1: ...tegorisation WHERE food_categorisation.food_id in ARRAY['f9d... ^

CodePudding user response：

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

CodePudding user response：

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.