Python 流式游标与缓存式（默认）游标的那些坑

Python 流式游标与缓存式游标的那些坑

一. 起因

本问题起源于自己在服务器大量解析图片数据。

运行过程中出现错误：

(2013, 'Lost connection to MySQL server during query')

因为这个，我很认真的仔细的查了Mysql有关的timeout的问题：见“Mysql的timeout 以及 python重连”。

最后排查自己的问题不出在connect_time（两个sql语句之间的等待时间），而在于我在不断获取下一排数据项（即调用fetchone（）方法）中间进行大量耗时的工作，导致超过net_write_time而连接自动断开。

原代码：

	#这是一个类方法里面的内容，简化版
  			sql = "select * from storys"
        try:
            # 获取一个链接，一个cursor，执行SQL语句，MysqlController为封装的数据库连接池处理工具
            conn = MysqlController.getConn()
            cursor = conn.cursor()
            cursor.execute(sql)

            #信号锁
            semaphore = threading.BoundedSemaphore(self.maxRunThread)

            while(True):
                result = cursor.fetchone()
                if not result:
                    break
#这里不是重点
#———————————————————————————————————————————————————————————————————————————————————————————
                #这是一个for循环，里面开启了10个线程，每次开启线程之前要求获得到信号锁。
  							#每个线程run方法结束之后会调用semaphore.release()，以此保证同时运行的线程不超过self.maxRunThread个
                for i in range(10):
                    semaphore.acquire()
                    t = threading.Thread(target=self.run, args=(semaphore,i))
                    t.start()
#———————————————————————————————————————————————————————————————————————————————————————————

            print('结束')
            cursor.close()
            conn.close()
        except Exception as e:
            print(e)

但是非常奇怪的是，尽管报错Lost connection，但是我之后的数据依旧取到了，并完成分析了。

于是我做了一个小测试：我预计取10个数据，在取第3个数据的时候，手动把连接断开了。按照我之前对fetchone（）的理解，是每一次cursor在mysql中下移一个位置，返回给客户端。如果连接一旦断开，cursor就不能获取到数据了。

import pymysql
import time

def mytest():
    connection = pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='',
    db='*******',
    charset='utf8')
    cursor = connection.cursor()
    cursor.execute("select * from storys limit 10")
    data = cursor.fetchone()
    i = 0
    while data != None:
        if(i == 3):
            connection.close()
            print('connection is close')
        print(data[0])
        i+=1
        data = cursor.fetchone()
    cursor.close()
    connection.close()

if __name__ == '__main__':
    mytest()

但是结果：

python3 run.py
23
24
25
connection is close
26
...

可以看见，手动关闭连接后，cursor依旧能取到数据。

最后结论：dbq，是我菜了，这个是假的fetchone。

二. 正事儿

经过查询，python中的cursor主要分为两大类：非缓存式游标和缓存式游标。

cursor = connection.cursor()

这种方法默认的是缓存式游标，缓存式游标顾名思义，不管是fetchone还是fetchall都是在执行语句的时候一次性返回所有数据到客户端。这种返回在数据量特别大的时候无疑是不利的，会占用大量内存，导致我的主机卡成ppt。

最正确的用法是使用非缓存式游标，即流式游标（SSCursor也可以）：

cursor = conn.cursor(pymysql.cursors.SSDictCursor)#返回字典式数据

三. 小石子Warning

在查明白cursor的区别后，我非常开心的把游标换了。但由于之前曾经处理过timeout的问题，我曾经非常多此一举的在代码里面添加过ping（）：

	#这是一个类方法里面的内容，简化版
  			sql = "select * from storys"
        try:
            # 获取一个链接，一个cursor，执行SQL语句，MysqlController为封装的数据库连接池处理工具
            conn = MysqlController.getConn()
            cursor = conn.cursor(pymysql.cursors.SSDictCursor)#这里改游标类型了！！！！！！！
            cursor.execute(sql)

            while(True):
              	conn.ping()# 就是这句话！！！！！！！！！画蛇添足！！！！！！！正确代码去掉这句话
                result = cursor.fetchone()
                if not result:
                    break
								#做了一些事儿

            print('结束')
            cursor.close()
            conn.close()
        except Exception as e:
            print(e)

要知道，在一个循环的不断取数据的过程中，如果使用了ping就会使之前的查询断掉。于是报错：

UserWarning: Previous unbuffered result was left incomplete warnings.warn("Previous unbuffered result was left incomplete")

这个是由于查询未完成而造成的，去掉ping就好了。

四. 链接

更多有关防止读取大量数据缓存溢出的内容：如果我早看到这个就不会掉这么多坑了！

https://techualization.blogspot.com/2011/12/retrieving-million-of-rows-from-mysql.html