Python aiomysql.create_pool() Examples

def init(cls, loop, **kwargs):
        logging.info('aiomysql.create_pool start')
        global dbPool
        dbPool = await aiomysql.create_pool(
            host=kwargs.get('host', 'localhost'),
            port=kwargs.get('port', 3306),
            user=kwargs['user'],
            password=kwargs['password'],
            db=kwargs['db'],
            charset=kwargs.get('charset', 'utf8'),
            autocommit=kwargs.get('autocommit', True),
            maxsize=kwargs.get('maxsize', 10),
            minsize=kwargs.get('minsize', 1),
            loop=loop
        )
        logging.info('aiomysql.create_pool end')
        return dbPool 

def start(self, _app, loop):
        _k = dict(loop=loop)
        if self.config:
            config = self.config
        else:
            config = _app.config.get('MYSQL')

        _k.update(config)

        _mysql = await create_pool(**_k)
        log.info('opening mysql connection for [pid:{}]'.format(os.getpid()))

        async def _query(sqlstr, args=None):
            async with _mysql.acquire() as conn:
                async with conn.cursor() as cur:
                    final_str = cur.mogrify(sqlstr, args)
                    log.info('mysql query [{}]'.format(final_str))
                    await cur.execute(final_str)
                    value = await cur.fetchall()
                    return value

        setattr(_mysql, 'query', _query)

        _app.mysql = _mysql 

def test_auth_plugin_renegotiation(mysql_server, loop):
    async with create_pool(**mysql_server['conn_params'],
                           auth_plugin='mysql_clear_password',
                           loop=loop) as pool:
        async with pool.get() as conn:
            async with conn.cursor() as cur:
                # Run simple command
                await cur.execute("SHOW DATABASES;")
                value = await cur.fetchall()

                assert len(value), 'No databases found'

                # Check we tried to use the cleartext plugin
                assert conn._client_auth_plugin == 'mysql_clear_password', \
                    'Client did not try clear password auth'

                # Check the server asked us to use MySQL's default plugin
                assert conn._server_auth_plugin in (
                    'mysql_native_password', 'caching_sha2_password'), \
                    'Server did not ask for native auth'
                # Check we actually used the servers default plugin
                assert conn._auth_plugin_used in (
                    'mysql_native_password', 'caching_sha2_password'), \
                    'Client did not renegotiate with server\'s default auth' 

def pool_creator(mysql_params, loop):
    pools = []

    @asyncio.coroutine
    def f(**kw):
        conn_kw = mysql_params.copy()
        conn_kw.update(kw)
        _loop = conn_kw.pop('loop', loop)
        pool = yield from aiomysql.create_pool(loop=_loop, **conn_kw)
        pools.append(pool)
        return pool

    yield f

    for pool in pools:
        pool.close()
        loop.run_until_complete(pool.wait_closed()) 

def test_create_pool_deprecations(mysql_params, loop):
    async with create_pool(loop=loop, **mysql_params) as pool:
        with warnings.catch_warnings(record=True) as w:
            warnings.simplefilter("always")
            async with pool.get() as conn:
                pass
    # The first warning emitted is expected to be DeprecationWarning:
    # in the past, we used to check for the last one but this assumption
    # breaks under Python 3.7 that also emits a `ResourceWarning` when
    # executed with `PYTHONASYNCIODEBUG=1`.
    assert issubclass(w[0].category, DeprecationWarning)
    assert conn.closed

    async with create_pool(loop=loop, **mysql_params) as pool:
        with warnings.catch_warnings(record=True) as w:
            warnings.simplefilter("always")
            with await pool as conn:
                pass
    assert issubclass(w[-1].category, DeprecationWarning)
    assert conn.closed 

def test_example():
    pool = yield from aiomysql.create_pool(
        host='127.0.0.1',
        port=3306,
        user='root',
        password='',
        db='mysql',
        loop=loop
    )
    with (yield from pool) as conn:
        cur = yield from conn.cursor()
        yield from cur.execute("SELECT 10")
        # print(cur.description)
        (r,) = yield from cur.fetchone()
        assert r == 10
    pool.close()
    yield from pool.wait_closed() 

def go():
    async with create_pool(host=MYSQL_HOST, port=3306,
                           user=MYSQL_USER, password=MYSQL_PASS,
                           db='mysql', loop=loop) as pool:
        async with pool.get() as conn:
            async with conn.cursor() as cur:
                await cur.execute("SELECT 42;")
                value = await cur.fetchone()
                print(value) 

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 __aenter__(self) -> "MySQLSource":
        # Verify MySQL connection using provided certificate, if given
        ssl_ctx = None
        if self.config.ca is not None:
            self.logger.debug(
                f"Secure connection to MySQL: CA file: {self.config.ca}"
            )
            ssl_ctx = ssl.create_default_context(cafile=self.config.ca)
        else:
            self.logger.critical("Insecure connection to MySQL")
        # Connect to MySQL
        self.pool = await aiomysql.create_pool(
            host=self.config.host,
            port=self.config.port,
            user=self.config.user,
            password=self.config.password,
            db=self.config.db,
            ssl=ssl_ctx,
        )
        self.__db = self.pool.acquire()
        self.db = await self.__db.__aenter__()
        return self 

def __aenter__(self) -> "MySQLDatabase":
        # Verify MySQL connection using provided certificate, if given
        ssl_ctx = None
        if self.config.ca is not None:
            self.logger.debug(
                f"Secure connection to MySQL: CA file: {self.config.ca}"
            )
            ssl_ctx = ssl.create_default_context(cafile=self.config.ca)
        else:
            self.logger.critical("Insecure connection to MySQL")
        # Connect to MySQL
        self.pool = await aiomysql.create_pool(
            host=self.config.host,
            port=self.config.port,
            user=self.config.user,
            password=self.config.password,
            db=self.config.db,
            ssl=ssl_ctx,
        )
        self.__db = self.pool.acquire()
        self.db = await self.__db.__aenter__()
        return self 

def _create_engine(minsize=1, maxsize=10, loop=None,
                         dialect=_dialect, pool_recycle=-1,
                         compiled_cache=None, **kwargs):

    if loop is None:
        loop = asyncio.get_event_loop()
    pool = await aiomysql.create_pool(minsize=minsize, maxsize=maxsize,
                                      loop=loop,
                                      pool_recycle=pool_recycle, **kwargs)
    conn = await pool.acquire()
    try:
        return Engine(dialect, pool, compiled_cache=compiled_cache, **kwargs)
    finally:
        pool.release(conn) 

def create_pool(loop, user, password, db, **kw):
    # 该函数用于创建连接池
    global __pool
    __pool = await aiomysql.create_pool(
        loop=loop,                               # 传递消息循环对象loop用于异步执行
        user=user,                               # user是通过关键字参数传进来的
        password=password,                       # 密码也是通过关键字参数传进来的
        db=db,                                   # 数据库名字
        host=kw.get('host', 'localhost'),        # 默认定义host名字为localhost
        port=kw.get('port', 3306),               # 默认定义mysql的默认端口是3306
        charset=kw.get('charset', 'utf8'),       # 默认数据库字符集是utf8
        autocommit=kw.get('autocommit', True),   # 默认自动提交事务
        maxsize=kw.get('maxsize', 10),           # 连接池最多同时处理10个请求
        minsize=kw.get('minsize', 1)             # 连接池最少1个请求
    )

# 用于SQL的SELECT语句。对应select方法,传入sql语句和参数 

def test_cached_sha256_pw(mysql_server, loop):
    connection_data = copy.copy(mysql_server['conn_params'])
    connection_data['user'] = 'user_caching_sha2'
    connection_data['password'] = 'pass_caching_sha2'

    async with create_pool(**connection_data,
                           loop=loop) as pool:
        async with pool.get() as conn:
            # User doesnt have any permissions to look at DBs
            # But as 8.0 will default to caching_sha2_password
            assert conn._auth_plugin_used == 'caching_sha2_password' 

def test_sha256_pw(mysql_server, loop):
    connection_data = copy.copy(mysql_server['conn_params'])
    connection_data['user'] = 'user_sha256'
    connection_data['password'] = 'pass_sha256'

    async with create_pool(**connection_data,
                           loop=loop) as pool:
        async with pool.get() as conn:
            # User doesnt have any permissions to look at DBs
            # But as 8.0 will default to caching_sha2_password
            assert conn._auth_plugin_used == 'sha256_password' 

def test_sha256_nopw(mysql_server, loop):
    connection_data = copy.copy(mysql_server['conn_params'])
    connection_data['user'] = 'nopass_sha256'
    connection_data['password'] = None

    async with create_pool(**connection_data,
                           loop=loop) as pool:
        async with pool.get() as conn:
            # User doesnt have any permissions to look at DBs
            # But as 8.0 will default to caching_sha2_password
            assert conn._auth_plugin_used == 'sha256_password' 

def test_issue_323(mysql_server, loop, recwarn):
    async with aiomysql.create_pool(**mysql_server['conn_params'],
                                    loop=loop) as pool:
        async with pool.get() as conn:
            async with conn.cursor() as cur:
                drop_db = "DROP DATABASE IF EXISTS bugtest;"
                await cur.execute(drop_db)

                create_db = "CREATE DATABASE bugtest;"
                await cur.execute(create_db)

                create_table = """CREATE TABLE IF NOT EXISTS `bugtest`.`testtable` (
                `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
                `bindata` VARBINARY(200) NOT NULL,
                PRIMARY KEY (`id`)
                );"""

                await cur.execute(create_table)

            try:
                recwarn.clear()

                async with conn.cursor() as cur:
                    await cur.execute("INSERT INTO `bugtest`.`testtable` "
                                      "(bindata) VALUES (%s);",
                                      (b'\xB0\x17',))

                    warnings = [warn for warn in recwarn.list
                                if warn.category is Warning]
                    assert len(warnings) == 0, \
                        "Got unexpected MySQL warning {}".\
                        format(' '.join(str(x) for x in warnings))

                    await cur.execute("SELECT * FROM `bugtest`.`testtable`;")
                    rows = await cur.fetchall()

                    assert len(rows) == 1, "Table should have 1 row"

            finally:
                async with conn.cursor() as cur:
                    await cur.execute("DELETE FROM `bugtest`.`testtable`;") 

def __aenter__(self) -> "DemoAppSource":
        self.pool = await aiomysql.create_pool(
            host=self.config.host,
            port=self.config.port,
            user=self.config.user,
            password=self.config.password,
            db=self.config.db,
        )
        self.__db = self.pool.acquire()
        self.db = await self.__db.__aenter__()
        return self 

def test_tls_connect(mysql_server, loop):
    async with create_pool(**mysql_server['conn_params'],
                           loop=loop) as pool:
        async with pool.get() as conn:
            async with conn.cursor() as cur:
                # Run simple command
                await cur.execute("SHOW DATABASES;")
                value = await cur.fetchall()

                values = [item[0] for item in value]
                # Spot check the answers, we should at least have mysql
                # and information_schema
                assert 'mysql' in values, \
                    'Could not find the "mysql" table'
                assert 'information_schema' in values, \
                    'Could not find the "mysql" table'

                # Check TLS variables
                await cur.execute("SHOW STATUS LIKE 'Ssl_version%';")
                value = await cur.fetchone()

                # The context has TLS
                assert value[1].startswith('TLS'), \
                    'Not connected to the database with TLS'


# MySQL will get you to renegotiate if sent a cleartext password 

def connect(self):
        """Create connection pool asynchronously.
        """
        self.pool = await aiopg.create_pool(
            loop=self.loop,
            timeout=self.timeout,
            database=self.database,
            **self.connect_params) 

def main():
    async with aiomysql.create_pool(
            host='localhost', port=3306, user='root',
            password='rootpw', ssl=ctx,
            auth_plugin='mysql_clear_password') as pool:

        async with pool.get() as conn:
            async with conn.cursor() as cur:
                # Run simple command
                await cur.execute("SHOW DATABASES;")
                value = await cur.fetchall()

                values = [item[0] for item in value]
                # Spot check the answers, we should at least have mysql
                # and information_schema
                assert 'mysql' in values, \
                    'Could not find the "mysql" table'
                assert 'information_schema' in values, \
                    'Could not find the "mysql" table'

                # Check TLS variables
                await cur.execute("SHOW STATUS LIKE 'Ssl_version%';")
                value = await cur.fetchone()

                # The context has TLS
                assert value[1].startswith('TLS'), \
                    'Not connected to the database with TLS' 

def test_example(loop):
    pool = await aiomysql.create_pool(host='127.0.0.1', port=3306,
                                      user='root', password='',
                                      db='mysql', loop=loop)
    async with pool.acquire() as conn:
        async with conn.cursor() as cur:
            await cur.execute("SELECT 42;")
            print(cur.description)
            (r,) = await cur.fetchone()
            assert r == 42
    pool.close()
    await pool.wait_closed() 

def connect(self):
        """Create connection pool asynchronously.
        """
        self.pool = await aiomysql.create_pool(
            loop=self.loop,
            db=self.database,
            connect_timeout=self.timeout,
            **self.connect_params) 

def get_mysql_pool_cli(self):
        """
        :return: an async mysql client
        """
        if self.mysql_pool_cli is None:
            self.mysql_pool_cli = await aiomysql.create_pool(host=self.host, port=self.port, user=self.user,
                                                             password=self.password, db=self.database, loop=self.loop,
                                                             minsize=1, maxsize=3, charset=self.charset)
            self.connection = await self.mysql_pool_cli.acquire()
            self.cursor = await self.connection.cursor()
        return self.mysql_pool_cli 

def get_mysql_pool_cli(self):
        """
        :return: an async mysql client
        """
        if self.mysql_pool_cli is None:
            self.mysql_pool_cli = await aiomysql.create_pool(host=self.host, port=self.port, user=self.user,
                                                             password=self.password, db=self.database, loop=self.loop,
                                                             minsize=1, maxsize=3, charset=self.charset)
            self.connection = await self.mysql_pool_cli.acquire()
            self.cursor = await self.connection.cursor()
        return self.mysql_pool_cli 

def _connect(self, database, **kwargs):
        if not mysql:
            raise ImproperlyConfigured('MySQLdb or PyMySQL must be installed.')
        conn_kwargs = {
            'charset': 'utf8',
            'use_unicode': True,
        }
        conn_kwargs.update(kwargs)
        return await aiomysql.create_pool(db=database, **conn_kwargs) 

def create_pool(loop, **kw):
    logging.info('创建连接池...')
    # 声明变量__pool是一个全局变量，如果不加声明，__pool就会被默认为一个私有变量，不能被其他函数引用
    global __pool
    # 调用一个自协程来创建全局连接池，create_pool的返回值是一个pool实例对象
    __pool = await aiomysql.create_pool(
        # 下面就是创建数据库连接需要用到的一些参数，从**kw（关键字参数）中取出来
        # kw.get的作用应该是，当没有传入参数是，默认参数就是get函数的第二项
        host=kw.get('host', 'localhost'),  # 数据库服务器位置，默认设在本地
        port=kw.get('port', 3306),  # mysql的端口，默认设为3306
        user=kw['user'],  # 登陆用户名，通过关键词参数传进来。
        password=kw['password'],  # 登陆密码，通过关键词参数传进来
        db=kw['db'],  # 当前数据库名
        charset=kw.get('charset', 'utf8'),  # 设置编码格式，默认为utf-8
        autocommit=kw.get('autocommit', True),  # 自动提交模式，设置默认开启
        maxsize=kw.get('maxsize', 10),  # 最大连接数默认设为10
        minsize=kw.get('minsize', 1),  # 最小连接数，默认设为1，这样可以保证任何时候都会有一个数据库连接
        loop=loop  # 传递消息循环对象，用于异步执行
    )

# =================================以下是SQL函数处理区====================================
# select和execute方法是实现其他Model类中SQL语句都经常要用的方法

# 将执行SQL的代码封装仅select函数，调用的时候只要传入sql，和sql所需要的一些参数就好
# sql参数即为sql语句，args表示要搜索的参数
# size用于指定最大的查询数量，不指定将返回所有查询结果