problem writing DateTime data to sqlite with sql.write_frame

[lebedov]

Although this problem was ostensibly solved, it still seems to affect the latest commit as of the present time; the following code throws an InterfaceError at the write_frame() call; changing the dtype of the date column to something other than DateTime (e.g., by removing the parameter parse_dates=[1] in the call to read_csv()) circumvents the problem:

import StringIO
import sqlite3
import pandas.io.sql

data_csv = u"""foo,2012-01-01                                                                                                                  
foo,2012-01-02                                                                                                                                 
bar,2011-01-01                                                                                                                                 
qux,2011-01-01                                                                                                                                 
"""
buf = StringIO.StringIO(data_csv)

df = pandas.read_csv(buf, header=None, 
       names=['name', 'date'], parse_dates=[1])

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
cursor.execute('create table data (name string, date date)')

pandas.io.sql.write_frame(df, 'data', conn, 'sqlite', 'append', False)

[jreback]

@jorisvandenbossche is this an open issue?

[lebedov]

(Still seems to be a problem as of commit 657d255.)

[jreback]

@lebedov I think you need to use to_sql() to get the new functionaility. write_frame is being deprecated.

[lebedov]

Changing the last line of the code in the original post to

df.to_sql('data', conn, 'sqlite', 'append', False)

still results in an InterfaceError.

[jreback]

AFAICT this is a fundamental problem with sqlite3 itself. I would think that SQLAlchemy could/would convert the datetimes to an appropriate format, but maybe that is not correct, see here: http://www.sqlite.org/datatype3.html.

I don't think this ever worked (the issue you pointed to simply closed the issue as wont' fix for an older version).

[lebedov]

Using the latest code with SQLAlchemy 0.9.4 seems to work (assuming that the type affinity of the name column is explicitly set to text):

import StringIO
import sqlalchemy
import pandas.io.sql

data_csv = u"""foo,2012-01-01                                                                                                                  
foo,2012-01-02                                                                                                                                 
bar,2011-01-01                                                                                                                                 
qux,2011-01-01                                                                                                                                 
"""
buf = StringIO.StringIO(data_csv)

df = pandas.read_csv(buf, header=None,
               names=['name', 'date'], parse_dates=[1])

eng = sqlalchemy.create_engine('sqlite://')
eng.execute('create table data (name text, date date)')

df.to_sql('data', eng, 'sqlite', 'append', False)

[jorisvandenbossche]

@lebedov late answer, but the fact that you have to create the sql table yourself shouldn't be necessary. Just using df.to_sql('data', eng, index=False) should work. Otherwise it would be a bug. Can you test that? (what error did you get if you didn't explicitely set the column to text?)

[lebedov]

Yes - that seems to work with the latest pandas code as of today.

[jorisvandenbossche]

OK, thanks for checking!