Optimize data Upload (MySQL,Python)

Question

I have a realy anoying daily routine work. I have to upload csv sheet to our database. Since the data source have a simple rest api its possible to request all data by JSON Url. For that i implement a small python script which laods the JSON and insert all data to my DB. The problem is that the inserts are realy slow (i expected less then 10 minutes) but after 3 houres i breaked up. Here is my code (a little bite change for better understanding):

db = MySQLdb.connect(host="...",    # your host, usually localhost
                         user="...",                     # your username
                         passwd="...",                      # your password
                         db="...")                     # name of the data base
with db:
     cur = db.cursor()

JSONURL = URL[0][y]+URL2+URL3+URL4+URL5     
response = urllib.urlopen(JSONURL)
data = json.loads(response.read())
n=len(data["results"])-1
for z in range(0,n):
     with db:
          sql="INSERT INTO t_data (col1,col2,col3) VALUES("+data["results"][0]+","+data["results"][1]+","+data["results"][2]+")"
          cur.execute(sql)

Like you can see i use MySQL. Maybe i can use the prepare statement (but i don`t know if it works on python if yes how i do that?) Is it possible to change the setting of MySQL to get a better performance? I am looking forward to any help.

why dont you use bulk-insert instead of insert query again and again. Here is the link for it dev.mysql.com/doc/refman/5.5/en/… — Anand Tripathi
– Anand Tripathi, Commented Mar 14, 2017 at 14:02
sorry for the late answer. i´m home soon and i will try it. Do you prefer the buld insert or maybe prefare is better? whats your experience?@anandtripathi — nicoschuck
– nicoschuck, Commented Mar 14, 2017 at 16:24
i dont know about the later part but i prefer bulk-insert and one thing is bulk-insert doesnt handle is that it doesnt return the list of id of records it just return the first insert_id. — Anand Tripathi
– Anand Tripathi, Commented Mar 15, 2017 at 6:44
wow from over 3 hours to 201s. Thats awesome. thank you a lot — nicoschuck
– nicoschuck, Commented Mar 16, 2017 at 10:42

Anand Tripathi · Accepted Answer · 2017-03-16 11:06:30Z

1

why dont you use bulk-insert instead of insert query again and again. Here is the link for it bulk-insert

answered Mar 16, 2017 at 11:06

Anand Tripathi

16.4k2 gold badges52 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Optimize data Upload (MySQL,Python)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related