What is the canonical way to type hint query results?

[Marto32]

Describe your question

Based on the documentation it seems as though the Query.all() method returns a list of model objects satisfying the query. However, when I've tried to implement this (see below), my type checker (mypy) complains that the hint I've set is not returned and instead and inferred Any type is returned.

After a bit of digging I found this question/answer that suggests using the _LW object from the sqlalchemy.util._collections module. Seeing as this is a private object from a private module, it doesn't seem like the proper way to do this.

Is there a canonical way of type hinting query results that doesn't require workarounds/using private objects?

Example - please use the Minimal, Complete, and Verifiable guidelines if possible

Example (example.py):

from typing import List

from sqlalchemy import (Column, Integer, String)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session

Base = declarative_base()


class Example(Base):

  __tablename__ = "examples"

  id = Column(Integer, primary_key=True)
  name = Column(String, nullable=False)
  ...

  @classmethod
  def query_model(cls, session: Session, name: str) -> List["Example"]:
    return session.query(cls).filter(cls.name == name).all()

Running mypy on the above (mypy example.py) would cause an error saying that "List[Example]" was expected but got "Any".

Versions

• OS: Linux Mint 20.1, MacOS BigSur 11.2.3
• Python: 3.9
• SQLAlchemy: 1.4.2
• Database: N/A
• DBAPI: N/A

Have a nice day!

[zzzeek]

type hinting for query results, when we are able to do it, will not be on session.query() as that is legacy stuff, it will be available via the 2.0 style interface.

my current understanding is that type hinting can't work for this case until variadic generics are available, which is beginning in Python 3.10, and even then I need to dig into that and figure out how to do it. Here's Guido and other folks talking about SQLAlchemy specifically with regards to this problem.

as for that stackoverflow answer, I have no idea what they're doing there. the lightweight_named_tuple() is no longer in SQLAlchemy and is replaced with the Row object.

anyway, one of the main points of the move to do everyhing based on Result/Row was to make way for pep-484 / mypy, where things are way easier if your functions and methods return the same types of objects every time. but the job of linking "select(A, B.id, C)" to flow through session.execute() into Row(A, int, C) is still a TODO and it needs the pep646 stuff IIUC.

you should also checkout the newly released mypy extension

[Marto32]

Thanks for the detailed answer! Looks like the new mypy extension is in alpha right now.

For the time being (until 3.10 and PEP 646), would the best approach just be to set the expected return type as Any?

[zzzeek]

what stubs are you using? the stubs at github.com/sqlalchemy/sqlalchemy2-stubs are just being written. Query.all() shuld return List[Any] but i havent gotten this stuff into sqlalchemy2-stubs yet.

[Marto32]

I'm still using the dropbox stubs here. Running mypy (after configuring the stubs in my ini file) still doesn't like List[Any]. I've also updated to the 2.0 query api (see below) and am getting:

error: Argument 1 to "Select" has incompatible type "Type[Example]"; expected "Optional[Iterable[Union[ColumnElement[Any], FromClause, int]]]"

Updated example:

  @classmethod
  def query_model(cls, session: Session, name: str) -> List[Any]:
    query = select(cls).where(cls.month == month)
    results = session.execute(query)
    return results.scalars().all()

Apologies if I misunderstood something from your first comment.

[zzzeek]

The dropbox stubs are not up to date for session.execute(). I've just committed the fix for session.execute() -> Result as well as Query here: sqlalchemy/sqlalchemy2-stubs@936bee5 .

I'm going to merge one more PR and ill release it.

[Marto32]

Ah perfect, thank you! I really appreciate your responsiveness on this as well.

[zzzeek]

that's released on pypi. using latest https://pypi.org/project/sqlalchemy2-stubs/ 0.0.1a4 and the new SQLAlchemy mypy extension I get a green result for the following file:

from typing import List, cast

from sqlalchemy import (Column, Integer, String, select)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session
from sqlalchemy.engine import Result

Base = declarative_base()


class Example(Base):

    __tablename__ = "examples"

    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)


    @classmethod
    def query_model(cls, session: Session, name: str) -> List["Example"]:
        return session.execute(
            select(cls).where(cls.name == name)
        ).scalars().all()

[zzzeek]

Result.all() vs. Result.scalars().all() is how mypy knows this is a list of objects and not rows. This is why I organized the Result objects this way in 1.4.

[Marto32]

@zzzeek I'm not sure if this is a mypy bug but I'm now running into the following error when executing with the new stubs:

➜ pipenv run mypy example/models.py
example/models.py:14: error: INTERNAL ERROR -- Please try using mypy master on Github:
https://mypy.rtfd.io/en/latest/common_issues.html#using-a-development-mypy-build
If this issue continues with mypy master, please report a bug at https://github.com/python/mypy/issues
version: 0.812
example/models.py:14: : note: please use --show-traceback to print a traceback when reporting a bug

For reference, here's my Pipfile (I had to allow pre-releases to use the sqlalchem2-stubs alpha) using the latest version for all packages:

[[source]]
url = "https://pypi.org/simple"
verify_ssl = true
name = "pypi"

[packages]
pytest = "*"
factory-boy = "*"
pytest-mock = "*"
pytz = "*"
python-dateutil = "*"
sqlalchemy = "*"
mypy = "*"
sqlalchemy2-stubs = "*"

[dev-packages]

[requires]
python_version = "3.9"

[pipenv]
allow_prereleases = true

Full traceback from mypy:

Please report a bug at https://github.com/python/mypy/issues
version: 0.812
Traceback (most recent call last):
  File "mypy/semanal.py", line 4835, in accept
  File "mypy/nodes.py", line 950, in accept
  File "mypy/semanal.py", line 1048, in visit_class_def
  File "mypy/semanal.py", line 1125, in analyze_class
  File "mypy/semanal.py", line 1134, in analyze_class_body_common
  File "mypy/semanal.py", line 1194, in apply_class_plugin_hooks
  File "/home/michael/.local/share/virtualenvs/example-Cki0lSKi/lib/python3.9/site-packages/sqlalchemy/ext/mypy/plugin.py", line 159, in _base_cls_hook
    decl_class._scan_declarative_assignments_and_apply_types(ctx.cls, ctx.api)
  File "/home/michael/.local/share/virtualenvs/example-Cki0lSKi/lib/python3.9/site-packages/sqlalchemy/ext/mypy/decl_class.py", line 126, in _scan_declarative_assignments_and_apply_types
    _scan_for_mapped_bases(cls, api, cls_metadata)
  File "/home/michael/.local/share/virtualenvs/example-Cki0lSKi/lib/python3.9/site-packages/sqlalchemy/ext/mypy/decl_class.py", line 913, in _scan_for_mapped_bases
    _scan_declarative_assignments_and_apply_types(
  File "/home/michael/.local/share/virtualenvs/example-Cki0lSKi/lib/python3.9/site-packages/sqlalchemy/ext/mypy/decl_class.py", line 99, in _scan_declarative_assignments_and_apply_types
    elif "_sa_decl_class_applied" in cls.info.metadata:
AttributeError: attribute 'metadata' of 'TypeInfo' undefined
example/models.py:14: : note: use --pdb to drop into pdb

[zzzeek]

it's definitely my bug can you send me a .py file that reproduces? there will be lots and lots of these failures for awhile, the mypy plugin has to be exremely specific about a very open ended structure it gets passed.

[Marto32]

Here's a .py that would reproduce as a gist. My pipfile is shared above, and my mypy ini is:

[mypy]
ignore_missing_imports = True
warn_return_any = True
warn_unused_configs = True
plugins = sqlalchemy.ext.mypy.plugin

I execute with:

pipenv run mypy example.py --show-traceback

[zzzeek]

I'm not able to reproduce this error. I'm also puzzled by the error, "AttributeError: attribute 'metadata' of 'TypeInfo' undefined", that's not a normal AttributeError, unless it has changed in python 3.9, im on 3.8, normally attribute error looks like: "AttributeError: 'Foo' object has no attribute 'asdf'".

but also, Mypy's TypeInfo object has a plain dictionary on it called "metadata", right here: https://github.com/python/mypy/blob/master/mypy/nodes.py#L2457 that is also in 0.812.

can you reproduce this without running "pipenv" and just running the mypy command line against file.py ?

[zzzeek]

can't do it with python 3.9 either.

[zzzeek]

also python 3.9 doesnt have that odd attribute error message either...

[Marto32]

Very strange - I purged the virtualenv and reinstalled everything and am no longer getting the error.

The query example above seems to pass. Though now that it's actually type checking, it seems to be complaining about unpacking into the Example object:

class Example(Base):

    __tablename__ = "examples"

    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)

    @classmethod
    def create_example(cls, name: str) -> "Example":
        data = {"name": name}
        return cls(**data)

Complains:

example.py: error: Argument 1 to "Example" has incompatible type "**Dict[str, object]"; expected "Optional[str]"

I guess it's performing the type checks prior to the unpack being evaluated?

[zzzeek]

i dunno this looks like a mypy bug. we apply the new constructor at the semantic parsing level, when the class is first built up.

    @classmethod
    def create_example(cls, name: str) -> "Example":

        # works
        return Example(id=1, name="Some name")

        # fails
        data = dict(id=1, name="some name")
        return Example(**data)

[zzzeek]

this is mypy not interpreting the dictionary for **kwargs at all

from typing import Optional


class Foo:
    def __init__(self, id: Optional[int] = None, name: Optional[str] = None):
        pass

    # fails
    @classmethod
    def make_foo(cls) -> "Foo":
        d = dict(id=5, name="name")
        return Foo(**d)


    # passes
    @classmethod
    def also_make_foo(cls) -> "Foo":
        return Foo(id=5, name="name")

output:

test4.py:12: error: Argument 1 to "Foo" has incompatible type "**Dict[str, object]"; expected "Optional[int]"
test4.py:12: error: Argument 1 to "Foo" has incompatible type "**Dict[str, object]"; expected "Optional[str]"

[zzzeek]

I can see how that might be expected. because it doesn't know what's in the dict. but I dont know how to type that correctly. anyway, mypy thing.

[Marto32]

Makes sense. Thanks again for all your help!

[zzzeek]

someone else got the same stack trace as you, cc @bryanforbes