Python mysql参数和加载数据命令(仍然不起作用)

Question

基于Python MySQLdb execute table variable和 MySQL LOAD DATA LOCAL INFILE example in python?这应该有效:

import pymysql, os

directory = os.path.join('path', 'to', 'directory')
filename = 'my_filename.csv'
filepath = os.path.join(directory, filename)
to_table_name = "my_table"

connection = pymysql.connect(..., local_infile=True)
with connection.cursor() as cursor:
    load_statement = """
    load data local infile %s
    into table %s
    fields terminated by ','
    optionally enclosed by '"'
    lines terminated by '\\n'
    ignore 1 lines
    """
    cursor.execute(load_statement % (filepath, to_table_name, ))
connection.commit()
connection.close

但我仍然看到此错误:

ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/path/to/directory/my_filename.csv\n    into ' at line 1")

当我在不带参数的情况下运行它时，即写入实际的文件路径和表名称，它可以工作。

任何帮助将不胜感激。

Answer 1

您还应该使用 Execute 的内置功能来进行字符串格式化(这可以避免 MYSQL 注入(inject)攻击和错误)...而不是使用 % (字符串插值)将参数传递给 load_statement ，而是将其作为参数传递给执行

 cursor.execute(load_statement , (filepath, to_table_name ))

注意逗号而不是 %