解决Python写入MySQL中datetime类型遇到的问题

问题背景

在Windows 10环境下，使用Python 3.6连接MySQL 5.6.38数据库时，尝试将Python中的datetime变量直接写入MySQL表的datetime类型字段。虽然程序运行返回exit code 0，但实际数据写入失败。环境配置涉及pymysql库及utf8字符集。

代码分析与错误原因

初始代码尝试通过字符串格式化将datetime对象转换为字符串后插入数据库：

import datetime
import pymysql.cursors

conn = pymysql.connect(host='127.0.0.1',
               port=3306,
               user='root',
               password='',
               db='test',
               charset='utf8',
               cursorclass=pymysql.cursors.DictCursor)

# 假设 dt 已赋值
dt = datetime.datetime(2001, 1, 2, 11, 0, 0)
print(dt.strftime('%Y-%m-%d %H:%M:%S'))
# 运行结果是 2001-1-2 11：00：00

# 原始尝试：直接格式化字符串
sql_insert = "INSERT into tablename(exTime) values(%s)" % (dt.strftime("%Y-%m-%d %H:%M:%S"))

try:
  with conn.cursor() as csor1:
    csor1.execute(sql_insert)
    conn.commit()
    csor1.close()
except Exception as e:
  # 错误回滚
  conn.rollback()
finally:
  conn.close()

上述代码中，虽然打印出的时间格式正确（如 2001-1-2 11：00：00），但直接通过字符串拼接方式生成SQL语句时，MySQL可能无法正确识别该字符串为datetime类型，导致写入失败。如果手动将值写死为字符串形式（如 '2001-1-2 11：00：00'）则可能运行成功，但这并非动态写入的正确做法。

解决方案：使用str_to_date函数

为确保Python datetime对象能正确转换为MySQL的datetime类型，应在SQL语句中使用MySQL内置函数 str_to_date。该函数用于将字符串按照指定格式转化为MySQL的datetime类型。

修正后的SQL插入语句如下：

sql_insert = "INSERT into tablename(exTime) values(str_to_date('%s','%%Y-%%m-%%d %%H:%%i:%%s'))" % (dt.strftime("%Y-%m-%d %H:%M:%S"))

在此语句中：

• %s 被替换为Python格式化后的时间字符串。
• '%%Y-%%m-%%d %%H:%%i:%%s' 指定了时间字符串的格式，注意在Python字符串格式化中，百分号需要转义为 %%。

修改程序后重新运行，数据即可成功写入MySQL表的exTime字段。

总结

在Python向MySQL写入datetime数据时，直接使用字符串格式化可能导致类型识别问题。通过引入MySQL的 str_to_date 函数，并正确指定格式字符串，可以确保数据准确写入。此方法适用于Python 3.6与MySQL 5.6.38的组合环境。

以上就是解决Python写入MySQL中datetime类型遇到的问题的详细内容，更多关于Python MySQL datetime操作资料请关注本站其它相关文章！