SQL炼金术|快速API。尝试在关联表中添加新行会出现 KeyError

Question

所以我有一个 fastAPI 项目，它使用 sqlalchemy 连接到 postgresql 数据库。 我使用 alembic 来构建数据库并对其进行版本控制。

我现在的问题是，一切都按照多对多关系的方式进行。所有股票都显示它们所属的投资组合，所有投资组合都显示它们所拥有的股票（这是通过在 PGAdmin 中手动输入数据进行测试的）。 现在的问题是，每次我尝试添加新连接（将股票添加到投资组合中）时，都会抛出“关键错误”。

这是 models.py：

class PortfolioStock(Base):
   __tablename__ = "portfolio_stocks"
   id = Column(Integer, primary_key=True)
   stock_id = Column(Integer, ForeignKey("stocks.id", ondelete="CASCADE"))
   portfolio_id = Column(Integer, ForeignKey("portfolios.id", ondelete="CASCADE"))
   count = Column(Integer, nullable=True)
   buy_in = Column(Float, nullable=True)
   stock = relationship("Stock", back_populates="portfolios")
   portfolio = relationship("Portfolio", back_populates="stocks")
   #a lot of these proxy associations here because of another bug I encountered
   #basically every column in the two models associated here have a association proxy row.(couldn't find another fix)
   stock_created_at = association_proxy(target_collection="stock", attr="created_at")
   portfolio_name = association_proxy(target_collection="portfolio", attr="name")
   def __init__(self, portfolio=None, stock=None, buy_in=None, count=None):
       self.portfolio = portfolio
       self.stock = stock
       self.buy_in = buy_in
       self.count = count


class Stock(Base):
   __tablename__ = "stocks"

   id = Column(Integer, primary_key=True, nullable=False)
   ...
   status = Column(Integer, nullable=False, server_default="0")
   portfolios = relationship("PortfolioStock", back_populates="stock")


class Portfolio(Base):
   __tablename__ = "portfolios"

   id = Column(Integer, primary_key=True, nullable=False)
   name = Column(String, nullable=False)
   user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False)
   monetary_goal = Column(Float, nullable=True)
   dividends_goal = Column(Float, nullable=True)
   created_at = Column(TIMESTAMP(timezone=True), nullable=False, server_default=text("now()"))
   stocks = relationship("PortfolioStock", back_populates="portfolio")

这里有一些片段展示了我在 fastapi 路由中所做的事情。 首先，我得到有问题的投资组合和传递到体内的股票。 我迭代股票并获取数据库中每只股票的相应条目。 最后，我尝试将这些股票添加到包含投资组合中所有模型的列表中。

portfolio = db.query(models.Portfolio).filter(models.Portfolio.id == portfolio_id).first()
   for stock in stock_ids:
        stock = db.query(models.Stock).filter(models.Stock.id == stock_id).first()
        portfolio.stocks.append(stock)
        setattr(stock, "status", "1")
    db.add(portfolio)
    db.commit()
    db.refresh(portfolio)
    return portfolio

这是当我尝试从邮递员调用此路线时收到的错误消息。

Traceback (most recent call last):
  File "/home/maxi/dev/captstone_venv/lib/python3.8/site-packages/uvicorn/protocols/http/httptools_impl.py", line 375, in run_asgi
    result = await app(self.scope, self.receive, self.send)
  File "/home/maxi/dev/captstone_venv/lib/python3.8/site-packages/uvicorn/middleware/proxy_headers.py", line 75, in __call__
    return await self.app(scope, receive, send)
  File "/home/maxi/dev/captstone_venv/lib/python3.8/site-packages/fastapi/applications.py", line 259, in __call__
    await super().__call__(scope, receive, send)
  File "/home/maxi/dev/captstone_venv/lib/python3.8/site-packages/starlette/applications.py", line 112, in __call__
    await self.middleware_stack(scope, receive, send)
  File "/home/maxi/dev/captstone_venv/lib/python3.8/site-packages/starlette/middleware/errors.py", line 181, in __call__
    raise exc
  File "/home/maxi/dev/captstone_venv/lib/python3.8/site-packages/starlette/middleware/errors.py", line 159, in __call__
    await self.app(scope, receive, _send)
  File "/home/maxi/dev/captstone_venv/lib/python3.8/site-packages/starlette/middleware/cors.py", line 84, in __call__
    await self.app(scope, receive, send)
  File "/home/maxi/dev/captstone_venv/lib/python3.8/site-packages/starlette/exceptions.py", line 82, in __call__
    raise exc
  File "/home/maxi/dev/captstone_venv/lib/python3.8/site-packages/starlette/exceptions.py", line 71, in __call__
    await self.app(scope, receive, sender)
  File "/home/maxi/dev/captstone_venv/lib/python3.8/site-packages/fastapi/middleware/asyncexitstack.py", line 21, in __call__
    raise e
  File "/home/maxi/dev/captstone_venv/lib/python3.8/site-packages/fastapi/middleware/asyncexitstack.py", line 18, in __call__
    await self.app(scope, receive, send)
  File "/home/maxi/dev/captstone_venv/lib/python3.8/site-packages/starlette/routing.py", line 656, in __call__
    await route.handle(scope, receive, send)
  File "/home/maxi/dev/captstone_venv/lib/python3.8/site-packages/starlette/routing.py", line 259, in handle
    await self.app(scope, receive, send)
  File "/home/maxi/dev/captstone_venv/lib/python3.8/site-packages/starlette/routing.py", line 61, in app
    response = await func(request)
  File "/home/maxi/dev/captstone_venv/lib/python3.8/site-packages/fastapi/routing.py", line 222, in app
    raw_response = await run_endpoint_function(dependant=dependant, values=values, is_coroutine=is_coroutine)
  File "/home/maxi/dev/captstone_venv/lib/python3.8/site-packages/fastapi/routing.py", line 159, in run_endpoint_function
    return await run_in_threadpool(dependant.call, **values)
  File "/home/maxi/dev/captstone_venv/lib/python3.8/site-packages/starlette/concurrency.py", line 39, in run_in_threadpool
    return await anyio.to_thread.run_sync(func, *args)
  File "/home/maxi/dev/captstone_venv/lib/python3.8/site-packages/anyio/to_thread.py", line 28, in run_sync
    return await get_asynclib().run_sync_in_worker_thread(func, *args, cancellable=cancellable,
  File "/home/maxi/dev/captstone_venv/lib/python3.8/site-packages/anyio/_backends/_asyncio.py", line 818, in run_sync_in_worker_thread
    return await future
  File "/home/maxi/dev/captstone_venv/lib/python3.8/site-packages/anyio/_backends/_asyncio.py", line 754, in run
    result = context.run(func, *args)
  File "/home/maxi/dev/./api/routers/stock.py", line 151, in add_stock_to_portfolio
    portfolio.stocks.append(stock)
  File "/home/maxi/dev/captstone_venv/lib/python3.8/site-packages/sqlalchemy/orm/collections.py", line 1169, in append
    item = __set(self, item, _sa_initiator)
  File "/home/maxi/dev/captstone_venv/lib/python3.8/site-packages/sqlalchemy/orm/collections.py", line 1134, in __set
    item = executor.fire_append_event(item, _sa_initiator)
  File "/home/maxi/dev/captstone_venv/lib/python3.8/site-packages/sqlalchemy/orm/collections.py", line 753, in fire_append_event
    return self.attr.fire_append_event(
  File "/home/maxi/dev/captstone_venv/lib/python3.8/site-packages/sqlalchemy/orm/attributes.py", line 1330, in fire_append_event
    value = fn(state, value, initiator or self._append_token)
  File "/home/maxi/dev/captstone_venv/lib/python3.8/site-packages/sqlalchemy/orm/attributes.py", line 1632, in emit_backref_from_collection_append_event
    child_impl = child_state.manager[key].impl
KeyError: 'portfolio' 

我已经尝试重命名所有关系，以找出错误到底来自哪里。 似乎 stock=relationship("Stock", back_populates="portfolios") 的关系是问题的罪魁祸首。

在调试模式下查看，我尝试访问的模型似乎没有正确的键，所以我的猜测是我要么使新的关联错误，并且它不是 portfolio.stocks.append( stock) （在我解决另一个问题之前就有效）或者关系是错误的/缺少某些东西来使其工作。

我真的非常感谢一些建议。

如果您需要更多信息，请告诉我。