Python aiomysql.DictCursor方法代碼示例

# 需要導入模塊: import aiomysql [as 別名]
# 或者: from aiomysql import DictCursor [as 別名]
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 

# 需要導入模塊: import aiomysql [as 別名]
# 或者: from aiomysql import DictCursor [as 別名]
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方法隻返回結果數，不返回結果集 

# 需要導入模塊: import aiomysql [as 別名]
# 或者: from aiomysql import DictCursor [as 別名]
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掃描映射關係，並存儲到自身的類屬性 

# 需要導入模塊: import aiomysql [as 別名]
# 或者: from aiomysql import DictCursor [as 別名]
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語句 

# 需要導入模塊: import aiomysql [as 別名]
# 或者: from aiomysql import DictCursor [as 別名]
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

# 這個函數在元類中被引用，作用是創建一定數量的占位符 

# 需要導入模塊: import aiomysql [as 別名]
# 或者: from aiomysql import DictCursor [as 別名]
def __aenter__(self) -> "MySQLSourceContext":
        self.__conn = self.parent.db.cursor(aiomysql.DictCursor)
        self.conn = await self.__conn.__aenter__()
        return self 

# 需要導入模塊: import aiomysql [as 別名]
# 或者: from aiomysql import DictCursor [as 別名]
def __aenter__(self) -> "MySQLDatabaseContext":
        self.__conn = self.parent.db.cursor(aiomysql.DictCursor)
        self.conn = await self.__conn.__aenter__()
        return self 

# 需要導入模塊: import aiomysql [as 別名]
# 或者: from aiomysql import DictCursor [as 別名]
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()) 

# 需要導入模塊: import aiomysql [as 別名]
# 或者: from aiomysql import DictCursor [as 別名]
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