title: Writing JSON column to Postgres using Pandas .to_sql
body: <p>During an <a href="https://en.wikipedia.org/wiki/Extract,_transform,_load" rel="noreferrer">ETL</a> process I needed to extract and load a JSON column from one Postgres database to another. We use Pandas for this since it has so many ways to read and write data from different sources/destinations and all the transformations can be written using Python and Pandas. We're quite happy with the approach to be honest.. but we hit a problem.</p>
  
  <p>Usually it's quite easy to read and write the data. You just use <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_sql_table.html" rel="noreferrer">pandas.read_sql_table</a> to read the data from the source and <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_sql_table.html" rel="noreferrer">pandas.to_sql</a> to write it to the destination. But, since one of the source tables had a column of type JSON (from Postgres) the <code>to_sql</code> function crashed with the following error message.</p>
  
  <pre><code>    df.to_sql(table_name, analytics_db)
    File "/home/ec2-user/python-virtual-environments/etl/local/lib64/python2.7/site-packages/pandas/core/generic.py", line 1201, in to_sql
      chunksize=chunksize, dtype=dtype)
    File "/home/ec2-user/python-virtual-environments/etl/local/lib64/python2.7/site-packages/pandas/io/sql.py", line 470, in to_sql
      chunksize=chunksize, dtype=dtype)
    File "/home/ec2-user/python-virtual-environments/etl/local/lib64/python2.7/site-packages/pandas/io/sql.py", line 1147, in to_sql
      table.insert(chunksize)
    File "/home/ec2-user/python-virtual-environments/etl/local/lib64/python2.7/site-packages/pandas/io/sql.py", line 663, in insert
      self._execute_insert(conn, keys, chunk_iter)
    File "/home/ec2-user/python-virtual-environments/etl/local/lib64/python2.7/site-packages/pandas/io/sql.py", line 638, in _execute_insert
      conn.execute(self.insert_statement(), data)
    File "/home/ec2-user/python-virtual-environments/etl/local/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 945, in execute
      return meth(self, multiparams, params)
    File "/home/ec2-user/python-virtual-environments/etl/local/lib64/python2.7/site-packages/sqlalchemy/sql/elements.py", line 263, in _execute_on_connection
      return connection._execute_clauseelement(self, multiparams, params)
    File "/home/ec2-user/python-virtual-environments/etl/local/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement
      compiled_sql, distilled_params
    File "/home/ec2-user/python-virtual-environments/etl/local/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context
      context)
    File "/home/ec2-user/python-virtual-environments/etl/local/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1393, in _handle_dbapi_exception
      exc_info
    File "/home/ec2-user/python-virtual-environments/etl/local/lib64/python2.7/site-packages/sqlalchemy/util/compat.py", line 202, in raise_from_cause
      reraise(type(exception), exception, tb=exc_tb, cause=cause)
    File "/home/ec2-user/python-virtual-environments/etl/local/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1159, in _execute_context
      context)
    File "/home/ec2-user/python-virtual-environments/etl/local/lib64/python2.7/site-packages/sqlalchemy/engine/default.py", line 459, in do_executemany
      cursor.executemany(statement, parameters)
  sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) can't adapt type 'dict'
  </code></pre>