Python operation MySQL streaming reading data (black technology you haven’t seen)

Time:2021-12-7

Finally meet you again! It’s been a long time since I updated my developer notes. Today, let’s share a big trick! Before sharing, you must have encountered the operation of reading a large amount of data by mysql. I think if you read a large amount of data at one time, your memory will not hold up. There is a possibility that the CPU is full or the server is down. So how can we avoid it? I think most people will think of reading in blocks and setting the value range according to the ID! This method is feasible, but there is a better method, that is, streaming reading method! Similar to reading video stream information, we’ll share some dry goods today. Remember to like + pay attention!

1、 MySQL streaming reading
# coding=utf-8
from DBUtils.PooledDB import PooledDB
import pymysql, sys

reload(sys)
sys.setdefaultencoding("utf-8")
MYSQL_DB_NAME = "xxxx"
MYSQL_DB_USER = "xxxx"
MYSQL_DB_PWD = "xxxx"

if sys.platform == 'win32':
    MYSQL_DB_HOST = "xxxx"
elif sys.platform == 'darwin':
    MYSQL_DB_HOST = "xxxx"
else:
    MYSQL_DB_HOST = "xxxx"

pool = PooledDB(pymysql, host=MYSQL_DB_HOST, user=MYSQL_DB_USER, passwd=MYSQL_DB_PWD, db='adinsights_v3', port=3306,
                charset="utf8", cursorclass=pymysql.cursors.SSCursor)

conn = pool.connection()
cs = conn.cursor()
sql = "SELECT * from material_new"
try:
    cs.execute(sql)
    while True:
        result = cs.fetchone()
        if result:
            print(result)
        else:
            break
except:
    pass
cs.close()
conn.close()

1. Use pymysql.cursors.sscursor instead of the default cursor. You can use the above code or write it like this: conn.cursor (pymysql. Cursors. Sscursor)
2. Use fetchone to get only one row at a time. Don’t use fetchall. You can also use fetchmay, but this is actually calling fetchone multiple times.

2、 Mysq streaming introduction

There is a wrong understanding of sscursor, that is, sscursor is that the server reads all data at one time and then returns it to the client one by one. In fact, this is not the case. This cursor actually does not cache any data. It will not read all data into memory. Its method is to read records from storage blocks and return them to you one by one. Here’s a better name: streaming cursor.

Today’s code is introduced here. I still want to say: “be a sharing programmer. If you have any questions, please leave a message”. If you think my article is OK, you are welcome to pay attention and praise. Thank you!