SQLAlchemy 2.0与外键和关联的问题

huangapple
go评论104阅读模式
英文:

SQLAlchemy 2.0 issue with foreign keys and relationships

问题

我正在使用异步的Postgres会话，并遇到了在我的两个表之间插入和提取数据的问题。

顾客表是一个独立的表，与其他表没有关系。这个表会被另一个插入操作独立更新。

对于报价表，我需要能够插入具有相应顾客ID的报价（可以是唯一的顾客号码或顾客表中的id列）。在选择数据时，我希望连接到顾客表，并像下面这样提取嵌套数据。

预期的报价表中的JSON插入

  1. {
  2.   "origin": "Ney York City",
  3.   "destination": "Houston",
  4.   "unique_account_number": "A9457HDA"
  5. }

从报价表的选择语句中预期的JSON

  1. {
  2.   "origin": "string",
  3.   "destination": "string",
  4.   "customer": { 
  5.     "unique_account_number": "ABCD1234",
  6.     "customer_name": "Customer LLC"
  7.   }
  8. }

我如何通过使用ORM来实现这一点？

英文:

I am using async postgres session and running into an issue with inserting and pulling data between 2 of my tables.

The customers table is a stand alone table that doesnt have a relationship with other tables. THis table gets updated independently by another insert.

For the quotes table, I need to be able to insert the quote with the corresponding customer ID (either the unique customer number or the id column in the customer table). When selecting data, I want to join to the customers table and pull the data back nested like the below.

  1. class Customers(Base):
  2.     __tablename__ = "customers"
  3.     __table_args__ = (UniqueConstraint("unique_account_number"),)
  4.     id = mapped_column(Integer, primary_key=True)
  5.     customer_name: Mapped[str]
  6.     unique_account_number: Mapped[str]
  7.     updated_datetime = mapped_column(
  8.         DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now
  9.     )
  10.     created_datetime = mapped_column(DateTime, default=datetime.datetime.now)
  13. class Quotes(Base):
  14.     __tablename__ = "quotes"
  16.     id = mapped_column(Integer, primary_key=True)
  17.     origin: Mapped[str]
  18.     destination: Mapped[str]
  19.     customer = relationship(Customers, foreign_key[Customers.unique_account_number])
  20.     updated_datetime = mapped_column(
  21.         DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now
  22.     )
  23.     created_datetime = mapped_column(DateTime, default=datetime.datetime.now)

Expected json insert into the quotes table

  1. {
  2.   "origin": "Ney York City",
  3.   "destination": "Houston",
  4.   "unique_account_number": "A9457HDA"
  5. }

expected json from a select statement on the quotes table.

  1. {
  2.   "origin": "string",
  3.   "destination": "string",
  4.   "customer": { 
  5.     "unique_account_number": "ABCD1234",
  6.     "customer_name": "Customer LLC"
  8. }

How can I achieve this through using the ORM?

答案1

得分: 1

以下是代码的翻译部分：

  1. import sys
  2. import asyncio
  3. import datetime
  4. import json
  6. from sqlalchemy import MetaData, select, UniqueConstraint, Integer, DateTime
  7. from sqlalchemy.sql import update, func
  9. from sqlalchemy.orm import DeclarativeBase, mapped_column, Mapped, relationship, joinedload
  10. from sqlalchemy.ext.asyncio import AsyncAttrs, async_sessionmaker, AsyncSession, create_async_engine
  13. class Base(AsyncAttrs, DeclarativeBase):
  14.     pass
  17. class Customers(Base):
  18.     __tablename__ = "customers"
  19.     __table_args__ = (UniqueConstraint("unique_account_number"),)
  20.     id = mapped_column(Integer, primary_key=True)
  21.     customer_name: Mapped[str]
  22.     unique_account_number: Mapped[str]
  23.     updated_datetime = mapped_column(
  24.         DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now
  25.     )
  26.     created_datetime = mapped_column(DateTime, default=datetime.datetime.now)
  29. class Quotes(Base):
  30.     __tablename__ = "quotes"
  32.     id = mapped_column(Integer, primary_key=True)
  33.     origin: Mapped[str]
  34.     destination: Mapped[str]
  35.     unique_account_number: Mapped[str]
  36.     customer = relationship(Customers,
  37.                             primaryjoin="Quotes.unique_account_number==Customers.unique_account_number",
  38.                             uselist=False,
  39.                             foreign_keys=[Customers.unique_account_number])
  40.     updated_datetime = mapped_column(
  41.         DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now
  42.     )
  43.     created_datetime = mapped_column(DateTime, default=datetime.datetime.now)
  46. async def insert_objects(async_session: async_sessionmaker[AsyncSession]) -> None:
  47.     async with async_session() as session:
  48.         async with session.begin():
  49.             session.add(Quotes(
  50.                 origin="New York City",
  51.                 destination="Houston",
  52.                 unique_account_number="A9457HDA"))
  53.             session.add(Customers(
  54.                 customer_name="Customer LLC",
  55.                 unique_account_number="A9457HDA"))
  58. async def select_objects(async_session: async_sessionmaker[AsyncSession]) -> None:
  59.     async with async_session() as session:
  60.         async with session.begin():
  61.             stmt = select(Quotes).options(joinedload(Quotes.customer))
  62.             quotes = (await session.execute(stmt)).unique().scalars().all()
  63.             return quotes
  66. def get_engine():
  67.     username, password, db = sys.argv[1:4]
  68.     return create_async_engine(
  69.         f"postgresql+asyncpg://{username}:{password}@/{db}",
  70.         echo=True,
  71.     )
  74. def serialize_quote(quote):
  75.     customer_dict = {attr: getattr(quote.customer, attr) for attr in ["unique_account_number", "customer_name"]}
  76.     quote_dict = {attr: getattr(quote, attr) for attr in ["origin", "destination"]}
  77.     quote_dict["customer"] = customer_dict
  78.     return quote_dict
  81. async def async_main() -> None:
  83.     engine = get_engine()
  85.     async_session = async_sessionmaker(engine, expire_on_commit=False)
  87.     async with engine.begin() as conn:
  88.         await conn.run_sync(Base.metadata.create_all)
  90.     await insert_objects(async_session)
  92.     quotes = (await select_objects(async_session))
  93.     print([json.dumps(serialize_quote(quote)) for quote in quotes])
  95.     await engine.dispose()
  98. asyncio.run(async_main())

注意：代码中的注释和变量名称等信息未被翻译，只有代码的文本被翻译成中文。

英文: 
  1. import sys
  2. import asyncio
  3. import datetime
  4. import json
  6. from sqlalchemy import MetaData, select, UniqueConstraint, Integer, DateTime
  7. from sqlalchemy.sql import update, func
  9. from sqlalchemy.orm import DeclarativeBase, mapped_column, Mapped, relationship, joinedload
  10. from sqlalchemy.ext.asyncio import AsyncAttrs, async_sessionmaker, AsyncSession, create_async_engine
  13. class Base(AsyncAttrs, DeclarativeBase):
  14.     pass
  17. class Customers(Base):
  18.     __tablename__ = "customers"
  19.     __table_args__ = (UniqueConstraint("unique_account_number"),)
  20.     id = mapped_column(Integer, primary_key=True)
  21.     customer_name: Mapped[str]
  22.     unique_account_number: Mapped[str]
  23.     updated_datetime = mapped_column(
  24.         DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now
  25.     )
  26.     created_datetime = mapped_column(DateTime, default=datetime.datetime.now)
  29. class Quotes(Base):
  30.     __tablename__ = "quotes"
  32.     id = mapped_column(Integer, primary_key=True)
  33.     origin: Mapped[str]
  34.     destination: Mapped[str]
  35.     unique_account_number: Mapped[str]
  36.     customer = relationship(Customers,
  37.                             primaryjoin="Quotes.unique_account_number==Customers.unique_account_number",
  38.                             uselist=False,
  39.                             foreign_keys=[Customers.unique_account_number])
  40.     updated_datetime = mapped_column(
  41.         DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now
  42.     )
  43.     created_datetime = mapped_column(DateTime, default=datetime.datetime.now)
  47. async def insert_objects(async_session: async_sessionmaker[AsyncSession]) -> None:
  48.     async with async_session() as session:
  49.         async with session.begin():
  50.             session.add(Quotes(
  51.                 origin="Ney York City",
  52.                 destination="Houston",
  53.                 unique_account_number="A9457HDA"))
  54.             session.add(Customers(
  55.                 customer_name="Customer LLC",
  56.                 unique_account_number="A9457HDA"))
  58. async def select_objects(async_session: async_sessionmaker[AsyncSession]) -> None:
  59.     async with async_session() as session:
  60.         async with session.begin():
  61.             stmt = select(Quotes).options(joinedload(Quotes.customer))
  62.             quotes = (await session.execute(stmt)).unique().scalars().all()
  63.             return quotes
  66. def get_engine():
  67.     username, password, db = sys.argv[1:4]
  68.     return create_async_engine(
  69.         f"postgresql+asyncpg://{username}:{password}@/{db}",
  70.         echo=True,
  71.     )
  74. def serialize_quote(quote):
  75.     customer_dict = {attr: getattr(quote.customer, attr) for attr in ["unique_account_number", "customer_name"]}
  76.     quote_dict = {attr: getattr(quote, attr) for attr in ["origin", "destination"]}
  77.     quote_dict["customer"] = customer_dict
  78.     return quote_dict
  80. async def async_main() -> None:
  82.     engine = get_engine()
  84.     async_session = async_sessionmaker(engine, expire_on_commit=False)
  86.     async with engine.begin() as conn:
  87.         await conn.run_sync(Base.metadata.create_all)
  89.     await insert_objects(async_session)
  91.     quotes = (await select_objects(async_session))
  92.     print ([json.dumps(serialize_quote(quote)) for quote in quotes])
  94.     await engine.dispose()
  97. asyncio.run(async_main())
  99. ``
  102. </details>

huangapple
  • 本文由 发表于 2023年6月29日 02:10:12
  • 转载请务必保留本文链接：https://go.coder-hub.com/76575716.html
  • orm
  • postgresql
  • python
  • sql
  • sqlalchemy
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定