use a mypy plugin as an alternative to .t ?

[martinuncountable]

Ensure stubs packages are not installed

• No sqlalchemy stub packages is installed (both sqlalchemy-stubs and sqlalchemy2-stubs are not compatible with v2)

Verify if the api is typed

• The api is not in a module listed in #6810 so it should pass type checking

Describe the typing issue

I originally raised a discussion here. However, I want an issue to reference for this problem.
(original discussion: #10487)

In SQLAlchemy 2.0/1.4 the runtime row object allows accessing column data via the column name/label, for example:

query = session.query(User.id, User.name)
for row in query:
    my_id = row.id
    my_name = row.name

This is the preferable approach for our large code base as this is more maintainable, as query columns are likely to be removed/modified/re-ordered over time. Using row[2] or now row.t[2] (to get the type in 2.0) greatly increases the risk of pushing incorrect code that will be missed in code review.

There is no option here. We will not be writing code using tuple index lookup syntax.

Based on the original discussion: there are no plans to support this typing use case. I do think it is possible to create a mypy plugin to do this. If we prove this is possible would this likely to be merged upstream?

To Reproduce

query = session.query(User.id, User.name)
for row in query:
    reveal_type(row)
    reveal_type(row.id)

    # Only way to get some sort of types:
    reveal_type(row.t)

--- mypy gives ---
example.py:27: note: Revealed type is "sqlalchemy.engine.row.Row[tuple[builtins.int, builtins.str]]"
example.py:28: note: Revealed type is "Any"
example.py:31: note: Revealed type is "tuple[builtins.int, builtins.str]"

Error

# Copy the complete text of any errors received by the type checker(s).
example.py:28: note: Revealed type is "Any"

Versions

• OS: any
• Python: 3.11
• SQLAlchemy: 2.0
• Type checker mypy 1.5.1

Additional context

No response

[CaselIT]

Hi,

Based on the original discussion: there are no plans to support this typing use case.

It's not that there is no plan, is that it's impossible as far as we know to support, since it would equate creating a class dynamically that's something that's not supported by static type checkers

I do think it is possible to create a mypy plugin to do this. If we prove this is possible would this likely to be merged upstream?

unlikely, we have (had) a mypy plug-in on 1.4 that is now deprecated since it's very hard to maintain (each version of mypy is likely to break it) and does not cater any other type checker. That said you are free to create it as a 3-rd party library

I'm not sure this issue add much the the already opened discussion

[zzzeek]

A mypy plugin for this particular thing might be much simpler since it's literally intercepting one single type and making it act like a Tuple. If you have insight on that, then feel free to implement as a PR which we can evaluate.

Suffice to say nobody is more unhappy with having to use .t than me, however this is the unfortunate state of Python typing where pep-646 fairly tragically stopped short of making the variadic generics feature useful in any way. I have no idea what project could be using this pep in its current form.

[zzzeek]

a mypy plugin of course does not solve the problem since that only accommodates mypy.

[zzzeek]

oh i see, there was already a discussion here

sorry, we really need to keep our "issues" small, these are things that for us are immediately actionable. if it's not immediately actionable, it goes into "ideas"

[zzzeek]

see #10276 . if you want to try approaching that and suggesting improvements to pep-646 to the typing sig, which has been at https://mail.python.org/archives/list/typing-sig@python.org/ but appears to be moving to https://discuss.python.org/c/typing/32 , that's what's really needed here. it would take many months to get it through but that way .t can finally be retired for all typing checkers, not just mypy with a plugin.

[zzzeek]

reading again, there is also no way for these values to be typed:

query = session.query(User.id, User.name)
for row in query:
    my_id = row.id
    my_name = row.name

that's named tuple access. There is no mechanism in pep-484 typing by which the names ".id" and ".name" could ever be typed, unless you had a declaration for them using a class namespace. Even Python's own namedtuple requires a class based NamedTuple declaration for that to work.

So if the Q is, "is there a plan for row.name to be typed instaed of row[1]", the answer is no, there is no way to achieve that short of having a syntax like this:

class MyRow(TypedRow):
    id: int
    name: str

query = session.query(MyRow(User.id, User.name))
for row in query:
    my_id = row.id
    my_name = row.name

we do have a construct called Bundle that somewhat resembles this but it's a little more of a legacy thing and does not map out to the full row right now.

[martinuncountable]

Thanks for providing all this context! My course of action is to evaluate what is possible via a third party mypy plugin until hopefully the typing PEP allows for this type of functionality (🤞 )

[pdc1]

In case this is useful, I found you can define a typed namedtuple and cast it to the row, to expose the fields/types you know are there.

MyRow = NamedTuple('MyType', [('id', int), ('name', str)])

for row in query:
    r = cast(MyRow, row)
    my_id = r.id
    my_name = r.name

With the above usage, VSCode will suggest id and name as fields, and the type checker will check that id is an int and name is a str. It's not automagic, but it adds zero overhead to execution. Because it's a cast, if you change your query or the type gets out of sync, it will break.

I have only started trying this in my code so I'm not sure about any downsides but simple cases appear to work.

[CaselIT]

It's not automagic, but it adds zero overhead to execution

Cast is a fuction call. So has some overhead, but not much

[zzzeek]

thanks for pointing out this issue, this will be fixed in 2.1 as we've had contributors successfully adapt pep-646 to our select/result objects see #10635