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.
JavaScript
x
23
23
1
import sqlalchemy as sa
2
3
def InsertIntoDB(self, tablename, colnames, data, create = False):
4
"""
5
Inserts data into given db table
6
Args:
7
tablename - name of db table with dbname
8
colnames - column names to insert to
9
data - a list of tuples, a tuple per row
10
"""
11
12
# reflect table into a sqlalchemy object
13
meta = sa.MetaData(bind=self.engine)
14
reflected_table = sa.Table(tablename, meta, autoload=True)
15
16
# prepare an input object for sa.connection.execute
17
execute_inp = []
18
for i in data:
19
execute_inp.append(dict(zip(colnames, i)))
20
21
# Insert values
22
self.connection.execute(reflected_table.insert(),execute_inp)
23
Advertisement
Answer
Try this for pyodbc
JavaScript
1
3
1
crsr = cnxn.cursor()
2
crsr.fast_executemany = True
3