Pandas - 将数据框写入 SQLite 数据库 - datetime64[ns]

Question

我想从一个PostgreSQL数据库中查询数据并将其插入到SQLite数据库中。

这是我的代码:

import pandas as pd
import pandas.io.sql as pd_sql
import sqlite3 as sql3
import psycopg2

#Aquire Data FROM PostgreSQL DB
conn_pg = psycopg2.connect("dbname='xx' user='xxxxx' host=xxx.xxx.xx.xxx password='xxxx'");
sql_1='SELECT * FROM table1 limit 5'
df_1=pd_sql.read_frame(sql_1,conn_pg)
conn_pg.close()

#Insert Into sqlite3 DB
conn_sqlite=sql3.connect('/xxxx/xxxx/xxxx/xxxx/my_db.db')
pd_sql.write_frame(df_1,'table1',conn_sqlite,'sqlite',if_exists='replace')
conn_sqlite.close()

df_1有如下类型：
field1 字符串
field2 日期时间
field3 浮点数
field4 字符串
dtype: object

我遇到了一个错误：

InterfaceError: Error binding parameter 1 - probably unsupported type.  

关于:

pd_sql.write_frame(df_1,'table1',conn_sqlite,'sqlite',if_exists='replace')

我猜测sqlite不支持field2的datetime64类型。我需要帮助确定：
1. 我应该将field2转换为哪种日期类型？
2. 如何在pandas DataFrame中完成此操作？
非常感谢您的帮助。
谢谢！

- Rowan_Gaffney

Answer 1

您说得没错，datetime64字段确实会引起问题。Sqlite没有真正的datetime类型，但是它们使用文本或整数类型来表示时间（请参见http://www.sqlite.org/datatype3.html和http://www.sqlite.org/lang_datefunc.html）。

因此，根据您想要做什么，您可以先将datetime列转换为字符串：

df['field2'] = df['field2'].apply(str)

或者转换为整数（自1970年01月01日00:00:00 UTC以来的秒数）：

df['field2'] = df['field2'].astype('int64')

然后将您的数据写入sqlite。

---

附注：

• 你使用的是哪个版本的pandas？因为在0.13版本（或以下版本）中，if_exists='replace'实现存在一个bug，在当前最新的0.13.1版本中已经修复。
• 在即将发布的pandas 0.14版本中，将会有一个基于sqlalchemy的新的sql函数实现，这里字符串转换将会自动发生（因此不再出现datetime64数据的错误）。

- joris