CODEWITHSUNDEEP
pythonmysqlsqlsyntax-errormysql-error-1064
Jordan Bryan.
Jordan Bryan.

Reputation: 11

MySql UPDATE query works in mysql, but 1064 from Python

I am trying to execute a query that increments the integer values in a column of a MySQL table. The query I wrote works fine when I try it from the command line in MySQL, but I get a syntax error when I try to execute it with Python. Here is the Python code:

refactorSequenceNumsQuery = """"
    UPDATE Contain
    SET sequencenum = sequencenum - 1
    WHERE sequencenum > {}
    AND albumid = {}
    """.format(deletePhotoSequencenum, albumid)
    execute_query_no_result(refactorSequenceNumsQuery)

And here is the execute_query_no_result() function, which works fine with other UPDATE queries I execute.

def execute_query_no_result(queryString):
  connection = app.mysql.connect()
  cursor = connection.cursor()
  cursor.execute(queryString)
  connection.commit()
  cursor.close()
  connection.close()

And finally, this is the 1064 error I get when I run the code:

ProgrammingError: (1064, '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 '"\n UPDATE Contain\n SET sequencenum = sequencenum - 1\n WHERE sequencenum' at line 1')"

This is quite perplexing because, as I said, the query works fine when I run it directly in the database. Any ideas why I'm getting a syntax error?

Upvotes: 1

Views: 269

Answers (1)

Mureinik
Mureinik

Reputation: 311326

Printing out the string reveals you have a redundant " at the beginning of refactorSequenceNumsQuery:

>>> deletePhotoSequencenum = 10
>>> albumid = 15
>>> refactorSequenceNumsQuery = """"
...     UPDATE Contain
...     SET sequencenum = sequencenum - 1
...     WHERE sequencenum > {}
...     AND albumid = {}
...     """.format(deletePhotoSequencenum, albumid)
>>> 
>>> print refactorSequenceNumsQuery
"
    UPDATE Contain
    SET sequencenum = sequencenum - 1
    WHERE sequencenum > 10
    AND albumid = 15

Just remove it, and you should be fine:

# Note: three "s, not four! 
refactorSequenceNumsQuery = """
    UPDATE Contain
    SET sequencenum = sequencenum - 1
    WHERE sequencenum > {}
    AND albumid = {}
    """.format(deletePhotoSequencenum, albumid)
    execute_query_no_result(refactorSequenceNumsQuery)

Upvotes: 1

Related Questions