在Python 2.7中将参数化查询与mysql.connector一起使用 [英] Use parameterized query with mysql.connector in Python 2.7

问题描述

我正在将Python 2.7与 mysql.connector 在pyCharm中运行

Im using Python 2.7 with mysql.connector running in pyCharm

我需要使用如下所示的参数化查询:也此处

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

给出这些示例，下面的cursor.execute("SELECT * FROM automatedReports WHERE pythonFunctionName = %s", (function_name))似乎应该起作用.

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

但是，当我在pyCharm中编写此行时，出现此错误:

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

检查说:

如果我运行代码，则会出现此错误:

If I run the code, I get this error:

您的SQL语法有错误；查看与您的MySQL服务器版本相对应的手册以获取正确的语法，以在第1行的'％s'附近使用

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

这是完整的代码:

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.

推荐答案

当尝试执行查询时，您从mysql中获得错误.传递给cursor.execute()的查询参数必须是一个元组，您传递的是一个值.要创建一个具有单个元素的元组，您需要在该元素后添加一个逗号:

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,))

否则，mysql.connector不会逸出任何内容，而将字面%s保留在查询中.

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

这篇关于在Python 2.7中将参数化查询与mysql.connector一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！