title: Write Large Pandas DataFrames to SQL Server database
body: <p>I have 74 relatively large Pandas DataFrames (About 34,600 rows and 8 columns) that I am trying to insert into a SQL Server database as quickly as possible. After doing some research, I learned that the good ole <code>pandas.to_sql</code> function is not good for such large inserts into a SQL Server database, which was the initial approach that I took (very slow - almost an hour for the application to complete vs about 4 minutes when using mysql database.)</p>
  
  <p><a href="http://docs.sqlalchemy.org/en/rel_0_8/faq.html#i-m-inserting-400-000-rows-with-the-orm-and-it-s-really-slow" rel="noreferrer">This article</a>, and many other StackOverflow posts have been helpful in pointing me in the right direction, however I've hit a roadblock:</p>
  
  <p>I am trying to use SQLAlchemy's Core rather than the ORM for reasons explained in the link above. So, I am converting the dataframe to a dictionary, using <code>pandas.to_dict</code> and then doing an <code>execute()</code> and <code>insert()</code>:</p>
  
  <pre><code>self._session_factory.engine.execute(
      TimeSeriesResultValues.__table__.insert(),
      data)
  # 'data' is a list of dictionaries.
  </code></pre>
  
  <p>The problem is that insert is not getting any values -- they appear as a bunch of empty parenthesis and I get this error:</p>
  
  <pre><code>(pyodbc.IntegretyError) ('23000', "[23000] [FreeTDS][SQL Server]Cannot
  insert the value NULL into the column...
  </code></pre>
  
  <p>There are values in the list of dictionaries that I passed in, so I can't figure out why the values aren't showing up.</p>
  
  <p>EDIT:</p>
  
  <p>Here's the example I'm going off of:</p>
  
  <pre><code>def test_sqlalchemy_core(n=100000):
      init_sqlalchemy()
      t0 = time.time()
      engine.execute(
          Customer.__table__.insert(),
          [{"name": 'NAME ' + str(i)} for i in range(n)]
      )
      print("SQLAlchemy Core: Total time for " + str(n) +
          " records " + str(time.time() - t0) + " secs")
  </code></pre>