时间戳

首页 > 解决方案 > Asyncpg 无法从数据库中获取数据

python - Asyncpg 无法从数据库中获取数据

问题描述

我正在使用 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] 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)

数据库文件是这样说的：

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 its own in trouble”

和 Pycharm：错误处理请求 asyncpg.exceptions._base.InterfaceError: pool is not initialized

Internet 上关于 asyncpg (sa) 的文章很少，因此如果您能帮助我解决问题，我将不胜感激。

我将添加我自己的代码。

主文件

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

应用程序.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)

网站.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

标签： pythonasyncpg

解决方案

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

postgres://user:pass@host:port/database?option=value是正确的格式。您忘记了用户名和密码。如果没有，也asyncpgsa.create_pool()应该等待。您不会收到错误消息，因为您只为变量分配了一个协程app['db']。所以连接池也没有创建。

您的第二个错误（来自site.py）是由未初始化连接池引起的。

您可以在此处的 asyncpg 文档中找到更多相关信息（因为 asyncpgsa 的连接池是基于 asyncpg 的连接池的）。

推荐阅读