python - 将带有时间的 Pandas 数据帧附加到 SQLite3 数据库并返回

Question

我正在尝试这个:

import pandas as pd
import sqlite3
import datetime, pytz

#nowtime=datetime.datetime.now(pytz.utc)
nowtime=datetime.datetime.now()

print(nowtime)
df = pd.DataFrame(columns=list('ABCD'))
df.loc[0]=(3,0.141,"five-nine",nowtime)
df.loc[1]=(1,0.41,"four-two",nowtime)

print(df)

db = sqlite3.connect(':memory:')
c = db.cursor()
c.execute('create table if not exists ABCD ( A integer, B real, C text, D timestamp );')
c.execute('insert into ABCD (A,B,C, D) values (?,?,?,?);',(1,2.2,'4',nowtime))
c.executemany('insert into ABCD (A,B,C, D) values (?,?,?,?);',df.to_records(index=False))

db.commit()

print(pd.read_sql('select * from ABCD;',db))

得到这个:

 2018-03-07 19:09:58.584953
   A      B          C                          D
0  3  0.141  five-nine 2018-03-07 19:09:58.584953
1  1  0.410   four-two 2018-03-07 19:09:58.584953
   A      B          C                           D
0  1  2.200          4  2018-03-07 19:09:58.584953
1  3  0.141  five-nine    b'\xa8hx?\t\xb9\x19\x15'
2  1  0.410   four-two    b'\xa8hx?\t\xb9\x19\x15'

理想情况下，我想将一些带有时间戳的数据推送到 sqlite3 中，然后以可互操作的方式将其恢复回 pandas/python/numpy。

我看过 Appending Pandas dataframe to sqlite table by primary key用于追加，但我不确定如何使用 sqlite3 使用 datetime.datetime、pandas Timestamps 或 numpy.datetime64 次。

此外，还有 How to read datetime back from sqlite as a datetime instead of string in Python?但我不知道如何在 Pandas 中做到这一点。

我花了很多时间做的一件事是 https://stackoverflow.com/a/21916253/1653571以及令人困惑的多个 to_datetime()。

使用时间、sqlite3 和 pandas 的好方法是什么？

＃＃＃＃＃＃＃ 更新:

我尝试了这些改变:

db = sqlite3.connect(':memory:',detect_types=sqlite3.PARSE_DECLTYPES)

#...
for index,row in df.iterrows():
    print(row)
    c.execute('insert into ABCD (A,B,C,D) values (?,?,?,?);',(row.A,row.B,row.C,row.D.to_pydatetime()))


x = pd.read_sql('select *  from ABCD;',db)

print('Type of a pd.read_sql(SQLite3) timestamp  : ',type(x['D'][0]))

x = c.execute('select * from ABCD').fetchall()

print(x)
print('Type of a sqlite.execute(SQLite3) timestamp  : ',type(x[0][3]))

使用 SQLite3 数据类型并测试返回值:

Type of a pd.read_sql(SQLite3) timestamp  :  <class 'pandas._libs.tslib.Timestamp'>
[(1, 2.2, '4', datetime.datetime(2018, 3, 8, 14, 46, 2, 520333)), (3, 141.0, 'five-nine', datetime.datetime(2018, 3, 8, 14, 46, 2, 520333)), (1, 41.0, 'four-two', datetime.datetime(2018, 3, 8, 14, 46, 2, 520333))]
Type of a sqlite.execute(SQLite3) timestamp  :  <class 'datetime.datetime'>

另外，当我尝试 datetime.datetime.now(pytz.utc) 获取 UTC 感知时间时，但它破坏了很多东西。使用 datetime.datetime.utcnow() 返回不受时区影响的非时区感知对象，效果更好。

另请注意有关 sqlite3.connect(detect_types=...) 参数的 Python sqlite3 文档。启用 detect_types=PARSE_DECLTYPES|PARSE_COLNAMES 提示 python 在系统之间传递的数据上运行转换器。

• https://docs.python.org/3/library/sqlite3.html#sqlite3.PARSE_DECLTYPES对于 create table ... xyzzy timestamp, ... conversions
• https://docs.python.org/3/library/sqlite3.html#sqlite3.PARSE_COLNAMES对于 select ... date as "dateparsed [datetime]"... conversions

Answer 1

主要问题是 SQLite 没有日期时间数据类型。

PARSE_DECLTYPES 在从 SQLite 中读取时无济于事，因为 SQLite 中列的声明数据类型永远不会是日期时间。

由于您控制着 Pandas 数据框，因此您知道将它们保存回 SQLite 时的类型。

您正在使用的read_sql 方法...

is a convenience wrapper around read_sql_table and read_sql_query (and for backward compatibility) and will delegate to the specific function depending on the provided input (database table name or SQL query).

在您的示例中，您提供了一个查询，因此它委托(delegate)给 read_sql_query 方法 https://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_sql_query.html#pandas.read_sql_query

这有一个参数 parse_dates 可以是:

Dict of {column_name: arg dict}, where the arg dict corresponds to the keyword arguments of pandas.to_datetime() Especially useful with databases without native Datetime support, such as SQLite

因为您提前知道哪些列是数据类型，您可以将它们存储为具有与此 parse_dates 期望的结构相匹配的结构的字典，然后将其传递到 read_sql方法。

在我将 pandas df 保存回 csv 或其他文件的其他情况下，我使用类似这样的方法保存模式，以便在将 csv 加载回 pandas 时重新引入。 read_csv 方法有一个 dbtypes 参数，它完全采用下面的结构。

def getPandasSchema(df):
    ''' 
    takes a pandas dataframe and returns the dtype dictionary
    useful for applying types when reloading that dataframe from csv etc
    '''
    return dict(zip(df.columns.tolist(),df.dtypes.tolist()))