In the last blog post, I introduced the use of MySQL database to obtain pymysql. For reference: https://www.cnblogs.com/minseo/p/15597428.html

This article introduces the use of asynchronous MySQL asynchronous driver aiomysql

1. Install asynchronous module

If there is no module, install the module using pip first

pip3 install asyncio
pip3 install aiomysql

2. Create MySQL database connection pool

Unlike the synchronous method, you cannot directly create a database connection conn using asynchronous. You need to create a database connection pool object first__ Pool creates database connections through this database connection pool object

The database configuration information is the same as the database used for pymysql synchronization

import asyncio,aiomysql,time
# Database configuration dict
db_config = {
    'host': 'localhost',
    'user': 'www-data',
    'password': 'www-data',
    'db': 'awesome'
}

# Create database connection pool coprocessor function
async def create_pool(**kw):
    global __pool
    __pool = await aiomysql.create_pool(
        host=kw.get('host', 'localhost'),
        port=kw.get('port', 3306),
        user=kw['user'],
        password=kw['password'],
        db=kw['db']
    )

loop=asyncio.get_event_loop()
loop.run_until_complete(create_pool(**db_config))
# The global variable is generated when the coprocessor function is run in the event loop__ Pool is a connection pool object < aiomysql. Pool. Pool object at 0x00000244ad1724c8 >
print(__pool)
# <aiomysql.pool.Pool object at 0x00000244AD1724C8>

3. Create a coprocessor function to execute sql statements

Because it is an asynchronous module, it can only be called through the await keyword in the event loop, using the coprocessor function that needs to be created to execute sql statements

Use the connection pool object created in the previous step to create the connection conn and the buoy object cur in the coroutine function. Execute the sql statement through the buoy object. The execution method is the same as that of the pymysql module

cursor.execute(sql,args)
sql # sql statements to be executed, such as' select * from table '_ name'
args # Replace the formatted string of the sql statement, that is, the sql statement can use% s to represent a string, and then replace it with the corresponding variable or parameter in args. Args is a list or tuple, that is, an ordered sequence, which needs to be one-to-one corresponding to% s in sql
# For example, sql='select * from table_name where id=%s'  args=['12345']
# It is equivalent to replacing% s in sql with parameters in args
# select * from table_name where id='12345'

Next, create two coroutine functions select execute, one to perform search operations and the other to perform insert, update, delete and other modification operations

# Execute the select function
async def select(sql,args,size=None):
    with await __pool as conn:
        cur = await conn.cursor(aiomysql.DictCursor)
        await cur.execute(sql.replace('?','?s'),args or ())
        if size:
            rs = await cur.fetchmany(size)
        else:
            rs = await cur.fetchall()
        await cur.close()
        return rs


# Execute the insert update delete function
async def execute(sql,args):
    with await __pool as conn:
        try:
            cur = await conn.cursor()
            await cur.execute(sql.replace('?','%s'),args)
            affetced = cur.rowcount
            await conn.commit()
            await cur.close()
        except BaseException as e:
            raise
        return affetced

4. Execute sql statements in practice

Before implementing sql statements, we first create a database and corresponding tables on the machine for testing

The host, user name, password, database name and table name corresponding to the database are as follows

host: localhost
user: www-data
password: www-data
db: awesome
table_name: users

The sql statement to create the table name is as follows. You need to create the corresponding table in the database

CREATE TABLE `users` (
  `id` varchar(50) NOT NULL,
  `email` varchar(50) NOT NULL,
  `passwd` varchar(50) NOT NULL,
  `admin` tinyint(1) NOT NULL,
  `name` varchar(50) NOT NULL,
  `image` varchar(500) NOT NULL,
  `created_at` double NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_email` (`email`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

The corresponding structure of the created table is as follows

mysql> desc users;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | varchar(50)  | NO   | PRI | NULL    |       |
| email      | varchar(50)  | NO   | UNI | NULL    |       |
| passwd     | varchar(50)  | NO   |     | NULL    |       |
| admin      | tinyint(1)   | NO   |     | NULL    |       |
| name       | varchar(50)  | NO   |     | NULL    |       |
| image      | varchar(500) | NO   |     | NULL    |       |
| created_at | double       | NO   | MUL | NULL    |       |
+------------+--------------+------+-----+---------+-------+
7 rows in set (2.68 sec)

① execute the insert operation

# insert start
import time
sql = 'insert into `users` (`email`, `passwd`, `admin`, `name`, `image`, `created_at`, `id`) values (?, ?, ?, ?, ?, ?, ?)'
args = ['test@qq.com','password',1,'test','about:blank',time.time(),'111111']
async def insert():
    await execute(sql,args)
loop.run_until_complete(insert())
# insert end

The execution method is no different from that of pymysql. The difference is that you need to use the keyword await call in the event loop

After execution, view the inserted data in MySQL

mysql> select * from users;
+--------+-------------+----------+-------+------+-------------+------------------+
| id     | email       | passwd   | admin | name | image       | created_at       |
+--------+-------------+----------+-------+------+-------------+------------------+
| 111111 | test@qq.com | password |     1 | test | about:blank | 1637738541.48629 |
+--------+-------------+----------+-------+------+-------------+------------------+
1 row in set (0.00 sec)

② execute the update operation

You can also execute the execute function directly in the loop event loop

# update start
import time
sql = 'update `users` set `email`=?, `passwd`=?, `admin`=?, `name`=?, `image`=?, `created_at`=? where `id`=?'
args = ['test2@qq.com','password',1,'test2','about:blank',time.time(),'111111']
loop.run_until_complete(execute(sql,args))
# update end

After execution, both email and name were modified

③ execute the delete operation

# delete start
sql = 'delete from `users` where `id`=?'
args = ['111111'] 
loop.run_until_complete(execute(sql,args))
# delete end

This data is also deleted according to the value specified by the keyword id

④ execute selete operation

Before performing the select operation, we ensure that there is at least one piece of data in the database

# select start
sql = 'select * from users'
args = []
rs = loop.run_until_complete(select(sql,args))
print(rs)
# select end

Here, the coroutine function select that directly performs the search returns the list of all results according to the definition of the function, and the element is the dictionary of the query results

Output as

[{'id': '111111', 'email': 'test@qq.com', 'passwd': 'password', 'admin': 1, 'name': 'test', 'image': 'about:blank', 'created_at': 1637739212.74493}]

If there are multiple results, use the subscript of the list to get them

　　

Supplement

Comparison of execution speed between synchronous module pymysql and asynchronous module aiomysql

If we need to insert 20000 pieces of data into the database, we use synchronous mode and asynchronous mode respectively

First, delete all test data in the database

delete from users;

Synchronized code

d:/learn-python3 / learning script / pymysql/use_pymysql.py

import pymysql
db_config = {
    'host': 'localhost',
    'user': 'www-data',
    'password': 'www-data',
    'db': 'awesome'
}
# Creating a connection is equivalent to passing key value pairs in the dictionary
# It is equivalent to executing pymysql.connect(host='localhost',user='www-data',password='www-data',db='awesome')
conn = pymysql.connect(**db_config)
# Create cursor
cursor = conn.cursor(pymysql.cursors.DictCursor)
sql = 'select * from users'
args = []
# Number of results returned by executing query
# Execute query
rs=cursor.execute(sql,args)
# Get query results
# Get the first result of the query and return a dict. The dict element is the key value pair corresponding to the query
# If there are multiple query results, execute once, move the cursor to the next data, and return another data after execution
# print(cursor.fetchone())
# print(cursor.fetchone())
# print(cursor.fetchall())
# print(cursor.fetchmany())
# {'id': '111111', 'email': 'test@qq.com', 'passwd': 'password', 'admin': 1, 'name': 'test', 'image': 'about:blank', 'created_at': 1637723578.5734}
# Get all the results of the query and return a list. The list element is dict, and the dict element is the key value pair corresponding to the query
# print(cursor.fetchall())
# [{'id': '111111', 'email': 'test@qq.com', 'passwd': 'password', 'admin': 1, 'name': 'test', 'image': 'about:blank', 'created_at': 1637723578.5734}]
# Get the first few results of the query and return a dict. The dict element is the key value pair corresponding to the query
# print(cursor.fetchmany(1))
# [{'id': '111111', 'email': 'test@qq.com', 'passwd': 'password', 'admin': 1, 'name': 'test', 'image': 'about:blank', 'created_at': 1637723578.5734}]
# Perform modification
import time
# # insert start
sql = 'insert into `users` (`email`, `passwd`, `admin`, `name`, `image`, `created_at`, `id`) values (?, ?, ?, ?, ?, ?, ?)'
args = ['test1@qq.com','password',1,'test','about:blank',time.time(),'1111121']
# Use replace to replace '?' Replace with '% s'
# rs = cursor.execute(sql.replace('?','%s'),args)
# print(cursor.rowcount)
# conn.commit()
# print(rs)
# insert end

# update start
# sql = 'update `users` set `email`=?, `passwd`=?, `admin`=?, `name`=?, `image`=?, `created_at`=? where `id`=?'
# args = ['test2@qq.com','password',1,'test2','about:blank',time.time(),'111111'] 
# print(cursor.execute(sql.replace('?','%s'),args))
# conn.commit()
# update end

# delete start
# sql = 'delete from `users` where `id`=?'
# args = ['111111'] 
# print(cursor.execute(sql.replace('?','%s'),args))
# conn.commit()
# delete end


# Written as a function call, the database connection object conn is used inside the function
# It can be defined as global variable first
def select(sql,args,size=None):
    
    cursor =  conn.cursor(pymysql.cursors.DictCursor)
    cursor.execute(sql.replace('?','%s'),args or ())
    if size:
        rs = cursor.fetchmany(size)
    else:
        rs = cursor.fetchall()
    cursor.close
    # logging.info('rows returned: %s' % len(rs))
    return rs
 
def execute(sql,args):
     
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    try:
        cursor.execute(sql.replace('?','%s'),args)
        # The rowcount method returns the influence function
        rs = cursor.rowcount
        cursor.close()
        conn.commit()
    except:
        raise
    return rs

start_time = time.time()
for n in range(20000):
    sql = 'insert into `users` (`email`, `passwd`, `admin`, `name`, `image`, `created_at`, `id`) values (?, ?, ?, ?, ?, ?, ?)'
    email = 'test%s@qq.com' %n
    args = [email,'password',1,'test','about:blank',time.time(),n] 
    execute(sql,args)
end_time = time.time()
# Difference between print start and end times
print(end_time - start_time)

We use a cycle of 20000 times to insert data into the database

Execute. There are too many inserted data and you need to wait for a period of time for output

D:\learn-python3\Functional programming>C:/Python37/python.exe d:/learn-python3/Learning script/pymysql/use_pymysql.py
77.46903562545776

The 20000 pieces of data can be queried in the database, and the field of the table is created_at stores the time stamp of creating this data. We can see that the later the id, the later the time stamp, indicating that the data is inserted one by one synchronously

We created according to the field_ At sort query

 

 

Let's delete all data and insert it asynchronously

The asynchronous code is as follows

D: / learn-python 3 / learning script / aiomysql/use_aiomysql.py

import asyncio,aiomysql,time
# Database configuration dict
db_config = {
    'host': 'localhost',
    'user': 'www-data',
    'password': 'www-data',
    'db': 'awesome'
}

# Create database connection pool coprocessor function
async def create_pool(**kw):
    global __pool
    __pool = await aiomysql.create_pool(
        host=kw.get('host', 'localhost'),
        port=kw.get('port', 3306),
        user=kw['user'],
        password=kw['password'],
        db=kw['db']
    )

loop=asyncio.get_event_loop()
loop.run_until_complete(create_pool(**db_config))
# The global variable is generated when the coprocessor function is run in the event loop__ Pool is a connection pool object < aiomysql. Pool. Pool object at 0x00000244ad1724c8 >
print(__pool)
# <aiomysql.pool.Pool object at 0x00000244AD1724C8>

# Execute the select function
async def select(sql,args,size=None):
    with await __pool as conn:
        cur = await conn.cursor(aiomysql.DictCursor)
        await cur.execute(sql.replace('?','?s'),args or ())
        if size:
            rs = await cur.fetchmany(size)
        else:
            rs = await cur.fetchall()
        await cur.close()
        return rs


# Execute the insert update delete function
async def execute(sql,args):
    with await __pool as conn:
        try:
            cur = await conn.cursor()
            await cur.execute(sql.replace('?','%s'),args)
            affetced = cur.rowcount
            await conn.commit()
            await cur.close()
        except BaseException as e:
            raise
        return affetced

# insert start
# import time
# sql = 'insert into `users` (`email`, `passwd`, `admin`, `name`, `image`, `created_at`, `id`) values (?, ?, ?, ?, ?, ?, ?)'
# args = ['test@qq.com','password',1,'test','about:blank',time.time(),'111111']
# async def insert():
#     await execute(sql,args)
# loop.run_until_complete(insert())
# insert end

# update start
# import time
# sql = 'update `users` set `email`=?, `passwd`=?, `admin`=?, `name`=?, `image`=?, `created_at`=? where `id`=?'
# args = ['test2@qq.com','password',1,'test2','about:blank',time.time(),'111111']
# loop.run_until_complete(execute(sql,args))

# update end

# delete start
# sql = 'delete from `users` where `id`=?'
# args = ['111111'] 
# loop.run_until_complete(execute(sql,args))
# delete end

# select start
# sql = 'select * from users'
# args = []
# rs = loop.run_until_complete(select(sql,args))
# print(rs)
# select end

async def insert1():
     for n in range(10000):
        sql = 'insert into `users` (`email`, `passwd`, `admin`, `name`, `image`, `created_at`, `id`) values (?, ?, ?, ?, ?, ?, ?)'
        email = 'test%s@qq.com' %n
        args = [email,'password',1,'test','about:blank',time.time(),n] 
        await execute(sql,args)

async def insert2():
     for n in range(10001,20001):
        sql = 'insert into `users` (`email`, `passwd`, `admin`, `name`, `image`, `created_at`, `id`) values (?, ?, ?, ?, ?, ?, ?)'
        email = 'test%s@qq.com' %n
        args = [email,'password',1,'test','about:blank',time.time(),n] 
        await execute(sql,args)

async def main():
    # It needs to be combined into one event to execute asynchronously, that is, insert2 is executed synchronously when insert1 is executed
    await asyncio.gather(insert1(),insert2())

start_time = time.time()
loop.run_until_complete(main())
end_time = time.time()
print(end_time - start_time)

Here, we define two coprocessor functions, which are used to insert the first 10000 data and the last 10000 data respectively. The two coprocessor functions are combined into an event loop in main()

After waiting for a period of time, the execution output is as follows. Ignoring this warning, you can see that the execution time is significantly shorter than the synchronization time

d:/learn-python3/Learning script/aiomysql/use_aiomysql.py:42: DeprecationWarning: with await pool as conn deprecated, useasync with pool.acquire() as conn instead
  with await __pool as conn:
39.794615507125854

Let's go to the database to query the data. We can also see that the id starts from 0 and the id starts from 10001 almost at the same time