MySql UPDATE query works in mysql, but 1064 from Python

Question

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?

Can you print the value of refactorSequenceNumsQuery after the formatting and share it here please? — Mureinik
– Mureinik, Commented Sep 22, 2015 at 21:47
I think you have an extra doublequote at the beginning of the string. — Barmar
– Barmar, Commented Sep 22, 2015 at 21:50

Mureinik · Accepted Answer · 2015-09-22 21:55:52Z

1

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)

answered Sep 22, 2015 at 21:55

Mureinik

316k54 gold badges399 silver badges405 bronze badges

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

Collectives™ on Stack Overflow

MySql UPDATE query works in mysql, but 1064 from Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related