1

Could somebody pls help me to perform insert operation to postgres database from python? I have a dataframe: df_routes

From To
A B
B A 
 for index, row in df_routes.iterrows(): 
    cursor.execute("insert into table(id, version, create_ts, created_by, station_from_code, station_to_code, station_from_icao_code, station_to_icao_code) select newid() as id, 0 as version, current_timestamp as create_ts, 'source_py' as created_by, ds_dep.station_code as station_from_code, ds_arr.station_code as station_to_code, ds_dep.icao_code as station_from_icao_code, ds_arr.icao_code as station_to_icao_code from dictionary ds_dep  join dictionary ds_arr  on ds_dep.station_code = %s and ds_arr.station_code = %s and ds_dep.delete_ts is null  and ds_arr.delete_ts is null where not exists  (select null from tsp_ams_navigation_route nr  where nr.station_from_code =  %s and nr.station_to_code =  %s and nr.delete_ts is null)",(row.station_from_code, row.station_to_code, row.station_from_code, row.station_to_code ))
    conn.commit()
    print('ROUTES inserted '+ row.station_from_code + '- ' + row.station_to_code)

This piece of code does not work. Execution is successful, but no rows inserted. Please assist me. Thanks!

Anakin Skywalker
  • 65
  • 4
  • Did you verified, that the sql query works on its own (e.g. in `psql`)? - It appears that there is a ';' missing at the end of that query. Regarding the python portion of the code, I strongly suggest to use the psycopg2 context managers available through `with` statement and the `executemany` functionality. – Marek Schwarz Sep 03 '21 at 11:22

0 Answers0