How can I use Python Variables with the UPDATE command in MySQL?

user_15060310 0
Viewed 59

I have been trying to make this part of my A-Level project where I have a database in MyPHPAdmin and my code grabs a value, compares it to a preset value and if it is unchanged, uses that location in the database to store a new value. (I cant just insert new data due to other parts of my code). I have been trying to use a variable with a value stored in it from an input box, however it refuses to take the value. I have tried lots of things and I cant seem to get it to work and I seem to get a different error everytime I try something different. I fear it may just be something simple I am missing, but for the life of me I cant figure it out and it is driving me insane. (Also yes I have looked at the documentation but it is not of much use for my situation)

My Code:

mycursor = database.cursor(buffered=True)
mycursor.execute("SELECT name FROM creationPokemon")
myresult = mycursor.fetchone()[0]
print(myresult)

if myresult == "Placeholder1":
    sql = ("UPDATE creationPokemon SET name = %s WHERE name = %s")
    val = ('Placeholder1', cName)
else:
    myresult = mycursor.fetchone()[0]
    print(myresult)

mycursor.execute(sql)
database.commit()

The error I have landed on this time is:

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 '%s WHERE name = %s' at line 1

Any help is much appreciated.

mysql-connector mysql python
1 Answers

python and mysql connector need at least 2 dimemsns when you use tuples and you need to fill both placeholders

mycursor = database.cursor(buffered=True)
mycursor.execute("SELECT name FROM creationPokemon")
myresult = mycursor.fetchone()[0]
print(myresult)

if myresult == "Placeholder1":
    sql = "UPDATE creationPokemon SET name = %s WHERE name = %s"
    val = (cName,cName)

    mycursor.execute(sql,val)
    database.commit()
else:
    myresult = mycursor.fetchone()[0]
    print(myresult)
user_51935360
Related