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

Question

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?

Answer 1

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)

Answer 2

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.default`, which is called
    when the encoder doesn't handle a type.
    """
    def default(self, o):
        if isinstance(o, decimal.Decimal):
            return str(o)
        else:
            # raises TypeError: o not JSON serializable
            return json.JSONEncoder.default(self, o)

def init_json(app):
    """
    Use custom JSON encoder with Flask
    """
    app.json_encoder = CustomJSONEncoder

Answer 3

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)

Answer 4

I found anwser here: https://github.com/flask-restful/flask-restful/issues/116#issuecomment-128419699 Summing it up, to run it with Flask-SQLAlchemy:

from flask import Flask, json                                            
from decimal import Decimal              

# define encoder                                                    
class JSONEncoder(json.JSONEncoder):                    
    def default(self, value):                           
        if isinstance(value, Decimal):   
            return str(value)                           
        return json.JSONEncoder.default(self, value)    

class Config:
    RESTFUL_JSON = {}

    # make sure RESTful and Flask encoders stay synchronized
    @staticmethod
    def init_app(app):
        app.config['RESTFUL_JSON']['cls'] = app.json_encoder = JSONEncoder

app = Flask(__name__)
app.config.from_object(Config)
Config.init_app(app)