I'm writing information from Python (a Pandas DataFrame) to different tables in a database. I'm currently testing whether there is a match between a 'cell' in my DataFrame and the name of one of the tables in the database, and if so sending the information in the relevant row from the DataFrame to that table. See code exerpt below:
for ii in range(0,row_count):
df_area= pon_transpose.index[ii]
export_date= pon_transpose.iloc[ii,0]
export_morning= pon_transpose.iloc[ii,1]
export_day= pon_transpose.iloc[ii,2]
export_eve= pon_transpose.iloc[ii,3]
export_night= pon_transpose.iloc[ii,4]
cur.execute("SELECT name FROM sqlite_master WHERE type='table'")
available_tables=[item[0] for item in cur.fetchall()] # list of strings of all the table names in the database
list_len= len(available_tables)
for iii in range (0, list_len):
if (re.match('\w*'+df_area, available_tables[iii])):
relevant_table=available_tables[iii]
cur.execute("INSERT INTO " + relevant_table + " VALUES (?,?,?,?,?)", (export_date, export_morning, export_day, export_eve, export_night))
con.commit()
iii=iii+1
ii=ii+1
The above is only part of my code and is located inside another loop. This causes information from Python to be read into the database tables several times, which I want to avoid. To avoid this I have tried changing the 4th last line in the above code to:
cur.execute("INSERT OR IGNORE INTO " + relevant_table + " VALUES (?,?,?,?,?)", (export_date, export_morning, export_day, export_eve, export_night))
and:
cur.execute("INSERT OR REPLACE INTO " + relevant_table + " VALUES (?,?,?,?,?)", (export_date, export_morning, export_day, export_eve, export_night))
This has been unsuccessful so far, however. I have a feeling I should be able to do this with "INSERT OR REPLACE", but right now this modification is not doing anything. Does anyone have any ideas or can spot any error? I'm unfamiliar with the SQLite language especially so perhaps I just can't spot a simple syntax fix that would solve my problem (I have researched it though).
Thank you in advance.
Aucun commentaire:
Enregistrer un commentaire