使用SSCursor 操作数据库

pipidi

zhujingdi1998@gmail.com

使用SSCursor 操作数据库

当我们遇到100w数据需要读进到我们的程序中去的时候 如果用传统的fetchall()内存很有可能被吃爆, 或者数据连接超时的异常
所以我们需要引进一个SSCursor的游标 他是一个迭代器,当我们迭代他的时候才会从数据库中取得数据 所以他是真正的一条条取数据的,不是一次直接读进内存的

import pymysql

from conf import MYSQL_HOST, MYSQL_USER, MYSQL_PASSWORD, MYSQL_DB
import pymysql.cursors

class MysqlClient(object):
    """
    Mysql client
    """

    def __init__(self, host, username, password, dbname):
        """

        :param host:
        :param username:
        :param password:
        :param dbname:
        """
        # self.__conn = pymysql.connect(
        self.host = host
        self.username = username
        self.password = password
        self.dbname = dbname


    def get_client(self):
        self.conn = pymysql.connect(
            self.host,
            self.username,
            self.password,
            self.dbname,

        )

    def get_sscrsor(self):
        self.get_client()
        self.cursor = self.conn.cursor(pymysql.cursors.SSCursor)

我们只需要对这个sscrsor进行迭代就可以了

conn,cur = get_cur()
with conn:
    with  cur:

        cur.execute("""select uid,createtime,purchased,oid,itemid,sku_id
        from test_order_d
        order by uid,createtime""")
        pre_user =defaultdict(lambda :0)
        for i in cur:
            # i[0],i[1]
            user_id = i[0]
            user_createtime = i[1]
            purchased = i[2]
            oid = i[3]
            itemid = i[4]
            sku_id = i[5]
            if purchased != 0:
                pre_user[user_id] = purchased
                print("pass!")
            else:
                pre_user[user_id] += 1
                user_buy_num = pre_user[user_id]
                # print(sku_id)
                user_order_array = np.array([user_id, user_createtime, user_buy_num, oid, itemid, sku_id])
                yield user_order_array

阅读量