redis 使用SQLAlchemy创建数据库表并运行Celery任务会引发“错误，状态为PGRES_TUPLES_OK，并且没有来自libpq的消息”

a11xaf1n  于 7个月前  发布在  Redis
关注(0)|答案(1)|浏览(117)

我有一个应用程序，每分钟运行2个任务。它们的代码几乎相同，除了它们与之通信的API端点（见下文）。运行任务时，我得到以下错误只有在我调用Base.metadata.create_all(engine)之前：

[2022-05-06 16:45:00,110: WARNING/ForkPoolWorker-2] Traceback (most recent call last):
[2022-05-06 16:45:00,110: WARNING/ForkPoolWorker-2] File "/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1799, in _execute_context
    self.dialect.do_execute(
[2022-05-06 16:45:00,110: WARNING/ForkPoolWorker-1] Traceback (most recent call last):
[2022-05-06 16:45:00,110: WARNING/ForkPoolWorker-2] File "/venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 717, in do_execute
    cursor.execute(statement, parameters)
[2022-05-06 16:45:00,111: WARNING/ForkPoolWorker-1] File "tasks.py", line 25, in run_logger_task
    accounts = db.get_accounts()
[2022-05-06 16:45:00,111: WARNING/ForkPoolWorker-2] **psycopg2.DatabaseError: error with status PGRES_TUPLES_OK and no message from the libpq**
[2022-05-06 16:45:00,111: WARNING/ForkPoolWorker-1] File "db_manager/crud.py", line 59, in get_accounts
    **return self.session.query(Account).all()**
[2022-05-06 16:45:00,111: WARNING/ForkPoolWorker-2] The above exception was the direct cause of the following exception:
[2022-05-06 16:45:00,111: WARNING/ForkPoolWorker-1] File "/venv/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 2711, in all
    return self._iter().all()
[2022-05-06 16:45:00,111: WARNING/ForkPoolWorker-2] Traceback (most recent call last):
[2022-05-06 16:45:00,111: WARNING/ForkPoolWorker-1] File "/venv/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 2846, in _iter
    result = self.session.execute(
[2022-05-06 16:45:00,111: WARNING/ForkPoolWorker-2] File "tasks.py", line 25, in run_logger_task
    accounts = db.get_accounts()  # TODO: encapsulate and move to crud?
[2022-05-06 16:45:00,111: WARNING/ForkPoolWorker-2] File "db_manager/crud.py", line 59, in get_accounts
    return self.session.query(Account).all()
[2022-05-06 16:45:00,111: WARNING/ForkPoolWorker-1] File "/venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 1692, in execute
    result = compile_state_cls.orm_setup_cursor_result(
[2022-05-06 16:45:00,112: WARNING/ForkPoolWorker-2] File "/venv/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 2711, in all
    return self._iter().all()
[2022-05-06 16:45:00,113: WARNING/ForkPoolWorker-1] File "/venv/lib/python3.8/site-packages/sqlalchemy/orm/context.py", line 333, in orm_setup_cursor_result
    return loading.instances(result, querycontext)
[2022-05-06 16:45:00,113: WARNING/ForkPoolWorker-2] File "/venv/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 2846, in _iter
    result = self.session.execute(
[2022-05-06 16:45:00,114: WARNING/ForkPoolWorker-1] File "/venv/lib/python3.8/site-packages/sqlalchemy/orm/loading.py", line 89, in instances
    cursor.close()
[2022-05-06 16:45:00,120: WARNING/ForkPoolWorker-2] File "/venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 1689, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
[2022-05-06 16:45:00,120: WARNING/ForkPoolWorker-1] File "/venv/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
[2022-05-06 16:45:00,120: WARNING/ForkPoolWorker-1] File "/venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
[2022-05-06 16:45:00,120: WARNING/ForkPoolWorker-2] File "/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1611, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
[2022-05-06 16:45:00,120: WARNING/ForkPoolWorker-1] File "/venv/lib/python3.8/site-packages/sqlalchemy/orm/loading.py", line 69, in instances
    *[
[2022-05-06 16:45:00,121: WARNING/ForkPoolWorker-2] File "/venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
    return connection._execute_clauseelement(
[2022-05-06 16:45:00,121: WARNING/ForkPoolWorker-1] File "/venv/lib/python3.8/site-packages/sqlalchemy/orm/loading.py", line 70, in <listcomp>
    query_entity.row_processor(context, cursor)
[2022-05-06 16:45:00,121: WARNING/ForkPoolWorker-2] File "/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1478, in _execute_clauseelement
    ret = self._execute_context(
[2022-05-06 16:45:00,121: WARNING/ForkPoolWorker-1] File "/venv/lib/python3.8/site-packages/sqlalchemy/orm/context.py", line 2484, in row_processor
    _instance = loading._instance_processor(
[2022-05-06 16:45:00,122: WARNING/ForkPoolWorker-2] File "/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1842, in _execute_context
    self._handle_dbapi_exception(
[2022-05-06 16:45:00,122: WARNING/ForkPoolWorker-1] File "/venv/lib/python3.8/site-packages/sqlalchemy/orm/loading.py", line 709, in _instance_processor
    primary_key_getter = result._tuple_getter(pk_cols)
[2022-05-06 16:45:00,122: WARNING/ForkPoolWorker-2] File "/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2023, in _handle_dbapi_exception
    util.raise_(
[2022-05-06 16:45:00,122: WARNING/ForkPoolWorker-1] File "/venv/lib/python3.8/site-packages/sqlalchemy/engine/result.py", line 901, in _tuple_getter
    return self._metadata._row_as_tuple_getter(keys)
[2022-05-06 16:45:00,122: WARNING/ForkPoolWorker-2] File "/venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
[2022-05-06 16:45:00,122: WARNING/ForkPoolWorker-1] File "/venv/lib/python3.8/site-packages/sqlalchemy/engine/result.py", line 106, in _row_as_tuple_getter
    indexes = self._indexes_for_keys(keys)
[2022-05-06 16:45:00,122: WARNING/ForkPoolWorker-1] **AttributeError: '_NoResultMetaData' object has no attribute '_indexes_for_keys'**
[2022-05-06 16:45:00,122: WARNING/ForkPoolWorker-2] File "/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1799, in _execute_context
    self.dialect.do_execute(
[2022-05-06 16:45:00,123: WARNING/ForkPoolWorker-2] File "/venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 717, in do_execute
    cursor.execute(statement, parameters)
[2022-05-06 16:45:00,123: WARNING/ForkPoolWorker-2] **sqlalchemy.exc.DatabaseError: (psycopg2.DatabaseError) error with status PGRES_TUPLES_OK and no message from the libpq
[SQL: SELECT account.id AS account_id, account.username AS account_username, account.password AS account_password, account.created_at AS account_created_at, account.updated_at AS account_updated_at 
FROM account]**
(Background on this error at: https://sqlalche.me/e/14/4xp6)

字符串
我的设置如下：
tasks.py

@app.task
def run_logger_task():
    try:
        session = SessionMaker()
        db = DBSession(session)
        accounts = db.get_accounts() # calls self.session.query(Account).all()
        for account in accounts:
        .
        .
        .
    finally:
        db.close()

型
database.py

SQLALCHEMY_DATABASE_URL = f"postgresql://{db_username}:{db_password}@{db_host}/{db_name}"
engine = create_engine(SQLALCHEMY_DATABASE_URL)
SessionMaker = sessionmaker(autocommit=False, autoflush=False, bind=engine)
**Base.metadata.create_all(engine)** # Commenting this, fixes the error. But running it should be safe even if db tables already exist

型
models.py

Base = declarative_base()

class Account(Base):

型
当我分别运行每个任务时，不会出现错误。

redis

1条答案

按热度按时间
mctunoxg

mctunoxg1#

我在使用FastAPI和SQLModel时遇到了类似的问题（SQLAlchemy在引擎盖下），评论中的解决方案确实对我有效。发布答案以获得更多可见性：
从这段代码开始，我在运行celery任务时遇到了一个错误。

sqlalchemy.exc.DatabaseError: (psycopg2.DatabaseError) error with status PGRES_TUPLES_OK and no message from the libpq

字符串
这是导致问题的代码。

from sqlmodel import Session, create_engine

from api import models

engine = create_engine(url="postgresql://postgres:secret@localhost/rssfeed", echo=True)

# Link user-defined SQL models
models.SQLModel.metadata.create_all(engine)  # type: ignore

# Gets db session through context manager
def get_session() -> Session:
    return Session(engine)

型
修复方法是简单地创建另一个引擎并将其传递到会话创建器。就这么简单！

from sqlmodel import Session, create_engine

from api import models

engine = create_engine(url="postgresql://postgres:secret@localhost/rssfeed", echo=True)
session_engine = create_engine(
    url="postgresql://postgres:secret@localhost/rssfeed", echo=True
)

# Link user-defined SQL models
models.SQLModel.metadata.create_all(engine)  # type: ignore

# Gets db session through context manager
def get_session() -> Session:
    return Session(session_engine)

型

赞(0）分享  回复(0）举报  7个月前
我来回答

相关问题

    查看更多
    微信公众号

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com