3.sanic使用aiopg

把单条数据变为字典

import aiopg
import asyncio
from sanic import Sanic, json

app = Sanic(name='test')

@app.listener('before_server_start')
async def setup_db_redis(app):
    dsn = 'dbname=aiopg user=aiopg password=passwd host=127.0.0.1 port=5432'
    app.ctx.pg = await aiopg.create_pool(dsn, minsize=2, maxsize=5, pool_recycle=3)

@app.get('/test')
async def test(request):
    async with app.ctx.pg.acquire() as conn:
        async with conn.cursor() as cur:
            await cur.execute("SELECT * FROM public.test LIMIT 1;")
            res = cur.description  # 是一个对象,[Column(name='xxx', type_code=20),...]
            columns_name = [i.name for i in res]  # 获取字段名
            res = await cur.fetchall()
            res_dict = dict(zip(columns_name, res[0]))
    return json(res_dict)

app.run(host='127.0.0.1', port=8088, debug=True)

把多条数据变为字典

import aiopg
import asyncio
from sanic import Sanic, json

app = Sanic(name='test')

@app.listener('before_server_start')
async def setup_db_redis(app):
    dsn = 'dbname=aiopg user=aiopg password=passwd host=127.0.0.1 port=5432'
    app.ctx.pg = await aiopg.create_pool(dsn, minsize=2, maxsize=5, pool_recycle=3)

async def mresults(pool):
    async with pool.acquire() as conn:
        async with conn.cursor() as cur:
            # 多个
            await cur.execute("SELECT * FROM public.test LIMIT 100;")
            res = cur.description
            columns_name = [i[0] for i in res]  # 获取字段名
            res = await cur.fetchall()
            res_dict = [dict(zip(columns_name, row_value)) for row_value in res]
            # res_dict是列表套字典
            return res_dict

@app.get('/test100')
async def test10(request):
    tasks = []
    for i in range(100):
        task = asyncio.create_task(mresults(app.ctx.pg))
        tasks.append(task)
    res = await asyncio.gather(*tasks)
    # res是[ [{},{}...], [{},{}...], ... ]
    return json(res)

app.run(host='127.0.0.1', port=8088, debug=True)