Ошибка при обработке запроса в asyncpg: pool is not initialized

Question

Пишу свой API, используя aiohttp, asycpg и asyncpgsa. Создаю свое приложение:

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] Connect call failed ('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:

Error handling request asyncpg.exceptions._base.InterfaceError: pool is not initialized

В интернете очень мало написано про 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

В доке asyncpg

app['pool'] = await asyncpg.create_pool(database='postgres',
                                            user='postgres')

В коде asyncpgsa

@wraps(asyncpg.create_pool)
def create_pool(*args,
                dialect=None,
                connection_class=_SAConnection,
                **connect_kwargs):

 ......
    pool = asyncpg.create_pool(*args, connection_class=connection_class,
                               **connect_kwargs)
    return pool

Тоесть Вам нужно всётаки авэйтнуть пул

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

А сообщение

 ConnectionRefusedError: [Errno 10061] Connect call failed ('127.0.0.1', 5432)

говорит о том что постгрес не запущен на этом адресе

БЛОГ НА HUSL

Ошибка при обработке запроса в asyncpg: pool is not initialized

Ответы (1 шт):