Hello. I am just curious whether there is a more efficient way to replace the data in my MYSQL database. What I am currently doing :
First, I delete all of the old data and then fill the table with the data that I need. As you can see from this function, the data that I get comes from a CSV table which looks like this:
I have a multiple of CSV tables and the Item number and serial will be different in each CSV.
When the CSV file is read and database is filled I am doing something with this data( Need to know exactly which items were used in particular CSV for this example: Item1, Item3, Item5) I then lookup these Items with my other MYSQL table and do some tasks... The next time I call this function with another CSV file, it might look like this:
As you can see, there are different items and quantity and and I want to fill my "komplektacija" table with this information ( I no longer care what was the last information since I have started a new operation).
Is using DELETE function to delete all the rows and collums a good solution in this case and are there any other methods of doing this?
Code:
def insertData_komplektacija(conn,file_name):
cur = conn.cursor()
cur.execute("DELETE FROM komplektacija")
data = pd.read_csv(file_name,header=0)
for col in data.itertuples():
#print(col)
#print(col[1])
#print(col[2])
#print(col[3])
cur = conn.cursor()
cur.execute("REPLACE INTO komplektacija(Item,Serial,Quantity) VALUES(%s, %s, %s)", (col[1],col[2],col[3]))
myConnection.commit()
cur.close()
item | Serial | Quantity |
Item1 | 1A2B | 1 |
Item3 | 3C4D | 2 |
Item5 | 5E6F | 3 |
I have a multiple of CSV tables and the Item number and serial will be different in each CSV.
When the CSV file is read and database is filled I am doing something with this data( Need to know exactly which items were used in particular CSV for this example: Item1, Item3, Item5) I then lookup these Items with my other MYSQL table and do some tasks... The next time I call this function with another CSV file, it might look like this:
Item | Serial | Quantity |
item1 | 1A2B | 5 |
item2 | 2B3C | 4 |
item3 | 3C4D | 3 |
item4 | 4D5E | 2 |
item9 | 3a2a | 3 |
As you can see, there are different items and quantity and and I want to fill my "komplektacija" table with this information ( I no longer care what was the last information since I have started a new operation).
Is using DELETE function to delete all the rows and collums a good solution in this case and are there any other methods of doing this?