SQLAlchemy で JSON 型に upsert する方法

• 概要
• 環境
• 準備
• サンプルコード

概要

JSON 側でフィールドがない場合には追加しある場合は更新します
結論としては update + json_set で OK です

環境

• macOS 11.6.1
• MySQL 8.0.26
• flask-sqlalchemy 2.5.1

準備

CREATE DATABASE test;
USE test;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `profile` json DEFAULT NULL,
  PRIMARY KEY (`id`)
);

サンプルコード

"""flask_sqlalchemyを使ってJSON型をテストするクラス."""
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_marshmallow import Marshmallow

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+mysqldb://root@localhost/test?charset=utf8'  # noqa: E501
db = SQLAlchemy(app)
ma = Marshmallow()


class User(db.Model):
    """Userテーブルのモデル."""

    id = db.Column(db.Integer, primary_key=True)
    profile = db.Column(db.JSON())


class UserResponseSchema(ma.Schema):
    """Userテーブル用のレスポンスデータスキーム."""

    id = ma.String(attribute="id")
    profile = ma.Dict(attribute="profile")


class UserTable():
    """Userテーブルを操作するクラス."""

    def insert(self, profile):
        """profileを登録します."""
        p = User(profile=profile)
        db.session.add(p)
        db.session.commit()

    def select_all(self):
        """全件取得."""
        return User.query.filter().all()

    def upsert(self, id, profile):
        """フィールドがない場合は登録します."""
        user_query = User.query.filter(User.id == id)
        for key, value in profile.items():
            user_query.update(
                {"profile": db.func.json_set(
                    User.profile,
                    "$." + key,
                    value)}, synchronize_session='fetch')
            db.session.commit()


if __name__ == '__main__':
    # テストレコード登録一度のみ
    # user_table = UserTable()
    # profile = {"name": "hawksnowlog"}
    # user_table.insert(profile)

    # レコード取得
    user_table = UserTable()
    records = user_table.select_all()
    ret = UserResponseSchema(many=True).dump(records)
    print(ret)

    # レコード追加
    new_profile = {'age': 10, 'name': 'snowlog'}
    user_table.upsert(ret[0]["id"], new_profile)