python - Django mysql 准备语句

Question

你好，我想在 Django 中使用 mysql 预处理语句。

我做了以下类(class):

class PreparedStatement(object):

    def __init__(self,name,query):
        self.name = name
        self.query = query
        self.vars = []
        self.prepare()

    def setVar(self,name,var):
        name = "@%s" % name
        if name not in self.vars:
            self.vars.append(name)
        SQL = "SET %s = " % (name)
        self.__executeQuery(SQL+" %s;",var)

    def prepare(self):
        SQL = "PREPARE %s FROM " % self.name
        self.__executeQuery(SQL + " %s ;", self.query)

    def execute(self):
        SQL = "EXECUTE %s " % self.name

        if len(self.vars):
            params = ""
            for var in self.vars:
            params += var + ", "
            params = params[:-2]
            SQL += "USING %s " % params
        result =  self.__executeQuery(SQL)
        self.vars = []
        return result

    def __executeQuery(self,query,*args):
        cursor = connection.cursor()
        if args:
            cursor.execute(query,args)
        else:
            cursor.execute(query)
        return cursor

我这样使用它:

getDiscountsById = PreparedStatement("getDiscountsById","""SELECT * FROM table 
WHERE id = ? LIMIT 1""")
getDiscountsById.setVar("id",5)
result = getDiscountsById.execute()

对于第一次加载的页面，它工作正常，但在更改 url 或重新加载页面后，MySQL 返回准备好的语句未找到错误。

问题出在哪里？有什么解决办法吗？

感谢您的回复，抱歉我的英语不好 :D

Answer 1

我怀疑您正在 View 之外构建 PreparedStatement 对象并将其作为全局访问。

需要注意的是django没有连接池的概念，每一次页面浏览都使用一个全新的数据库连接。 MySQL 准备好的语句仅在定义它们的连接/ session 的生命周期内存在。因此，第一个 pagview 加载 PreparedStatement 所在的模块，并将其放入数据库中，但第二个连接尝试执行在前一个连接中准备的语句，这显然失败了。

要解决此问题，请在您需要的 View 中创建 PreparedStatement，或使用类似这样的方法(以 Python 方式进行操作，而不是模仿 PHP):

class PreparedStatement(object):

    def __init__(self, name, query, vars):
        self.name = name
        self.query = query
        self.vars = vars

    def prepare(self):
        SQL = "PREPARE %s FROM " % self.name
        self.__executeQuery(SQL + " %s ;", self.query)

    def get_prepared(self):
        # store a map of all prepared queries on the current connection
        return getattr(connection, "__prepared", default={})

    def execute(self, **kwvars):

        if not self.name in self.get_prepared().keys()
           # Statement will be prepared once per session.
           self.prepare()

        SQL = "EXECUTE %s " % self.name

        if self.vars:
            missing_vars = set(self.vars) - set(kwvars)
            if missing_vars:
                raise TypeError("Prepared Statement %s requires variables: %s" % (
                                    self.name, ", ".join(missing_variables) ) )

            param_list = [ var + "=%s" for var in self.vars ]
            param_vals = [ kwvars[var] for var in self.vars ]

            SQL += "USING " + ", ".join( param_list )

            return self.__executeQuery(SQL, *param_vals)
        else:
            return self.__executeQuery(SQL)

    def __executeQuery(self,query, *args):
        cursor = connection.cursor()
        if args:
            cursor.execute(query,args)
        else:
            cursor.execute(query)
        return cursor

然后像这样使用它

# Global
getDiscountsById = PreparedStatement(
     "getDiscountsById",
     "SELECT * FROM table WHERE id = ? LIMIT 1",
     vars=["id"] # List out the names of the placeholders. This will assist in error checking.
)

#local to a view
result = getDiscountsById.execute(id=5)

免责声明:我没有对此进行测试，但它应该可以运行或者很容易调整运行。