Mysql.connector error（？） 在 Visual Studio Code 上使用 Python 3.10

Question

我正在开发一个python程序来运行一个简单的基本库列表，出于某种原因，我和我的一个朋友收到了2个错误：

PS C：\用户\腐烂

还有这个

文件“c:\Users\Rotten\Downloads\import mysql.connector as sqltor.py”，第7行，位于光标中。执行（“创建表用户（用户名varchar（20），密钥varchar，salt varchar）；”）文件“C:\Users\Rotten\AppData\Local\Programs\Python\Python310\lib\site packages\mysql\connector\cursor_cext.py”，第269行，执行结果=self.cnx。cmd_query（stmt，raw=self._raw，文件“C:\Users\Rotten\AppData\Local\Programs\Python\Python310\lib\site packages\mysql\connector\connection_cext.py”，第528行，在cmd_quiry中引发错误。get_mysql_exception（exc.errno，msg=exc.msg，mysql.connector.errors.ProgrammingErrors:1064（42000）：SQL语法错误；请查看与您的MySQL服务器版本对应的手册，以了解在第1行“varchar（70），salt varchar）（70））”附近使用的正确语法

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.")

为什么会发生这种情况，我该如何解决？

Answer 1

发生该错误是因为您正在尝试创建一个名为“key”的列，该列是mysql中的保留字。在键周围使用 ' 来转义它。

编辑:删除< code >用户中的< code >，，其中在< code>checkkey()下