Solution: pymysql.err.InterfaceError: (0,'')

When debugging the automation code, pymysql keeps reporting: pymysql.err.InterfaceError: (0,'')

Traceback (most recent call last):  File "F:/gitee/first/xzProjectTeamApi/lmsfirst_com/apiTemplate/parameter.py", line 71, in <module>    bb = aa.parameterization(cc)  File "F:/gitee/first/xzProjectTeamApi/lmsfirst_com/apiTemplate/parameter.py", line 62, in parameterization    data = self.common_data(tp.department, data, self.com.department())  File "F:\gitee\first\xzProjectTeamApi\lmsfirst_com\apiDatabase\common_info.py", line 31, in department    get_data = self.get_mysql_data(sql, self.company_id, is_more=True)  File "F:\gitee\first\xzProjectTeamApi\lmsfirst_com\common\handle_mysql.py", line 28, in get_mysql_data    cur.execute(sql, args=args)  File "D:\Python\Python37\lib\site-packages\pymysql\cursors.py", line 170, in execute    result = self._query(query)  File "D:\Python\Python37\lib\site-packages\pymysql\cursors.py", line 328, in _query    conn.query(q)  File "D:\Python\Python37\lib\site-packages\pymysql\connections.py", line 516, in query    self._execute_command(COMMAND.COM_QUERY, sql)  File "D:\Python\Python37\lib\site-packages\pymysql\connections.py", line 750, in _execute_command    raise err.InterfaceError("(0, '')")pymysql.err.InterfaceError: (0, '')

After consulting the information, the conclusion is: the database connection time is too long, and the error caused by the disconnection...

image

Next. Pretend to start....

Specific and reason:

The mysql database has a wait_timeout configuration, the default value is 28800 (that is, 8 hours).

If the service starts 8 hours after the mysql database is accessed again, the mysql database will be denied access.

View timeout time: show variables like'%timeout%';



The solution is as follows:

The first one: increase the ping method:

Just add a piece of code:

self.conn.ping(reconnect=True)

Before the cursor is created, check if there is any disconnection, and then reconnect after disconnection to ensure that no error is reported when executing later.

# 部分代码展示     def get_mysql_data(self, sql, args=None, is_more=False):        """        获取数据库数据        :param sql:         sql语句        :param args:        元组参数        :param is_more:     判断为真为假        :return:            为真拿多条数据,为假拿一条数据        """        self.conn.ping(reconnect=True)        cur = self.conn.cursor()        cur.execute(sql, args=args)        self.conn.commit()        if is_more:            return cur.fetchall()        else:            return cur.fetchone()

Although this is simple, it consumes more performance...

The second: reference thread pool

from dbutils.pooled_db import PooledDB
 # 部分代码展示  import pymysqlfrom dbutils.pooled_db import PooledDBfrom first_com.common.switch_environment import base_db  class HandelMySql:     def __init__(self):        connkwargs = PooledDB(            creator=pymysql,            blocking=True,            ping=1,  # 检查连接。默认1,即当连接被取走,做一次ping操作。0是从不ping,1是默认,2是当该连接创建游标时ping,4是执行sql语句时ping,7是总是ping            **base_db(),            charset='utf8',            cursorclass=pymysql.cursors.DictCursor        )         self.conn = connkwargs.connection()     def get_mysql_data(self, sql, args=None, is_more=False):        """        获取数据库数据        :param sql:         sql语句        :param args:        元组参数        :param is_more:     判断为真为假        :return:            为真拿多条数据,为假拿一条数据        """        cur = self.conn.cursor()        cur.execute(sql, args=args)        self.conn.commit()        if is_more:            return cur.fetchall()        else:            return cur.fetchone()
 __init__(self)方法中就是设置了线程池的操作...

Compare authoritative chestnut view connection

The following pictures are some descriptions of the pooled database on the official website and running effect diagrams, which are available for reference


At this point, the problem is solved, continue to code...

image

The above summary may help you, or may not help you, but I still hope to help you. If you have any questions or ambiguities, the comments in the comment area will be revised and released in time, thank you!

To be continued...

Have been working hard, hope you are too

WeChat search public account: just use python