Untitled conversation
- User
from typing import Any, TypeVar, Dict from sqlalchemy.orm import ( Mapped, RelationshipProperty, mapped_column as column, Session, ) from sqlalchemy.sql.functions import now from sqlalchemy.exc import IntegrityError from utils.constants import TIME from utils.functions import camelToSnake from utils.db import Base T = TypeVar("T", bound="BaseModel") class BaseModel(Base): __abstract__: bool = True __allow_unmapped__: bool = True __tablename__: str created: Mapped[TIME] = column(server_default=now()) updated: Mapped[TIME] = column(server_default=now(), onupdate=now()) comments: Mapped[str | None] @classmethod def __init_subclass__(cls, **kwargs: Any) -> None: """ Create tables with names derived from the classes that originated them. """ super().__init_subclass__(**kwargs) # pyright: ignore[reportUnknownMemberType] cls.__tablename__ = camelToSnake(cls.__name__) @classmethod def _get_or_create_own( cls: type[T], session: Session, defaults: Dict[str, Any] | None = None, **kwargs: Any, ) -> tuple[T, bool]: """ Get or create an instance of the model. Returns a tuple of (instance, created), where `created` is a boolean indicating whether the instance was created. """ instance = session.query(cls).filter_by(**kwargs).first() if instance: return instance, False try: instance = cls(**kwargs, **(defaults or {})) session.add(instance) session.commit() return instance, True except IntegrityError: session.rollback() instance = session.query(cls).filter_by(**kwargs).first() if not instance: raise return instance, False @classmethod def get_or_create( cls: type[T], session: Session, data: Dict[str, Any], defaults: Dict[str, Any] | None = None, ) -> tuple[T, bool]: """ Recursively get or create an instance of the model and its nested relationships. Returns a tuple of (instance, created), where `created` is a boolean indicating whether the instance was created. """ # Separate relationship data from direct attributes attributes: Dict[str, Any] = {} relationships: Dict[str, Dict[str, Any]] = {} for key, value in data.items(): if isinstance(value, dict): # True if this is a relationship relationships[key] = value else: attributes[key] = value # Handle nested relationships first for rel_name, rel_data in relationships.items(): # Get the relationship property rel_prop: RelationshipProperty[Any] = getattr( cls, rel_name ).property related_cls = rel_prop.mapper.class_ # Recursively get or create the related object related_obj, _ = related_cls.get_or_create(session, rel_data) attributes[rel_name] = related_obj # Now get or create the main object return cls._get_or_create_own(session, defaults=defaults, **attributes)from sqlalchemy import ( ForeignKey as fk, UniqueConstraint as unique, ) from sqlalchemy.orm import ( Mapped, mapped_column as column, relationship, validates, ) from typing import Optional from utils.base_model import BaseModel from utils.constants import ( CNPJ, GENERIC_STRING as STRING, CEP, STATE, AddressType, ) from utils.functions import ( backref, childOf, defaultPrimaryKey as pk, digits, parentPrimaryKey as ppk, ) class Region(BaseModel): name: Mapped[STRING] = column(primary_key=True) # Relationships neighborhoods: Mapped[list["Neighborhood"]] = childOf("region") adjacent_regions: Mapped[list["Region"]] = relationship( secondary="adjacent_regions", primaryjoin="AdjacentRegions.left_id==Region.name", secondaryjoin="AdjacentRegions.right_id==Region.name", back_populates="adjacent_regions", ) class Neighborhood(BaseModel): id: Mapped[int] = pk() name: Mapped[STRING] = column() city: Mapped[STRING] = column() state: Mapped[STATE] # Foreign keys region_name: Mapped[STRING] = column(fk("region.name")) # Relationships region: Mapped["Region"] = backref("neighborhoods") addresses: Mapped[list["Address"]] = childOf("neighborhood") __table_args__: tuple[unique,] = (unique("name", "city", "state"),) class Address(BaseModel): id: Mapped[int] = pk() street: Mapped[STRING] number: Mapped[int | None] complement: Mapped[STRING | None] postal_code: Mapped[CEP] = column(digits("postal_code")) type: Mapped[AddressType] # Foreign keys neighborhood_id: Mapped[int] = column(fk("neighborhood.id")) # Relationships neighborhood: Mapped["Neighborhood"] = backref("addresses") facility: Mapped[Optional["Facility"]] = childOf("address") residents: Mapped[list["Person"]] = childOf("address") __table_args__: tuple[unique,] = ( unique("street", "number", "complement", "neighborhood_id"), ) class Facility(BaseModel): id: Mapped[int] = ppk("address") name: Mapped[str] active: Mapped[bool] = column(default=True) # Relations address: Mapped["Address"] = backref("facility") employees: Mapped[list["Employee"]] = relationship( back_populates="workplaces", secondary="workplace_employee" ) dispensations: Mapped[list["Dispensation"]] = backref("pharmacy") distributor: Mapped[Optional["Distributor"]] = childOf("facility") supply_requests: Mapped[list["SupplyRequest"]] = childOf("facility") stock: Mapped["Stock"] = childOf("facility") shipments_sent: Mapped[list["Shipment"]] = relationship( foreign_keys="Shipment.origin_id", back_populates="origin", ) shipments_received: Mapped[list["Shipment"]] = relationship( foreign_keys="Shipment.destination_id", back_populates="destination", ) @validates("address") def validate_address(self, key: Mapped["Address"], address: "Address"): if address and address.type == AddressType.Residence: raise ValueError( "Facility cannot reference an address of type 'Residence'" ) return address class Stock(BaseModel): medication_instance_id: Mapped[int] = ppk("medication_instance") facility_id: Mapped[int] = column(fk("facility.id")) # Relationships facility: Mapped["Facility"] = backref("stock") medication_instance: Mapped["MedicationInstance"] = backref("storage") class Distributor(BaseModel): id: Mapped[int] = ppk("facility") # Foreign keys supplier_id: Mapped[CNPJ] = column(fk("company.id")) # Relationships supplier: Mapped["Company"] = backref("distributors") facility: Mapped["Facility"] = backref("distributor")Imagine I use get_or_create with the Address class to recursively create a region, a neighborhood associated with it and then a address associated with that neighborhood. How are foreign keys being set to match the recursively created parent classes?
Assistant
Looking at your code, the foreign keys are being set correctly through the SQLAlchemy relationship system. Here's how it works:
When you call
get_or_createwith nested data for an Address, the process flows like this:- Data separation: The method separates relationship data (dictionaries) from direct attributes
- Relationship processing: For each relationship (e.g.,
neighborhood), it:- Gets the relationship property using
getattr(cls, rel_name).property - Extracts the related class from the relationship mapper
- Recursively calls
get_or_createon that related class
- Gets the relationship property using
- Object assignment: The created/retrieved related object is assigned to the attributes dictionary
Here's the key part where the magic happens:
# Recursively get or create the related object related_obj, _ = related_cls.get_or_create(session, rel_data) attributes[rel_name] = related_obj # This is where the relationship is setWhen you later create the main object with:
return cls._get_or_create_own(session, defaults=defaults, **attributes)SQLAlchemy automatically handles the foreign key assignment because:
-
Relationship definitions: Your models define relationships with proper back references:
# In Address neighborhood: Mapped["Neighborhood"] = backref("addresses") # In Neighborhood region: Mapped["Region"] = backref("neighborhoods") -
Foreign key columns: You've explicitly defined the foreign key columns:
# In Address neighborhood_id: Mapped[int] = column(fk("neighborhood.id")) # In Neighborhood region_name: Mapped[STRING] = column(fk("region.name")) -
SQLAlchemy's ORM behavior: When you assign a related object to a relationship attribute (e.g.,
address.neighborhood = neighborhood_obj), SQLAlchemy automatically:- Sets the foreign key value (
address.neighborhood_id = neighborhood_obj.id) - Maintains the bidirectional relationship (adds the address to
neighborhood.addresses)
- Sets the foreign key value (
So if you call:
address, created = Address.get_or_create(session, { "street": "Main St", "number": 123, "neighborhood": { "name": "Downtown", "city": "New York", "state": "NY", "region": { "name": "Manhattan" } } })The process would be:
- Create/get Region with name="Manhattan"
- Create/get Neighborhood with the region object assigned, which sets
region_name="Manhattan" - Create/get Address with the neighborhood object assigned, which sets
neighborhood_idto the neighborhood's id
This all happens transparently through SQLAlchemy's relationship management system.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.