Mypy errors: Incompatible type for optional ids & Incompatible type for SQLModel attribute in expression

[javivdm]

First Check

• I added a very descriptive title to this issue.
• I used the GitHub search to find a similar issue 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 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

from typing import Optional

from sqlmodel import SQLModel, create_engine, Session, Field, select


class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str


engine = create_engine("sqlite:///.deleted/database.db")

SQLModel.metadata.create_all(engine)

hero_1 = Hero(name="Grey Hound")


def format_id(id: int):
    return f"{id:04d}"


with Session(engine) as session:
    session.add(hero_1)
    session.commit()
    session.refresh(hero_1)

    session.exec(select(Hero).where(Hero.id.in_([1, 2])))
    print(format_id(hero_1.id))

Description

When executing mypy on the code above I get the following output:

sqlmodel_mypy_issue.py:27: error: Item "int" of "Optional[int]" has no attribute "in_"
sqlmodel_mypy_issue.py:27: error: Item "None" of "Optional[int]" has no attribute "in_"
sqlmodel_mypy_issue.py:28: error: Argument 1 to "format_id" has incompatible type "Optional[int]"; expected "int"
Found 3 errors in 1 file (checked 1 source file)

So:

• Is there any way to tell mypy that upon read a SQLmodel id has type int instead of Optional[int] without having to convert it to another model?
• How should I build expressions such as .where(Hero.id.in_([1, 2])) so that mypy and Pycharm don't flag the in_ as not being an attribute of the model attribute type?

Thank you!

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.6

Python Version

Python 3.9.10

Additional Context

mypy version:
mypy 0.931

[StefanBrand]

This might be considered an "exotic pattern", just like my #271 . 🙂

[javivdm]

@StefanBrand note that there are two different mypy issues above.

One is the use of .in_, which honestly I think is one of the most common expressions used for building queries so I would be surprised that it would follow under the exotic patterns, but who knows.

The other is the most basic use of SQLModel which is to define a model with an id field as Optional[int] = Field(default=None, primary_key=True). That one also gets an error by mypy when trying to access the id in a function that reads the data from the db (hence the id is always filled), but mypy complains that it might be optional. There is no way it can be considered an exotic pattern, as it is the most basic functionality SQLModel provides.

[antont]

The other is the most basic use of SQLModel which is to define a model with an id field as Optional[int] = Field(default=None, primary_key=True).

Is a bit strange though to declare a PK 'optional', as it is always there, in the db model. I guess you get this though when don't have separate models for handling POST requests for creation or whatever, but just reuse the same classes. I've been using separate create, db and view classes, like in the tutorial, and find it quite nice. Anyhow am not arguing, I guess this would be nice to solve somehow.

[javivdm]

@antont this happens even when you use different models for db and creation. If you separate them, you can exclude the id field from the creation model, but when you create the db instance from the creation instance, the id is empty. Therefore as I understand it, if you don't have an optional int as the id field the Pydantic validation for the model will fail, as before saving it to the database the instance of the db model has no id yet.
And actually in the tutorial, in the section about having different models the id is still optional, as unfortunately it has to be.

[antont]

@javivdm I autogenerate the id's at creation, so they won't be empty even though the *Create model that gets the POST data does not have them.

However my current code does declare them optional in the DB class too, I had forgotten why, and had added a comment, I can try at some point if it works when that is not Optional.

id: Optional[int] = Field(primary_key=True, nullable=False) #XXX why optional here?

In the POST handler for creation I do

dbob = dbmodel.from_orm(createob)

[StefanBrand]

This is the tutorial section @javivdm is referring to: https://sqlmodel.tiangolo.com/tutorial/fastapi/multiple-models/#multiple-hero-schemas It explains why you need Optional for the database model, and can omit it for the create model.

[antont]

@StefanBrand - right. The explanation is not very elaborate, though, but I guess it is because the db model is first instantiated in memory, with no value for id yet, and only when it's then written to the db, the id gets generated. Am still curious to try it out but I figure will get an error because of that.

[anentropic]

I have similar errors for a not null check in a query like MyModel.bool_field.isnot(None)

repository.py:142: error: Item "bool" of "Optional[bool]" has no attribute "isnot"  [union-attr]
repository.py:142: error: Item "None" of "Optional[bool]" has no attribute "isnot"  [union-attr]

(update: seems like this is #109)

[danielunderwood]

I'm not sure about solving the first point, but to

How should I build expressions such as .where(Hero.id.in_([1, 2])) so that mypy and Pycharm don't flag the in_ as not being an attribute of the model attribute type?

Replace Hero.id.in_([1,2]) with col(Hero.id).in_([1,2]), which should make type checkers realize that you're applying in_(...) to a column object rather than directly to the type of the column.

[YuriiMotov]

The has no attribute "in_" error is fixed by using col as @danielunderwood pointed.

As for incompatible type "Optional[int]"; expected "int" error, you can either make the input parameter of format_id optional and then assert that it's not None inside:

def format_id(id: int | None):
    assert id is not None
    return f"{id:04d}"

Or, create one more model and re-define the type of id field:

class HeroBase(SQLModel):
    name: str

class HeroDB(HeroBase, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)

class Hero(HeroBase):
    id: int

Then after fetching the object from DB, convert it to Hero:

    session.refresh(hero_1)
    hero_1_read = Hero.model_validate(hero_1)
    print(format_id(hero_1_read.id))

Runnable code example in the details:

from typing import Optional

from sqlmodel import Field, Session, SQLModel, col, create_engine, select

class HeroBase(SQLModel):
    name: str

class HeroDB(HeroBase, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)

class Hero(HeroBase):
    id: int

engine = create_engine("sqlite:///")
SQLModel.metadata.create_all(engine)

hero_1 = HeroDB(name="Grey Hound")

def format_id(id: int):
    return f"{id:04d}"

with Session(engine) as session:
    session.add(hero_1)
    session.commit()
    session.refresh(hero_1)

with Session(engine) as session:
    hero_1_db = session.exec(select(HeroDB).where(col(HeroDB.id).in_([1, 2]))).first()
    hero_1_read = Hero.model_validate(hero_1_db)
    print(format_id(hero_1_read.id))