MySQL插入日期：dd/mm/yy转yy/mm/dd及DATE_FORMAT未定义错误解决

首先，咱们来理清问题根源：

1. DATE_FORMAT未定义的错误：DATE_FORMAT是MySQL的内置函数，只能在SQL语句中使用，不能直接作为Python代码里的函数调用——这就是你报错的核心原因。而且你想用它转换输入日期其实也不对，DATE_FORMAT是用来格式化输出日期的，转换输入字符串到日期类型应该用MySQL的STR_TO_DATE函数。

2. 日期反转问题：你的源日期是dd/mm/yy格式（比如11/04/19），但MySQL默认会按mm/dd/yy的规则解析字符串，再加上你数据库的显示格式设置，最终导致日期被错误解析为2011-04-19（把日当成了年，年当成了日）。

下面给你两种可行的解决方案，按需选择：

方案1：在Python中提前转换日期格式 #

用Python的datetime模块把源日期字符串转换成MySQL兼容的格式，再插入数据库。这种方法的好处是在应用层处理，逻辑更直观。

修正后的完整代码：

import mysql.connector
from datetime import datetime

# 连接数据库（请补充你的连接信息）
sql = mysql.connector.connect(host='你的主机名', user='你的用户名', password='你的密码', db='你的数据库名')
cursor = sql.cursor()

# 注意：Windows路径要加r前缀或者双反斜杠，避免转义字符问题
with open(r"C:\Cumulus\data\Apr19log.txt", "r") as f:
    st = [i.strip().split(',') for i in f.readlines()]

# 转换日期格式并构造插入数据
record = []
for i, j in enumerate(st):
    # 解析源日期（dd/mm/yy格式）
    source_date = j[0]
    try:
        dt = datetime.strptime(source_date, "%d/%m/%y")
        # 转换成yy/mm/dd格式（如果你的date字段是字符串类型）
        formatted_date = dt.strftime("%y/%m/%d")
        # 如果date字段是MySQL DATE类型，推荐用YYYY-MM-DD格式：
        # formatted_date = dt.strftime("%Y-%m-%d")
    except ValueError:
        print(f"无效日期格式：{source_date}，跳过该条数据")
        continue
    # 构造记录
    record.append((i+1, formatted_date, j[1], j[2], j[3]))

# 执行批量插入
sqllist = "INSERT INTO station_fenelon (variable, date, time, outside_temp, outside_humidity) VALUES (%s, %s, %s, %s, %s)"
cursor.executemany(sqllist, record)
sql.commit()

# 关闭连接
cursor.close()
sql.close()

方案2：在MySQL语句中直接转换日期 #

让MySQL自己处理日期格式转换，使用STR_TO_DATE函数把dd/mm/yy格式的字符串转换成日期类型。这种方法适合不想在Python中处理日期逻辑的场景。

修正后的完整代码：

import mysql.connector

# 连接数据库（请补充你的连接信息）
sql = mysql.connector.connect(host='你的主机名', user='你的用户名', password='你的密码', db='你的数据库名')
cursor = sql.cursor()

# 注意路径的转义问题
with open(r"C:\Cumulus\data\Apr19log.txt", "r") as f:
    st = [i.strip().split(',') for i in f.readlines()]

# 构造插入数据（直接用源日期字符串）
record = [(i+1, j[0], j[1], j[2], j[3]) for i, j in enumerate(st)]

# 修改SQL语句，用STR_TO_DATE转换日期
sqllist = """INSERT INTO station_fenelon (variable, date, time, outside_temp, outside_humidity)
VALUES (%s, STR_TO_DATE(%s, '%d/%m/%y'), %s, %s, %s)"""

cursor.executemany(sqllist, record)
sql.commit()

# 关闭连接
cursor.close()
sql.close()

额外提醒：

• 尽量把数据库的date字段设置为DATE类型，而不是字符串类型，这样MySQL会自动处理日期的存储和格式规范，避免很多格式问题。
• 处理文件时推荐使用with语句，它会自动关闭文件，更安全。

内容的提问来源于stack exchange，提问作者John