在Python中运行SQL语句时出现操作错误

Question

我试图在Visual代码中的Python环境中运行一些SQL语句。我正在查询的数据库在MySQL Workbench 8.0中。我的程序运行顺利，直到到达查询部分。它可以很好地连接到数据库。这是我的代码：

from gettext import install
import pymysql

con = pymysql.Connect(                                             #Creating connection
    host = 'localhost',                                                 #
    port = 3306,                                                        #
    user = 'root',                                                      #
    password = 'Musa2014',                                              #       
    db = 'referees',                                                    #
    charset = 'utf8'                                                    #
)


Ref_Info = input("Enter referee details: ")                       #First input statement

Ref_First_Name, Ref_Last_Name, Ref_Level = Ref_Info.split()

Ref_Info_Table = []

RefID = 1                                                         #Setting the value of the RefID

while Ref_Info != '':                                             #Creating loop to get referee information 
    Ref_Info = Ref_Info.split()
    Ref_Info.insert(0, int(RefID))
    Ref_Info_Table.append(Ref_Info)                               #Updating Ref_Info_Table with new referee data
    print(Ref_Info_Table)                                         #Printing values                                                     #
    print(Ref_Info)                                                     #
    print('Referee ID:', RefID)                                         #
    print('Referee First Name:', Ref_First_Name)                        #
    print('Referee Last Name:', Ref_Last_Name)                          #
    print('Referee Level:', Ref_Level)                                  #
    
    Ref_First_Name = Ref_Info[1]
    Ref_Last_Name = Ref_Info[2]
    Ref_Level = Ref_Info[3]
    
    RefID = RefID + 1                                             #Increasing the value of RefID
    Ref_Info = input("Enter referee details: ")                   #Establishing recurring input again


cur = con.cursor()
sql_query1 = 'INSERT INTO ref_info VALUES(1, MuhammadMahd, Ansari, B&W)'
sql_query2 = 'SELECT * FROM ref_info'
cur.execute(sql_query1)
cur.execute(sql_query2)
data = cur.fetchall()
con.commit()
cur.close()
con.close()

这是我收到的错误:出现了异常: OperationalError (1054，“字段列表”中的“未知列'MuhammadMahd‘”)文件"C:\Users\mahd_.vscode\Code文件夹\list\test2.py“，第44行，cur.execute(sql_query1)

如果您能解释错误并告诉我如何解决它，这将是有帮助的。

谢谢。

Answer 1

sql_query1中的语句

sql_query1 = 'INSERT INTO ref_info VALUES(1, MuhammadMahd, Ansari, B&W)'

忽略字符串文字的"引号字符；此语句将发送到您的mysql服务器

INSERT INTO ref_info VALUES(1, MuhammadMahd, Ansari, B&W)

其中MuhammadMahd、Ansari和B&W不引用任何列或表达式，也不是字符串文本。您必须添加"才能使它们成为字符串文本：

sql_query1 = 'INSERT INTO ref_info VALUES(1, "MuhammadMahd", "Ansari", "B&W")'

此外，为了不必处理特殊字符转义，可以使用python的"""启动和结束字符串，这在用python编写sql时更好：

sql_query1 = """INSERT INTO ref_info VALUES(1, "MuhammadMahd", "Ansari", "B&W")"""