Python，mysqldb错误1064

Question

所以我有以下错误:

_mysql_exceptions.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 'I, Alexander_Bernard16@milton.edu, D0NBT6)' at line 1")

<小时/>

这是我的代码:

cnx = MySQLdb.connect(
    user=username, passwd=password, host=hostname, db=databaseName)
cursor = cnx.cursor()
cursor.execute("CREATE TABLE if not exists gotchaTable(id int(11) PRIMARY KEY "
               "AUTO_INCREMENT, selfFirstName TEXT NOT NULL, selfLastName TEXT NOT NULL, "
               "selfGrade TEXT NOT NULL, selfCode TEXT NOT NULL, targetCode TEXT NOT "
               "NULL);")
cnx.commit()

add_data = (
    "INSERT INTO gotchaTable (selfFirstName, selfLastName, selfGrade, selfCode, targetCode) VALUES ({0}, {1}, {2}, {3}, {4});"
)

studentlist = []
with open('Gotcha.csv', 'rb') as csvfile:
    gotchaData = csv.DictReader(csvfile)
    for row in gotchaData:
        student = Student(
            row['First'], row['Last'], row['Class'], row['Email'])
        studentlist.append(student)
studentlist = randomList(studentlist)
for x in xrange(1, len(studentlist)):
    studentlist[x].target = studentlist[
        x + 1] if x < len(studentlist) - 1 else studentlist[0]
    cursor.execute(add_data.format(studentlist[x].first, studentlist[x].last, 
        studentlist[x].grade, studentlist[x].email, 
        studentlist[x].code, studentlist[x].target.code))
    cnx.commit()
    print studentlist[x].getData()

<小时/>

这是我的学生类(class):

class Student(object):

"""docstring for Student"""

def __init__(self, f, l, c, e):
    self.first = f
    self.last = l
    self.grade = c
    self.email = e
    self.code = id_generator()
    self.target = None

def getData(self):
    return self.first + ' ' + self.last + ' ' + self.grade + ' ' + self.email + ' ' + self.code

<小时/>

我正在尝试编写一个程序，从 csv(已经可以工作)中获取数据并将其放入 SQL 表中。如何修复错误 1064，我尝试使用“%s”而不是“{0}”，但出现相同的错误。有什么建议吗？

id_generator() 方法返回一串随机字符。randomList(a) 使数组随机。

Answer 1

不要使用字符串格式来参数化 SQL 查询 - 这很危险，而且如您所见，会出现错误提示。相反，让 MySQL 驱动程序担心它:

add_data = """
    INSERT INTO 
        gotchaTable 
        (selfFirstName, selfLastName, selfGrade, selfCode, targetCode) 
     VALUES 
        (%s, %s, %s, %s, %s)
"""

然后，当您调用 execute() 时，在单独的参数中传递参数:

cursor.execute(add_data, [
    studentlist[x].first, 
    studentlist[x].last, 
    studentlist[x].grade, 
    # studentlist[x].email,  ALSO WATCH THIS ONE (there are only 5 placeholders in the query)
    studentlist[x].code, 
    studentlist[x].target.code
])