python读取千万级数据库数据类型_使用python遍历mysql中有千万行数据的大表

最近工作中遇到了一个问题&＃xff1a;将mysql的数据同步到elasticsearch中&＃xff0c;现在有很多方案&＃xff0c; logstash-jdbc &＃xff0c; elasticsearch-jdbc &＃xff0c; go-mysql-elasticsearch&＃xff0c;本来原来是使用logstash-jdbc的&＃xff0c;但是由于其配置文件是基于ruby语法的&＃xff0c;导致遇到问题需要查很多资料&＃xff0c;加上logstash调试困难&＃xff08;很可能是我用的姿势不对。。。&＃xff09;&＃xff0c;所以决定手动在elasticsearch中建表&＃xff0c;然后写脚本定期更新数据&＃xff0c;那么问题就来了&＃xff1a;第一次插入需要一次性插入以前的所有数据&＃xff0c;以前使用pymysql时用的都是DictCursor游标&＃xff0c;原理是一次性讲数据加载到内存中&＃xff0c;但是现有表中有几张有数千万行&＃xff0c;几个G大小&＃xff0c;一次性读到内存中很不明智&＃xff0c;google后&＃xff0c;发现一篇blog不错&＃xff0c;翻译共享一下&＃xff08;渣英语&＃xff0c;不要笑&＃xff0c;原文链接在文末&＃xff09;。

当使用sql查询的结果有非常多行时&＃xff0c;如果使用默认的cursor&＃xff0c;你的程序在接受数据的的时候很可能卡住或者被杀死&＃xff0c;原因是mysql客户端&＃xff08;Java&＃xff0c;Pyhton&＃xff09;默认在内存里缓存下所有行然后再处理&＃xff0c;如果内存溢出后&＃xff0c;你的程序就会被杀死。

解决方式是实用流式游标&＃xff0c;在Python中&＃xff0c;你可以使用pymysql.cursors.SSCursor&＃xff08;或者SSDictCursor&＃xff09;来解决这个问题

import pymysql

conn &＃61; pymysql.connect(...)

cursor &＃61; pymysql.cursors.SSCursor(conn)

cursor.execute(...)

while True:

row &＃61; cursor.fetchone()

if not row:

break

...

这里有两点需要注意下&＃xff1a;

使用pymysql.cursors.SSCursor代替默认的cursor。可以使用以上代码&＃xff0c;或者这样写&＃xff1a;conn.cursor(pymysql.cursors.SSCursor)

使用fetchone去每次只获得一行&＃xff0c;别使用fetchall。也可以使用fetchmay&＃xff0c;但是这样其实是多次调用fetchone。

对于SSCursor有一个错误的理解&＃xff0c;就是SSCursor是服务端一次性读出所有数据然后一条一条返给客户端&＃xff0c;其实不是这样的&＃xff0c;这个cursor实际上没有缓存下来任何数据&＃xff0c;它不会读取所有所有到内存中&＃xff0c;它的做法是从储存块中读取记录&＃xff0c;并且一条一条返回给你。这里有一个更适合的名字&＃xff1a;流式游标。

因为SSCursor是没有缓存的游标&＃xff0c;这里有几条约束&＃xff1a;

这个connection只能读完所有行之后才能处理其他sql。如果你需要并行执行sql&＃xff0c;在另外一个connection中执行&＃xff0c;否则你会遇到 error 2014 , "Commands out of sync; you can&＃39;t run this command now."

必须一次性读完所有行&＃xff0c;每次读取后处理数据要快&＃xff0c;不能超过60s&＃xff0c;否则mysql将会断开这次连接&＃xff08; error2013 , “Lost connection to MySQL server during query&＃xff09;&＃xff0c;也可以修改 SET NET_WRITE_TIMEOUT &＃61; xx 来增加超时间隔。