如何解决Python向SurrealDB插入datetime数据的报错问题？

我尝试向SurrealDB的trade表插入以下交易记录：

{'date': '2023-07-03T07:18:52.841147', 'open': 27430.0, 'high': 27730.0, 'low': 27420.0, 'close': 27720.0, 'final': 27490.0, 'y_final': 27190.0, 'vol': 713407.0, 'val': 19755901740.0, 'count': 560.0, 'ins': 'instrument:IRO1ABIK0001'}

但收到数据库错误：

surrealdb.ws.SurrealPermissionException: There was a problem with the database: Found '2023-07-03T07:18:52.841147' for field date, with record 
trade:mb5zls5xah1xyo4rsa1o, but expected a datetime.

将date字段转为Python的datetime对象后，又出现JSON序列化错误：

TypeError: Object of type datetime is not JSON serializable

方法1：使用SurrealDB原生datetime字面量 #

SurrealDB支持通过datetime()包裹ISO格式字符串来识别datetime类型，直接在插入字典中使用该格式即可，无需转换为Python datetime对象：

import asyncio
from surrealdb import Surreal


async def inser_trade():
    async with Surreal("ws://localhost:8000/rpc") as db:
        await db.signin({"user": "root", "pass": "root"})
        await db.use("algo_trading", "algo_trading")
        record = {
            'date': "datetime('2023-07-03T07:18:52.841147')",
            'open': 27430.0,
            'high': 27730.0,
            'low': 27420.0,
            'close': 27720.0,
            'final': 27490.0,
            'y_final': 27190.0,
            'vol': 713407.0,
            'val': 19755901740.0,
            'count': 560.0,
            'ins': 'instrument:IRO1ABIK0001'
        }
        result = await db.create('trade', record)

asyncio.run(inser_trade())

方法2：自定义JSON序列化器处理datetime对象 #

若需要保留Python datetime对象的使用，可以自定义JSON编码器，在初始化Surreal连接时指定该编码器：

import asyncio
import datetime as dt
import json
from surrealdb import Surreal


class DateTimeEncoder(json.JSONEncoder):
    def default(self, obj):
        if isinstance(obj, dt.datetime):
            return obj.isoformat()
        return super().default(obj)


async def inser_trade():
    async with Surreal("ws://localhost:8000/rpc", json_encoder=DateTimeEncoder) as db:
        await db.signin({"user": "root", "pass": "root"})
        await db.use("algo_trading", "algo_trading")
        record = {
            'date': dt.datetime.fromisoformat('2023-07-03T07:18:52.841147'),
            'open': 27430.0,
            'high': 27730.0,
            'low': 27420.0,
            'close': 27720.0,
            'final': 27490.0,
            'y_final': 27190.0,
            'vol': 713407.0,
            'val': 19755901740.0,
            'count': 560.0,
            'ins': 'instrument:IRO1ABIK0001'
        }
        result = await db.create('trade', record)

asyncio.run(inser_trade())

内容的提问来源于stack exchange，提问作者یاقوب