Operational Error when running SQL statements in Python

Question

I am trying to run some SQL statements in a Python environment in Visual Studio Code. The database that I am querying is in MySQL Workbench 8.0. My program runs smoothly until it reaches the querying part. It connects to the database fine. Here is my code:

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()

This is the error that I recieved: Exception has occurred: OperationalError (1054, "Unknown column 'MuhammadMahd' in 'field list'") File "C:\Users\mahd_.vscode\Code Folders\Testing\test2.py", line 44, in cur.execute(sql_query1)

It would be helpful if you could explain the error and tell me how to resolve it.

Thank you.

Answer 1

The statement in sql_query1

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

miss the " quoting character for string literal; this statement would be sent to your mysql server as

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

in which MuhammadMahd and Ansari and B&W do not refer to any column or expression, and are not string literals either. You have to add " to make them string literals:

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

And, to not have to deal to special character escape, you could use python's """ to start and end a string, which is better when you are writing sql in python:

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