Fastest Way to insert data( millions of data) into mysql in python

Question

Insert data into CiqHistorical master table in Mysql

 sql ="""INSERT INTO CiqHistorical(CiqRefID, CoID, GVKEY, IID, GRID, CreateDID, SectorID, UserID, ClientID, MinPeriodID, MaxPeriodID, MaxPeriodDID, MinAnnualID, MaxAnnualID, MaxAnnualDID) VALUES(%s,%s,'%s','%s',%s,GetDateID(now()),%s,%s,%s,%s,%s, GetDateID('%s'),%s,%s,GetDateID('%s'));""" %(ciq_ref_id, coid, gvkey, iid, grid, sector_id,user_id, client_id, min_period_id, max_period_id, max_period_did, min_annual_id, max_annual_id, max_annual_did) ciq_hist = self.mysql_hermes.execute(sql)

here is my code, how to know this query how much time to take for execution. — ammy
– ammy, Commented Dec 13, 2016 at 5:14

Fomalhaut · Accepted Answer · 2016-12-13 06:56:52Z

You can insert many records using one INSERT, but you should keep the size of your query not too large. Here's a script that does what you need:

CHUNK_SIZE = 1000 def insert_many(data) index = 0 while True: chunk = data[index : index + CHUNK_SIZE] if not chunk: break values_str = ", ".join( "('{0}', '{1}', '{2}', ...)".format(row['field1'], row['field2'], row['field3'], ...) for row in chunk ) sql = "INSERT INTO `your_table` (field1, field2, field3, ...) VALUES {0}".format(values_str) self.mysql_hermes.execute(sql) index += CHUNK_SIZE

Collectives™ on Stack Overflow

Fastest Way to insert data( millions of data) into mysql in python

Insert data into CiqHistorical master table in Mysql

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

Insert data into CiqHistorical master table in Mysql

1 Answer 1

Comments

Related