sqlmodel
sqlmodel copied to clipboard
tiangolo
Using hybrid_property of SQLAlchemy leads to CompileError
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
from sqlalchemy.ext.hybrid import hybrid_property
from typing import Optional
from sqlmodel import SQLModel, Field, create_engine
from datetime import datetime
class ObjectTable(SQLModel, table=True):
object_id: Optional[int] = Field(primary_key=True, default=None)
first_detection_time: datetime = Field(index=True)
last_detection_time: datetime = Field(index=True)
@hybrid_property
def detection_time(self) -> float:
return (self.last_detection_time - self.first_detection_time).total_seconds()
class Config:
arbitrary_types_allowed = True
if __name__ == "__main__":
engine = create_engine("sqlite:///database.db")
SQLModel.metadata.create_all(engine)
Description
I am trying to create a hybrid property in an SQLModel class to allow more complex querying. Following the steps as described in the sqlalchemy docs here: https://docs.sqlalchemy.org/en/14/orm/extensions/hybrid.html I assumed that this would work and create a valid table. However, this code gives the error:
sqlalchemy.exc.CompileError: (in table 'objecttable', column detection_time'): Can't generate DDL for NullType(); did you forget to specify a type on this Column?
At first, I assumed that a type hint was missing so I added the float return type to the hybrid_property. I am not sure what the problem is exactly but I assumed that this code would yield a valid table.
Operating System
Linux
Operating System Details
Ubuntu 20.04
SQLModel Version
0.0.6
Python Version
Python 3.8.10
Additional Context
No response
Nope. @tiangolo that would be really cool if you can take a look into it.
Any update regarding this issue?
My model:
class Model(SQLModel: table=True):
active_until: Optional[datetime.datetime]
class Config:
arbitrary_types_allowed = True
@hybrid_property
def is_active(self) -> bool:
return self.active_until is not None and self.active_until > datetime.datetime.now()
On 0.0.8 I've got an error:
... python3.10/site-packages/sqlmodel/main.py", line 414, in get_sqlachemy_type
raise ValueError(f"The field {field.name} has no matching SQLAlchemy type")
ValueError: The field is_active has no matching SQLAlchemy type
I also struggled with column_property and hybrid_property. Am now using workaround where just call custom code to add properties in code to my "read" objects.
Am also using normal Python properties where suitable, actually, wouldn't that is_active work as a plain @property?
@tiangolo Bro is there any problem in pull request if it be merged it would really help :)
I found a somewhat simple workaround: annotate the property as a class var (which pydantic will ignore)
from typing import ClassVar, Optional
from datetime import datetime
from sqlalchemy.ext.hybrid import hybrid_property
from sqlmodel import SQLModel, Field, create_engine
def _detection_time(self) -> float:
return (self.last_detection_time - self.first_detection_time).total_seconds()
class ObjectTable(SQLModel, table=True):
object_id: Optional[int] = Field(primary_key=True, default=None)
first_detection_time: datetime = Field(index=True)
last_detection_time: datetime = Field(index=True)
detection_time: ClassVar[float] = hybrid_property(_detection_time)
if __name__ == "__main__":
engine = create_engine("sqlite:///database.db")
SQLModel.metadata.create_all(engine)
NOTE: I think in this example you need to add an expression for this to work as expected in queries.
@tiangolo any update on this yet? The above feels like a work-around (and I'm so far not able to hybrid props and comparators working using sqlmodel and fast api)
@jjjacksn Could you please elaborate how to add an expression to use the detection_time in a query?
For instance:
select(ObjectTable).where(ObjectTable.detection_time > 1000)
