title: pyodbc/sqlAchemy enable fast execute many
body: <p>In response to my question <a href="https://stackoverflow.com/questions/51966096/how-to-speed-up-data-wrangling-a-lot-in-python-pandas-sqlalchemy-mssql-t-s?noredirect=1#comment90886403_51966096">How to speed up data wrangling A LOT in Python + Pandas + sqlAlchemy + MSSQL/T-SQL</a> I was kindly directed to <a href="https://stackoverflow.com/questions/48006551/speeding-up-pandas-dataframe-to-sql-with-fast-executemany-of-pyodbc">Speeding up pandas.DataFrame.to_sql with fast_executemany of pyODBC</a> by @IljaEverilä.</p>
  
  <p>NB For test purposes I am only reading/writing 10k rows.</p>
  
  <p>I added the event listener and a) the function is called but b) clearly executemany is not set as the IF fails and cursor.fast_executemay is not set.</p>
  
  <pre><code>def namedDbSqlAEngineCreate(dbName):
      # Create an engine and switch to the named db
      # returns the engine if successful and None if not
      # 2018-08-23 added fast_executemany accoding to this https://stackoverflow.com/questions/48006551/speeding-up-pandas-dataframe-to-sql-with-fast-executemany-of-pyodbc?rq=1
      engineStr = 'mssql+pyodbc://@' + defaultDSN
      engine = sqla.create_engine(engineStr, echo=False)
  
      @event.listens_for(engine, 'before_cursor_execute')
      def receive_before_cursor_execute(conn, cursor, statement, params, context, executemany):
          # print("FUNC call")
          if executemany:
              print('executemany')
              cursor.fast_executemany = True
      try:
          engine.execute('USE ' +dbName)
          return(engine)
      except sqla.exc.SQLAlchemyError as ex:
          if ex.orig.args[0] == '08004':
              print('namedDbSqlAEngineCreate:Database %s does not exist' % dbName)
          else:
              print(ex.args[0])
          return(None)
  </code></pre>
  
  <p>Naturally there is no change in speed.</p>
  
  <p>The code in my original question is unchanged in the to_sql</p>
  
  <pre><code>nasToFillDF.to_sql(name=tempTableName, con=engine.engine, if_exists='replace', chunksize=100, index=False)
  </code></pre>
  
  <p>because I tried, per the example, setting chunksize = None and receive the error message (which I had encountered previously)</p>
  
  <blockquote>
    <p>(pyodbc.ProgrammingError) ('The SQL contains -31072 parameter markers,
    but 100000 parameters were supplied', 'HY000')</p>
  </blockquote>
  
  <p>What have I done wrong? I guess the executemany parameter of the receive_before_cursor_execute is not set, but if that is the answer I have no idea how to fix it. </p>
  
  <p>Setup is pyodbc 4.0.23, sqlAchemy 1.2.6, Python 3.6.something</p>