CODEWITHSUNDEEP
pythonmysql-connector-python
Pavan
Pavan

Reputation: 43

Passing a column name in an SQL statement in Python?

This is the code I've written

def edititem():
    edit_code = int(input("Enter  the product code of the item you would like to edit:"))
    edit_cat =  input("Enter the category of the item you would like to edit:")
    edit_val =  int(input("Enter the new value"))
    edit = """UPDATE products SET %s = %s where prod_code = %s"""
    cur.execute(edit,(edit_cat,edit_val,edit_code,))
    connector.commit()

And this is the error I'm getting:

mysql.connector.errors.ProgrammingError: 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 '' prod_code' = 1 where prod_code = 1231' at line 1

I've not been able to figure out what exactly the error is,would be a great help.Thanks in advance.

Upvotes: 4

Views: 1547

Answers (1)

AzyCrw4282
AzyCrw4282

Reputation: 7744

As @S3DEV mentioned, you cannot use SQL parameters to interpolate column names. You'll have to use classic string formatting for those parts.

For example, the following is a using string formatting for the column

cursor.execute("SELECT * FROM PacketManager WHERE {} = ?".format(filters[0]), (parameters[0],))

Also note the importance of validating the permissible column names, to ensure no injection can take place.

You should also see the example from here - Use Python list in SQL query for column names

Upvotes: 3

Related Questions