Python aiomysql.DictCursor() Examples

def select(sql, args, size=None):
    log(sql, args)
    # 异步等待连接池对象返回可以连接线程，with语句则封装了清理（关闭conn）和处理异常的工作
    async with __pool.get() as conn:
        # 等待连接对象返回DictCursor可以通过dict的方式获取数据库对象，需要通过游标对象执行SQL
        async with conn.cursor(aiomysql.DictCursor) as cur:
            await cur.execute(sql.replace('?', '%s'), args)  # 将sql中的'?'替换为'%s'，因为mysql语句中的占位符为%s
            # 如果传入size
            if size:
                resultset = await cur.fetchmany(size)  # 从数据库获取指定的行数
            else:
                resultset = await cur.fetchall()      # 返回所有的结果集
        logging.info('rows returned: %s' % len(resultset))
        return resultset

# 用于SQL的INSERT INTO，UPDATE，DELETE语句，execute方法只返回结果数，不返回结果集 

def select(cls, sql, args=(), size=None):
        uid = uuid.uuid4().hex
        logging.info("uid:%s,DBPoolC.select get conn start " % (uid,))
        with (await dbPool) as conn:
            logging.info("uid:%s,DBPoolC.select get conn end %s " % (uid, conn))
            logging.info("uid:%s,DBPoolC.select get cursor start " % (uid,))
            cur = await conn.cursor(aiomysql.DictCursor)
            logging.info("uid:%s,DBPoolC.select get cursor end %s " % (uid, cur))
            sql = sql.replace('?', '%s')
            logging.info("uid:%s,DBPoolC.select execute start " % (uid,))
            await cur.execute(sql, args)
            logging.info("uid:%s,DBPoolC.select execute end " % (uid,))
            if size:
                logging.info("uid:%s,DBPoolC.select fetchmany start " % (uid,))
                rs = await cur.fetchmany(size)
                logging.info("uid:%s,DBPoolC.select fetchmany end " % (uid,))
            else:
                logging.info("uid:%s,DBPoolC.select fetchall start " % (uid,))
                rs = await cur.fetchall()
                logging.info("uid:%s,DBPoolC.select fetchall end " % (uid,))
            await cur.close()
        return rs 

def execute(sql, args, autocommit=True):
    log(sql, args)
    async with __pool.get() as conn:
        if not autocommit:       # 若设置不是自动提交，则手动开启事务
            await conn.begin()
        try:
            async with conn.cursor(aiomysql.DictCursor) as cur:  # 打开一个DictCursor，它与普通游标的不同在于，以dict形式返回结果
                await cur.execute(sql.replace('?', '%s'), args)
                affected = cur.rowcount    # 返回受影响的行数
            if not autocommit:             # 同上, 如果设置不是自动提交的话，手动提交事务
                await conn.commit()
        except BaseException as e:
            if not autocommit:             # 出错, 回滚事务到增删改之前
                await conn.rollback()
            raise e
        return affected


# 这是一个元类，它定义了如何来构造一个类，任何定义了__metaclass__属性或指定了metaclass的都会通过元类定义的构造方法构造类
# 任何继承自Model的类，都会自动通过ModelMetaclass扫描映射关系，并存储到自身的类属性 

def select(sql, args, size=None):
    log(sql, args)
    # 声明全局变量，这样才能引用create_pool函数创建的__pool变量
    global __pool
    # 从连接池中获得一个数据库连接
    # 用with语句可以封装清理（关闭conn)和处理异常工作
    async with __pool.get() as conn:
        # 等待连接对象返回DictCursor可以通过dict的方式获取数据库对象，需要通过游标对象执行SQL
        async with conn.cursor(aiomysql.DictCursor) as cur:
            # 设置执行语句，其中sql语句的占位符为？，而python为%s, 这里要做一下替换
            # args是sql语句的参数
            await cur.execute(sql.replace('?', '%s'), args or ())
            # 如果制定了查询数量，则查询制定数量的结果，如果不指定则查询所有结果
            if size:
                rs = await cur.fetchmany(size)  # 从数据库获取指定的行数
            else:
                rs = await cur.fetchall()  # 返回所有结果集
        logging.info("返回的行数：%s" % len(rs))
        return rs  # 返回结果集

# 定义execute()函数执行insert update delete语句 

def execute(sql, args, autocommit=True):
    # execute()函数只返回结果数，不返回结果集，适用于insert, update这些语句
    log(sql)
    async with __pool.get() as conn:
        if not autocommit:
            await conn.begin()
        try:
            async with conn.cursor(aiomysql.DictCursor) as cur:
                await cur.execute(sql.replace('?', '%s'), args)
                affected = cur.rowcount  # 返回受影响的行数
            if not autocommit:
                await conn.commit()
        except BaseException as e:
            if not autocommit:
                await conn.rollback()
            raise
        return affected

# 这个函数在元类中被引用，作用是创建一定数量的占位符 

def __aenter__(self) -> "MySQLSourceContext":
        self.__conn = self.parent.db.cursor(aiomysql.DictCursor)
        self.conn = await self.__conn.__aenter__()
        return self 

def __aenter__(self) -> "MySQLDatabaseContext":
        self.__conn = self.parent.db.cursor(aiomysql.DictCursor)
        self.conn = await self.__conn.__aenter__()
        return self 

def table(loop, connection, table_cleanup):
    async def f():
        cursor = await connection.cursor(DictCursor)
        sql = """CREATE TABLE bulkinsert (id INT(11), name CHAR(20),
                 age INT, height INT, PRIMARY KEY (id))"""
        await cursor.execute(sql)
    table_cleanup('bulkinsert')
    loop.run_until_complete(f()) 

def assert_dict_records(connection):
    async def f(data):
        cursor = await connection.cursor(DictCursor)
        await cursor.execute(
            "SELECT id, name, age, height FROM bulkinsert")
        result = await cursor.fetchall()
        await cursor.execute('COMMIT')
        assert sorted(data, key=lambda k: k['id']) == \
            sorted(result, key=lambda k: k['id'])
    return f