Repository: pandas-dev/pandas-stubs (language: python)

Issue:
read_sql_query should support tuples as `params` dict values
**Describe the bug**
The type for [`pandas.read_sql_query`](https://pandas.pydata.org/docs/reference/api/pandas.read_sql_query.html) is specified by `pandas-stubs` as a dictionary mapping from `str` to `str | bytes | date | timedelta | datetime64 | timedelta64 | int | float | complex`. But for some database drivers like `psycopg2` or `mysql-connector`, it's also possible to pass in tuples e.g. as the right-hand side of `IN` conditions (`WHERE ... IN (1, 2, 3)`).

**To Reproduce**
A minimal example using Postgres (it looks similar for MySQL except the parameter is written as `:ids` in the query):

1. Launch a Postgres Docker container: `docker run -p 9876:5432 -e POSTGRES_HOST_AUTH_METHOD=trust postgres`
2. Install dependencies and run this script to see that tuple substitutions for `IN` conditions work fine (I included [PEP 723](https://peps.python.org/pep-0723/) script metadata so you can just do `uv run` or `pipx run` on it to avoid needing to install dependencies manually):

```python
# To make this executable directly w/ pipx run or uv run:
# /// script
# dependencies = [
#   "psycopg2==2.9.9",
#   "pandas==2.2.2"
# ]
# ///

import pandas as pd
import psycopg2

conn = psycopg2.connect(
  host="localhost", dbname="postgres", user="postgres", port=9876
)
cur = conn.cursor()
cur.execute("CREATE TABLE test (id serial PRIMARY KEY)")
cur.execute("INSERT INTO test(id) VALUES(1)")
cur.execute("INSERT INTO test(id) VALUES(2)")
cur.execute("INSERT INTO test(id) VALUES(3)")

df = pd.read_sql_query(
  "SELECT * FROM test WHERE id IN %(ids)s",
  conn,
  params={"ids": (1, 2)},
)
print(df)

```
Output is as expected:
```
   id
0   1
1   2
```

3. Run Mypy on it and get this error message:
```
bug.py:24: error: Dict entry 0 has incompatible type "str": "list[int]"; expected "str": "str | bytes | date | timedelta | datetime64 | timedelta64 | int | float | complex"  [dict-item]
```


**Please complete the following information:**
 - OS: Linux
 - OS Version: Ubuntu 22.04
 - python version: 3.10
 - version of type checker: Mypy 1.11.2
 - version of installed `pandas-stubs`: 2.2.2.240909


**Additional context**
Something like this doesn't work for `sqlite3` connections, because it really does only accept "primitive" values for substitutions. 

But IMO it doesn't make sense to restrict the set of allowed types to the universally supported subset, because some other weird database could decide to not even support `int` substitutions or something like that and then even the current set wouldn't be valid.



Discussion / hints:
Thanks for the report.  A PR with a change to `pandas-stubs/io/sql.pyi` is welcome.

For tests, we use `sqllite3`.  Not sure if your example will work with that.  If so, then PR should include a test in `test_io.py` in function `test_read_sql_query()`.  If not, then it's OK to do a PR without a test.



Fix patch:
diff --git a/pandas-stubs/io/sql.pyi b/pandas-stubs/io/sql.pyi
index 7157b70e..d53f9b05 100644
--- a/pandas-stubs/io/sql.pyi
+++ b/pandas-stubs/io/sql.pyi
@@ -66,7 +66,14 @@ def read_sql_query(
     con: _SQLConnection,
     index_col: str | list[str] | None = ...,
     coerce_float: bool = ...,
-    params: list[Scalar] | tuple[Scalar, ...] | Mapping[str, Scalar] | None = ...,
+    params: (
+        list[Scalar]
+        | tuple[Scalar, ...]
+        | tuple[tuple[Scalar, ...], ...]
+        | Mapping[str, Scalar]
+        | Mapping[str, tuple[Scalar, ...]]
+        | None
+    ) = ...,
     parse_dates: list[str] | dict[str, str] | dict[str, dict[str, Any]] | None = ...,
     *,
     chunksize: int,
@@ -79,7 +86,14 @@ def read_sql_query(
     con: _SQLConnection,
     index_col: str | list[str] | None = ...,
     coerce_float: bool = ...,
-    params: list[Scalar] | tuple[Scalar, ...] | Mapping[str, Scalar] | None = ...,
+    params: (
+        list[Scalar]
+        | tuple[Scalar, ...]
+        | tuple[tuple[Scalar, ...], ...]
+        | Mapping[str, Scalar]
+        | Mapping[str, tuple[Scalar, ...]]
+        | None
+    ) = ...,
     parse_dates: list[str] | dict[str, str] | dict[str, dict[str, Any]] | None = ...,
     chunksize: None = ...,
     dtype: DtypeArg | None = ...,
@@ -91,7 +105,14 @@ def read_sql(
     con: _SQLConnection,
     index_col: str | list[str] | None = ...,
     coerce_float: bool = ...,
-    params: list[Scalar] | tuple[Scalar, ...] | Mapping[str, Scalar] | None = ...,
+    params: (
+        list[Scalar]
+        | tuple[Scalar, ...]
+        | tuple[tuple[Scalar, ...], ...]
+        | Mapping[str, Scalar]
+        | Mapping[str, tuple[Scalar, ...]]
+        | None
+    ) = ...,
     parse_dates: list[str] | dict[str, str] | dict[str, dict[str, Any]] | None = ...,
     columns: list[str] = ...,
     *,
@@ -105,7 +126,14 @@ def read_sql(
     con: _SQLConnection,
     index_col: str | list[str] | None = ...,
     coerce_float: bool = ...,
-    params: list[Scalar] | tuple[Scalar, ...] | Mapping[str, Scalar] | None = ...,
+    params: (
+        list[Scalar]
+        | tuple[Scalar, ...]
+        | tuple[tuple[Scalar, ...], ...]
+        | Mapping[str, Scalar]
+        | Mapping[str, tuple[Scalar, ...]]
+        | None
+    ) = ...,
     parse_dates: list[str] | dict[str, str] | dict[str, dict[str, Any]] | None = ...,
     columns: list[str] = ...,
     chunksize: None = ...,


Test patch:
diff --git a/tests/test_io.py b/tests/test_io.py
index c93ac40f..8f89b0ba 100644
--- a/tests/test_io.py
+++ b/tests/test_io.py
@@ -1238,6 +1238,39 @@ def test_read_sql_query_via_sqlalchemy_engine_with_params():
         engine.dispose()
 
 
+@pytest.mark.skip(
+    reason="Only works in Postgres (and MySQL, but with different query syntax)"
+)
+def test_read_sql_query_via_sqlalchemy_engine_with_tuple_valued_params():
+    with ensure_clean() as path:
+        db_uri = "postgresql+psycopg2://postgres@localhost:5432/postgres"
+        engine = sqlalchemy.create_engine(db_uri)
+
+        check(
+            assert_type(
+                read_sql_query(
+                    "select * from test where a in %(a)s",
+                    con=engine,
+                    params={"a": (1, 2)},
+                ),
+                DataFrame,
+            ),
+            DataFrame,
+        )
+        check(
+            assert_type(
+                read_sql_query(
+                    "select * from test where a in %s",
+                    con=engine,
+                    params=((1, 2),),
+                ),
+                DataFrame,
+            ),
+            DataFrame,
+        )
+        engine.dispose()
+
+
 def test_read_html():
     check(assert_type(DF.to_html(), str), str)
     with ensure_clean() as path: