Efficiently replace old MYSQL table data while deleting the old one

Thread Starter

zazas321

Joined Nov 29, 2015
856
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 :
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()
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:

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?
 

djsfantasi

Joined Apr 11, 2010
7,688
How many rows do you think you’ll have in your “komplektacija” table?

It appears to be a small finite number. Since the data is transient, have you considered keeping the data in memory?
 

Thread Starter

zazas321

Joined Nov 29, 2015
856
How many rows do you think you’ll have in your “komplektacija” table?

It appears to be a small finite number. Since the data is transient, have you considered keeping the data in memory?
Yes it is going to be a small number. Up to 10 rows maximum. The reason why I prefer to save it in database, is because an operator or myself can keep track which "komplektacija" is currently active and see the details clearly on phpmyadmin
 
Top