[Python] 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

Continue reading...