python - Creating JSON type Column in SQLite with sqlalchemy

10
votes

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

3
pythonjsonsqlitesqlalchemy
what version of SQLite are you using? It may not support the JSON type. - Martin Lear
SQLite version 3.16.0 But how shouid I check if it supports JSON type or not? - Satty

3 Answers

6
votes

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.

8
votes

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

6
votes

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

pip install --user -U --pre SQLAlchemy

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