博客

一.try…except…无法捕获此类异常

背景：多线程执行以下mydb_insert函数，错误发生在


res=cursor.execute(sql, [my_id, url, html_zh, html_en, crawl_time,flag])


一行，但是异常无法捕获，只能通过如下方式捕获异常：（不知道原因）

try:
    mydb_insert()
except:
    ...

mydb_insert函数如下：

def mydb_insert(self,url,html_zh,html_en,flag):
    self.conn.ping(reconnect=True)
    cursor = self.conn.cursor()
    sql='insert into url_table (my_id,url,html_zh,html_en,time,is_get_success) values (%s,%s,%s,%s,%s,%s);'
    crawl_time=datetime.datetime.now()
    crawl_time=str(crawl_time).strip().split(' ')[0]
    my_id=url.split('/')[4]
    try:
       res=cursor.execute(sql, [my_id, url, html_zh, html_en, crawl_time,flag])
       self.conn.commit()
    except:
       self.conn.rollback()
    cursor.close()

二.报错汇总

1. pymysql.err.OperationalError: (1153, “Got a packet bigger than ‘max_allowed_packet’ bytes”)

Traceback (most recent call last):
  File "/home/qinbo/google/code/Mydb.py", line 24, in mydb_insert
    res=cursor.execute(sql, [my_id, url, html_zh, html_en, crawl_time,flag])
  File "/home/qinbo/.local/lib/python3.6/site-packages/pymysql/cursors.py", line 163, in execute
    result = self._query(query)
  File "/home/qinbo/.local/lib/python3.6/site-packages/pymysql/cursors.py", line 321, in _query
    conn.query(q)
  File "/home/qinbo/.local/lib/python3.6/site-packages/pymysql/connections.py", line 505, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/home/qinbo/.local/lib/python3.6/site-packages/pymysql/connections.py", line 724, in _read_query_result
    result.read()
  File "/home/qinbo/.local/lib/python3.6/site-packages/pymysql/connections.py", line 1069, in read
    first_packet = self.connection._read_packet()
  File "/home/qinbo/.local/lib/python3.6/site-packages/pymysql/connections.py", line 676, in _read_packet
    packet.raise_for_error()
  File "/home/qinbo/.local/lib/python3.6/site-packages/pymysql/protocol.py", line 223, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/home/qinbo/.local/lib/python3.6/site-packages/pymysql/err.py", line 107, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.OperationalError: (1153, "Got a packet bigger than 'max_allowed_packet' bytes")

参考博客：

https://www.freesion.com/article/9288880619/

原因

：插入的数据超过了MySQL默认的max_allowed_packet(默认值是4M)，无法将数据插入到MySQL中。

解决

：修改MySQL的max_allowed_packet值。

方法

：

（1）第一步：命令行修改

mysql> show VARIABLES like '%max_allowed_packet%';

mysql> set global max_allowed_packet = 1024*1024*128;#128M

（2）第二步：修改Mysql配置文件（linux一般是./mysql/my.cnf）。

[mysqld]
max_allowed_packet = 128M

重启Mysql服务后max_allowed_packet将会变化。

（3）可以提前判别插入的数据大小。（转换为字节，与128M比较大小）

test_str = '[{"key":"value", "": ""},{"key":"value", "": ""},{"key":"value", "": ""}]'
 
test_byte = bytes(test_str.encode('utf-8'))
print(len(test_byte))
print(len(test_byte) > 1024*1024*4)

2.pymysql.err.InterfaceError: (0, ”)

Traceback (most recent call last):
  File "/home/whb/tool/anaconda3/lib/python3.6/threading.py", line 916, in _bootstrap_inner
    self.run()
  File "crawl.py", line 19, in run
    crawl(self.tname,self.q)
  File "crawl.py", line 58, in crawl
    db.mydb_insert(url,html_zh,html_en,1)
  File "/home/qinbo/google/code/Mydb.py", line 27, in mydb_insert
    self.conn.rollback()
  File "/home/qinbo/.local/lib/python3.6/site-packages/pymysql/connections.py", line 431, in rollback
    self._execute_command(COMMAND.COM_QUERY, "ROLLBACK")
  File "/home/qinbo/.local/lib/python3.6/site-packages/pymysql/connections.py", line 742, in _execute_command
    raise err.InterfaceError(0, '')
pymysql.err.InterfaceError: (0, '')

参考博客：

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

原因

：pymysql和数据库连接断开，具体原因有很多，比如连接时间太久服务断开（Mysql的机制），比如发生异常导致 “

MySQL server has gone away”，

连接也会断开然后报该错误。

解决

：每次进行sql操作前，检查连接情况，若断开则重连。（

只能解决没有异常情况下报该错误的情况

）

方法

：在执行sql语句前增加一行检查并重连操作。

# 检查连接是否断开，如果断开就进行重连
self.conn.ping(reconnect=True)
# 使用 execute() 执行sql语句
cursor=self.conn.cursor()
cursor.execute(sql)

3.pymysql.err.OperationalError: (2006, “MySQL server has gone away (BrokenPipeError(32, ‘Broken pipe’))”)

Traceback (most recent call last):
  File "/home/qinbo/google/code/Mydb.py", line 24, in mydb_insert
    res=cursor.execute(sql, [my_id, url, html_zh, html_en, crawl_time,flag])
  File "/home/qinbo/.local/lib/python3.6/site-packages/pymysql/cursors.py", line 163, in execute
    result = self._query(query)
  File "/home/qinbo/.local/lib/python3.6/site-packages/pymysql/cursors.py", line 321, in _query
    conn.query(q)
  File "/home/qinbo/.local/lib/python3.6/site-packages/pymysql/connections.py", line 504, in query
    self._execute_command(COMMAND.COM_QUERY, sql)
  File "/home/qinbo/.local/lib/python3.6/site-packages/pymysql/connections.py", line 773, in _execute_command
    self.write_packet(sql[:packet_size])
  File "/home/qinbo/.local/lib/python3.6/site-packages/pymysql/connections.py", line 634, in write_packet
    self._write_bytes(data)
  File "/home/qinbo/.local/lib/python3.6/site-packages/pymysql/connections.py", line 710, in _write_bytes
    "MySQL server has gone away (%r)" % (e,))
pymysql.err.OperationalError: (2006, "MySQL server has gone away (BrokenPipeError(32, 'Broken pipe'))")

原因

：数据库连接断开（具体断开原因很多，断开只是一个结果）

解决

：暂时未解决。

三.

try:
    a[3]
except Exception as e:
    print('错误类型是',e.__class__.__name__)
    print('错误明细是',e)