Django mysql准备语句

Question

你好，我尝试在django中使用mysql准备的语句。在

我上了以下课程：

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

我是这样用的：

^{pr2}$

对于第一个加载的页面，它可以正常工作，但在更改url或重新加载页面之后，MySQL返回prepared statement not found error。在

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

谢谢你的回复，对不起我的英语：D

Answer 1

多亏了托马斯，我才让它变成这样：

class PreparedStatement(object):

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

    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.get_prepared().append(self.name)
        self.__executeQuery(SQL + " %s ;", self.query)

    def get_prepared(self):
        try:
            getattr(connection, "__prepared")
        except AttributeError:
            setattr(connection,"__prepared",[])
        finally:
            return getattr(connection, "__prepared")

    def f_execute(self):
        return self.execute().fetchall()

    def execute(self):
        if not self.name in self.get_prepared():
            self.prepare()
        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

使用保持不变：

^{pr2}$

Answer 2

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

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

要解决这个问题，请在需要它的视图中创建PreparedStatement，或者使用类似这样的方法（更像pythonical，而不是模仿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

像这样使用它

^{pr2}$

免责声明：我没有测试这个，但它应该运行或是足够容易调整运行。在