pandas to_sql方法给出日期列错误 [英] pandas to_sql method gives error with date column

问题描述

我有一个看起来像这样的数据框:

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

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

also have a sqlite3 db with a table called fee_profit

fee_profit有4个字段:

fee_profit has 4 fields:

• id-整数-主键
• perf_period_id-整数
• 日期-日期
• 利润-真实

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

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')

我得到以下代码:

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.

任何想法，我要去哪里错了.在任何地方都找不到使用此方法的示例.

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

使用熊猫0.13.1和sqlite 3 2.6.0.数据库是通过Django 1.6模型创建的

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

推荐答案

更新:从熊猫0.15开始，to_sql支持将两个sqlite连接的日期时间值编写为sqlalchemy引擎.因此，不再需要下面描述的解决方法.
熊猫0.15将于10月发布，并且该功能已合并到开发版本中.

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.

出现上述错误的原因是df'date'列是datetime64列，而sqlite3不支持此类型.因此，您应该先将其转换为字符串(对于sqlite，这不是自动完成的，可能是一个错误/缺失功能)，或者将其转换为datetime.date对象(被sqlite3识别，但也将转换为字符串)因为sqlite没有datetime类型).

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'] = df.index.map(lambda x: x.strftime('%d-%m-%Y'))进行了此操作，但是随后又用df['date'] = df.index覆盖了该列，因此这可能是代码示例中的错误.但是，如果您首先将其转换为字符串，它将起作用:

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 = 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')

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

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):

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')

像将来一样，仍然支持使用普通的sqlite连接对象而不是sqlalchemy引擎(但仅适用于sqlite！).

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!).

这篇关于 pandas to_sql方法给出日期列错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！