title: Using Metadata for data types when writing to a database
body: <p>I'm trying to copy some large data tables on a regular basis from one database to another. Looking for a fast transfer between the 2 databases without data types changing during the process e.g. boolean converting to text.</p>
  
  <p>I started with pandas <code>read_sql_table()</code> and <code>to_sql()</code> which does a good job retaining the original data types into the new database. However, these to do run rather slow for 1M+ rows of data.</p>
  
  <p>Next, I've tried using temporary CSV files with <code>COPY</code> statements. This drastically improves the copying and writing time of a table to the new database. However, I'm losing the original data types during this process; date becoming datetime, boolean becoming text. Ideally I don't want to have additional code to re-convert data types in the database.</p>
  
  <p>Currently, I've been exploring the idea of using <code>sqlalchemy</code> to copy a table and using the table's metadata to write a table into a new database with it's original data types.</p>
  
  <pre class="lang-py prettyprint-override"><code>import sqlalchemy as db
  engine = db.create_engine("postgresql+psycopg2://username:password@/database?host=datawarehouse")
  connection = engine.connect()
  
  # Gets metadata of the customers table
  metadata = db.MetaData()
  metadata.reflect(bind=engine)
  metadata.tables['customers']
  
  # Gets data from the customers table
  customers = db.Table('customers', metadata, autoload=True, autoload_with=engine)
  query = db.select([customers])
  ResultProxy = connection.execute(query)
  ResultSet = ResultProxy.fetchall()
  </code></pre>
  
  <p>The data comes into python as list (list of lists) which copies fast (similar speed as temporary CSV process).</p>
  
  <p>Is there a way to push the data into a new table with data types specified from the metadata?</p>
  
  <p>I'm hoping there is a way to transfer large tables fast without losing the original datatypes, particularly booleans. Plus, be generalizable for many tables.</p>
  
  <p>Thanks in advance.</p>