Type annotations and functions that return SQL query results

[rdimaio]

I am opening this thread mainly for future reference, without changing the documentation for now.

Current approach

My current approach to this issue is:

1. Until SQLA2.1 is released, don't change anything in the query logic itself, and keep using TypedDict hints to annotate the return type of these functions. Ultimately, in this context, this is what we mainly care about (we can assume that the types returned by the query are correct, since we perform validation when entering the data in the DB - e.g. we can assume that the Heartbeats.readable data coming from the query is definitely str | None).
2. After SQLA2.1 is released, we can upgrade to 2.1 and do eventual refactoring to enable type checking within the function itself, between the query result and the returned dict. I don't expect that any refactoring will be needed on our side, because SQLA2.0 -> SQLA2.1 changes the type of the Result and Row objects, as explained here: sqlalchemy/sqlalchemy@0007200#diff-d54af7d55637bc92aefa7c48b51e08b36fa6cd7ae0adc5461d06638e438d08cbR41-R44

Motivation

In short, SQLAlchemy 2.0 cannot expose the types of individual columns in the Result object.

This is due to the fact that it doesn't support PEP-646:

• https://peps.python.org/pep-0646/

SQLAlchemy 2.1 adds PEP-646 support:

• Added here: sqlalchemy/sqlalchemy@0007200

However, SQLA 2.1 is not out yet.

There is a workaround in SQLA 2.0 by using the Row._tuple() method (this method is specifically created for this purpose - it returns self as runtime, so it's only used for type-checking: https://docs.sqlalchemy.org/en/20/core/connections.html#sqlalchemy.engine.Row._tuple).

Example:

    stmt = select(
        Heartbeats.readable, # this is str | None
        Heartbeats.hostname,
        Heartbeats.pid,
        Heartbeats.thread_name,
        Heartbeats.updated_at,
        Heartbeats.created_at,
        Heartbeats.payload
    ).order_by(
        Heartbeats.readable,
        Heartbeats.hostname,
        Heartbeats.thread_name
    )
    result = session.execute(stmt).all()
    for element in result:
        element_tuple = element._tuple()
        x = element_tuple[0] # x is correctly analyzed as str | None
        ... # manually build a dictionary from the tuple, conforming to a TypedDict

I am not in favour of using this workaround, as it might be prone to bugs in case the query changes (e.g. let's say we swap readable and thread_name in the query - they're both str | None, so the type checker would not notice any issues)

Resources

There are some key SQLAlchemy discussions about this:

• What is the canonical way to type hint query results? sqlalchemy/sqlalchemy#6102
• Typing support for query results sqlalchemy/sqlalchemy#5521
• Use PEP-646 instead of tuple workaround sqlalchemy/sqlalchemy#10633
• Type hints for result objects from select sqlalchemy/sqlalchemy#10760
• Inferring query results sqlalchemy/sqlalchemy#10273
• use a mypy plugin as an alternative to .t ? sqlalchemy/sqlalchemy#10496
• pep484 Type annotations ,mypy compatibility sqlalchemy/sqlalchemy#4609

And a relevant SO question: https://stackoverflow.com/questions/63070515/get-class-hint-after-query-data-by-sqlalchemy

[rdimaio]

Some relevant discussion in this past PR #6497 (comment)

[rdimaio]

For future reference: once SQLA2.1 is released and we add it as dependency, we should review all related instances of:

• type: ignore
• typing.cast, e.g.:

  rucio/lib/rucio/core/naming_convention.py

  Line 131 in 2c389be

  return [cast("NamingConventionDict", row._asdict()) for row in query]

(by related, I mean "in the context of functions returning TypedDicts constructed from SQLA query results")