I would like to switch on the fast_executemany option for the pyODBC driver while using SQLAlchemy to insert rows to a table. By default it is of and the code runs really slow… Could anyone suggest how to do this?
Edits:
I am using pyODBC 4.0.21 and SQLAlchemy 1.1.13 and a simplified sample of the code I am using are presented below.
import sqlalchemy as sa def InsertIntoDB(self, tablename, colnames, data, create = False): """ Inserts data into given db table Args: tablename - name of db table with dbname colnames - column names to insert to data - a list of tuples, a tuple per row """ # reflect table into a sqlalchemy object meta = sa.MetaData(bind=self.engine) reflected_table = sa.Table(tablename, meta, autoload=True) # prepare an input object for sa.connection.execute execute_inp = [] for i in data: execute_inp.append(dict(zip(colnames, i))) # Insert values self.connection.execute(reflected_table.insert(),execute_inp)
Advertisement
Answer
Try this for pyodbc
crsr = cnxn.cursor() crsr.fast_executemany = True