Python – PyMySQL error：class ‘pymysql.err.InterfaceError’
今天在通过Python的PyMySQL向数据库写入的时候，遇到了问题，如题所示。
具体错误如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
D:\PyCharm_data\Python_Excel_MySQL\venv\Scripts\python.exe D:/PyCharm_data/Python_Excel_MySQL/Situation_1.py $$$$$$$$$$$$$$$$$$$$$$$$$ msg [notify] --> 【当前SHEET - 行：196】 %%%%%%%%%%%%%%%%%%%%%%%%%%%%% $$$$$$$$$$$$$$$$$$$$$$$$$ msg [notify] --> 【查询公司数据：失败】 $$$$$$$$$$$$$$$$$$$$$$$$$ msg [notify] --> 【插入公司数据：执行中】 $$$$$$$$$$$$$$$$$$$$$$$$$ msg [notify] --> 【插入公司 - SQL：insert into cmdb.company(name) values('Gucci_Amada14')】 !!!!!!!!!!!!!!!!!!!!!!!!! msg [error] --> 【无法执行SQL】 <class 'pymysql.err.InterfaceError'> : (0, '') Traceback (most recent call last): File "D:\PyCharm_data\Python_Excel_MySQL\MySQL_Driver.py", line 143, in MySQL_execute_noSelect self.cursor.execute(string_command) File "D:\PyCharm_data\Python_Excel_MySQL\venv\lib\site-packages\pymysql\cursors.py", line 170, in execute result = self._query(query) File "D:\PyCharm_data\Python_Excel_MySQL\venv\lib\site-packages\pymysql\cursors.py", line 328, in _query conn.query(q) File "D:\PyCharm_data\Python_Excel_MySQL\venv\lib\site-packages\pymysql\connections.py", line 516, in query self._execute_command(COMMAND.COM_QUERY, sql) File "D:\PyCharm_data\Python_Excel_MySQL\venv\lib\site-packages\pymysql\connections.py", line 750, in _execute_command raise err.InterfaceError("(0, '')") pymysql.err.InterfaceError: (0, '') During handling of the above exception, another exception occurred: Traceback (most recent call last): File "D:\PyCharm_data\Python_Excel_MySQL\MySQL_Driver.py", line 161, in MySQL_execute_noSelect self.db.rollback() File "D:\PyCharm_data\Python_Excel_MySQL\venv\lib\site-packages\pymysql\connections.py", line 429, in rollback self._execute_command(COMMAND.COM_QUERY, "ROLLBACK") File "D:\PyCharm_data\Python_Excel_MySQL\venv\lib\site-packages\pymysql\connections.py", line 750, in _execute_command raise err.InterfaceError("(0, '')") pymysql.err.InterfaceError: (0, '') During handling of the above exception, another exception occurred: Traceback (most recent call last): File "D:/PyCharm_data/Python_Excel_MySQL/Situation_1.py", line 494, in <module> Excel_analyze_Single_Sheet(excel_object=obj_excel,sheet_name="BGP",db_object=db_mysql) File "D:/PyCharm_data/Python_Excel_MySQL/Situation_1.py", line 218, in Excel_analyze_Single_Sheet print(if_no_company_then_insert_return_companyId(db_object,"Gucci_Amada14")) File "D:/PyCharm_data/Python_Excel_MySQL/Situation_1.py", line 128, in if_no_company_then_insert_return_companyId insert_company(object_db,companyName) File "D:/PyCharm_data/Python_Excel_MySQL/Situation_1.py", line 103, in insert_company result = object_db.MySQL_execute_noSelect(string_sql) File "D:\PyCharm_data\Python_Excel_MySQL\MySQL_Driver.py", line 166, in MySQL_execute_noSelect self.MySQL_close() File "D:\PyCharm_data\Python_Excel_MySQL\MySQL_Driver.py", line 74, in MySQL_close self.db.close() File "D:\PyCharm_data\Python_Excel_MySQL\venv\lib\site-packages\pymysql\connections.py", line 354, in close raise err.Error("Already closed") pymysql.err.Error: Already closed Process finished with exit code 1 |
对于这个问题，PyMySQL提供了一个接口解决：Connection.ping()
该方法的源码（官方）如下
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
def ping(self, reconnect=True): """Check if the server is alive""" if self._sock is None: if reconnect: self.connect() reconnect = False else: raise err.Error("Already closed") try: self._execute_command(COMMAND.COM_PING, "") return self._read_ok_packet() except Exception: if reconnect: self.connect() return self.ping(False) else: raise |
我的数据库执行方法：
之前：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 |
# 查询： # 查询模式： # ALL, 所有记录 # ONE, 只有一条记录 # MANY,NUM, 返回多行记录，由调用时指定条目 def MySQL_select_return_SET(self,string_sql="",running_mode="",lines=0): # variable result_set = "" # do try: # 执行SQL命令：查询 self.cursor.execute(string_sql) if running_mode == "all": result_set = self.cursor.fetchall() elif running_mode == "one": result_set = self.cursor.fetchone() elif running_mode == "many": result_set = self.cursor.fetchmany(lines) else: CommonSolution.message_out(type="error",message_string="指定结果集的获取模式[running_mode]") except: # variable error_info = sys.exc_info() # display CommonSolution.message_out(type="error", message_string="无法执行SQL") print(error_info[0]," : ",error_info[1]) finally: # 不管成功还是失败，总要关闭数据库会话连接 self.MySQL_close() # return return result_set # 执行SQL：增删改 def MySQL_execute_noSelect(self,string_command): # variable isSuccess = False # do try: # 执行SQL self.cursor.execute(string_command) # 数据库：手动提交 self.db.commit() # 修改状态标识 isSuccess = True except: # variable error_info = sys.exc_info() # display CommonSolution.message_out(type="error", message_string="无法执行SQL") print(error_info[0], " : ", error_info[1]) # 数据库：回滚操作 self.db.rollback() finally: # 不管成功还是失败，总要关闭数据库会话连接 self.MySQL_close() # return return isSuccess |
之后：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 |
# 执行SQL：增删改 def MySQL_execute_noSelect(self,string_command): # variable isSuccess = False # do try: # 执行前检查 self.db.ping(reconnect=True) # 执行SQL self.cursor.execute(string_command) # 数据库：手动提交 self.db.commit() # 修改状态标识 isSuccess = True except: # variable error_info = sys.exc_info() # display CommonSolution.message_out(type="error", message_string="无法执行SQL") print(error_info[0], " : ", error_info[1]) # 数据库：回滚操作 self.db.rollback() finally: # 不管成功还是失败，总要关闭数据库会话连接 self.MySQL_close() # return return isSuccess # 查询： # 查询模式： # ALL, 所有记录 # ONE, 只有一条记录 # MANY,NUM, 返回多行记录，由调用时指定条目 def MySQL_select_return_SET(self,string_sql="",running_mode="",lines=0): # variable result_set = "" # do try: # 执行前检查 self.db.ping(reconnect=True) # 执行SQL命令：查询 self.cursor.execute(string_sql) if running_mode == "all": result_set = self.cursor.fetchall() elif running_mode == "one": result_set = self.cursor.fetchone() elif running_mode == "many": result_set = self.cursor.fetchmany(lines) else: CommonSolution.message_out(type="error",message_string="指定结果集的获取模式[running_mode]") except: # variable error_info = sys.exc_info() # display CommonSolution.message_out(type="error", message_string="无法执行SQL") print(error_info[0]," : ",error_info[1]) finally: # 不管成功还是失败，总要关闭数据库会话连接 self.MySQL_close() # return return result_set |
如上，在每次执行SQL前，都做了Ping检查：
|
1 2 |
# 执行前检查 self.db.ping(reconnect=True) |
这样就好了。
————————————————————
Done。
