sqlmodel
sqlmodel copied to clipboard
tiangolo
How to create computed columns ?
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 Album(SQLModel):
title: str
slug: str
description: str
# what i've tried but doesn't work e.g :
# slug: str = column_property(slugify(title))
# E NameError: name 'title' is not defined
class Album(SQLModel):
title: str
slug: str = column_property(slugify(title))
description: str
Description
I'm trying to generate a column named "slug" that is a slugified version of "title" so it should be persisted in database, and be updated when title changes.
But so far no luck, I've looked at column property but didn't manage to make it work with SQLModel. I saw that there are events "before update" ... but I don't think this is the way to go
Operating System
Windows
Operating System Details
No response
SQLModel Version
0.0.4
Python Version
3.9.2
Additional Context
No response
I tried using @hybrid_property decorator from sqlalchemy, but it raises :
E fastapi.exceptions.FastAPIError: Invalid args for response field! Hint: check that <class 'sqlalchemy.ext.hybrid.hybrid_property'> is a valid pydantic field type
And if I set :
from pydantic import BaseConfig
BaseConfig.arbitrary_types_allowed = True
We now have :
ValueError: [TypeError("'wrapper_descriptor' object is not iterable"), TypeError('vars() argument must have __dict__ attribute')]
Hi @sorasful,
So basically you want the slug = slugify(title)?
To do so I would do the following:
from sqlmodel import SQLModel
from pydantic import root_validator
from typing import Optional
class Album(SQLModel):
title: str
slug: Optional[str]
description: str
@root_validator
def create_slug(cls, values):
title = values.get("title")
slugify_title = slugify(title)
values["slug"] = slugify_title
return values
The input:
{
"title": "My Album Title",
"description": "This is a description"
}
The output I got:
{
"title": "My Album Title",
"description": "This is a description",
"slug": "my-album-title"
}
@FabiEbert Hello, yeah that's what I ended up doing. But I don't feel like is the cleaner way you know. Also, if you want the slug to be updated each time you update the title you need to add this in your model
class Config:
validate_assignment = True
Any other thoughts on how to actually use the computed_property() from SQL alchemy? E.g.
class Parent(SQLModel):
....
child_count: Optional[int] = Field(sa_column=column_property(
select(func.count(Child.id)).where(Child.parent_id == id)
))
This results in in "column Parent.child_count does not exist."
Related to this issue https://github.com/tiangolo/sqlmodel/issues/240
I think this is one of biggest SQLModel drawbacks I've encountered.
I tried the root-validator approach (which needs validate_assignment=True in order to get the computer column properly updated), but the root validator method is sometime invoked with an incomplete dict (having the additional _sa_instance_state field, but without other required fields such as required_field1 in the example below).
I am trying to use the root validator in a 'mix-in' class, in order to calculate an hash-value on a subset of the model fields.
class ModelWithHashField(BaseModel):
final_hash: str = ""
@root_validator(pre=False,
skip_on_failure=True)
def calculate_global_hash(cls, values) -> Dict:
values["final_hash"] = sha256_hash_values(
values['required_field1'], values['required_field2']
)
return values
class MyModel(SQLModel, ModelWithHashField, table=True):
required_field1: str
required_field2: str
Pydantic v2.0 adds support for computed fields, which should allow computed columns once Pydantic V2 support is added (related to #532, #621)
This worked for me
@computed_field(return_type=str)
@declared_attr
def hello_world(self):
return column_property(
func.concat('hello ', "world")
)
but could not do a query
Update, this is working:
# from the Conversation table
@computed_field(return_type=Optional[bool])
@property
def has_any_unread_message(self):
if session := object_session(self):
return (
session.exec(
select(Message)
.where(Message.conversation_id == self.id)
.limit(1)
).first()
is not None
)
Workaround: Generating a dynamic property (this does NOT add a column to the database)
As @samidarko already mentioned, with pydantic 2.0's computed_field decorator, you can generate a dynamic property:
from sqlmodel import SQLModel
from pydantic import computed_field
from slugify import slugify
class Album(SQLModel):
title: str
description: str
@computed_field
@property
def slug(self) -> str:
return slugify(self.title)
Result:
>>> Album(title='A Title With Many Spaces', description='I am the description')
Album(title='A Title With Many Spaces', description='I am the description', slug='a-title-with-many-spaces')
Note that this solution computes the 'column' each time it is accessed and does not create a new column in the database. However, when disregarding efficiency considerations, I believe that this is a good workaround for many cases.
Application to other cases
You can replace the code in slug() with whatever your need is. You can even access relationship attributes:
class Article(SQLModel, table=True):
__tablename__ = "food_article"
id: int | None = Field(default=None, primary_key=True)
price_history: list["PriceHistory"] = Relationship()
@computed_field
@property
def unit_price_gross(self) -> float | None:
"""Returns the most recent price from self.price_history."""
if self.price_history:
prices_sorted = sorted(
self.price_history,
key=lambda item: item.valid_from,
reverse=True
)
current_price = prices_sorted[0].unit_price_gross
return current_price
else:
return None
@ErikFub in your example slug is available as a property on class but is not actually made as a column in the database table. Is that what you are seeing as well?
I believe OPs original question wanted the slug field/value to be persisted to the database.
I'm using the latest sqlmodel version 0.14 and pydantic 2.0.
@gavinest True that, I missed the part that it should persist in the database - thanks for pointing that out! I've adjusted my answer accordingly
