'Why is to_sql() slower than iterrows() and cursor.execute()?
In this code simple cursor.execute() is used which takes much less time than sqlalchemy
insert = 'INSERT INTO {} ('.format('TCHOTEL_BO') + ', '.join(df_hotel.columns) + ') VALUES '
df_hotel['uploaded']=['0']*df_hotel.shape[0]
for i,row in df_hotel.iterrows():
values = map((lambda x: "'"+str(x)+"'"), row[:-1])
inserting = insert +'('+ ', '.join(values) +');'
cursor.execute(inserting )
# cursor.commit()
Time taken by this 0:00:36
In this code sqlalchemy.engine is used which is taking too much time
df_trips.to_sql('TCTRIPS_BO', con = engine, if_exists = 'replace', chunksize = 1000, index = False)
Time taken by this 0:01:21
Can anyone please tell me why sqlalchemy is taking too much time although it is more efficent ways that must be much faster that simple cursor.execute method because for every iteration it creates connection with database
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
