Unfortunately I didn't find an answer to my question, so I try it here. I think it is pretty easy, but I just don't get it right. Since it is my first question here I hope I did it right.
I'm using Python 3.5 with MySql. I use the MySql connector module and have mysql-server installed and active. I want to generate a query dynamically with variables from user input. It's a simple program where I want to let the user edit table entries. It works with all cases like:
SET title = %s
or WHERE title = %s
But when I want to make the column name title a variable too, it gives an error (Here ('title') ad ('owner') are both from user input). The error is sent when executing the second c.execute() Block on the bottom of the code Block:
> 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 '('title') = ('owner') WHERE id = ('4')' at line 1
the "title" is the column name of the table. So I want the user to select which column he wants to change, not only the values. I just don't get the formatting of the first variable right. See the Code snippet below, it hopefully makes it clearer (the last 3 lines are the main focus here:
id_edit = input("Which Application do you want to change? (give Id): ")
id_edit = str(id_edit)
# field_edit is the word "Company" or "Title" for selecting the right column
old_field = input('Which field of Application "%s" do you want to change?: ' % (id_edit))
old_field = old_field.lower()
# new_field: Here the user gives the Input How he wants to name the Field instead
new_field = input('What should be the new value for the id "%s"?: ' % (old_field))
new_field = new_field.lower()
if old_field in table_names():
# this works fine:
c.execute("UPDATE applications SET title = (%s) WHERE id = (%s)", (new_field, id_edit))
# this is how I want it to work:
c.execute("UPDATE applications SET (%s) = (%s) WHERE id = (%s)", (old_field, new_field, id_edit))
conn.commit()
Thank you in advance!
