Creating JSON type Column in SQLite with sqlalchemy

Question

Is it possible to create JSON type Column in SQLite with sqlalchemy? I've tried

import sqlalchemy.types as types
...
myColumn = Column(types.JSON())

and

from sqlalchemy import JSON
...
mycolumn = Column(JSON) 

Both get error message:

Compiler can't render element of type

Wondering if there is any solution in sqlalchemy or I should just change into SQL instead. Thanks in advance.

[Updates] SQLite version 3.16.0

Answer 1

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

Answer 2

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.

Answer 3

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.