Python中操作MySQL数据库时如何处理数据类型转换问题

目录

• 一、先搞懂：Python 与 MySQL 数据类型的默认映射
• 二、手动处理数据类型转换的场景与示例
• 1. 场景 1：DECIMAL 类型（金额 / 精度数据）避免浮点误差
• 2. 场景 2：日期时间类型的自定义格式转换
• 3. 场景 3：JSON 类型的处理（MySQL 5.7 + 支持）
• 4. 场景 4：BLOB / 二进制数据（如图片、文件）
• 三、避坑要点
• 总结

pymysql会自动完成基础类型的转换，你先记住核心映射规则，大部分场景不用手动处理：

MySQL 数据类型	Python 类型	说明
INT/TINYINT/BIGINT	int	整数类型直接映射
FLOAT/DOUBLE/DECIMAL	float/Decimal	DECIMAL 默认转 float（需精准则手动转）
VARCHAR/TEXT	str	字符串自动转 Unicode
DATE/DATETIME/TIMESTAMP	datetime.datetime	日期时间自动转 datetime 对象
BOOLEAN	bool	MySQL 的 BOOL 本质是 TINYINT (1)
NULL	None	空值对应 Python 的 None
BLOB	bytes	二进制数据转 bytes

自动转换满足 80% 的场景，但遇到精度要求高、格式自定义、特殊类型时，需要手动干预，以下是高频场景：

MySQL 的 DECIMAL 用于存储金额、汇率等精准数值，pymysql默认转 float 会丢失精度，需手动转decimal.Decimal：

import pymysql
from decimal import Decimal

# 1. 连接时指定转换规则（推荐）
config = {
    'host': 'localhost',
    'user': 'root',
    'password': '你的密码',
    'database': 'test_db',
    'conv': pymysql.converters.conversions.copy()  # 复制默认转换规则
}
# 重写DECIMAL转换规则：转Decimal而非float
config['conv'][pymysql.FIELD_TYPE.DECIMAL] = Decimal
config['conv'][pymysql.FIELD_TYPE.NEWDECIMAL] = Decimal

# 2. 连接并查询
connection = pymysql.connect(**config)
cursor = connection.cursor(pymysql.cursors.DictCursor)

# 创建测试表（含DECIMAL字段）
cursor.execute("""
CREATE TABLE IF NOT EXISTS order_info (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_no VARCHAR(20),
    amount DECIMAL(10,2)  # 金额，10位总长度，2位小数
)
""")
# 插入数据（直接传Decimal更精准）
cursor.execute("INSERT INTO order_info (order_no, amount) VALUES (%s, %s)", 
               ("ORD202501", Decimal("99.99")))
connection.commit()

# 查询验证（amount直接是Decimal类型）
cursor.execute("SELECT * FROM order_info WHERE id = %s", (1,))
result = cursor.fetchone()
print(f"金额：{result['amount']}，类型：{type(result['amount'])}")  # 输出：99.99，类型：<class 'decimal.Decimal'>

cursor.close()
connection.close()

MySQL 的 DATETIME 自动转datetime.datetime，但有时需要转字符串（如接口返回）或自定义格式：

import pymysql
from datetime import datetime

connection = pymysql.connect(
    host='localhost',
    user='root',
    password='你的密码',
    database='test_db',
    cursorclass=pymysql.cursors.DictCursor
)
cursor = connection.cursor()

# 1. 写入：Python datetime对象直接传入（自动转MySQL DATETIME）
insert_time = datetime(2025, 1, 19, 10, 30, 0)
cursor.execute("INSERT INTO users (name, create_time) VALUES (%s, %s)", 
               ("王五", insert_time))
connection.commit()

# 2. 读取：datetime对象转自定义格式字符串
cursor.execute("SELECT create_time FROM users WHERE name = %s", ("王五",))
result = cursor.fetchone()
dt_obj = result['create_time']
# 转成"YYYY-MM-DD HH:MM:SS"字符串
dt_str = dt_obj.strftime("%Y-%m-%d %H:%M:%S")
print(f"创建时间：{dt_str}，类型：{type(dt_str)}")  # 输出：2025-01-19 10:30:00，类型：str

# 3. 反向：字符串转datetime写入
dt_str2 = "2025-01-20 15:00:00"
dt_obj2 = datetime.strptime(dt_str2, "%Y-%m-%d %H:%M:%S")
cursor.execute("UPDATE users SET create_time = %s WHERE name = %s", 
               (dt_obj2, "王五"))
connection.commit()

cursor.close()
connection.close()

MySQL 的 JSON 类型需手动序列化 / 反序列化（pymysql不会自动转 Python 字典）：

import pymysql
import json

connection = pymysql.connect(
    host='localhost',
    user='root',
    password='你的密码',
    database='test_db',
    cursorclass=pymysql.cursors.DictCursor
)
cursor = connection.cursor()

# 1. 创建含JSON字段的表
cursor.execute("""
CREATE TABLE IF NOT EXISTS user_profile (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    profile JSON  # JSON类型字段
)
""")

# 2. 写入：Python字典转JSON字符串
profile_dict = {"hobby": ["篮球", "编程"], "address": {"city": "北京"}}
profile_json = json.dumps(profile_dict, ensure_ascii=False)  # ensure_ascii=False保留中文
cursor.execute("INSERT INTO user_profile (user_id, profile) VALUES (%s, %s)", 
               (1, profile_json))
connection.commit()

# 3. 读取：JSON字符串转Python字典
cursor.execute("SELECT profile FROM user_profile WHERE user_id = %s", (1,))
result = cursor.fetchone()
profile_str = result['profile']
profile_dict2 = json.loads(profile_str)
print(f"爱好：{profile_dict2['hobby']}，城市：{profile_dict2['address']['city']}")
# 输出：爱好：['篮球', '编程']，城市：北京

cursor.close()
connection.close()

MySQL 的 BLOB 类型对应 Python 的 bytes，读写时需处理二进制数据：

import pymysql

connection = pymysql.connect(
    host='localhost',
    user='root',
    password='你的密码',
    database='test_db',
    cursorclass=pymysql.cursors.DictCursor
)
cursor = connection.cursor()

# 1. 创建含BLOB字段的表
cursor.execute("""
CREATE TABLE IF NOT EXISTS file_store (
    id INT AUTO_INCREMENT PRIMARY KEY,
    file_name VARCHAR(100),
    file_data BLOB  # 二进制数据字段
)
""")

# 2. 写入：读取文件转bytes写入
with open("test.jpg", "rb") as f:
    file_bytes = f.read()
cursor.execute("INSERT INTO file_store (file_name, file_data) VALUES (%s, %s)", 
               ("test.jpg", file_bytes))
connection.commit()

# 3. 读取：bytes写入文件
cursor.execute("SELECT file_data FROM file_store WHERE file_name = %s", ("test.jpg",))
result = cursor.fetchone()
file_bytes2 = result['file_data']
with open("test_copy.jpg", "wb") as f:
    f.write(file_bytes2)
print("文件已保存为test_copy.jpg")

cursor.close()
connection.close()

• 避免手动拼接类型转换：不要用str(数值)拼接 SQL，始终用%s参数占位符，pymysql会自动处理类型映射；
• 空值处理：MySQL 的 NULL 对应 Python 的 None，写入时直接传 None，读取时判断是否为 None 避免报错；
• 编码问题：字符串类型（VARCHAR/TEXT）确保连接时指定charset='utf8mb4'，避免中文 / 特殊字符转码错误；
• 超大整数处理：MySQL 的 BIGINT 如果超过 Python int 范围（如大于 2^63-1），需用pymysql的long转换（Python 3 已统一为 int，无需额外处理）。

• 基础类型：pymysql自动完成 int/str/bool/None 等类型映射，无需手动处理；
• 高精度 / 特殊类型：DECIMAL 转decimal.Decimal、JSON 用json模块序列化、BLOB 用 bytes 处理；
• 日期时间：利用datetime模块完成对象与字符串的互转，写入时直接传 datetime 对象更安全；
• 核心原则：优先用参数化查询（% s），避免手动类型拼接，防止转换错误和 SQL 注入。

到此这篇关于Python中操作MySQL数据库时如何处理数据类型转换问题的文章就介绍到这了,更多相关Python操作MySQL数据类型转换内容请搜索风君子博客以前的文章或继续浏览下面的相关文章希望大家以后多多支持风君子博客！

您可能感兴趣的文章:

• python中的一些类型转换函数小结
• 常用python数据类型转换函数总结
• 浅谈Python数据类型之间的转换
• Python数据类型转换详解