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.