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
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.
