彻底搞懂PyMySQL数据类型映射：避免90%的类型转换坑
项目地址: https://gitcode.com/gh_mirrors/py/PyMySQL 你是否曾遇到Python整数存入MySQL变成字符串？datetime对象读取后格式异常？本文将系统梳理PyMySQL中Python与MySQL数据类型的映射关系，通过官方源码解析和实战案例，帮你解决类型转换难题。
类型映射核心机制
PyMySQL的类型转换由两大模块控制：编码（Python→MySQL）和解码（MySQL→Python）。编码逻辑定义在pymysql/converters.py的encoders字典，解码逻辑在同文件的decoders字典，使用MySQL字段类型常量作为键，这些常量定义在pymysql/constants/FIELD_TYPE.py。
数据流转流程
- Python类型 → 经
encoders编码为SQL文本 - 执行SQL → 存储到MySQL对应类型字段
- 查询时 → MySQL类型经
decoders解码为Python类型
完整类型映射表
| MySQL类型 | 类型常量 | Python类型 | 转换函数 | 说明 |
|---|---|---|---|---|
| INT/BIGINT | FIELD_TYPE.LONG/LONGLONG | int | int() | 直接转换 |
| FLOAT/DOUBLE | FIELD_TYPE.FLOAT/DOUBLE | float | float() | 浮点运算注意精度 |
| DECIMAL/NEWDECIMAL | FIELD_TYPE.DECIMAL/NEWDECIMAL | Decimal | Decimal() | 高精度计算推荐 |
| VARCHAR/TEXT | FIELD_TYPE.VARCHAR/STRING | str | through() | 原样返回字符串 |
| DATETIME/TIMESTAMP | FIELD_TYPE.DATETIME/TIMESTAMP | datetime.datetime | convert_datetime() | 支持微秒级精度 |
| DATE | FIELD_TYPE.DATE | datetime.date | convert_date() | 仅日期部分 |
| TIME | FIELD_TYPE.TIME | datetime.timedelta | convert_timedelta() | 注意负数时间处理 |
| YEAR | FIELD_TYPE.YEAR | int | int() | 年份转为整数 |
| BIT | FIELD_TYPE.BIT | bytes | convert_bit() | 二进制数据需特殊处理 |
| BLOB系列 | FIELD_TYPE.BLOB等 | bytes | through() | 二进制原样保留 |
特殊类型处理说明
- JSON类型：MySQL的JSON类型在PyMySQL中被映射为字符串，需手动使用
json模块解析 - ENUM/SET：同样以字符串形式返回，需根据业务逻辑验证有效值
- 0000-00-00日期：因Python不支持无效日期，会返回原始字符串
实战案例与常见问题
日期时间处理示例
# 插入datetime
import datetime
import pymysql
conn = pymysql.connect(host='localhost', user='user', password='pass', db='test')
cursor = conn.cursor()
# Python datetime → MySQL DATETIME
now = datetime.datetime(2023, 10, 30, 15, 30, 45, 123456)
cursor.execute("INSERT INTO logs (event_time) VALUES (%s)", (now,))
# MySQL DATETIME → Python datetime
cursor.execute("SELECT event_time FROM logs WHERE id=1")
result = cursor.fetchone()[0]
print(type(result)) # <class 'datetime.datetime'>
print(result.microsecond) # 123456 (保留微秒)
常见问题解决方案
- Decimal精度丢失
# 错误示例：使用float处理Decimal
cursor.execute("SELECT price FROM products WHERE id=1")
price = cursor.fetchone()[0] # Decimal类型
total = price * 1.1 # 可能产生精度误差
# 正确做法：保持Decimal运算
from decimal import Decimal
total = price * Decimal('1.1')
- TIME类型转换为timedelta
cursor.execute("SELECT duration FROM tasks WHERE id=1")
duration = cursor.fetchone()[0] # datetime.timedelta对象
hours = duration.total_seconds() / 3600 # 转为小时数
- BIT类型数据处理
cursor.execute("SELECT flags FROM settings WHERE id=1")
flags = cursor.fetchone()[0] # bytes类型
is_enabled = (flags[0] & 0b0001) != 0 # 按位解析
自定义类型转换
PyMySQL允许通过conv参数自定义类型转换器，覆盖默认行为：
# 自定义JSON类型转换
import json
def custom_json_decoder(obj):
if isinstance(obj, str):
try:
return json.loads(obj)
except json.JSONDecodeError:
return obj
return obj
conn = pymysql.connect(
host='localhost',
user='user',
password='pass',
db='test',
conv={FIELD_TYPE.JSON: custom_json_decoder} # 添加自定义解码器
)
最佳实践总结
- 类型匹配原则：Python类型应与MySQL类型对应，如整数用int而非float
- 精度敏感场景：金额计算必须用Decimal而非float
- 日期处理：优先使用datetime对象而非字符串，避免时区问题
- 大数据处理：BLOB/TEXT类型建议用迭代器分段处理
- 异常处理：对可能返回无效日期的查询，做好字符串回退处理
完整转换逻辑可查阅pymysql/converters.py源码，包含各类边界情况处理。合理利用类型映射能大幅减少数据处理错误，提升程序健壮性。
扩展学习资源
创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考
