title: How to speed up Pandas to_sql() for writing to Teradata
body: <p>Writing Terabytes of Data to Teradata Database but to_sql() is incredibly slow.  Currently taking ~60 seconds per 100 rows, which means the data is growing much faster than I can write it!    I have approximately 450 columns and hundreds of millions of rows.  Would like to keep the process inside of Python due to all the data cleanup that is occurring (I haven not included that here in order to keep it more simple) and it's easy to automate.</p>
  
  <pre><code>import pandas as pd
  import glob
  import sqlalchemy
  from sqlalchemy import create_engine, text, update, insert
  from datetime import datetime
  import numpy as np
  from timeit import default_timer as timer
  
  keys = pd.read_csv('C:/dictionary.csv')
  
  fwidths = [4, 6, 8]
  
  
  for n in range(441):
      fwidths.append(4)
      fwidths.append(2)
      fwidths.append(2)
      fwidths.append(8)
  
  
  cols = ['ACT_NUMBER', 'Subject_Code', 'Segment']
  
  t = 1
  for n in range(441):
      cols.append('KEY{0}'.format(t))
      cols.append('DATA_TYPE{0}'.format(t))
      cols.append('SIGN{0}'.format(t))
      try:
          cols.append(keys.loc[keys.Key_Num == t, 'Name'].item())
      except:
          cols.append('N/A')
      t+=1
  
  conn = create_engine('teradata://username:password')
  chunksize = 100
  
  for file_path in glob.glob('C:/files/*.txt'):
       for chunk in pd.read_fwf(file_path,widths = fwidths, names = cols, iterator = True, skiprows=1, chunksize = chunksize):
           start = timer()
           chunk.to_sql('new_table', con=conn, if_exists = 'append', schema='mySchema', index = False, dtype={'ACT_NUMBER': sqlalchemy.types.VARCHAR(length=255), 'Subject_Code': sqlalchemy.types.VARCHAR(length=255), 'Segment': sqlalchemy.types.VARCHAR(length=255)})
           end = timer()
           print('writing chunk time {0}'.format(end - start)) 
  </code></pre>