postgresql - PostgreSQL JSONB 中的日期时间对象

Question

有可能有一个datetime吗？ JSONB 中的对象带有 SQLAlchemy 的 PostgreSQL 9.5.2 中的对象(一种用于存储 JSON 的结构化格式)。

from sqlalchemy.dialects.postgresql import JSONB
from datetime import datetime

class Object(Base):
    __tablename__ = 'object'
    id = Column(Integer, primary_key=True)
    attributes = Column(JSONB, nullable=False)

并插入数据库:

with transaction.manager:
    object = Object(attributes = {'name': 'miss piggy',
                                  'created': datetime.now()})
    session.add(object)
session.commit()

给我以下错误:

StatementError: (builtins.TypeError)
datetime.datetime(2016, 4, 7, 9, 51, 9, 893315) is not JSON serializable

[SQL: 'INSERT INTO object (attributes) VALUES (%(attributes)s)RETURNING object.id']
[parameters: [
    {'attributes': {'name': 'miss piggy',
                    'created': datetime.datetime(2016, 4, 7, 9, 51, 9, 893315)}}]]

Answer 1

要解决这个问题，建议 univerio , 以下函数从日期时间对象创建字典，反之亦然:

def DictDatetime(t):
    dl = ['Y','m','d','H','M','S','f']
    if type(t) is datetime:
        return {a: t.strftime('%{}'.format(a)) for a in dl}
    elif type(t) is dict:
        return datetime.strptime(''.join(t[a] for a in dl),'%Y%m%d%H%M%S%f')

应用:

# Create datetime object
a = datetime.now()
# Convert datetime object to dictionary
b = DictDatetime(a)
# Convert dictionary to datetime object
c = DictDatetime(b)

检查:

a == c

返回:True