這邊紀錄了一下 JSON 欄位的操作方式，
有任何錯誤或是更好的寫法，歡迎留言討論喔~

一、建立資料以及表結構

本次使用 政府開放資料 109年族語認證測驗各身分性別通過人數統計 作為範例資料

(一)、建立表結構

建立表結構如果有不清楚的地方可以看 這篇文章

• main.py
  註: 突然改用 mysql 的原因是，在使用 sqlite 寫入 JSON 時，發現中文寫不進去
  註: 已解決，參考 這個連結

# -*- coding: utf-8 -*-
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column
from sqlalchemy import Integer, JSON
from sqlalchemy.orm import sessionmaker

Base = declarative_base()
engine_url = "mysql+pymysql://root:123456@127.0.0.1:3306/test"
engine = create_engine(engine_url, echo=True)


class Test(Base):
    __tablename__ = "test"
    id = Column(Integer, primary_key=True, autoincrement=True)
    records = Column(JSON)


def create_table():
    Base.metadata.create_all(engine)


def drop_table():
    Base.metadata.drop_all(engine)


def create_session():
    Session = sessionmaker(bind=engine)
    session = Session()

    return session


if __name__ == '__main__':
    drop_table()
    create_table()

(二)、建立測試資料

對於新增資料有不懂的地方可以參考 這篇文章

• test.py

import json
from data_struct import Test, create_session
from pprint import pprint

with open("./test_data.json", "rb") as f:
    data = json.load(f)
    pprint(data[0])

    session = create_session()
    for row in data[0]["result"]["records"]:
        data_obj = {"records": row}
        session.add(Test(**data_obj))
        session.commit()

    session.close()

二、利用 SQL 語法查詢

• 語法結構:
  SELECT * FROM test WHERE JSON_EXTRACT(<欄位名稱>, "$.<key>")=<條件>;

• 簡單範例:
  SELECT * FROM test WHERE JSON_EXTRACT(records, "$.Seq")=1;
  

• 中文 key:
  如果 key 值為中文，則必須 key 部分的語法改為 '$."<中文 key>"'

• 簡單範例:
  SELECT * FROM test WHERE JSON_EXTRACT(records, '$."性別"')="男";
  

三、利用 sqlalchemy 查詢

• 語法結構:
  同樣支援 all() 和 first()
  session.query(<表結構>).filter(<表結構>.<欄位>[key] == <條件>).all()

• 簡單範例

# -*- coding: utf-8 -*-
from main import Test, create_session
from pprint import pprint

session = create_session()
res = session.query(Test).filter(Test.records["性別"] == "女").all()

for row in res:
    print(row.id, end=" ")
    print(row.records["Seq"], end=" ")
    print(row.records["性別"], end=" ")
    print(row.records["級別"])

output:

C:\Users\bear\Desktop\sqlalchemy_test\venv\Scripts\python.exe C:/Users/bear/Desktop/sqlalchemy_test/test.py
11 11 女 初級
12 12 女 中級
13 13 女 中高級
14 14 女 高級
15 15 女 優級
16 16 女 初級
17 17 女 中級
18 18 女 中高級
19 19 女 高級
20 20 女 優級

Process finished with exit code 0

三、利用 sqlalchemy 更新

• 語法結構:

from sqlalchemy import func

session.query(<表結構>).filter(<條件>).update({<表結構>.<欄位>: func.json_set(<表結構>.<欄位>, "$.<key>", <要更改的內容>))})

• 簡單範例:

session = create_session()

session.query(Test).filter_by(id=1).update({Test.records: func.json_set(Test.records, "$.Seq", "測試測試測試")})

session.commit()
session.close()

註: 最後要記得 commit 資料庫中的資料才會更改