Using a custom JSON encoder for SQLAlchemy's PostgreSQL JSONB implementation
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)
If you, like me, are finding a nice way to get this running with Flask-SQLAlchemy, this is what I did. If you import and pass flask.json
instead of the standard library json
module, you’ll get automatic deserialization of dates, datetimes and uuid.UUID
instances.
class HackSQLAlchemy(SQLAlchemy):
""" Ugly way to get SQLAlchemy engine to pass the Flask JSON serializer
to `create_engine`.
See https://github.com/mitsuhiko/flask-sqlalchemy/pull/67/files
"""
def apply_driver_hacks(self, app, info, options):
options.update(json_serializer=json.dumps)
super(HackSQLAlchemy, self).apply_driver_hacks(app, info, options)
If you're using Flask, you already have an extended JSONEncoder defined in flask.json
which handles UUID
, but not Decimal
. It can be mapped into the SqlAlchemy engine with the json_serializer
param as in @univerio's answer:
from flask import json
engine = create_engine(
app.config['SQLALCHEMY_DATABASE_URI'],
convert_unicode=True,
json_serializer=json.dumps,
)
You can further extend the Flask JSONEncoder
to support decimal.Decimal
with the following:
import decimal
from flask import json
class CustomJSONEncoder(json.JSONEncoder):
"""
Override Flask's JSONEncoder with the single method `default`, which
is called when the encoder doesn't know how to encode a specific type.
"""
def default(self, obj):
if type(obj) is decimal.Decimal:
return str(obj)
else:
# raises TypeError: obj not JSON serializable
return json.JSONEncoder.default(self, obj)
def init_json(app):
"""
Use custom JSON encoder with Flask
"""
app.json_encoder = CustomJSONEncoder