Visual Studio 代码上的 python 3.10 出现 mysql.connector 错误（？）

Question

我正在开发一个用于运行简单基本库列表的 python 程序，并且由于某种原因，我以及我的一个朋友收到了 2 个错误：

PS C:\Users\Rotten> & C:/Users/Rotten/AppData/Local/Programs/Python/Python310/python.exe “c：/Users/Rotten/Downloads/导入 mysql.connector 作为 sqltor.py” 回溯（最近一次调用最后一次）：文件 “C:\Users\Rotten\AppData\Local\Programs\Python\Python310\lib\site-packages\mysql\connector\connection_cext.py”， 第 523 行，在 cmd_query 中 self._cmysql.query(查询, _mysql_connector.MySQLInterfaceError：您的 SQL 语法有错误；检查与您的 MySQL 服务器版本对应的手册 在 'varchar(70),salt varchar(70))' 附近使用正确的语法 第 1 行

，这也是

文件“c:\Users\Rotten\Downloads\import mysql.connector as sqltor.py”，第 7 行，在 cursor.execute("创建表users(用户名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”， 第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_query 中 引发errors.get_mysql_exception(exc.errno, msg=exc.msg, mysql.connector.errors.ProgrammingError: 1064 (42000): 你有一个 SQL 语法错误；检查与您对应的手册 MySQL 服务器版本，以便在附近使用正确的语法 'varchar(70),salt varchar(70))'位于第 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.")

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

Answer 1

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

编辑：删除 checkkey() 下 users, where 中的 ,