Python, mysqldb error 1064

Question

So I have the following error:

_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, [email protected], D0NBT6)' at line 1")

---

Here's my code:

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()

---

And here's my student 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

---

Im trying to make a program that gets data from a csv (which already works) and puts it into a SQL table. How do i fix the error 1064, i've tried using "%s" instead of '{0}' but i get the same error. Any suggestions?

the id_generator() method returns a string of random characters. randomList(a) makes the array random.

Answer 1

Don't use string formatting to parameterize an SQL query - this is dangerous and, as you can see, error-prompt. Instead, let the MySQL driver worry about it:

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

Then, when you call execute() pass parameters in a separate argument:

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
])