问题描述

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

用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 = userself.password = passwordself.host = hostself.port = portself.database = databaseself.conn = Noneself.cursor = Nonedef 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 selfdef __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 dataelse:breakdef select_all(self, sql):"""查询方法:return:"""self.cursor.execute(sql)data = self.cursor.fetchall()return datadef sql_delete(self, sql):"""删除方法:return:"""row_count = 0  # 删除计数try:self.cursor.execute(sql)self.conn.commit()row_count = self.cursor.rowcountexcept Exception as e:print(str(e))# finally:#     self.close()return row_countdef insert(self, sql, params):"""插入方法:return:"""row_count = 0  # 插入计数try:self.cursor.execute(sql, params)self.conn.commit()row_count = self.cursor.rowcountexcept Exception as e:print(str(e))return row_countdef insertmany(self, sql, params):"""插入方法:return:"""row_count = 0  # 插入计数try:self.cursor.executemany(sql, params)self.conn.commit()row_count = self.cursor.rowcountexcept Exception as e:print(str(e))return row_count

原因分析：

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

解决方案：

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

class PyMssqlDB:def __init__(self, user, password, host, port, database):self.user = userself.password = passwordself.host = hostself.port = portself.database = databaseself.conn = Noneself.cursor = None# 新增代码开始 self.conn1 = Noneself.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.rowcountexcept Exception as e:print(str(e))return row_count