CODEWITHSUNDEEP
pythonsqlalchemypyodbc
J.K.
J.K.

Reputation: 1618

Specifying pyODBC options (fast_executemany = True in particular) using SQLAlchemy

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)

Upvotes: 2

Views: 3014

Answers (2)

Gord Thompson
Gord Thompson

Reputation: 123829

Starting with version 1.3, SQLAlchemy has directly supported fast_executemany, e.g.,

engine = create_engine(connection_uri, fast_executemany=True)

Upvotes: 2

Umar Asghar
Umar Asghar

Reputation: 4064

Try this for pyodbc

crsr = cnxn.cursor()
crsr.fast_executemany = True

Upvotes: 2

Related Questions