关系()上的sqlalchemy + postgres asyncpg.exceptions.ForeignKeyViolationError

Question

到目前为止，在我的项目中，我正在制作原型，并使用sqlite和sqlalchemy。我有几个带有oneToMany关系的表，都运行得很好。现在，在切换到postgres之后，我获得了定义ForeignKeys的表的asyncpg.exceptions.ForeignKeyViolationError。

下面是我的sqlalchemy父模型和子模型：

from sqlalchemy.orm import relationship
from .database import Base
from fastapi_users_db_sqlalchemy import SQLAlchemyBaseUserTable


class Company(Base):
    __tablename__ = "company"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, unique=True)
    type = Column(
        String(length=10),
        server_default="No type given",
        nullable=False,
    )

    users = relationship("User", back_populates="company")

class User(Base, SQLAlchemyBaseUserTable):
    first_name = Column(
        String(length=50),
        index=True,
        server_default="No name given",
        nullable=False,
    )

    company_id = Column(Integer, ForeignKey("company.id"), nullable=False)
    company = relationship("Company", back_populates="users")

当尝试注册用户时，我得到：

INFO:     172.17.0.1:63534 - "POST /auth/register HTTP/1.1" 500 Internal Server Error
ERROR:    Exception in ASGI application
Traceback (most recent call last):
  File "/usr/lib/python3.9/site-packages/uvicorn/protocols/http/h11_impl.py", line 373, in run_asgi
    result = await app(self.scope, self.receive, self.send)
  File "/usr/lib/python3.9/site-packages/uvicorn/middleware/proxy_headers.py", line 75, in __call__
    return await self.app(scope, receive, send)
  File "/usr/lib/python3.9/site-packages/fastapi/applications.py", line 208, in __call__
    await super().__call__(scope, receive, send)
  File "/usr/lib/python3.9/site-packages/starlette/applications.py", line 112, in __call__
    await self.middleware_stack(scope, receive, send)
  File "/usr/lib/python3.9/site-packages/starlette/middleware/errors.py", line 181, in __call__
    raise exc
  File "/usr/lib/python3.9/site-packages/starlette/middleware/errors.py", line 159, in __call__
    await self.app(scope, receive, _send)
  File "/usr/lib/python3.9/site-packages/starlette/middleware/cors.py", line 92, in __call__
    await self.simple_response(scope, receive, send, request_headers=headers)
  File "/usr/lib/python3.9/site-packages/starlette/middleware/cors.py", line 147, in simple_response
    await self.app(scope, receive, send)
  File "/usr/lib/python3.9/site-packages/starlette/exceptions.py", line 82, in __call__
    raise exc
  File "/usr/lib/python3.9/site-packages/starlette/exceptions.py", line 71, in __call__
    await self.app(scope, receive, sender)
  File "/usr/lib/python3.9/site-packages/starlette/routing.py", line 656, in __call__
    await route.handle(scope, receive, send)
  File "/usr/lib/python3.9/site-packages/starlette/routing.py", line 259, in handle
    await self.app(scope, receive, send)
  File "/usr/lib/python3.9/site-packages/starlette/routing.py", line 61, in app
    response = await func(request)
  File "/usr/lib/python3.9/site-packages/fastapi/routing.py", line 226, in app
    raw_response = await run_endpoint_function(
  File "/usr/lib/python3.9/site-packages/fastapi/routing.py", line 159, in run_endpoint_function
    return await dependant.call(**values)
  File "/usr/lib/python3.9/site-packages/fastapi_users/router/register.py", line 32, in register
    created_user = await user_manager.create(user, safe=True, request=request)
  File "/usr/lib/python3.9/site-packages/fastapi_users/manager.py", line 153, in create
    created_user = await self.user_db.create(db_user)
  File "/usr/lib/python3.9/site-packages/fastapi_users_db_sqlalchemy/__init__.py", line 159, in create
    await self.database.execute(query, user_dict)
  File "/usr/lib/python3.9/site-packages/databases/core.py", line 169, in execute
    return await connection.execute(query, values)
  File "/usr/lib/python3.9/site-packages/databases/core.py", line 295, in execute
    return await self._connection.execute(built_query)
  File "/usr/lib/python3.9/site-packages/databases/backends/postgres.py", line 210, in execute
    return await self._connection.fetchval(query_str, *args)
  File "/usr/lib/python3.9/site-packages/asyncpg/connection.py", line 645, in fetchval
    data = await self._execute(query, args, 1, timeout)
  File "/usr/lib/python3.9/site-packages/asyncpg/connection.py", line 1659, in _execute
    result, _ = await self.__execute(
  File "/usr/lib/python3.9/site-packages/asyncpg/connection.py", line 1684, in __execute
    return await self._do_execute(
  File "/usr/lib/python3.9/site-packages/asyncpg/connection.py", line 1731, in _do_execute
    result = await executor(stmt, None)
  File "asyncpg/protocol/protocol.pyx", line 201, in bind_execute
asyncpg.exceptions.ForeignKeyViolationError: insert or update on table "user" violates foreign key constraint "user_company_id_fkey"
DETAIL:  Key (company_id)=(1) is not present in table "company".

错误是正确的，表company中没有列company_id -只有id。我将user-table中的列company_id定义为使用值company.id。为什么程序在company_id上检查

我遵循下面的sqlalchemy文档来定义这些关系：https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html它在sqlite中工作得很好。

我可以在回溯中看到，它连接到正确的后端(postgres)，那么这是否意味着sqlalchemy没有以正确的方式为postgres转换关系？

有什么办法解决这个问题吗？

Answer 1

解决了-对于任何也有这个问题的人：

postgres似乎不像sqlite那样重置id计数器。因为我在脚本中创建了测试数据，总是从1开始直到范围限制，所以我使用了不再存在的ids。

在我的例子中:我用来创建新用户的company_id显然已经不存在了。

这是我偶然发现的。这条错误消息完全误导了我。