Search code examples
pythonmysqlstored-proceduresbackticksmysql-connector-python

Calling MySQL stored procedures with backticked names using Connector/Python

I'm having trouble using MySQL Connector/Python to call stored procedures with names surrounded by backticks. I get the following syntax error when doing so with the cursor.callproc() method:

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 '`insert_page`_args1=NULL' at line 1

Here is my stored procedure call, causing the error:

args = (None, 'foo', 'bar')
cursor.callproc('`sp_insert_page`', args)

I have seen various examples demonstrating the use of the cursor with extended string formatting in order to escape strings that would evaluate to incorrect MySQL syntax, but that doesn't seem to be applicable to the callproc() method; regardless of formatting approach, I get the above syntax error.

Calling stored procedures whose names include backticks from the MySQL Command Line Client works fine, so the problem must lie somewhere within the python application.

I hope someone is able to shed some light on the problem.

Solution

  • Johan de Meersman on the MySQL mailing list pointed out that the backtick is a MySQL delimiter and not actually part of the name, so they should not be included in the call.

    I could have sworn I had tried calling the procedure without backticks, but it turns out that was the error.