Asyncpg无法从数据库获取数据

Question

我正在使用aiohttp、asycpg和asyncpgsa编写我的API。我创建我的应用程序：

async def create_app():
    app = web.Application(client_max_size=1024 ** 2 * 70)

然后我执行以下代码行：

async def on_start(app):
    app['db'] = asyncpgsa.create_pool(dsn="postgresql://127.0.0.1:5432/backend")

async def on_shutdown(app):
    app['db'].close()

app.on_startup.append(on_start)
app.on_cleanup.append(on_shutdown)

一般来说，在我得到它的示例中，它是这样写的：

app['db'] = await asyncpgsa.create_pool(dsn="postgresql://127.0.0.1:5432/backend")

但是如果我这样写，就会抛出一个错误"ConnectionRefusedError: Errno 10061连接调用失败('127.0.0.1'，5432)“

但这也没关系。现在，当用户访问我需要的URL时，应该会触发此函数：

async def post(request):
    async with request.app["db"].acquire() as conn:
        query = select([datab.post])
        result = await conn.fetch(query)

datab文件是这样写的：

from sqlalchemy import Table, Text, VARCHAR, Integer, MetaData, Column

meta = MetaData()

post = Table(
    "post", meta,
    Column("id", Integer, primary_key=True),
    Column("title", VARCHAR, nullable=True),
    Column("body", Text))

但是当我转到我想要的URL时，站点提示我"500 Internal Server Error Server got itself in trouble“

和Pycharm:错误处理请求asyncpg.exceptions._base.InterfaceError:池未初始化

互联网上关于asyncpg (sa)的文章很少，所以如果你能帮助我解决这个问题，我将不胜感激。

我将添加我自己的代码。

main.py

from aiohttp import web
from demo import create_app
import argparse
import sqlalchemy
import asyncpgsa


async def post_handler(request):
    body1 = await request.json()
    print(body1)
    return web.json_response(data=body1, status=201)


def main():
    parser = argparse.ArgumentParser()
    parser.add_argument("--host", help="IPv4/IPv6 address API server would listen on", default="0.0.0.0")
    parser.add_argument('--port', help='TCP port API server would listen on', default=8080, type=int)
    args = parser.parse_args()

    app = create_app()
    web.run_app(app, host=args.host, port=args.port)


if __name__ == '__main__':
    main()

app.py

from aiohttp import web
import asyncpgsa
from .routes import setup_routes


async def on_start(app):
    app['db'] = asyncpgsa.create_pool(dsn="postgresql://127.0.0.1:5432/backendyandex")

async def on_shutdown(app):
    app['db'].close()


async def create_app():
    app = web.Application(client_max_size=1024 ** 2 * 70)
    setup_routes(app)
    app.on_startup.append(on_start)
    app.on_cleanup.append(on_shutdown)
    return app

如果你写

app['db'] = await asyncpgsa.create_pool(dsn="postgresql://127.0.0.1:5432/backend")

然后出现错误

unhandled exception during asyncio.run() shutdown
task: <Task finished coro=<_run_app() done, defined at C:\Users\lisgl\Desktop\PycharmProjects\BackendYandex\venv\lib\site-packages\aiohttp\web.py:287> exception=ConnectionRefusedError(10061, "Connect call failed ('127.0.0.1', 5432)")>
Traceback (most recent call last):
  File "C:\Users\lisgl\Desktop\PycharmProjects\BackendYandex\venv\lib\site-packages\aiohttp\web.py", line 508, in run_app
    loop.run_until_complete(main_task)
  File "C:\Users\lisgl\AppData\Local\Programs\Python\Python37\lib\asyncio\base_events.py", line 587, in run_until_complete
    return future.result()
  File "C:\Users\lisgl\Desktop\PycharmProjects\BackendYandex\venv\lib\site-packages\aiohttp\web.py", line 319, in _run_app
    await runner.setup()
  File "C:\Users\lisgl\Desktop\PycharmProjects\BackendYandex\venv\lib\site-packages\aiohttp\web_runner.py", line 275, in setup
    self._server = await self._make_server()
  File "C:\Users\lisgl\Desktop\PycharmProjects\BackendYandex\venv\lib\site-packages\aiohttp\web_runner.py", line 375, in _make_server
    await self._app.startup()
  File "C:\Users\lisgl\Desktop\PycharmProjects\BackendYandex\venv\lib\site-packages\aiohttp\web_app.py", line 416, in startup
    await self.on_startup.send(self)
  File "C:\Users\lisgl\Desktop\PycharmProjects\BackendYandex\venv\lib\site-packages\aiohttp\signals.py", line 34, in send
    await receiver(*args, **kwargs)  # type: ignore
  File "C:\Users\lisgl\Desktop\PycharmProjects\BackendYandex\demo\app.py", line 11, in on_start
    app['db'] = await asyncpgsa.create_pool(dsn="postgresql://127.0.0.1:5432/backendyandex")
  File "C:\Users\lisgl\Desktop\PycharmProjects\BackendYandex\venv\lib\site-packages\asyncpg\pool.py", line 407, in _async__init__
    await self._initialize()
  File "C:\Users\lisgl\Desktop\PycharmProjects\BackendYandex\venv\lib\site-packages\asyncpg\pool.py", line 435, in _initialize
    await first_ch.connect()
  File "C:\Users\lisgl\Desktop\PycharmProjects\BackendYandex\venv\lib\site-packages\asyncpg\pool.py", line 127, in connect
    self._con = await self._pool._get_new_connection()
  File "C:\Users\lisgl\Desktop\PycharmProjects\BackendYandex\venv\lib\site-packages\asyncpg\pool.py", line 482, in _get_new_connection
    **self._connect_kwargs)
  File "C:\Users\lisgl\Desktop\PycharmProjects\BackendYandex\venv\lib\site-packages\asyncpg\connection.py", line 1997, in connect
    max_cacheable_statement_size=max_cacheable_statement_size,
  File "C:\Users\lisgl\Desktop\PycharmProjects\BackendYandex\venv\lib\site-packages\asyncpg\connect_utils.py", line 677, in _connect
    raise last_error
  File "C:\Users\lisgl\Desktop\PycharmProjects\BackendYandex\venv\lib\site-packages\asyncpg\connect_utils.py", line 668, in _connect
    record_class=record_class,
  File "C:\Users\lisgl\Desktop\PycharmProjects\BackendYandex\venv\lib\site-packages\asyncpg\connect_utils.py", line 634, in _connect_addr
    tr, pr = await compat.wait_for(connector, timeout=timeout)
  File "C:\Users\lisgl\Desktop\PycharmProjects\BackendYandex\venv\lib\site-packages\asyncpg\compat.py", line 103, in wait_for
    return await asyncio.wait_for(fut, timeout)
  File "C:\Users\lisgl\AppData\Local\Programs\Python\Python37\lib\asyncio\tasks.py", line 442, in wait_for
    return fut.result()
  File "C:\Users\lisgl\Desktop\PycharmProjects\BackendYandex\venv\lib\site-packages\asyncpg\connect_utils.py", line 547, in _create_ssl_connection
    host, port)
  File "C:\Users\lisgl\AppData\Local\Programs\Python\Python37\lib\asyncio\base_events.py", line 962, in create_connection
    raise exceptions[0]
  File "C:\Users\lisgl\AppData\Local\Programs\Python\Python37\lib\asyncio\base_events.py", line 949, in create_connection
    await self.sock_connect(sock, address)
  File "C:\Users\lisgl\AppData\Local\Programs\Python\Python37\lib\asyncio\selector_events.py", line 473, in sock_connect
    return await fut
  File "C:\Users\lisgl\AppData\Local\Programs\Python\Python37\lib\asyncio\selector_events.py", line 503, in _sock_connect_cb
    raise OSError(err, f'Connect call failed {address}')
ConnectionRefusedError: [Errno 10061] Connect call failed ('127.0.0.1', 5432)

site.py

from aiohttp import web
from sqlalchemy import select
from . import datab


async def post(request):
    async with request.app["db"].acquire() as conn:
        query = select([datab.post])
        result = await conn.fetch(query)

    return web.Response(body=str(result))

如果您转到我需要的URL，下面是崩溃的错误：

Error handling request
Traceback (most recent call last):
  File "C:\Users\lisgl\Desktop\PycharmProjects\BackendYandex\venv\lib\site-packages\aiohttp\web_protocol.py", line 422, in _handle_request
    resp = await self._request_handler(request)
  File "C:\Users\lisgl\Desktop\PycharmProjects\BackendYandex\venv\lib\site-packages\aiohttp\web_app.py", line 499, in _handle
    resp = await handler(request)
  File "C:\Users\lisgl\Desktop\PycharmProjects\BackendYandex\demo\site.py", line 13, in post
    async with request.app["db"].acquire() as conn:
  File "C:\Users\lisgl\Desktop\PycharmProjects\BackendYandex\venv\lib\site-packages\asyncpg\pool.py", line 785, in __aenter__
    self.connection = await self.pool._acquire(self.timeout)
  File "C:\Users\lisgl\Desktop\PycharmProjects\BackendYandex\venv\lib\site-packages\asyncpg\pool.py", line 622, in _acquire
    self._check_init()
  File "C:\Users\lisgl\Desktop\PycharmProjects\BackendYandex\venv\lib\site-packages\asyncpg\pool.py", line 745, in _check_init
    raise exceptions.InterfaceError('pool is not initialized')
asyncpg.exceptions._base.InterfaceError: pool is not initialized

Answer 1

您的dsn不正确，因此无法连接到数据库。

postgres://user:pass@host:port/database?option=value是正确的格式。您忘记了用户和密码。如果不是，也应该等待asyncpgsa.create_pool()。您不会得到这个错误，因为您只将协程赋给了变量app['db']。因此也不会创建连接池。

您的第二个错误(来自site.py)是由于未初始化连接池而导致的。

更多信息可以在asyncpg here的文档中找到(因为asyncpgsa的连接池是基于asyncpg的连接池的)。