mysql报错pymysql.err.InterfacError插入问题解决

一、pymysql.err.InterfaceError: (0, '')错误导致原因

     目前我这边导致InterfaceError: (0, '')的原因是 没有连接上数据库就在进行数据插入操作；在此之前会报ERROR (2013, 'Lost connection to MySQL server during query')错误，即mysql连接丢失。

二、解决方法

2.1、python连接语句

conn= pymysql.connect(host='10.24.21.192', port=9009, user='root', passwd='123456', db='test')

 2.2、pymysql自带 ping函数，可以检测连接是否还正，ping函数源码(来自python包)：

    def ping(self, reconnect=True):
        """
        Check if the server is alive.

        :param reconnect: If the connection is closed, reconnect.
        :raise Error: If the connection is closed and reconnect=False.
        """
        if self._sock is None:
            if reconnect:
                self.connect()
                reconnect = False
            else:
                raise err.Error("Already closed")
        try:
            self._execute_command(COMMAND.COM_PING, "")
            self._read_ok_packet()
        except Exception:
            if reconnect:
                self.connect()
                self.ping(False)
            else:
                raise

2.3、问题解决

方法1：

import pymysql

conn= pymysql.connect(host='10.24.21.192', port=9009, user='root', passwd='123456', db='test')

def test_conn():
    sql_i = "INSERT INTO test_1(request_date, id, msg, label) VALUES (%s,%s,%s,%s)"
    vals = ('20200427', '566453015','您好，这有啥', 'label_ask')
    #conn.close()
    while True:
        try:
            with conn.cursor() as cursor:  # 获取游标
                cursor.execute(sql_i, vals)
            conn.commit()
            break
        except pymysql.err.InterfaceError as e:
            print(e, type(e))
            conn.ping(True）

方法2：

import pymysql

conn= pymysql.connect(host='10.24.21.192', port=9009, user='root', passwd='123456', db='test')

def test_conn():
    '''测试 '''
    sql_i = "INSERT INTO test_1(request_date, id, msg, label) VALUES (%s,%s,%s,%s)"
    vals = ('20200427', '566453015','您好，这有啥', 'label_ask')
    conn.ping(True）
    with conn.cursor() as cursor:  # 获取游标
        cursor.execute(sql_i, vals)
    conn.commit()

 

参考网址：

https://www.cnblogs.com/wintest/p/12772670.html

https://www.cnblogs.com/aaronthon/p/10331415.html

https://blog.csdn.net/u013948858/article/details/88343426

http://www.itgo.me/a/7316792386468297992/python-pymysql-err-operationalerror-2013-lost-connection-to-mysql-server-dur