Python3.x 将 pandas Timestamp() 转换为 datetime.datetime() 以支持 peewee DateTimeField()

ANSWER个

阿比的回答是:https://stackoverflow.com/a/74057074/4907339给了我一个很好的线索.

peewee.DateTimeField()还接受DateTime作为字符串.

这是奏效的:

hdf.time = hdf.time.astype(str)
hdf_dict = hdf.to_dict(orient="records")
db.Candles1Minute.insert_many(hdf_dict).execute()

qlite> select * from candles1minute limit 1;
id  symbol  time                 open    high    low      close    volume
--  ------  -------------------  ------  ------  -------  -------  ------
1   USDCAD  2022-10-06 17:15:00  1.3744  1.3745  1.37435  1.37435        
sqlite> 

Original Question个

我try 在数据帧上使用pandas.to_dict()，这样我就可以通过peewee.insert_many()批量插入操作将词典插入sqlite.为此，我需要将Timestamp()转换为datetime.datetime()，这样它才能与peewee.DateTimeField()兼容

我在这里看到的许多答案都提到了转换到datetime.date()，这不是我想要的.

我也不想用to_json().这会将Timestamp()转换为int()，虽然这将与peewee兼容，但我不想将日期存储为int.

我找到了一些描述to_pydatetime()不同用法的答案，但我似乎不能正确回答，因为结果仍然是Timestamp():

# print(hdf.info())
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 1 entries, 0 to 0
Data columns (total 6 columns):
 #   Column  Non-Null Count  Dtype         
---  ------  --------------  -----         
 0   symbol  1 non-null      object        
 1   time    1 non-null      datetime64[ns]
 2   open    1 non-null      float64       
 3   high    1 non-null      float64       
 4   low     1 non-null      float64       
 5   close   1 non-null      float64       
dtypes: datetime64[ns](1), float64(4), object(1)
memory usage: 176.0+ bytes
None

# print(hdf.tail(5))
   symbol                time     open     high      low    close
0  USDCAD 2022-10-13 09:20:00  1.39680  1.39685  1.39375  1.39475
1  USDCAD 2022-10-13 09:21:00  1.39475  1.39605  1.39470  1.39580
...

# hdf.time = hdf.time.apply(lambda x: x.to_pydatetime())
# hdf_dict = hdf.to_dict(orient="records")
# print(hdf_dict)
[{'symbol': 'USDCAD', 'time': Timestamp('2022-10-13 09:20:00'), 'open': 1.3968, 'high': 1.39685, 'low': 1.39375, 'close': 1.39475}, {'symbol': 'USDCAD', 'time': Timestamp('2022-10-13 09:21:00'), 'open': 1.39475, 'high': 1.39605, 'low': 1.3947, 'close': 1.3958}]
# db.Candles1Minute.insert_many(hdf_dict).execute()
InterfaceError                            Traceback (most recent call last)
File ~/Library/Caches/pypoetry/virtualenvs/ariobot-bfns45lq-py3.10/lib/python3.10/site-packages/peewee.py:3197, in Database.execute_sql(self, sql, params, commit)
   3196 try:
-> 3197     cursor.execute(sql, params or ())
   3198 except Exception:

InterfaceError: Error binding parameter 1 - probably unsupported type.

其中parameter 1对应于Peewee模型中的DateTimeField() 声明:

class Candles1Minute(BaseModel):
    symbol = TextField()
    time = DateTimeField()
    open = FloatField()
    high = FloatField()
    low = FloatField()
    close = FloatField()
    volume = IntegerField(null=True)

    class Meta:
        indexes = ((("symbol", "time"), True),)

数据帧中有数以万计的行，所以我希望这种转换又快又高效，所以我想更多的是在Pandas级别进行转换，而不是迭代遍历字典列表并在那里进行转换.