Passing a column name in an SQL statement in Python?

Question

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.

To my recollection, you cannot pass object names (column names) into the %s replacemant logic. Formatters are isolated to values. — s3dev
– s3dev, Commented Jul 15, 2020 at 12:26
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
– s3dev, Commented Jul 15, 2020 at 13:20

AzyCrw4282 · Accepted Answer · 2020-07-15 17:47:46Z

3

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

answered Jul 15, 2020 at 17:47

AzyCrw4282

7,8945 gold badges26 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Passing a column name in an SQL statement in Python?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related