SQLAlchemy玩转MySQL JSON数据类型

传统的关系型数据库在处理结构化数据方面表现出色，但面对半结构化或非结构化数据时，它们的能力就显得有些力不从心。为了解决这一问题，MySQL引入了对JSON数据类型的支持。

SQLAlchemy作为Python领域中一个强大的ORM（对象关系映射）工具，为我们提供了一种优雅的方式来操作这些JSON数据，它提供了一种高层的Pythonic抽象，以方便地与数据库进行交互。

在本篇文章中，将通过示例演示如何使用SQLAlchemy来操作MySQL中的JSON数据类型，同时也给出对应的SQL语句

创建数据库引擎和会话

from sqlalchemy import create_engine, Column, Integer, String, JSON, select, func, update, text
from sqlalchemy.orm import sessionmaker, declarative_base

Base = declarative_base()
engine = create_engine('mysql+pymysql://user:password@localhost/dbname')
Session = sessionmaker(bind=engine)
session = Session()

创建表

class Document(Base):
    __tablename__ = 'document'
    doc_id = Column(Integer, primary_key=True, autoincrement=True)
    doc_name = Column(String(255), nullable=False)
    extra = Column(JSON) # 额外信息，JSON格式

Base.metadata.create_all(engine)

CREATE TABLE document (
    doc_id INT AUTO_INCREMENT PRIMARY KEY,
    doc_name VARCHAR(255) NOT NULL,
    extra JSON -- 额外信息，JSON格式
);

插入数据

documents = [
    Document(doc_name='Document 1', extra={"author": "John Doe", "keywords": ["SQL", "Database"]}),
    Document(doc_name='Document 2', extra={"author": "Jane Smith", "keywords": ["MySQL", "JSON"]}),
    Document(doc_name='Document 3', extra={"author": "Michael Johnson", "keywords": ["Data Analysis", "Python"]}),
    Document(doc_name='Document 4', extra={"author": "Emily Davis", "keywords": ["Programming", "Algorithms"]}),
    Document(doc_name='Document 5', extra={"author": "Chris Wilson", "keywords": ["Web Development", "JavaScript"]}),
    Document(doc_name='Document 6', extra={"author": "Amanda Brown", "keywords": ["Machine Learning", "AI"]}),
    Document(doc_name='Document 7', extra={"author": "Daniel Lee", "keywords": ["Cloud Computing", "AWS"]}),
    Document(doc_name='Document 8', extra={"author": "Sophia Martinez", "keywords": ["Cybersecurity", "Networks"]}),
    Document(doc_name='Document 9', extra={"author": "William Taylor", "keywords": ["Mobile Apps", "iOS", "Android"]}),
    Document(doc_name='Document 10', extra={"author": "Olivia Anderson", "keywords": ["Big Data", "Hadoop", "Spark"]})
]
session.add_all(documents)
session.commit()

INSERT INTO document (doc_name, extra) VALUES
('Document 1', '{"author": "John Doe", "keywords": ["SQL", "Database"]}'),
('Document 2', '{"author": "Jane Smith", "keywords": ["MySQL", "JSON"]}'),
('Document 3', '{"author": "Michael Johnson", "keywords": ["Data Analysis", "Python"]}'),
('Document 4', '{"author": "Emily Davis", "keywords": ["Programming", "Algorithms"]}'),
('Document 5', '{"author": "Chris Wilson", "keywords": ["Web Development", "JavaScript"]}'),
('Document 6', '{"author": "Amanda Brown", "keywords": ["Machine Learning", "AI"]}'),
('Document 7', '{"author": "Daniel Lee", "keywords": ["Cloud Computing", "AWS"]}'),
('Document 8', '{"author": "Sophia Martinez", "keywords": ["Cybersecurity", "Networks"]}'),
('Document 9', '{"author": "William Taylor", "keywords": ["Mobile Apps", "iOS", "Android"]}'),
('Document 10', '{"author": "Olivia Anderson", "keywords": ["Big Data", "Hadoop", "Spark"]}');

查询

# 查询文档表中的数据以及额外信息中的作者
stmt = (
    select(Document)
    .where(Document.extra['author'] == 'Sophia Martinez')
)
result = session.execute(stmt).scalars().all()
for row in result:
    print(row.doc_id, row.doc_name, row.extra)

SELECT document.*,extra->'$.author'
FROM document
WHERE extra->'$.author'='Sophia Martinez';

替换整个JSON对象

# 替换文档ID为1的整个extra
stmt = (
    update(Document).
    where(Document.doc_id == 1).
    values({
        Document.extra: {"author": "John Doe2", "keywords": ["SQL2", "Database2"]}
    })
)
session.execute(stmt)
session.commit()

UPDATE document
SET extra = '{"author": "John Doe2", "keywords": ["SQL2", "Database2"]}'
WHERE doc_id = 1;

更新某个键对应的值

# 更新文档ID为1的作者
stmt = (
    update(Document).
    where(Document.doc_id == 1).
    values(extra=func.JSON_SET(Document.extra, '$.author', 'New Author'))
)
session.execute(stmt)
session.commit()

UPDATE document
SET extra = JSON_SET(extra, '$.author', 'New Author')
WHERE doc_id = 1;

新增键值对

# 为文档ID为1添加新的字段
stmt = (
    update(Document).
    where(Document.doc_id == 1).
    values({
        Document.extra: func.JSON_SET(Document.extra, '$.new_field', 'new_value')
    })
)
session.execute(stmt)
session.commit()

UPDATE document
SET extra = JSON_SET(extra, '$.new_field', 'new_value')
WHERE doc_id = 1;

删除键值对

# 从文档ID为1的extra字段中删除new_field
stmt = (
    update(Document).
    where(Document.doc_id == 1).
    values({
        Document.extra: func.JSON_REMOVE(Document.extra, '$.new_field')
    })
)
session.execute(stmt)
session.commit()

UPDATE document
SET extra = JSON_REMOVE(extra, '$.new_field')
WHERE doc_id = 1;

添加数组中元素

# 给keywords数组末尾添加一个元素
stmt = (
    update(Document).
    where(Document.doc_id == 1).
    values({
        Document.extra: func.JSON_SET(
            Document.extra,
            '$.keywords',
            func.JSON_ARRAY_APPEND(
                func.JSON_EXTRACT(Document.extra, '$.keywords'),  # 提取现有的keywords数组
                '$',
                'New Keyword'  # 要添加的新关键词
            )
        )
    })
)
session.execute(stmt)
session.commit()

UPDATE document
SET extra = JSON_SET(extra, '$.keywords', JSON_ARRAY_APPEND(extra->'$.keywords', '$', 'New Keyword'))
WHERE doc_id = 1;

删除数组中元素

# 删除keywords数组中的第一个元素
stmt = (
    update(Document).
    where(Document.doc_id == 1).
    values({
        Document.extra: func.JSON_SET(
            Document.extra,
            '$.keywords',
            func.JSON_REMOVE(func.JSON_EXTRACT(Document.extra, '$.keywords'), '$[0]')
        )
    })
)
session.execute(stmt)
session.commit()

UPDATE document
SET extra = JSON_SET(extra, '$.keywords', JSON_REMOVE(extra->'$.keywords', '$[0]'))
WHERE doc_id = 1;