How to massage / normalise data read from DB

[madduck]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already searched the Web "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

# not related to specific code

Description

I have to deal with a database that regularly changes, but which imposes no constraints on the data in columns. The software is so badly designed, but there is nothing I can do about it. It's a Microsoft Access nightmare.

There are columns that are supposed to be integers, but the cells contain 0 and null interchangeably. Also, there are date columns that have both null and 1899-12-30 12:34:56 to indicate that there is no valid date stored.

When slurping up those data into SQLModels, I'd love to normalise the data, i.e. turn 0 and the weird date into None.

But how?

Validation does not work when data come from the database because it is assumed that data in the database are proper. Well, not in this case.

With SQLAlchemy's voodoo magic replacing __init__ on models, I could find no way to invoke code to run after a new instance was created and populated with data, but before it's returned to the user.

Please don't make me have to provide proxy properties, or hack __getattr__. Why can't we just have a __post_init__?

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.24

Python Version

3.13.3

Additional Context

No response

[madduck]

I just had to discover that even overriding __getattr__ won't let me do anything to the data. :(

[20-buck-spin]

I have not dealt with a similar situation. But the only thing that comes to mind is the following

class TestBase(SQLModel):
    problem_field_1 : str | None 
    problem_field_2 : int | None 

class Test(TestBase, table=True):
    id: int | None = Field(primary_key=True. default=None)

class TestPublic(TestBase):

    id: int

    @computed_field
    @property
    def corrected_field_1(self) -> str:
        # self.problem_field_1 and fix it 
        return f"Fixed str after logic"

    @computed_field
    @property
    def corrected_field_2(self) -> int:
        # self.problem_field_2 and fix it 
        return 1

You get your rows from the db
data = session.exec(select(Test)).all()
formatted_data = [TestPublic.model_validate(entry) for entry in data]

Then you can access the corrected fields. This way you'd also retain access to whatever was on the DB.

[madduck]

Hey, thank you for your reply and time and thought you put into this.

Your test case works, and it is an interesting approach. It still requires me to "remember" that I should not use the data returned from executing the statement directly. It's nice an elegant in that it uses pydantic validation to get at the data, and so it seems like everything is nicely in the place where it should be, but the additional classes appear to me to be a potential source of confusion, and there's a bit of boilerplate going on, which will be amplified given I am dealing with a couple dozen models.

If I go down the route to massage the data into another model hierarchy, I might just as well provide hybrid/computed attributes, or custom _validate methods. But I am still on the lookout for an approach that handles this implicitly. Hope dies last.

Maybe the best way, given that I am only ever reading data, is to use an SQL view to do the normalisation, and then to craft a model onto this view?? I should try that.