Typing sqlalchemy where clauses

Following this doc:

https://docs.sqlalchemy.org/en/20/orm/extensions/mypy.html

I tried to type-check my test.py file:

from sqlalchemy import Column, Integer, String, select
from sqlalchemy.orm import declarative_base
Base = declarative_base()
class Usr(Base):
    __tablename__ = "usr"
    id = Column(Integer, primary_key=True)
    name = Column(String)
stmt = select(
    Usr.name
).where(
    Usr.id == "test"  # comparing an int and an str
)

using the following command:

mypy --strict --config-file mypy.ini test.py

where mypy.ini contains:

[mypy]
plugins = sqlalchemy.ext.mypy.plugin

Mypy does not raise any error on the where clause (Usr.id == "blabla").

Is there a way to have mypy complain with some Non-overlapping equality check or some workaround for that case ?

Solution

From a native typing-perspective it is not possible to detect Non-overlapping equality check / reportUnnecessaryComparison here as the signature of Column.__eq__ returns a ColumnEntry[bool] object, i.e. a usable non-trivial object usable by the where method.

You can write a custom class like the following that will raise a type-error on non-integer comparison.

class IntColumn(Column[int]):

    if TYPE_CHECKING:
        @overload
        def __eq__(self, other: int) -> ColumnElement[bool]:  # type: ignore[override]  # noqa: E501
            ...
        @overload
        def __eq__(self, other: object) -> Literal[False]: ...

class Usr(Base):
    __tablename__ = "usr"
    id = IntColumn(Integer, primary_key=True)

However, when using this class with a non-literal, i.e. Usr.id == obj where obj: int | str you will also get a type-error (that you could ignore as it is runtime safe).

Alternatively, use for the second signature def __eq__(self, other: object) -> ColumnElement[Literal[False]]: .... This will not raise errors, however this in turn will again not raise an error on a string comparison.
These could be entry points to write a custom plugin to raise an error on ColumnElement[Literal[False]] and possibly also ignore the necessity of --strict-equality. (I've never written a plugin so I cannot tell more here)