How can I make DateTimeTZRange work? #1946
Replies: 6 comments
|
I experienced no problems saving instances with the |
|
Which version of PostgreSQL are you using? |
|
This is a bit tricky due to Pydantic need validators etc. Basically the DateTimeTZRange type does not have the required method to convert its data to a dict, so you need to do that manually.
from psycopg2.extras import DateTimeTZRange as DateTimeTZRangeBase
from sqlalchemy.dialects.postgresql import TSTZRANGE
from sqlmodel import (
Column,
Field,
Identity,
SQLModel,
)
from pydantic.json import ENCODERS_BY_TYPE
ENCODERS_BY_TYPE |= {DateTimeTZRangeBase: str}
class DateTimeTZRange(DateTimeTZRangeBase):
@classmethod
def __get_validators__(cls):
yield cls.validate
@classmethod
def validate(cls, v):
if isinstance(v, str):
lower = v.split(", ")[0][1:].strip().strip()
upper = v.split(", ")[1][:-1].strip().strip()
bounds = v[:1] + v[-1:]
return DateTimeTZRange(lower, upper, bounds)
elif isinstance(v, DateTimeTZRangeBase):
return v
raise TypeError("Type must be string or DateTimeTZRange")
@classmethod
def __modify_schema__(cls, field_schema):
field_schema.update(type="string", example="[2022,01,01, 2022,02,02)")
class EventBase(SQLModel):
__tablename__ = "event"
timestamp_range: DateTimeTZRange = Field(
sa_column=Column(
TSTZRANGE(),
nullable=False,
),
)
class Event(EventBase, table=True):
id: int | None = Field(
default=None,
sa_column_args=(Identity(always=True),),
primary_key=True,
nullable=False,
)Pydantic issues:
Pydantic reading: |
|
We had a similar question for our project, but for a We adapted @Zaffer's suggestion, and it worked splendidly for persisting a value. But when the instance was refreshed/reloaded from the db, the field's resulting value on the I'm still trying to understand the mechanisms used to hydrate a row of SQL data into a Pydantic instance, but I would've expected it would attempt to cover the loaded data into the specified type on the Is this a bug, a misconfiguration, or a misunderstanding of how SQLModel/Pydantic/SQLAlchemy work? from datetime import date
from psycopg2.extras import DateRange as DateRangeBase
from sqlalchemy.dialects.postgresql import DATERANGE
def _to_date(value: str) -> date:
vals = map(
int, value.strip().replace("[", "").replace(")", "").split("-", maxsplit=2)
)
return date(*vals)
class DateRange(DateRangeBase):
"""
A range of dates, used for persisting in a PostgreSQL database.
"""
@classmethod
def __get_validators__(cls):
yield cls.validate
@classmethod
def validate(cls, v):
if isinstance(v, DateRangeBase):
return v
if isinstance(v, str):
lower, upper = map(_to_date, v.split(sep=",", maxsplit=1))
bounds = v[:1] + v[-1:]
return DateRange(lower, upper, bounds)
raise TypeError("Type must be a string or DateRange")
@classmethod
def __modify_schema__(cls, field_schema):
field_schema.update(type="string", example="[2022-01-01,2022-02-02)")
class EventBase(SQLModel):
date_range: DateRange = Field(sa_column=Column(DATERANGE(), nullable=False)) |
|
@shaug - did you ever get any further with this as a solution? |
|
We've ended up needing to create SQLAlchemy TypeDecorators for every type we wanted to properly convert from its database representation into its Python instance. |
Uh oh!
There was an error while loading. Please reload this page.
First Check
Commit to Help
Example Code
Description
I have a model that has a DateTimeTZRange field. When I try to save an instance of this model, I get the following error:
When I use
conlistrather thanDateTimeTZRange, I don't get errors but the new objects always haveNonein theirtime_range. I couldn't find any documentation on how to use custom PostgreSQL fields in SQLModel, and the documentation I found on sqlalchemy doesn't seem to work smoothly on SQLModel.Operating System
macOS
Operating System Details
No response
SQLModel Version
0.0.4
Python Version
3.9.9
Additional Context
No response
All reactions