Optimising mysql insert query via python

Question

db = MySQLdb.connect(host="xxx.xx.xx.x",   
                    user="xxx",       
                    passwd="xxx",  
                    db="xxxx")
for loop on json data:
    cursor = db.cursor()
    cursor.execute('Insert Query')
    db.commit() 
db.close()

Would it be possible for me to improve this query? I'm considering doing multiple cursor.execute before db.commit()

I'm unsure how db.commit() works and the importance of it.

I'm basically looping a json data and inserting it with a loop. I cannot avoid having multiple inserts.

INSERT INTO tab_name(col_list) VALUES (...),(...),(...),(...). Insert multiple values at once. — Lukasz Szozda
– Lukasz Szozda, Commented Mar 4, 2016 at 20:59
@lad2025 I cant do that, the Loop is a must. I just have to do multiple Insert, cant help it. — JohnDotOwl
– JohnDotOwl, Commented Mar 4, 2016 at 21:00
Is Insert Query a string? because you can always create a string the way you want. and send the execute every 10 values or something. — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Mar 4, 2016 at 21:02
Then maybe you could Improve your question with a more clear sample. — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Mar 4, 2016 at 21:12

mechanical_meat · Accepted Answer · 2016-03-04 21:22:48Z

1

Depending on how json_data is structured you should be able to use .executemany():

db = MySQLdb.connect(host="xxx.xx.xx.x",   
                     user="xxx",       
                     passwd="xxx",  
                     db="xxxx")
cursor = db.cursor()
cursor.executemany('Insert Query',json_data)
db.commit()
cursor.close()
db.close()

answered Mar 4, 2016 at 21:22

mechanical_meat

170k25 gold badges237 silver badges231 bronze badges

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

2 Comments

JohnDotOwl Over a year ago

Can I do data processing in the sql statement value %s eg. str(%s)

mechanical_meat Over a year ago

No you cannot do it in that way. There may be another way.

Collectives™ on Stack Overflow

Optimising mysql insert query via python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related