How to access joined table values in a right way?

[DenysMoskalenko]

The problem is in accessing attributes after tables join.

So my models are next:

class Itinerary(Base):
    __tablename__ = 'itineraries'

    id = Column(Integer, primary_key=True)
    name = Column(String(128))
    bus_id = Column(Integer, ForeignKey('buses.id'), nullable=False)

class Bus(Base):
    __tablename__ = 'buses'

    id = Column(Integer, primary_key=True)
    name = Column(String(128), nullable=False)
    number_plate = Column(String(16), nullable=False, unique=True, index=True)

The Pydantic models:

class ItinerarySchema(BaseModel):
    id: inty
    name: str
    bus: BusSchema


class BusSchema(BaseModel):
    id: int
    name: str
    number_plate: str

Also the function to get itinerary:

async def get_itinerary_by_id(db: Database, itinerary_id: int) -> ItinerarySchema:
    query = select(Itinerary, Bus).join(Bus).filter(Itinerary.id == itinerary_id)
    itinerary = await db.fetch_one(query=query)

    bus = BusSchema(
        id=itinerary.id_1,  # <- HERE is the problem
        name=itinerary.name_1,  # <- HERE is the problem
        number_plate=itinerary.number_plate,
    )

    return ItinerarySchema(id=itinerary['id'], name=itinerary['name'], bus=bus)

It's works, but looks weird. Could you pls describe how I can access to the attributes in a more right way than *.id_1 and *.name_1

[berkio3x]

@DenysMoskalenko I guess, the concern of alias names for id and name etc.. is more related to the query builder , in this case sqlalchemy.