title: Python Pandas - Using to_sql to write large data frames in chunks
body: <p>I'm using Pandas' <code>to_sql</code> function to write to MySQL, which is timing out due to large frame size (1M rows, 20 columns).</p>
  
  <p><a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_sql.html">http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_sql.html</a></p>
  
  <p>Is there a more official way to chunk through the data and write rows in blocks?  I've written my own code, which seems to work.  I'd prefer an official solution though.  Thanks!</p>
  
  <pre><code>def write_to_db(engine, frame, table_name, chunk_size):
  
      start_index = 0
      end_index = chunk_size if chunk_size &lt; len(frame) else len(frame)
  
      frame = frame.where(pd.notnull(frame), None)
      if_exists_param = 'replace'
  
      while start_index != end_index:
          print "Writing rows %s through %s" % (start_index, end_index)
          frame.iloc[start_index:end_index, :].to_sql(con=engine, name=table_name, if_exists=if_exists_param)
          if_exists_param = 'append'
  
          start_index = min(start_index + chunk_size, len(frame))
          end_index = min(end_index + chunk_size, len(frame))
  
  engine = sqlalchemy.create_engine('mysql://...') #database details omited
  write_to_db(engine, frame, 'retail_pendingcustomers', 20000)
  </code></pre>