如何在pymysql的INSERT语句中正确使用STR_TO_DATE()函数？

问题描述 #

在phpMyAdmin中，以下INSERT命令可正常执行：

INSERT INTO babydb.sales_agro_products (customer_id, product_name, product_class, product_price_kG, amount_ordered, sales_value, Date) VALUES ('Big John', 'Kill Everything', 'product_class', 112.34, 678, 76166.52, STR_TO_DATE('05/08/2024', '%d/%m/%Y'))

在Python中使用pymysql时，不含日期的插入语句能正常运行：

import pymysql.cursors

def insert(cname, pname, pclass, priceKg, kilos, totalprice):
    # 连接数据库
    connection = pymysql.connect(host='localhost',
                                 user='pedro',
                                 password='letmein',
                                 database='babydb',
                                 charset='utf8mb4',
                                 cursorclass=pymysql.cursors.DictCursor)

    with connection:
        with connection.cursor() as cursor:
            # 插入新记录
            sql = "INSERT INTO sales_agro_products (customer_id, product_name, product_class, product_price_kG, amount_ordered, sales_value) VALUES (%s, %s, %s, %s, %s, %s)"
            cursor.execute(sql, (cname, pname, pclass, priceKg, kilos, totalprice))

        # pymysql默认不自动提交，需手动提交保存更改
        connection.commit()

        with connection.cursor() as cursor:
            # 查询所有记录
            sql = "SELECT * FROM sales_agro_products" 
            cursor.execute(sql)
            result = cursor.fetchall()
            print(result)

但包含日期的语句执行失败：

sql = "INSERT INTO sales_agro_products (customer_id, product_name, product_class, product_price_kG, amount_ordered, sales_value, Date) VALUES (%s, %s, %s, %s, %s, %s, STR_TO_DATE(%s, '%d/%m/%Y')"
cursor.execute(sql, (cname, pname, pclass, priceKg, kilos, totalprice, date))

报错信息：

TypeError: not enough arguments for format string

解决方法 #

方法1：补全SQL语句的括号 #

你出错的核心原因是SQL语句中STR_TO_DATE(%s, '%d/%m/%Y')末尾缺少了一个右括号，导致SQL语法不完整，pymysql解析参数时出现错误。修正后的SQL语句如下：

sql = "INSERT INTO sales_agro_products (customer_id, product_name, product_class, product_price_kG, amount_ordered, sales_value, Date) VALUES (%s, %s, %s, %s, %s, %s, STR_TO_DATE(%s, '%d/%m/%Y'))"
cursor.execute(sql, (cname, pname, pclass, priceKg, kilos, totalprice, date))

补全右括号后，参数数量和占位符数量匹配，就能正常执行。

方法2：在Python中转换日期后直接传入 #

另一种更简洁的方式是在Python中先将日期字符串转换为datetime对象，直接传入SQL语句，无需使用STR_TO_DATE：

from datetime import datetime

# 假设date是类似"05/08/2024"的字符串
formatted_date = datetime.strptime(date, "%d/%m/%Y")

sql = "INSERT INTO sales_agro_products (customer_id, product_name, product_class, product_price_kG, amount_ordered, sales_value, Date) VALUES (%s, %s, %s, %s, %s, %s, %s)"
cursor.execute(sql, (cname, pname, pclass, priceKg, kilos, totalprice, formatted_date))

pymysql会自动将datetime对象转换为MySQL可识别的日期格式，这种方式更符合Python的代码习惯，也避免了SQL语法错误的风险。

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