Geek Answers Handbook

Using custom JSON encoder to implement SQLAlchemy PostgreSQL JSONB

I am using the core SQLAlchemy library to access some PostgreSQL database. 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 connection engine.execute(ins)

The last sentence fails with the following error:

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

That's why I ask this question: is there a way to specify a custom encoder for SQLAchemy to use when encoding json data in a PostgreSQL dialect?

+9
source share
4 answers

This is supported by using the json_serializer keyword argument to create_engine , as described in 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)
+21
source

If you, like me, find a good way to get this to work with Flask-SQLAlchemy, this is what I did. If you import and pass flask.json instead of the standard json library module, you will get automatic deserialization of the dates, times 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)
+4
source

If you use Flask, you already have an extended JSONEncoder defined in flask.json that handles the UUID but not Decimal . It can be mapped to the SqlAlchemy engine with the json_serializer parameter, 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 expand the JSONEncoder flag to support decimal.Decimal as follows:

 import decimal from flask import json class CustomJSONEncoder(json.JSONEncoder): """ Override Flask 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
+3
source

I found the answer here: https://github.com/flask-restful/flask-restful/issues/116#issuecomment-128419699 To summarize, 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)
0
source

More articles:


All Articles