DelightedD0D DelightedD0D - 1 month ago 8
MySQL Question

Use parameterized query with mysql.connector in Python 2.7

Im using Python 2.7 with

running in pyCharm

I need to use a parameterized query like shown here and also here

Given those examples, it seems like
cursor.execute("SELECT * FROM automatedReports WHERE pythonFunctionName = %s", (function_name))
in the below should work.

However, when I write this line in pyCharm, I get this error:

enter code here

The inspection says:

enter image description here

If I run the code, I get this error:


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' at line 1


Here is the full code:

class DatabaseManager():
def get_report_settings_for_function_named(self, function_name):
"""
Get the settings for a given report from the database based on the name of the function that was called
to process the report

This is how we get the, email subject and email addresses to send the report to

:param function_name: The name of the function that was called to process the report
:type function_name: str.
:returns: array -- the settings row from the database.

"""
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor(dictionary=True)

cursor.execute("SELECT * FROM automatedReports WHERE pythonFunctionName = %s", (function_name))

row = cursor.fetchone()
cursor.close()
cnx.close()

print cursor.statement

return row

print DatabaseManager().get_report_settings_for_function_named('process_elation_credit_report')


What am I doing wrong here? Is this an old syntax? I wouldnt think so...



Note, if I type the value into the query string it all works fine, it's just wont let me use the parameter.

Answer

The error you get is from mysql when it tries to execute the query. The query parameters passed to cursor.execute() need to be a tuple, you're passing a single value. To create a tuple with a single element you need to add a comma after the element:

cursor.execute("SELECT * FROM automatedReports WHERE pythonFunctionName = %s", (function_name,))

Otherwise mysql.connector doesn't escape anything and leaves the literal %s in the query.