If you are not familiar with SQLAlchemy, check out my article Getting Started with SQLAlchemy.

Let's say you have a database with a JSON column. For example, perhaps the database is postgresql and the following command was used to create a JSONB (json bytes) column.

alter table my_table add column my_column jsonb

Flask uses the MVC (Model View Controller) Framework. Let's say your Flask app has something like this.

• Model -> models.py
• View -> views.py
• Controller -> __init__.py

And let's say your Flask app has the following structure.

├── main.py
├── database (directory)
│   ├── example.db
├── my-project (directory)
│   ├── __init__.py
│   ├── views.py
│   ├── models.py
│   ├── templates (directory)
│   │   ├── home.html

Let's say models.py contains the following. In this example, notice that my_column type is JSON.

from . import db

class users(db.Model):
  id        = db.Column(db.Integer, nullable=False, unique=True, primary_key=True)
  firstname = db.Column(db.String(20), nullable=False, unique=True)
  lastname  = db.Column(db.String(40), nullable=False, unique=False)
  my_column = db.Column(db.JSON,       nullable=False, unique=False)

Often, the logic for the initial setup of the database is placed in __init__.py using create_all or create_engine.

• Connect to MariaDB using Flask-SQLAlchemy create_all
• Connect to MariaDB using SQLAlchemy create_engine
• Connect to mySQL using SQLAlchemy create_engine
• Connect to Postgres using SQLAlchemy create_all
• Connect to Postgres using SQLAlchemy create_engine
• Connect to SQLite using SQLAlchemy

In this example, create_engine is used to connect to a Postgres database.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

def app_obj():
    app = Flask(__name__)
    uri = 'postgresql+psycopg2://johndoe:itsasecret@10.11.12.13:5432/mydb'
    app.config['SQLALCHEMY_DATABASE_URI'] = uri 
    app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
    db.init_app(app)

    with app.app_context():
        try:
            engine = create_engine(uri) # or db.create_all()
        except exc.SQLAlchemyError as exception:
            print(exception)
        except Exception as exception:
            print(exception)
        else:
            print("no exceptions were raised")

    return app

Let's say ID 1 in my_table contains the following.

~]$ select * from my_table where id=1"

id        | 1
my_column | {"data": [{"bar": "World", "foo": "Hello"}]}

Here is an example of how you could query my_table and pass the results to one of your HTML templates.

@blueprint.route('/example')
def example():

    data = my_table.query.all()
    db.session.close()
    return render_template('example.html', my_data=my_data)

And here is how you could display the value of the foo and bar in my_column.

{% for row in result %}
  {% for item in row['my_column']['data'] %}
    item['foo'] = {{item['foo']}} <br />
    item['bar'] = {{item['bar']}} <br />
  {% endfor %}
{% endfor %} 

Of course, you are probably going to want to update the JSON column in the database. This article says:

The JSON type, when used with the SQLAlchemy ORM, does not detect in-place mutations to the structure. In order to detect these, the sqlalchemy.ext.mutable extension must be used. This extension will allow “in-place” changes to the datastructure to produce events which will be detected by the unit of work. See the example at HSTORE for a simple example involving a dictionary.

Thus, to be able to update the JSON column in the database, I had to update my model.py to have MutableDict.

from . import db
from sqlalchemy.ext.mutable import MutableDict

class users(db.Model):
  id        = db.Column(db.Integer, nullable=False, unique=True, primary_key=True)
  firstname = db.Column(db.String(20), nullable=False, unique=True)
  lastname  = db.Column(db.String(40), nullable=False, unique=False)
  my_column = db.Column(MutableDict.as_mutable(db.JSON))

Here is a basic example of how you could update record 1 in the database with JSON.

from flask import Blueprint
from . import db
from .models import my_table

@blueprint.route('/updateDB')
def updateDB():
  db.engine.connect()
  records = my_table.query.filter_by(id=1).one()
  my_table.my_column = [{"foo":"goodbye","bar":"world"}]
  db.session.commit()
  db.session.close()
  return "all good"

And here is how you could append to the "data" list in the JSON in my_column.

from flask import Blueprint
from . import db
from .models import my_table

@blueprint.route('/updateDB')
def updateDB():
  db.engine.connect()
  records = my_table.query.filter_by(id=1).one()
  new_json = my_table.my_column
  new_json['data'] += [{"foo":"goodbye","bar":"world"}]
  my_table.my_column = new_json
  db.session.commit()
  db.session.close()
  return "all good"