Flask Sanic 数据库操作对比

Sanic

sanic 既然是使用了 io 异步,那么我们就使用 aiomysql 和 sqlalchemy 来对数据进行处理

获取 engine

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
from aiomysql.sa import create_engine

DB_SETTING = {
"user": "root",
"db": "sanic_demo",
"host": "127.0.0.1",
"password": "123456",
"autocommit": True
}


async def get_engine() -> object:
engine = await create_engine(
user=DB_SETTING.get("user"),
db=DB_SETTING.get("db"),
host=DB_SETTING.get("host"),
password=DB_SETTING.get("password"),
autocommit=DB_SETTING.get("autocommit")
)
return engine

创建model

创建一张叫做 sanic 的表,字段为 id 和 user_name。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
import sqlalchemy as sa
from sanic_part.model import get_engine

metadata = sa.MetaData()

tbl = sa.Table(
'sanic',
metadata,
sa.Column('id', sa.Integer),
sa.Column('user_name', sa.String(16))
)


async def insert_data(**data: dict) -> None:
engine = await get_engine()
async with engine.acquire() as conn:
await conn.execute(tbl.insert().values(data))

新增数据

在 POST 中定义一组数据,每次调用一次就会新增一组数据。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
class SimpleAsyncView(HTTPMethodView):
async def get(self, request):
return text('I am async get method')

async def post(self, request):
data = {
"id": "1",
"user_name": "test"
}
await insert_data(**data)
return text('I am async post method')

async def put(self, request):
return text('I am async put method')

测试

这里用压测来新增数据,其实是不规范的,不过这样也是测试的 aiomysql 的一种方式。

1
wrk -t4 -c10 -d10s -T10s --script=post.lua --latency http://127.0.0.1:8673/async
直接 Python 启动
1
2
3
4
5
6
7
8
9
10
11
12
13
14
192:wrk rex$ wrk -t4 -c10 -d10s -T10s --script=post.lua --latency http://127.0.0.1:8673/async
Running 10s test @ http://127.0.0.1:8673/async
4 threads and 10 connections
Thread Stats Avg Stdev Max +/- Stdev
Latency 13.81ms 4.78ms 71.24ms 77.99%
Req/Sec 146.06 12.73 190.00 61.25%
Latency Distribution
50% 13.10ms
75% 15.87ms
90% 19.13ms
99% 28.23ms
5832 requests in 10.02s, 803.04KB read
Requests/sec: 581.76
Transfer/sec: 80.11KB

Requests/sec 为:581.76

gunicorn 启动
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
192:wrk rex$ wrk -t4 -c10 -d10s -T10s --script=post.lua --latency http://127.0.0.1:8673/async
Running 10s test @ http://127.0.0.1:8673/async
4 threads and 10 connections
Thread Stats Avg Stdev Max +/- Stdev
Latency 63.26ms 54.39ms 215.45ms 77.59%
Req/Sec 34.98 33.90 120.00 74.66%
Latency Distribution
50% 26.45ms
75% 111.19ms
90% 149.55ms
99% 187.75ms
1325 requests in 10.04s, 10.95MB read
Non-2xx or 3xx responses: 1325
Requests/sec: 132.01
Transfer/sec: 1.09MB

Flask

初始化连接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
with app.app_context():
engine = create_engine(
"mysql+pymysql://root:[email protected]:3306/sanic_demo",
pool_pre_ping=True,
encoding='utf-8',
poolclass=NullPool,
convert_unicode=True,
echo=True
)

session_factory = sessionmaker(bind=engine)


@contextmanager
def session_scope():
session = session_factory()
try:
yield session
session.commit()
except Exception as e:
print(e)
session.rollback()
raise
finally:
pass

创建 model

1
2
3
4
5
6
7
8
from flask_part import Base
from sqlalchemy import Column, String


class User(Base):
__tablename__ = 'sanic'
id = Column(String(10), nullable=True, primary_key=True)
user_name = Column(String(10), nullable=True)

新增数据

1
2
3
4
5
6
7
8
9
10
11
@app.route("/save_data", methods=["POST"])
def post():
data = {
"id": "1",
"user_name": "test"
}
with session_scope() as session:
obj = User(**data)
session.add(obj)
session.commit()
return "I am flask post method"

测试

直接 Python 启动
1
2
3
4
5
6
7
8
9
10
11
12
13
14
192:wrk rex$ wrk -t4 -c10 -d10s -T10s --script=post.lua --latency http://127.0.0.1:8673/save_data
Running 10s test @ http://127.0.0.1:8673/save_data
4 threads and 10 connections
Thread Stats Avg Stdev Max +/- Stdev
Latency 25.36ms 4.72ms 50.67ms 71.32%
Req/Sec 78.24 9.29 101.00 69.00%
Latency Distribution
50% 24.58ms
75% 27.83ms
90% 31.76ms
99% 39.28ms
3145 requests in 10.07s, 540.55KB read
Requests/sec: 312.37
Transfer/sec: 53.69KB
gunicorn 启动