嵌入Python脚本的SQL查询 - 错误

Question

我想写一个脚本，可以遍历一个文件夹里的csv文件，然后把它们导入到MySQL数据库里。我已经成功把一个csv文件导入了，但在把文件名传给SQL脚本时遇到了问题。

这是我使用的代码

file_path="C:\csv-files"
files=os.listdir(file_path)
files.sort()
for n in files:

    cursor.execute(" LOAD DATA LOCAL INFILE '%s' INTO TABLE new_table FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' Lines terminated by '\n' IGNORE 1 LINES ",(n))

然后我遇到了以下错误

raise errorclass, errorvalue
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 'file1.csv'' INTO TABLE new_table FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY' at line 1")

如果我直接使用文件名，而不是传递它，代码就能正常工作。

从错误信息来看，SQL脚本里似乎有个问题。

这就是完整的代码

import csv
import MySQLdb
import sys
import os
connection = MySQLdb.connect(host='localhost',
    user='root',
    passwd='password',
    db='some_db')


cursor = connection.cursor()

file_path="C:\csv-files"
files=os.listdir(file_path)
files.sort()
for n in files:
    print n



    cursor.execute(" LOAD DATA LOCAL INFILE %s INTO TABLE new_table FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' Lines terminated by '\n' IGNORE 1 LINES " %n)



connection.commit()
cursor.close()

Answer 1

首先，把查询中的'%s'替换成%s。MySQLdb会自动处理引号的问题。

下面是经过一些修正和修改的代码：

import MySQLdb
import os

CSV_DIR = "C:\csv-files"

connection = MySQLdb.connect(host='localhost',
                             user='root',
                             passwd='password',
                             db='some_db',
                             local_infile=1)

cursor = connection.cursor()

try:
    for filename in sorted(os.listdir(CSV_DIR)):
        cursor.execute("""LOAD DATA LOCAL INFILE %s
                          INTO TABLE new_table
                          FIELDS
                          TERMINATED BY ','
                          OPTIONALLY ENCLOSED BY '"'
                          ESCAPED BY '"'
                          LINES TERMINATED BY '\n'
                          IGNORE 1 LINES""",
                      (os.path.join(CSV_DIR, filename),))

    connection.commit()
finally:
    cursor.close()

注意：我在MySQLdb.connect中把local_infile参数设置为1，并且把文件名放在一个元组里传给execute。

这样对我来说是有效的。