2

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.

AzyCrw4282
  • 7,222
  • 5
  • 19
  • 35
Pavan
  • 23
  • 6
  • 2
    To my recollection, you cannot pass object names (column names) into the `%s` replacemant logic. Formatters are isolated to values. – S3DEV Jul 15 '20 at 12:26
  • So how can you pass column names to a query? – Pavan Jul 15 '20 at 12:30
  • You can create the statement as a string and use string formatting to add the table names dynamically. Just be aware of possible injection opportunities; if this applies to your working environment. – S3DEV Jul 15 '20 at 13:20
  • @Zeck is the problem solvedD? – AzyCrw4282 Jul 16 '20 at 22:16

1 Answers1

1

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

AzyCrw4282
  • 7,222
  • 5
  • 19
  • 35