Creating JSON type Column in SQLite with sqlalchemy

My solution is:

import json
from sqlalchemy import TypeDecorator, types

class Json(TypeDecorator):

    @property
    def python_type(self):
        return object

    impl = types.String

    def process_bind_param(self, value, dialect):
        return json.dumps(value)

    def process_literal_param(self, value, dialect):
        return value

    def process_result_value(self, value, dialect):
        try:
            return json.loads(value)
        except (ValueError, TypeError):
            return None

...

myColumn = Column("name", Json)

For more information: TypeDecorator


SQLAlchemy 1.3 includes support for SQLite JSON extension, so don't forget to upgrade:

pip install --user -U SQLAlchemy

The dialect specific type sqlite.JSON implements JSON member access, usable through the base type types.JSON as well.


JSON was not added to SQLite until version 3.9. You'll either need to upgrade your SQLite or convert your json to a string and save it as such, while converting it back to a json object when you pull it out.