sqlmodel
sqlmodel copied to clipboard
tiangolo
Example: Understanding inheritance and relationships between model classes
First Check
- [X] I added a very descriptive title to this issue.
- [X] I used the GitHub search to find a similar issue and didn't find it.
- [X] I searched the SQLModel documentation, with the integrated search.
- [X] I already searched in Google "How to X in SQLModel" and didn't find any information.
- [X] I already read and followed all the tutorial in the docs and didn't find an answer.
- [X] I already checked if it is not related to SQLModel but to Pydantic.
- [X] I already checked if it is not related to SQLModel but to SQLAlchemy.
Commit to Help
- [X] I commit to help with one of those options 👆
Example Code
class Contract(SQLModel):
"""A contract defines the business conditions of a project"""
title: str = Field(description="Short description of the contract.")
client: Client = Relationship(
back_populates="contracts",
)
# Contract n:1 Client
client_id: Optional[int] = Field(
default=None,
foreign_key="client.id",
)
currency: str
term_of_payment: Optional[int] = Field(
description="How many days after receipt of invoice this invoice is due.",
default=31,
)
class TimeContract(Contract, table=True):
"""A time-based contract with a rate per time unit"""
id: Optional[int] = Field(default=None, primary_key=True)
rate: condecimal(decimal_places=2) = Field(
description="Rate of remuneration",
)
unit: TimeUnit = Field(
description="Unit of time tracked. The rate applies to this unit.",
sa_column=sqlalchemy.Column(sqlalchemy.Enum(TimeUnit)),
default=TimeUnit.hour,
)
class WorksContract(Contract, table=True):
"""A contract with a fixed price"""
id: Optional[int] = Field(default=None, primary_key=True)
price: condecimal(decimal_places=2) = Field(
description="Price of the contract",
)
deliverable: str = Field(description="Description of the deliverable")
class Client(SQLModel, table=True):
"""A client the freelancer has contracted with."""
id: Optional[int] = Field(default=None, primary_key=True)
name: str
contracts: List["Contract"] = Relationship(back_populates="client")
Description
I would like to understand inheritance better using the following example:
- There are two types of contracts, a time-based contract with a rate per time unit, and a works contract with a fixed price.
- Every contract is related to a client.
The code example is my first attempt at implementing this. However, It is not yet correct:
InvalidRequestError: One or more mappers failed to initialize - can't proceed with initialization of other mappers. Triggering mapper: 'mapped class Client->client'. Original exception was: When initializing mapper mapped class Client->client, expression 'Contract' failed to locate a name ('Contract'). If this is a class name, consider adding this relationship() to the <class 'tuttle.model.Client'> class after both dependent classes have been defined.
Questions:
- Every
Contractis related to a client. That makes me thinkclientshould be a member of theContractbase class. However, I don't understand how to properly implement the relationship. Is it necessary to moveclientto the table classes (thereby duplicating it)? - When using inheritance from a model class, can I still
selectfromContract(rather than from the different contract types separately)?
Operating System
macOS, Other
Operating System Details
No response
SQLModel Version
0.0.8
Python Version
3.10
Additional Context
No response
You need to use mapping (https://docs.sqlalchemy.org/en/14/orm/inheritance.html) to achieve this, but it's a bit tricky with sqlmodel.
- If you inherit from the SQLModel class with table=True you need to explicitly add a mapping to the registry.
- I still don't know how to prevent sqlmodel from creating the fields of the parent table in the child table.
Example of working code:
from typing import Optional, List
from sqlmodel import SQLModel, Field, Relationship, create_engine, Session
from sqlalchemy.orm import registry, with_polymorphic
mapper_registry = registry()
class Contract(SQLModel, table=True):
"""A contract defines the business conditions of a project"""
id: Optional[int] = Field(default=None, primary_key=True)
title: str = Field(description="Short description of the contract.")
client: "Client" = Relationship(
back_populates="contracts",
)
client_id: Optional[int] = Field(
default=None,
foreign_key="client.id",
)
currency: str
term_of_payment: Optional[int] = Field(
description="How many days after receipt of invoice this invoice is due.",
default=31,
)
contact_type: str
__mapper_args__ = {
"polymorphic_identity": "contract",
"polymorphic_on": "contact_type",
}
@mapper_registry.mapped
class TimeContract(Contract, table=True):
"""A time-based contract with a rate per time unit"""
contract_id: Optional[int] = Field(
default=None, foreign_key="contract.id", primary_key=True
)
rate: float = Field(
description="Rate of remuneration",
)
unit: str = Field(
description="Unit of time tracked. The rate applies to this unit.",
default="hour",
)
__mapper_args__ = {
"polymorphic_identity": "time",
}
@mapper_registry.mapped
class WorksContract(Contract, table=True):
"""A contract with a fixed price"""
contract_id: Optional[int] = Field(
default=None, foreign_key="contract.id", primary_key=True
)
price: float = Field(
description="Price of the contract",
)
deliverable: str = Field(description="Description of the deliverable")
__mapper_args__ = {
"polymorphic_identity": "works",
}
class Client(SQLModel, table=True):
"""A client the freelancer has contracted with."""
id: Optional[int] = Field(default=None, primary_key=True)
name: str
contracts: List["Contract"] = Relationship(back_populates="client")
engine = create_engine(
"sqlite:///",
# echo=True,
)
with Session(engine) as session:
SQLModel.metadata.create_all(engine)
c = Client()
c.name = "client name"
c.contracts = [
TimeContract(currency=12.2, title="title1", term_of_payment=1, rate=3.4),
WorksContract(
title="title2",
currency=43.4,
term_of_payment=6,
price=344.2,
deliverable="---",
),
TimeContract(currency=13.2, title="title3", term_of_payment=12, rate=56.4),
]
session.add(c)
session.commit()
session.refresh(c)
contract_manager = with_polymorphic(Contract, [TimeContract, WorksContract])
query = session.query(contract_manager)
for i in query.all():
print(i)
Edit:
Maybe we can fix the problem mentioned in 2 with this:
(main.py:292)
for k, v in new_cls.__fields__.items():
if isinstance(getattr(new_cls, k, None), InstrumentedAttribute):
continue
col = get_column_from_field(v)
Thanks @meirdev for the code example, I will try that.
I still don't know how to prevent sqlmodel from creating the fields of the parent table in the child table.
If I do not care about minimizing the size of the database, is there still a problem with this?
Would it be too much to ask for a framework like SQLModel to abstract away these parts of the code so that it's straightforward to use inheritance in models? Perhaps some black Python magic could add this in the background when subclasses are created.
__mapper_args__ = {
"polymorphic_identity": "time",
}
contract_manager = with_polymorphic(Contract, [TimeContract, WorksContract])
@mapper_registry.mapped
From a user perspective, querying by Contract base class rather than contract_manager is more intuitive. Could this be a direction for an enhancement? @tiangolo
If I do not care about minimizing the size of the database, is there still a problem with this?
I think this is ok, and it looks like sqlalchemy updates both tables each time.
t = session.get(TimeContract, 1)
t.title = "new title"
session.add(t)
session.commit()
INFO sqlalchemy.engine.Engine UPDATE contract SET title=? WHERE contract.id = ?
INFO sqlalchemy.engine.Engine [generated in 0.00010s] ('new title', 1)
INFO sqlalchemy.engine.Engine UPDATE timecontract SET title=? WHERE timecontract.id = ? AND timecontract.contract_id = ?
INFO sqlalchemy.engine.Engine [generated in 0.00005s] ('new title', 1, 1)
All these configurations give you flexibility to manipulate and query your data, for example: if you want to know how much contract time you have, you don't need to use with_polymorphic:
session.query(TimeContract.id).count()
2. I still don't know how to prevent sqlmodel from creating the fields of the parent table in the child table.
SQLModel thinks the fields of the parent table are in the child table. This currently makes it unusable to use with joined table inheritance.
This workaround proposed here with mapper_registry = registry() does not work for me.
Ideally, joined table inheritance should work out of the box in SQLModel. In the meantime, does anyone have a fully working example?
Hello, Is there anything new on this topic since then? The proposed code fails with sqlmodel 0.0.16 (using pydantic 2.6.4 and sqlalchemy 2.0.28). Is there another approach that is more SQLmodel-oriented? Fred
Is this really an inheritance issue? Looks more like an import issue.
I'm having the same issue, but when I merge all models in one module, then problem is gone.
The problem is I got many models with lots of code, so it's better to keep them separate. But to fix the circular import issue, I used the proposed workaround to use TYPE_CHEKING from the documents.
It fixes the circular import error, but seems like is causing this problem.
I would like to suggest an enhancement for SQLModel to support single table inheritance, similar to what is available in SQLAlchemy. This feature would allow us to define a class hierarchy on a single database table, which can be extremely useful for scenarios where different types of entities share common attributes but also have their unique fields. It would greatly enhance the flexibility and power of SQLModel. Here's the SQLAlchemy documentation for reference: Single Table Inheritance.
