pymssql._pymssql.OperationalError: Statement not executed or executed statement has no resultset报错解决

问题描述

提示：这里描述项目中遇到的问题：

用pymssql的fetchone接口从 sql server数据库中获取然后插入到另外的表中，会产生如下报错
pymssql._pymssql.OperationalError: Statement not executed or executed statement has no resultset

class PyMssqlDB:
    def __init__(self, user, password, host, port, database):
        self.user = user
        self.password = password
        self.host = host
        self.port = port
        self.database = database
        self.conn = None
        self.cursor = None

    def connection(self):
        """
        sql server 数据库连接
        :return:
        """
        self.conn = pymssql.connect(user=self.user,
                                    password=self.password,
                                    host=self.host,
                                    port=self.port,
                                    database=self.database,
                                    )
        self.cursor = self.conn.cursor()

    def close(self):
        """
        关闭连接
        :return:
        """
        self.cursor.close()
        self.conn.close()

    def __enter__(self):
        """
        上下文管理魔术方法
        :return:
        """
        self.connection()
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        """
        上下文管理魔术方法
        :param exc_type:
        :param exc_val:
        :param exc_tb:
        :return:
        """
        self.close()

    def select_one(self, sql):
        """
        查询方法
        生成器模式，防止内存撑爆
        :return:
        """
        self.cursor.execute(sql)
        # data = self.cursor.fetchall()
        while True:
            data = self.cursor.fetchone()
            if data:
                yield data
            else:
                break

    def select_all(self, sql):
        """
        查询方法
        :return:
        """
        self.cursor.execute(sql)
        data = self.cursor.fetchall()
        return data

    def sql_delete(self, sql):
        """
        删除方法
        :return:
        """
        row_count = 0  # 删除计数
        try:
            self.cursor.execute(sql)
            self.conn.commit()
            row_count = self.cursor.rowcount
        except Exception as e:
            print(str(e))
        # finally:
        #     self.close()
        return row_count

    def insert(self, sql, params):
        """
        插入方法
        :return:
        """
        row_count = 0  # 插入计数
        try:
            self.cursor.execute(sql, params)
            self.conn.commit()
            row_count = self.cursor.rowcount
        except Exception as e:
            print(str(e))

        return row_count

    def insertmany(self, sql, params):
        """
        插入方法
        :return:
        """
        row_count = 0  # 插入计数
        try:
            self.cursor.executemany(sql, params)
            self.conn.commit()
            row_count = self.cursor.rowcount
        except Exception as e:
            print(str(e))
        return row_count

---

原因分析：

怀疑是游标的问题
可能是游标先查询，再插入时，该游标失效了，导致没有取到下一条结果数据

---

解决方案：

初始化时，创建两个连接和游标，一个游标用来取数据，另一个游标用来插入数据，
尝试后，发现可以成功运行了！

class PyMssqlDB:
    def __init__(self, user, password, host, port, database):
        self.user = user
        self.password = password
        self.host = host
        self.port = port
        self.database = database
        self.conn = None
        self.cursor = None
		
		# 新增代码开始 
        self.conn1 = None
        self.cursor1 = None
        # 新增代码结束

    def connection(self):
        """
        sql server 数据库连接
        :return:
        """
        self.conn = pymssql.connect(user=self.user,
                                    password=self.password,
                                    host=self.host,
                                    port=self.port,
                                    database=self.database,
                                    )
        self.cursor = self.conn.cursor()

		# 新增代码开始
        # 再创建一个连接和游标用于fetchone获取数据时的插入，不然会报错,可能游标问题引起的
        self.conn1 = pymssql.connect(user=self.user,
                                     password=self.password,
                                     host=self.host,
                                     port=self.port,
                                     database=self.database,
                                     )
        self.cursor1 = self.conn1.cursor()
        # 新增代码结束

    def close(self):
        """
        关闭连接
        :return:
        """
        self.cursor.close()
        self.conn.close()
		
		# 新增代码开始
        self.cursor1.close()
        self.conn1.close()
        # 新增代码结束

    def insert(self, sql, params):
        """
        插入方法
        :return:
        """
        row_count = 0  # 插入计数
        try:
        	# 用游标1来向表中插入数据
            self.cursor1.execute(sql, params)
            self.conn1.commit()
            row_count = self.cursor1.rowcount
        except Exception as e:
            print(str(e))

        return row_count