Having to add a series of custom fields to a quite relational application, I decided to try the new JSON fields.
As of now you can:
– create json fields
– manipulate them with json_extract, json_unquote
– create generated fields from json entries
You can not:
– index json fields directly, create a generated field and index it
– retain the original json datatype (eg. string, int), as json_extract always returns strings.
Let’s start with a simple flask app:
# requirements.txt mysql-connector-python Flask-SQLAlchemy==2.0 SQLAlchemy>=1.1.3
Let’s create a simple flask app connected to a db.
import flask
import flask_sqlalchemy
from sqlalchemy.dialects.mysql import JSON
# A simple flask app connected to a db
app = flask.Flask('app')
app.config['SQLALCHEMY_DATABASE_URI']='mysql+mysqlconnector://root:secret@localhost:3306/test'
db = flask_sqlalchemy.SQLAlchemy(app)
Add a class to the playground and create it on the db. We need sqlalchemy>=1.1 to support the JSON type!
# The model
class MyJson(db.Model):
name = db.Column(db.String(16), primary_key=True)
json = db.Column(JSON, nullable=True)
def __init__(self, name, json=None):
self.name = name
self.json = json
# Create table
db.create_all()
Thanks to flask-sqlalchemy we can just db.session ;)
# Add an entry
entry = MyJson('jon', {'do': 'it', 'now': 1})
db.session.add(entry)
db.session.commit()
We can now verify using a raw select that the entry is now serialized on db
# Get entry in Standard SQL entries = db.engine.execute(db.select(columns=['*'], from_obj=MyJson)).fetchall() (name, json_as_string), = first_entry # unpack result (it's just one!) assert isinstance(json_as_string, basestring)
A raw select to extract json fields now:
entries = db.engine.execute(db.select(columns=[name, 'json_extract(json, "$.now")'], from_obj=MyJson)).fetchall() (name, json_now), = first_entry # unpack result (it's just one!) assert isinstance(json_now, basestring) assert json_now != entry.json['now'] # '1' != 1