Shulou(Shulou.com)06/02 Report--

This article mainly explains "analyzing the reason why PyMySQL getting a piece of data will make memory explode". The content in the article is simple and clear, and it is easy to learn and understand. Please follow Xiaobian's train of thought to study and learn "analyze the reason why PyMySQL obtains a piece of data will make memory explode".

When Python has the need to read and write MySQL data, we often use PyMySQL, a third-party library.

Sometimes if the data in a table is very large, but we only need to read one piece of data, we may take it for granted that we can use the cursor.fetchone () method to read only one piece of data:

Import pymysql connection = pymysql.connect (host='localhost', user='user', password='passwd', db='db', charset='utf8mb4' Cursorclass=pymysql.cursors.DictCursor) with connection.cursor () as cursor: db = 'select * from users where age > 10' cursor.execute (db) one_user = cursor.fetchone ()

But in fact, the above code is no different from the following code:

... With connection.cursor () as cursor: sql = 'select * from users where age > 10' cursor.execute (sql) all_users = cursor.fetchall () one_user = all_users [0]

This is because when we execute to cursor.execute (sql), PyMySQL has already read all the data in the table into memory. The following cursor.fetchall () or cursor.fetchone () just returns all the data from memory or one piece of data.

Let's look at the source code of PyMySQL [1]. The code for the cursor.execute () method is shown in the following figure:

Line 163 calls the self._query method. Let's go back to this method:

Seeing line 322 of the code, the self._do_get_result () method is called. Let's take a look at this method again:

Notice line 342, where all the data has been stored in the self._rows list.

Now let's look at the cursor.fetchone () method:

As you can see, this is just reading a piece of data from the list according to the subscript.

Then take a look at the cursor.fetchall () method:

If cursor.fetchone () has been called several times before, the self.rownumber will continue to increase. When cursor.fetchall () is called, the data that has been returned before is skipped and all the remaining data is returned directly. If cursor.fetchone () has not been called before, all data is returned directly.

Therefore, using cursor.fetchone () alone will not save memory, and if the data in the table is very large, there is still a risk of memory explosion.

So what is the real solution? The real solution is to specify the cursor type when creating a database connection. Pymysql.connect has a parameter called cursorclass, which can be solved by setting its value to pymysql.SSDictCursor.

Let's look at how to use it correctly:

Import pymysql connection = pymysql.connect (host='localhost', user='user', password='passwd', db='db', charset='utf8mb4' Cursorclass=pymysql.cursors.SSDictCursor) with connection.cursor () as cursor: db = 'select * from users where age > 10' cursor.execute (db) for row in cursor: print ('iterate cursor directly Read a piece of data from the database every time you loop. All data will not be read into memory in advance.') Print (row ['name']) Thank you for your reading, the above is the content of "analyzing the reason why PyMySQL obtains a piece of data will make memory explosion". After the study of this article, I believe you have a deeper understanding of the analysis of PyMySQL access to a piece of data will make memory explosion, the specific use of the situation also needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.