Solve the problem of high memory usage caused by mysqldb querying large amounts of data

1. Source Code

Connection=MySQLdb.Connect(Host="Thehost",User="Theuser",passwd="Thepassword",DB="Thedb")cursor=Connection.cursor()cursor.Execute(Query) forRowinchCursor.fetchall ():    Print(Row)

2. The normal operation of the problem whether it is fetchall () or Fetchone () is to load the data locally before the calculation, a large amount of data will cause memory resources to consume light. The workaround is to use the SSCUROSR cursor for processing.

3. Optimized code

Import MySQLdb.cursorsconnection=MySQLdb.Connect(Host="Thehost",User="Theuser",passwd="Thepassword",DB="Thedb",Cursorclass= MySQLdb.Cursors.Sscursor)cursor=Connection.cursor()cursor.Execute(Query) forRowinchcursor:    Print(Row)

Documentation: http://mysql-python.sourceforge.net/MySQLdb.html#
Key paragraph interception:

basecursor

The base class for Cursor OB Jects. This does is 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 was 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 is column names and the values are column values. Note that if the column names is not unique, i.e., is selecting from and tables that share column names, some of the M'll be rewritten as table.column. This can is avoided by using the SQL as keyword. (This is yet-another reason does not have use * in SQL queries, particularly where joins is involved.)

Cursor

The

default cursor class. This class is composed of cursorwarningmixin, cursorstoreresultmixin, cursortuplerowsmixin, and basecursor, i.e. it raises Warning, uses mysql_store_result (), and returns rows a S tuples.

Dictcursor

like Cursor except it returns rows as dictionaries.

Sscursor

A "server-side" cursor. Like the Cursor but uses cursoruseresultmixin. The use of only if is dealing with potentially large result sets.

Ssdictcursor

like sscursor except it returns rows as dictionaries.

Solve the problem of high memory usage caused by mysqldb querying large amounts of data