pandas to_sql方法给出了日期列的错误

I have a dataframe that looks like this:

我有一个如下所示的数据框:

df = pd.DataFrame(index= pd.date_range('2014-01-01', periods=10))
df['date'] = df.index.map(lambda x: x.strftime('%d-%m-%Y'))
df['date'] = df.index
df['profit']= rand(10)
df['perf_period_id']=2

also have a sqlite3 db with a table called fee_profit

还有一个带有一个名为fee_profit的表的sqlite3数据库

fee_profit has 4 fields:

fee_profit有4个字段:

• id - integer - primary key

id - 整数 - 主键

• perf_period_id - integer

perf_period_id - 整数

• date - date

日期 - 日期

• profit - real

利润 - 真实

When I try to write the dataframe to the database with (not showing db connection):

当我尝试使用(不显示数据库连接)将数据帧写入数据库时​​:

df.to_sql(name='fee_profit', index=False, con=db, if_exists='append')

I get the following code:

我得到以下代码:

252     else:
253         data = [tuple(x) for x in frame.values.tolist()]
--> 254     cur.executemany(insert_query, data)
255 
256 
InterfaceError: Error binding parameter 0 - probably unsupported type.

Not passing the primary key (could this be the problem?) I have jumbled the table around and it is definitely looks like the date that is the problem. Have tried various combinations of passing the date in index and also is string, bit nothing works.

没有传递主键(这可能是问题?)我把桌子弄乱了,看起来肯定是问题的日期。尝试了在索引中传递日期的各种组合,也是字符串,bit无效。

Any idea where I am going wrong. Cannot find examples of using this method anywhere.

知道我哪里错了。无法在任何地方找到使用此方法的示例。

using Pandas 0.13.1 and sqlite 3 2.6.0. Database was created through django 1.6 model

使用Pandas 0.13.1和sqlite 3 2.6.0。数据库是通过django 1.6模型创建的

1 个解决方案

#1

Update: starting with pandas 0.15, to_sql supports writing datetime values for both sqlite connections as sqlalchemy engines. So the workaround described below should not be needed anymore.
Pandas 0.15 will be released in coming October, and the feature is merged in the development version.

更新:从pandas 0.15开始,to_sql支持将两个sqlite连接的datetime值写为sqlalchemy引擎。因此,不再需要下面描述的解决方法。 Pandas 0.15将于今年10月发布,该功能将在开发版本中合并。

---

The reason of the above error is that the df 'date' column is a datetime64 column, and this type is not supported by sqlite3. So you should convert it to a string first (that this is not done automatically for sqlite is maybe a bug/missing feature), or to a datetime.date object (which is recognized by sqlite3, but it will also be converted to a string as sqlite has no datetime type).

出现上述错误的原因是df'date'列是datetime64列,sqlite3不支持此类型。所以你应该首先将它转换为字符串(这不是为sqlite自动完成可能是一个错误/缺失的功能),或者转换为datetime.date对象(由sqlite3识别,但它也将被转换为字符串因为sqlite没有日期时间类型)。

You did that in your code example with df['date'] = df.index.map(lambda x: x.strftime('%d-%m-%Y')), but then you overwrite the column again with df['date'] = df.index, so maybe that was an error in your code example. But if you first convert it to strings, it works:

您在代码示例中使用df ['date'] = df.index.map(lambda x:x.strftime('%d-%m-%Y'))执行此操作,但随后使用df再次覆盖该列['date'] = df.index,所以这可能是你的代码示例中的一个错误。但是,如果您首先将其转换为字符串,它的工作原理:

df = pd.DataFrame(index= pd.date_range('2014-01-01', periods=10))
df['date'] = df.index.map(lambda x: x.strftime('%d-%m-%Y'))
df['profit']= rand(10)
df['perf_period_id']=2

df.to_sql(name='fee_profit', index=False, con=db, if_exists='append')

Starting from pandas 0.14, the main sql functions are refactored to use sqlalchemy to deal with different database flavors. If you use this, it will work correctly with the datetime column (it will convert it to a string automatically):

从pandas 0.14开始,主要的sql函数被重构为使用sqlalchemy来处理不同的数据库风格。如果使用它,它将与datetime列一起正常工作(它会自动将其转换为字符串):

df = pd.DataFrame(index= pd.date_range('2014-01-01', periods=10))
df['profit']= rand(10)
df['perf_period_id']=2

import sqlalchemy
db2 = sqlalchemy.create_engine('...')
df.to_sql(name='fee_profit', index=False, con=db2, if_exists='append')

Using a plain sqlite connection object instead of a sqlalchemy engine, like you did, will still be supported in the future (but only for sqlite!).

使用普通的sqlite连接对象而不是像我一样使用sqlalchemy引擎,将来仍然会支持(但仅适用于sqlite!)。