I'm having trouble with an SQL syntax error

Question

I'm trying to add data to a table collected on a tkinter GUI. This error keeps coming up:

Exception in Tkinter callback
Traceback (most recent call last):
  File "C:\Users\xande\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\connection_cext.py", line 489, in cmd_query
    raw_as_string=raw_as_string)
_mysql_connector.MySQLInterfaceError: 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 'Rank,Email,Phone) VALUES(5509,'mandy123','password123','Mike','Andy','MJR','mand' at line 1

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Users\xande\AppData\Local\Programs\Python\Python37\lib\tkinter\__init__.py", line 1705, in __call__
    return self.func(*args)
    File "C:\Users\xande\OneDrive\Desktop\School\YR 14\CCF program\CCF code.py", line 146, in addldrsubmit
    mycursor.execute(insertSQL,(LID,str(lunentry),str(lpwentry),str(lfnentry),str(lsnentry),str(lrankentry),str(lemailfullentry),lphoneentry,))
  File "C:\Users\xande\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\cursor_cext.py", line 266, in execute
    raw_as_string=self._raw_as_string)
  File "C:\Users\xande\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\connection_cext.py", line 492, in cmd_query
    sqlstate=exc.sqlstate)
mysql.connector.errors.ProgrammingError: 1064 (42000): 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 'Rank,Email,Phone) VALUES(5509,'mandy123','password123','Mike','Andy','MJR','mand' at line 1

The code I'm using is:

import mysql.connector
if True:
    mydb = mysql.connector.connect(
        host="localhost",
        user="root",
        passwd="abarclay172",
        database="rbaiccf"
        )
mycursor=mydb.cursor()

LID = random.randint(1000,9999)
    lunentry = lunentrye.get()
    lpwentry = lpwentrye.get()
    lfnentry = lfnentrye.get()
    lsnentry = lsnentrye.get()
    lrankentry = addldrranke.get()
    lemailentry = lemailentrye.get()
    lemailaddressentry = addldremailaddresse.get()
    lemailfullentry = lemailentry + lemailaddressentry
    lphoneentry = "+44" + lphoneentrye.get()
insertSQL="""INSERT INTO leader(leaderID,Username,Password,Forename,Surname,Rank,Email,Phone) VALUES(%s,%s,%s,%s,%s,%s,%s,%s)"""
    mycursor.execute(insertSQL,(LID,str(lunentry),str(lpwentry),str(lfnentry),str(lsnentry),str(lrankentry),str(lemailfullentry),lphoneentry,))
    mydb.commit()

Why don't you print `insertSQL` before passing it to `execute`? That way you can see what it's doing? — Tom Karzes, Oct 19 '20 at 08:58
Fix the indentation of the code please. Use backticks (`) rank for column name and it might fix the issue. — Delrius Euphoria, Oct 19 '20 at 09:07

score 1 · Accepted Answer · answered Oct 19 '20 at 09:03

From the description you have provided, I guess it is the usage of RANK as a column name. It is a reserved keyword. You could try this solution - using backticks.

But if there is something else going on, you shold provide more info, as stated in comments. Table definition, full SQL, you are trying to execute.

I'm having trouble with an SQL syntax error

1 Answers1