减轻内存负担,在 pymysql 中使用 SSCursor 查询结果集较大的 SQL

前言

默认情况下,使用 pymysql 查询数据使用的游标类是 Cursor,比如:

import pymysql.cursors

# 连接数据库
connection = pymysql.connect(host='localhost',
                             user='user',
                             password='passwd',
                             db='db',
                             charset='utf8mb4')

try:
    with connection.cursor() as cursor:
        # 读取所有数据
        sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
        cursor.execute(sql, ('webmaster@python.org',))
        result = cursor.fetchall()
        print(result)
finally:
    connection.close()

这种写法会将查询到的所有数据写入内存中,若在结果较大的情况下,会对内存造成很大的压力,所幸 pymysql 实现了一种 SSCursor 游标类,它允许将查询结果按需返回,而不是一次性全部返回导致内存使用量飙升。

SSCursor

官方文档的解释为:

Unbuffered Cursor, mainly useful for queries that return a lot of data,
or for connections to remote servers over a slow network.

Instead of copying every row of data into a buffer, this will fetch
rows as needed. The upside of this is the client uses much less memory,
and rows are returned much faster when traveling over a slow network
or if the result set is very big.

There are limitations, though. The MySQL protocol doesn't support
returning the total number of rows, so the only way to tell how many rows
there are is to iterate over every row returned. Also, it currently isn't
possible to scroll backwards, as only the current row is held in memory.

大致翻译为:无缓存的游标,主要用于查询大量结果集或网络连接较慢的情况。不同于普通的游标类将每一行数据写入缓存的操作,该游标类会按需读取数据,这样的好处是客户端消耗的内存较小,而在网络连接较慢或结果集较大的情况下,数据的返回也会更快。当然,缺点就是它不支持返回结果的行数(也就是调用 rowcount 属性将不会得到正确的结果,一共有多少行数据则需要全部迭代完成才能知道),当然它也不支持往回读取数据(这也很好理解,毕竟是生成器嘛)。

它的写法如下:

from pymysql.cursors import SSCursor

connection = pymysql.connect(host='localhost',
                             user='user',
                             password='passwd',
                             db='db',
                             charset='utf8mb4')

# 创建游标
cur = connection.cursor(SScursor)
cur.execute('SELECT * FROM test_table')

# 读取数据
# 此时的 cur 对内存消耗相对 Cursor 类来说简直微不足道
for data in cur:
    print(data)

本质上对所有游标类的迭代都是在不断的调用 fetchone 方法,不同的是 SSCursor 对 fetchone 方法的实现不同罢了。这一点查看源码即可发现:
Cursor 类 fetchone 方法源码(可见它是在根据下标获取列表中的某条数据):

image

SSCursor 类 fetchone 方法源码(读取数据并不做缓存):

pylittleimage-20201025192825032.png

跳坑

当然,如果没有坑就没必要为此写一篇文章了,开开心心的用着不香吗。经过多次使用,发现在使用 SSCursor 游标类(以及其子类 SSDictCursor)时,需要特别注意以下两个问题:

1. 读取数据间隔问题

每条数据间的读取间隔若超过 60s,可能会造成异常,这是由于 MySQL 的 NET_WRITE_TIMEOUT 设置引起的错误(该设置值默认为 60),如果读取的数据有处理时间较长的情况,那么则需要考虑更改 MySQL 的相关设置了。(tips: 使用 sql SET NET_WRITE_TIMEOUT = xx 更改该设置或修改 MySQL配置文件)

2. 读取数据时对数据库的其它操作行为

因为 SSCursor 是没有缓存的,只要结果集没有被读取完成,就不能使用该游标绑定的连接进行其它数据库操作(包括生成新的游标对象),如果需要做其它操作,应该使用新的连接。比如:

from pymysql.cursors import SSCursor

def connect():
    connection = pymysql.connect(host='localhost',
                                 user='user',
                                 password='passwd',
                                 db='db',
                                 charset='utf8mb4')
    return connection

conn1 = connect()
conn2 = connect()

cur1 = conn1.cursor(SScursor)
cur2 = conn1.cursor()

with conn1.cursor(SSCursor) as ss_cur, conn2.cursor() as cur:
    try:
        ss_cur.execute('SELECT id, name FROM test_table')

        for data in ss_cur:
            # 使用 conn2 的游标更新数据
            if data[0] == 15:
                cur.execute('UPDATE tset_table SET name="kingron" WHERE id=%s', args=[data[0])

            print(data)
    finally:
        conn1.close()
        conn2.close()

参考

  1. Cursor Objects — PyMySQL 0.7.2 documentation
  2. Using SSCursor (streaming cursor) to solve Python using pymysql to query large amounts of data leads to memory usage is too high
最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 219,110评论 6 508
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 93,443评论 3 395
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 165,474评论 0 356
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 58,881评论 1 295
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 67,902评论 6 392
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 51,698评论 1 305
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 40,418评论 3 419
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 39,332评论 0 276
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 45,796评论 1 316
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,968评论 3 337
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 40,110评论 1 351
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 35,792评论 5 346
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 41,455评论 3 331
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 32,003评论 0 22
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 33,130评论 1 272
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 48,348评论 3 373
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 45,047评论 2 355