0

I am trying to update a MySQL table with dynamic column name and value. I m always getting a syntax Error when I m doing the update. I have similar INSERT and SELECT statements which work as expected. I have already tried different variants of the query to get the right syntax

  cursor.execute("""UPDATE movies SET %s="%s" WHERE Rank_id = %s""",(column.replace("'",""),str(movie[key]).replace("'",""),rank))
  or
  query = """UPDATE movies SET %s="%s" WHERE Rank_id = %s"""
  values = (column,movie[key],rank)
  cursor.execute(query,(column,str(movie[key]).replace("'",""),rank))

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'="'fsdsd'" WHERE Rank_id = '1'' at line 1

I tried to execute the SQL command on DBeaver and this works

update movies set Title="djhdfghgfjhjfg" where Rank_id=1

What am I doing wrong here?

Code that works

query = "INSERT INTO movies(Title,Genre,Description,Director,Actors,Release_Year,Runtime_Minutes,Rating,Votes,Revenue_Millions,Metascore) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
values =(title, genre, description, director,actors, release_year,runtime_minut, rating,votes,revenue_millions,metascore)
cursor.execute(query,values)

query = "SELECT Title,Genre,Description FROM movies WHERE Rank_id = %s";
cursor.execute(query,(rank,))

Update

I am able to get it working when I hardcode the column name in the Query (Though not the desired solution)

Karl Knechtel
  • 62,466
  • 11
  • 102
  • 153
Vini
  • 1,978
  • 8
  • 40
  • 82
  • 1
    I marked it as a duplicate of another answer that explains that you can't use query parameters for SQL identifiers. That's what you did wrong. Query parameters are always treated as if you had used string literals in your query (hence the single-quotes). All identifiers must be fixed in the string before you pass it to `execute()`. – Bill Karwin Feb 01 '23 at 19:28
  • 1
    Please do not edit questions to contain an answer - especially not here, since the question is clearly a duplicate *and the solution you came up with is clearly described by the answers of the duplicate*. This is a question-and-answer website, not a (question plus answer)-and-nothing website. – Karl Knechtel Feb 04 '23 at 19:23

0 Answers0