iT邦幫忙

0

Python & SQLALchemy 學習筆記_JSON 欄位操作

這邊紀錄了一下 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()

https://ithelp.ithome.com.tw/upload/images/20211123/201440246MjBBcivre.png

二、利用 SQL 語法查詢

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

  • 簡單範例:
    SELECT * FROM test WHERE JSON_EXTRACT(records, "$.Seq")=1;
    https://ithelp.ithome.com.tw/upload/images/20211123/2014402401y5F4HaZm.png

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

  • 簡單範例:
    SELECT * FROM test WHERE JSON_EXTRACT(records, '$."性別"')="男";
    https://ithelp.ithome.com.tw/upload/images/20211123/20144024nt13qN3fzq.png

三、利用 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 資料庫中的資料才會更改
https://ithelp.ithome.com.tw/upload/images/20211123/20144024LxBL0UltVd.png


尚未有邦友留言

立即登入留言