While doing insertion in mysql using Python , getting an error as "Unknown Column in field list"

Question

While trying to insert the Query in MYSql through python Script, I am facing an error as "1054 (42S22): Unknown column 'Abhinav' in 'field list'". There is some minor syntax error , but I am not able to Find it.

my_cursor.execute("CREATE DATABASE IF NOT EXISTS task")
my_cursor.execute("CREATE TABLE IF NOT EXISTS EmployeeList(user_id INT AUTO_INCREMENT PRIMARY KEY,EMPID INT, Emp_Name VARCHAR(100),Designation VARCHAR(100), Role VARCHAR(100), Updated_by VARCHAR(100), LastUpdate TIMESTAMP DEFAULT NOW())")
EMP_ID = input("Enter the Employement Id : ")
EmpName = input("Enter the Employee Name : ")
Designations = input("Enter the Designation : ")
Roles = input("Enter the Role : ")
Updatedby = input("Enter the name of the Person updated by: ") 
try:
    sql_insert_query = f"INSERT INTO EmployeeList(EMPID,Emp_Name,Designation,Role,Updated_by) VALUES ({EMP_ID},{EmpName},{Designations},{Roles},{Updatedby})"
    my_cursor.execute(sql_insert_query)
    mydb.commit()
    print("Record inserted successfully")

except mysql.connector.Error as error:
    print("Failed to update record to database: {}".format(error))
finally:
    if (mydb.is_connected()):
        my_cursor.close()
        mydb.close()
        print("MySQL connection is closed")

user input details has to be given after running the script.

Enter the Employement Id : 1
Enter the Employee Name : Abhinav
Enter the Designation : Software Engineer
Enter the Role : GE
Enter the name of the Person updated by: Abhi

Error -

Failed to update record to database: 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 'Engineer,GE,Abhi)' at line 1
MySQL connection is closed

Answer 1

Rather than format the INSERT statement by hand, you should add placeholders and pass the actual values as params to the execute method:

sql_insert_query = "INSERT INTO EmployeeList(EMPID,Emp_Name,Designation,Role,Updated_by) VALUES (%s,%s,%s,%s,%s)"
my_cursor.execute(sql_insert_query, params=(EMP_ID, EmpName, Designations, Roles, Updatedby))

Here's some documentation: https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html