Use parameterized query with mysql.connector in Python 2.7
<p>Im using Python 2.7 with <a href="https://dev.mysql.com/doc/connector-python/en/connector-python-example-cursor-select.html" rel="nofollow"><code>mysql.connector</code></a> running in pyCharm</p>

<p>I need to use a parameterized query like shown <a href="https://dev.mysql.com/doc/connector-python/en/connector-python-example-cursor-select.html" rel="nofollow">here</a> and <a href="http://stackoverflow.com/a/775399/1376624">also here</a> </p>

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

<p>However, when I write this line in pyCharm, I get this error:</p>

<p><a href="https://i.stack.imgur.com/RUAF7.png" rel="nofollow"><img src="https://i.stack.imgur.com/RUAF7.png" alt="enter code here"></a></p>

<p>The inspection says:</p>

<p><a href="https://i.stack.imgur.com/kLgwL.png" rel="nofollow"><img src="https://i.stack.imgur.com/kLgwL.png" alt="enter image description here"></a></p>

<p>If I run the code, I get this error:</p>

<blockquote>
  <p>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</p>
</blockquote>

<p>Here is the full code:</p>

<pre><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')
</code></pre>

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

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

<p>The error you get is from mysql when it tries to execute the query. The query parameters passed to <code>cursor.execute()</code> 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:</p>

<pre><code>cursor.execute("SELECT * FROM automatedReports WHERE pythonFunctionName = %s", (function_name,))
</code></pre>

<p>Otherwise <code>mysql.connector</code> doesn't escape anything and leaves the literal <code>%s</code> in the query.</p>

python mysql mysql-connector mysql-connector-python