I was working on a python program for running a simple rudimentary library list, and, for some reason, me, as well as a friend of mine, recieved 2 errors:
PS C:\Users\Rotten> & C:/Users/Rotten/AppData/Local/Programs/Python/Python310/python.exe "c:/Users/Rotten/Downloads/import mysql.connector as sqltor.py" Traceback (most recent call last): File "C:\Users\Rotten\AppData\Local\Programs\Python\Python310\lib\site-packages\mysql\connector\connection_cext.py", line 523, in cmd_query self._cmysql.query(query, _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 'varchar(70),salt varchar(70))' at line 1
and this too
File "c:\Users\Rotten\Downloads\import mysql.connector as sqltor.py", line 7, in cursor.execute("create table users(username varchar(20),key varchar(70),salt varchar(70));") File "C:\Users\Rotten\AppData\Local\Programs\Python\Python310\lib\site-packages\mysql\connector\cursor_cext.py", line 269, in execute result = self._cnx.cmd_query(stmt, raw=self._raw, File "C:\Users\Rotten\AppData\Local\Programs\Python\Python310\lib\site-packages\mysql\connector\connection_cext.py", line 528, in cmd_query raise errors.get_mysql_exception(exc.errno, msg=exc.msg, 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 'varchar(70),salt varchar(70))' at line 1
import mysql.connector as sqltor
import hashlib
import os
from datetime import date
mycon=sqltor.connect(host="localhost",user="root",passwd="123456",database="library")
cursor=mycon.cursor()
cursor.execute("create table users(username varchar(20),key varchar(70),salt varchar(70));")
cursor.execute("create table userlist(username varchar(20),book varchar(200));")
def users(username,password):
cursor.execute("select * from users where username={}".format(username))
data=cursor.fetchone()
if data=="(NULL)":
return False
elif data==True:
salt=data[2]
key=hashlib.pbkdf2_hmac('sha256', password.encode('utf-8'), salt, 100000)
if data[1]==key:
return True
elif data[1]!=key:
return False
else:
print("error")
else:
print("error")
return False
def userlist(username,bookcode,bookborrowdate):
booktick=cursor.execute("Select book from userlist where username={}".format(username))
if booktick!="NULL":
bookdict=booktick
else:
bookdict={}
bookdict.update({'bookcode':'bookborrowdate'})
cursor.execute("insert into userlist values('{}','{}');".format(username,bookdict))
mycon.commit()
def userfull():
cursor.execute("select book from userlist;")
bookdata=cursor.fetchone()
bookneed=bookdata[0]
for key, value in bookneed.items():
print(key, ' : ', value)
def checkkey():
cursor.execute("select count(username) from users, where username='{}';".format(username))
if 1 in cursor.fetchone():
return True
elif 0 in cursor.fetchone():
return False
else:
print("error")
return False
def userret(username,bookcode):
cursor.execute("select book from userlist where username={};".format(username))
bookreturn=cursor.fetchone()
bookretex=bookreturn[0]
if bookcode in bookretex:
borrowdate=bookretex.get(bookcode)
pricecheck=date.today
dayspast=(pricecheck-borrowdate).days
rate=dayspast*0.5
del bookretex[bookcode]
return rate
def adduser(username,password):
salt = os.urandom(32)
key = hashlib.pbkdf2_hmac('sha256', password.encode('utf-8'), salt, 100000)
cursor.execute("insert into users values('{}','{}','{}'".format(username,key,salt))
mycon.commit()
while True:
print("WELCOME TO THE LIBRARY SYSTEM")
print("1.LOGIN")
print("2.NEW USER")
print("3.EXIT")
ch=int(input("Enter your choice : "))
if ch==1:
print("WELCOME, PLEASE ENTER YOUR USERNAME AND PASSWORD")
username=input("Enter username:")
password=input("Enter password:")
users(username,password)
if users==True:
while True:
print("Welcome back ",username,". What do you want to do? \n")
print("1.Add book to borrow")
print("2.Return book borrowed")
print("3.Check existing borrowed book(s)")
print("4.Logout")
usch=int(input("Enter your choice: "))
if usch==1:
while True:
bookcode=input("Enter code of book to be borrowed")
bookborrowdate=date.today()
userlist(username,bookcode,bookborrowdate)
td=timedelta(10)
print("Please return this book by",bookborrowdate td)
choice=input("Continue?[y/n]")
if choice in 'Yy':
continue
else:
break
elif usch==2:
while True:
bookcode=input("Enter code of borrowed book to be returned")
returnticket=userret(username,bookcode)
print("Book returned, please pay Rs.",returnticket," at the reception")
returncookie=input("continue? [y/n]: ")
if returncookie in "Yy":
continue
else:
break
elif usch==3:
userfull()
elif usch==4:
print("Logging out of ",username,"...")
username=None
password=None
break
else:
print("Wrong input, try again")
elif users==False:
print("Username or password wrong, please try again")
else:
if mycon.is_connected():
print("Unknown error, please try again")
else:
print("Error connecting to database, please try again")
elif ch==2:
while True:
print("WELCOME, NEW USER, PLEASE ENTER A VALID USERNAME AND PASSWORD")
usertest=input("Enter username: ")
usertest2=usertest
checkkey(usertest)
if checkkey==True:
password=input("Enter Password: ")
username=usertest2
adduser(username,password)
print("USER CREATED")
break
elif checkkey==False:
print("Username already exists, try again")
continue
else:
print("Error")
break
elif ch==3:
mycon.close()
break
else:
print("Invalid choice. please try once again.")
Why is this happening, and how do I solve it?
CodePudding user response：
The error occurs because you are trying to make a column called "key" which is a reserved word in mysql.
Use ` around key to escape it.