pythonasyncpg

Asyncpg cannot fetch data from the database

I am writing my API using aiohttp, asycpg and asyncpgsa. I create my application:

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

Then I execute these lines:

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)

In general, in the example where I got it from, it is written like this:

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

But if I write like this, then an error is thrown "ConnectionRefusedError: [Errno 10061] Connect call failed ('127.0.0.1', 5432)"

But that's okay. Now, when the user visits the URL I need, this function should be triggered:

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

The datab file says this:

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))

But when I go to the URL I want, the site gives me "500 Internal Server Error Server got itself in trouble "

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

Very little has been written on the Internet about asyncpg (sa), so I will be immensely grateful if you can help me fix the problem.

I'll add my own code.

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

If you write

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

Then an error occurs

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))

And here is the error that crashes if you go to the URL I need:

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
Solution

  • Your dsn is incorrect, so you can't connect to your database.

    postgres://user:pass@host:port/database?option=value is the correct format. You forgot the user and the password. Also asyncpgsa.create_pool() should be awaited, if not. You don't get the error, because you only assign a coroutine to the variable app['db']. So the connection pool is also not created.

    Your second error (from site.py) is caused by not initizalising the connection pool.

    More about that you can find in the documentation of asyncpg here (because asyncpgsa's connection pool is based on asyncpg's connection pool).