I have a list of datetime data in python. I have a table in a SQL database that has two columns, an empty Date_Time column and an ID column. The ID column simply increases as 1,2,3 etc. The number of table rows is the same as the length of the datetime list. I insert the list of datetime data into the table fairly simply through the following code:
def populate_datetime(table, datetimes):
sql = cnxn.cursor()
for i in range(1, len(datetimes)+1):
query = '''
update {0}
set Date_Time = '{1}'
where ID = {2};'''.format(table, datetimes[i-1], i)
sql.execute(query)
table is the name of the table in the sql database, and datetimes is the list of datetime data.
This code works perfectly, but the data is lengthy, approximately 800,000 datetimes long. As a result, the code takes approx. 16 minutes to run on average. Any advice on how to reduce the run time?
