跳到主要内容 PyMySQL JSON 数据处理：Python 与 MySQL 交互 | 极客日志
Python
PyMySQL JSON 数据处理：Python 与 MySQL 交互 PyMySQL 提供 Python 与 MySQL 之间的 JSON 数据交互能力。Python 字典和列表可自动转换为 MySQL JSON 格式存储。查询时 JSON 字段自动解析为 Python 对象，支持直接访问嵌套属性。结合 MySQL 原生 JSON 函数可实现高效查询与更新。针对大数据量可使用服务器端游标优化内存，支持自定义编码器处理特殊类型。批量操作可提升效率，性能优化建议包括创建索引、部分提取及连接池使用。
SqlMaster 发布于 2025/11/20 PyMySQL JSON 数据处理：Python 与 MySQL 交互
你是否在 Python 开发中遇到过 MySQL JSON 字段处理的难题？当需要存储复杂结构数据时，传统关系型字段总是显得力不从心。本文将带你掌握 PyMySQL 中 JSON 数据的完整交互方案，从基础存储到高级查询，让你轻松应对各类 JSON 数据场景。
JSON 数据存储基础
在开始使用 PyMySQL 处理 JSON 数据前，我们需要先了解 MySQL 的 JSON 数据类型。MySQL 从 5.7 版本开始支持原生 JSON 类型，提供了高效的 JSON 数据存储和查询能力。使用 PyMySQL 操作 JSON 数据的核心在于理解 Python 数据结构与 MySQL JSON 格式之间的转换机制。
基本数据插入
使用 PyMySQL 插入 JSON 数据非常简单，只需将 Python 字典或列表直接作为参数传递给 SQL 语句。PyMySQL 会自动处理基本的类型转换，将 Python 数据结构转换为 MySQL 可接受的 JSON 格式。
import
from
import
'localhost'
'your_username'
'your_password'
'test_db'
'utf8mb4'
try
with
as
"""
CREATE TABLE IF NOT EXISTS user_profiles (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
preferences JSON NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
"""
"INSERT INTO user_profiles (name, preferences) VALUES (%s, %s)"
"theme"
"dark"
"notifications"
"email"
True
"push"
False
"favorite_features"
"search"
"analytics"
"reports"
"display_options"
"font_size"
14
"density"
"comfortable"
"John Doe"
finally
PyMySQL 的类型转换逻辑主要在 converters.py 中实现。该模块定义了 Python 数据类型与 MySQL 数据类型之间的转换规则，确保字典、列表等复杂结构能正确序列化为 JSON 格式存储到数据库中。
数据类型映射 PyMySQL 处理 JSON 数据时，会将 Python 数据类型按照以下规则映射为 MySQL JSON 类型：
Python 类型 MySQL JSON 类型 转换说明 dict JSON 对象 转换为 {"key": "value"} 格式 list JSON 数组 转换为 ["item1", "item2"] 格式 str JSON 字符串 直接作为 JSON 字符串存储 int/float JSON 数字 保持数字类型 bool JSON 布尔值 转换为 true 或 false None JSON null 转换为 null
这些转换规则由 converters.py 中的 encoders 字典定义，确保 Python 数据能正确序列化为 MySQL 可接受的格式。
JSON 数据查询与解析 从 MySQL 查询 JSON 数据时，PyMySQL 会自动将 JSON 字段解析为 Python 字典或列表，使你可以像操作普通 Python 数据结构一样处理 JSON 数据。
基本查询操作 import pymysql
from pymysql.cursors import DictCursor
connection = pymysql.connect(
host='localhost' ,
user='your_username' ,
password='your_password' ,
db='test_db' ,
charset='utf8mb4' ,
cursorclass=DictCursor
)
try :
with connection.cursor() as cursor:
select_sql = "SELECT id, name, preferences FROM user_profiles WHERE id = %s"
cursor.execute(select_sql, (1 ,))
result = cursor.fetchone()
if result:
print (f"User: {result['name' ]} " )
print ("Preferences:" )
preferences = result['preferences' ]
print (f"Theme: {preferences['theme' ]} " )
print (f"Email notifications: {preferences['notifications' ]['email' ]} " )
print ("Favorite features:" )
for feature in preferences['favorite_features' ]:
print (f" - {feature} " )
preferences['theme' ] = 'light'
preferences['display_options' ]['font_size' ] = 16
update_sql = "UPDATE user_profiles SET preferences = %s WHERE id = %s"
cursor.execute(update_sql, (preferences, result['id' ]))
connection.commit()
finally :
connection.close()
当从 MySQL 读取 JSON 字段时，PyMySQL 会使用 converters.py 中的 decoders 字典定义的规则，将 JSON 数据转换回相应的 Python 数据类型。这使得我们可以直接像操作普通 Python 字典一样处理 JSON 数据。
使用 MySQL JSON 函数查询 MySQL 提供了丰富的 JSON 函数，可以在 SQL 层面直接操作 JSON 数据。结合 PyMySQL，我们可以灵活地查询和修改 JSON 字段的特定部分：
SELECT name, preferences- > '$.theme' AS theme FROM user_profiles;
SELECT name, JSON_EXTRACT(preferences, '$.display_options.font_size' ) AS font_size
FROM user_profiles
WHERE JSON_EXTRACT(preferences, '$.display_options.font_size' ) > 14 ;
SELECT name, preferences- > '$.favorite_features' AS features
FROM user_profiles
WHERE JSON_SEARCH(preferences, 'one' , 'analytics' ) IS NOT NULL ;
UPDATE user_profiles
SET preferences = JSON_SET(preferences, '$.notifications.push' , true )
WHERE id = % s;
高级 JSON 处理技巧
处理大型 JSON 数据 对于大型 JSON 数据，PyMySQL 提供了流式处理能力，可以通过 SSCursor（服务器端游标）来减少内存占用：
from pymysql.cursors import SSCursor
connection = pymysql.connect(
host='localhost' ,
user='your_username' ,
password='your_password' ,
db='large_data_db' ,
cursorclass=SSCursor
)
try :
with connection.cursor() as cursor:
sql = "SELECT id, large_json_data FROM big_data_table"
cursor.execute(sql)
for row in cursor:
record_id, json_data = row
process_large_json(record_id, json_data)
finally :
connection.close()
自定义 JSON 编码器和解码器 PyMySQL 允许你自定义 JSON 数据的编码和解码逻辑，以满足特定需求。你可以通过修改转换器来自定义 JSON 处理行为：
import json
from datetime import datetime
from pymysql import converters
original_dict_encoder = converters.encoders[dict ]
def custom_json_encoder (value, mapping=None ):
if isinstance (value, dict ):
processed_dict = {}
for k, v in value.items():
if isinstance (v, datetime):
processed_dict[k] = v.isoformat()
else :
processed_dict[k] = v
json_str = json.dumps(processed_dict)
return original_dict_encoder(json_str, mapping)
return original_dict_encoder(value, mapping)
converters.encoders[dict ] = custom_json_encoder
批量操作 JSON 数据 PyMySQL 支持批量插入和更新 JSON 数据，提高处理效率：
try :
with connection.cursor() as cursor:
insert_sql = "INSERT INTO user_profiles (name, preferences) VALUES (%s, %s)"
users = [
("Alice" , {"theme" : "light" , "notifications" : {"email" : True }}),
("Bob" , {"theme" : "dark" , "notifications" : {"email" : False , "push" : True }}),
("Charlie" , {"theme" : "blue" , "favorite_features" : ["dashboard" , "reports" ]})
]
cursor.executemany(insert_sql, users)
connection.commit()
update_sql = "UPDATE user_profiles SET preferences = JSON_SET(preferences, '$.notifications.sms', false)"
cursor.execute(update_sql)
connection.commit()
finally :
connection.close()
常见问题与解决方案
JSON 解码错误 当 JSON 数据格式不正确或包含不受支持的数据类型时，PyMySQL 可能会抛出解码错误。解决方法是使用 try-except 捕获错误，并实现自定义错误处理：
try :
cursor.execute("SELECT id, problematic_json FROM data_table" )
for row in cursor:
try :
json_data = row['problematic_json' ]
except (ValueError, TypeError) as e:
print (f"Error parsing JSON for id {row['id' ]} : {e} " )
fix_corrupted_json(cursor, row['id' ])
except pymysql.Error as e:
print (f"MySQL error: {e} " )
性能优化 处理大量 JSON 数据时，可以通过以下方法优化性能：
创建 JSON 字段索引 ：对经常查询的 JSON 路径创建索引部分提取 ：只获取需要的 JSON 字段，减少数据传输批量操作 ：使用 executemany 减少网络往返连接池 ：使用连接池减少连接开销
ALTER TABLE user_profiles ADD INDEX idx_preferences_theme ((preferences- > '$.theme' ));
总结 PyMySQL 提供了强大而灵活的 JSON 数据处理能力，通过自动类型转换和与 MySQL JSON 函数的无缝集成，使得 Python 与 MySQL 之间的 JSON 数据交互变得简单直观。无论是存储简单的配置信息，还是处理复杂的嵌套 JSON 结构，PyMySQL 都能满足你的需求。
使用 Python 字典和列表直接操作 MySQL JSON 数据
利用 MySQL JSON 函数进行高效的查询和更新
处理大型 JSON 数据和批量操作
自定义 JSON 编码和解码逻辑
优化 JSON 数据处理性能
要深入了解 PyMySQL 的 JSON 处理实现细节，可以查看 converters.py 源代码，该文件定义了所有数据类型的转换规则。更多 PyMySQL 使用示例可以参考官方文档。
掌握 PyMySQL 的 JSON 数据处理技巧，将帮助你在 Python 和 MySQL 开发中更灵活地应对复杂数据结构，构建更强大的应用程序。
相关免费在线工具 curl 转代码 解析常见 curl 参数并生成 fetch、axios、PHP curl 或 Python requests 示例代码。 在线工具，curl 转代码在线工具，online
Base64 字符串编码/解码 将字符串编码和解码为其 Base64 格式表示形式即可。 在线工具，Base64 字符串编码/解码在线工具，online
Base64 文件转换器 将字符串、文件或图像转换为其 Base64 表示形式。 在线工具，Base64 文件转换器在线工具，online
Markdown转HTML 将 Markdown（GFM）转为 HTML 片段，浏览器内 marked 解析；与 HTML转Markdown 互为补充。 在线工具，Markdown转HTML在线工具，online
HTML转Markdown 将 HTML 片段转为 GitHub Flavored Markdown，支持标题、列表、链接、代码块与表格等；浏览器内处理，可链接预填。 在线工具，HTML转Markdown在线工具，online
JSON 压缩 通过删除不必要的空白来缩小和压缩JSON。 在线工具，JSON 压缩在线工具，online
