`read_sql` and `read_sql_query` reject a `params` dict that mixes a scalar and a sequence

[notatallshaw-gts]

Describe the bug

params accepts two kinds of dict, Mapping[str, Scalar] and Mapping[str, tuple[Scalar, ...]]. A dict with a date under one key and a tuple under another matches neither. So filtering by a date and an IN list in one query does not type check.

Positional params have the same gap: [date, (1, 2)] matches neither list[Scalar] nor tuple[tuple[Scalar, ...], ...].

pandas itself annotates params as list[Any] | Mapping[str, Any] | None. #997 added tuple support for #996 as a second option instead of widening the value type, which is why no type fits the mixed dict.

To Reproduce

Runs on sqlite and prints [1, 2]. The read_sql_query call is rejected.

import datetime as dt

import pandas as pd
import sqlalchemy as sa

engine = sa.create_engine("sqlite://")
with engine.begin() as con:
    con.execute(sa.text("CREATE TABLE t (id INTEGER, d DATE)"))
    con.execute(sa.text("INSERT INTO t VALUES (1, '2026-08-11'), (2, '2026-08-11')"))

statement = sa.text("SELECT id FROM t WHERE d = :d AND id IN :ids").bindparams(
    sa.bindparam("ids", expanding=True)
)
with engine.connect() as con:
    df = pd.read_sql_query(statement, con, params={"d": dt.date(2026, 8, 11), "ids": (1, 2)})

print(df["id"].tolist())

pyright 1.1.406:

error: Argument of type "dict[str, date | tuple[int, int]]" cannot be assigned to parameter
"params" of type "list[Scalar] | ... | Mapping[str, tuple[Scalar, ...]] | None"
in function "read_sql_query"
    Type parameter "_VT_co@Mapping" is covariant, but "date | tuple[int, int]" is not a
    subtype of "Scalar"

mypy 1.19.1, pyrefly 1.2.0 and ty 0.0.69 reject it the same way.

System specifications

• OS and its version: Rocky Linux 9 / Linux 6.1.29
• python version: 3.12.13
• type checker and its version: pyright 1.1.406, mypy 1.19.1, pyrefly 1.2.0, ty 0.0.69
• version of installed pandas-stubs: 3.0.5.260730 (pandas 3.0.5, SQLAlchemy 2.0.51)

Additional context

One type for the bind value fixes both cases:

_SQLBindValue: TypeAlias = Scalar | None | Sequence[Scalar | None]

params: Sequence[_SQLBindValue] | Mapping[str, _SQLBindValue] | None = None,

This covers all five current options, so nothing that passes today starts failing. None is included because binding NULL is common and Scalar omits it. Sets and nested sequences stay rejected.

#997's tuple test is skipped because it needed Postgres; the example above runs on sqlite, so it can be unskipped.

[Dr-Irv]

Another possible solution is to change the references from Scalar to ScalarT that is imported from pandas._typing

[notatallshaw-gts]

I don't think ScalarT fixes this one. The bound is what rejects the mixed dict, trying it out:

ty:    Argument type `date | tuple[int, int]` does not satisfy upper bound ... of type variable `ScalarT`
mypy:  Value of type variable "ScalarT" of "read_sql_query" cannot be "object"  [type-var]

I looked at where ScalarT is used, it gets past list invariance (#124) by solving to the element union, so that union has to sit inside Scalar. Here the dict's values are date | tuple[int, int], and a tuple is not a Scalar.

Not part of this issue: read_sql_query types the positional arm list[Scalar], so passing a declared list[str] hits the #97 problem. read_sql already uses Sequence[Scalar].

error: Argument "params" to "read_sql_query" has incompatible type "list[str]"
note: "list" is invariant -- see ...#variance
note: Consider using "Sequence" instead, which is covariant

I think my _SQLBindValue would fix both.

[Dr-Irv]

OK, that makes sense. The issue here is that a tuple is allowed and you have mixed parameters (scalar and tuple).

PR with tests welcome.

[Boulea7]

I reproduced this on current main with both read_sql and read_sql_query. I’ll add SQLite-backed tests for mixed scalar/sequence mappings and positional parameters, while keeping nested sequences and sets rejected, and update the shared SQL parameter type.