首页 > 代码库 > 解决mysqldb查询大量数据导致内存使用过高的问题
解决mysqldb查询大量数据导致内存使用过高的问题
1.源代码
3.优化后的代码
connection=MySQLdb.connect(
host="thehost",user="theuser",
passwd="thepassword",db="thedb")
cursor=connection.cursor()
cursor.execute(query)
for row in cursor.fetchall():
print(row)2.问题
普通的操作无论是fetchall()还是fetchone()都是先将数据载入到本地再进行计算，大量的数据会导致内存资源消耗光。解决办法是使用SSCurosr光标来处理。
3.优化后的代码
import MySQLdb.cursors
connection=MySQLdb.connect(
host="thehost",user="theuser",
passwd="thepassword",db="thedb",
cursorclass = MySQLdb.cursors.SSCursor)
cursor=connection.cursor()
cursor.execute(query)
for row in cursor:
print(row)参考文档：http://mysql-python.sourceforge.net/MySQLdb.html#
关键段落截取：
- BaseCursor
- The base class for Cursor objects. This does not raise Warnings.
- CursorStoreResultMixIn
- Causes the Cursor to use the mysql_store_result() function to get the query result. The entire result set is stored on the client side.
- CursorUseResultMixIn
- Causes the cursor to use the mysql_use_result() function to get the query result. The result set is stored on the server side and is transferred row by row using fetch operations.
- CursorTupleRowsMixIn
- Causes the cursor to return rows as a tuple of the column values.
CursorDictRowsMixIn
Causes the cursor to return rows as a dictionary, where the keys are column names and the values are column values. Note that if the column names are not unique, i.e., you are selecting from two tables that share column names, some of them will be rewritten as table.column. This can be avoided by using the SQL ASkeyword. (This is yet-another reason not to use * in SQL queries, particularly where JOIN is involved.)
- Cursor
- The default cursor class. This class is composed of CursorWarningMixIn, CursorStoreResultMixIn, CursorTupleRowsMixIn, and BaseCursor, i.e. it raises Warning, usesmysql_store_result(), and returns rows as tuples.
- DictCursor
- Like Cursor except it returns rows as dictionaries.
- SSCursor
- A "server-side" cursor. Like Cursor but uses CursorUseResultMixIn. Use only if you are dealing with potentially large result sets.
- SSDictCursor
- Like SSCursor except it returns rows as dictionaries.
解决mysqldb查询大量数据导致内存使用过高的问题
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。