Python Pandas 0.14.0. Error with timestamp format when using dataframe.to_sql

Question

Code is pretty straight forward:

import Quandl
import sqlite3

myData = Quandl.get("DMDRN/AAPL_ALLFINANCIALRATIOS")

cnx = sqlite3.connect("APPL.db")
myData.to_sql('AAPL', cnx)

I make a call to Quandl API. It gives me a pandas dataframe. When I try to commit the data to a SQL table I get this error

sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.

The index is a Timestamp.

I have tried this 1- How to write Pandas dataframe to sqlite with Index 2- Set the index to an other value + Convert numpy.datetime64 to string object in python

For the first one I still get an error binding parameter 1 and 2 isn't working.

What I should do (or what's the best way) if I want to commit the dataframe to a sqlite table and keep the date as the index.

Answer 1

See pandas to_sql method gives error with date column

The problem is that writing datetime64 values is not yet supported with a sqlite connection. With upcoming pandas 0.15, this bug will be fixed. Writing the index is already supported (from pandas 0.14), and controllable with the index keyword (default of True).

You some options to solve this:

• use sqlalchemy to make the connection (you need at least 0.14 for this), as this already supports writing datetime values:

  import sqlalchemy
  engine = sqlalchemy.create_engine('sqlite:///APPL.db')
  myData.to_sql('AAPL', engine, index=True)
  

• convert the datetime index to strings (and then you can keep using sqlite connection directly). You can do this with:

  myData.index = myData.index.map(lambda x: x.strftime('%Y-%m-%d %H:%M:%S'))
  

• use pandas development version (https://github.com/pydata/pandas)