Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
514 views
in Technique[技术] by (71.8m points)

python - Using a custom JSON encoder for SQLAlchemy's PostgreSQL JSONB implementation

I am using SQLAlchemy's core library to access some PostgreSQL database. Consider I have the following table:

create table foo (j jsonb);

And the following python code:

from decimal import *
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey, DateTime
from sqlalchemy.dialects.postgresql import JSONB
metadata = MetaData(schema="public")
foo = Table('foo', metadata,Column('f', JSONB))
d = Decimal(2)
ins = foo.insert().values(j = {'d': d})
# assuming engine is a valid sqlalchemy's connection
engine.execute(ins)

This last sentence fails with the following error:

StatementError("(builtins.TypeError) Decimal('2') is not JSON serializable",)

Which is why I am asking this question: Is there a way to specify a custom encoder for SQLAchemy to use when encoding json data into PostgreSQL dialect?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

This is supported via the json_serializer keyword argument to create_engine, as documented under sqlalchemy.dialects.postgresql.JSON:

def _default(val):
    if isinstance(val, Decimal):
        return str(val)
    raise TypeError()

def dumps(d):
    return json.dumps(d, default=_default)

engine = create_engine(..., json_serializer=dumps)

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...