pandas to_sql 方法给出日期列错误

pandas to_sql method gives error with date column

提问by Joop

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

还有一个 sqlite3 db 和一个名为 fee_profit 的表

fee_profit has 4 fields:

费用_利润有 4 个字段：

• id - integer - primary key
• perf_period_id - integer
• date - date
• profit - real

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

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.

没有传递主键（这可能是问题吗？）我把桌子弄乱了，看起来肯定是日期问题。尝试了在索引中传递日期的各种组合，也是字符串，没有任何效果。

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 模型创建的

回答by joris

Update:starting with pandas 0.15, to_sqlsupports 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 连接的日期时间值作为 sqlalchemy 引擎写入。因此，不再需要下面描述的解决方法。
Pandas 0.15 将在即将到来的 10 月发布，该功能将合并到开发版本中。

---

---

The reason of the above error is that the df'date' column is a datetime64column, 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.dateobject (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来处理不同的数据库风格。如果您使用它，它将与日期时间列一起正常工作（它会自动将其转换为字符串）：

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！）。