python best way to insert 60k rows in mysql

Question

i'm trying to insert 60k rows in mysql but this takes 10 minutes to complete

for obj in parsed_objs:
    key = 'randomstring'
    mysqlc.execute("""
        REPLACE INTO infos
            (`key`, `value`)
        VALUES
            (%(key)s, %(value)s)
    """, {'key':key, 'value':obj['val']})
mysqlc.connection.commit()

does this is a good way to do this?

when I run commit(), the script runs separated queries or merges all?

Maybe a good idea should be build one unique string like this?

insert into table my_table(col1, col2) VALUES (val1_1, val2_1), (val1_2, val2_2);

Have you looked into "LOAD DATA INFILE"?

user2370125
– user2370125

2014-01-23 03:39:55 +00:00
Commented Jan 23, 2014 at 3:39 — user2370125
– user2370125, Commented Jan 23, 2014 at 3:39
yes, this is another option i'll try

user3175226
– user3175226

2014-01-23 03:40:37 +00:00
Commented Jan 23, 2014 at 3:40 — user3175226
– user3175226, Commented Jan 23, 2014 at 3:40

Lindsay Winkler · Accepted Answer · 2014-01-23 04:27:34Z

2

If possible, you'd be better off using the LOAD DATA INFILE... statement in SQL to bulk load data. This will avoid creating a huge number of separate queries to load each row of data.

In the code you've posted, you'll be generating one query per row of data that you want to load. commit() just commits the transaction, encompassing all the queries since the transaction was started.

If you do definitely want to do this in Python, you could try using executemany, something like this:

db = MySQLdb.connect(host="your_host", user="your_user", passwd="your_password", db="your_database")
write_cursor = db.cursor()
write_list = []
for obj in parsed_objs:
  key = 'randomstring'
  write_list.append( (key,obj[key]) )

sql = "REPLACE INTO infos (`key`, `value`) VALUES (%s, %s)"
write_cursor.executemany(sql, write_list)
db.commit()

answered Jan 23, 2014 at 4:27

Lindsay Winkler

7716 silver badges13 bronze badges

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

1 Comment

user3175226 Over a year ago

great! this made 50k inserts in less than 10 seconds. thanks!

Collectives™ on Stack Overflow

python best way to insert 60k rows in mysql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related