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

1

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

answered Dec 13, 2016 at 6:56

Fomalhaut

9,99111 gold badges59 silver badges111 bronze badges

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

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

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Insert data into CiqHistorical master table in Mysql

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related