dataframe.read_sql_query does not work with datetime index
- Dominant language
- Python
- Stars
- 13.9k
- Forks
- 2k
- PR merge metrics
- No merged PRs in 30d
Description
**What happened**:
Dask failed to read_sql_query for a datetime index.
See SO question [here](https://stackoverflow.com/questions/73340732/loading-dask-dataframe-with-sqlalchemy-fails/73344622#73344622) (not mine, only reporting from it), and MCVE below.
**What you expected to happen**:
Dask reads the database and returns a df.
**Minimal Complete Verifiable Example**:
```py
from datetime import datetime
import dask.dataframe as dd
import pandas as pd
from sqlalchemy import Column, DateTime, Integer, create_engine, select
from sqlalchemy.orm import Session, declarative_base
Base = declarative_base()
class Loan(Base):
__tablename__ = "test_loans"
id = Column(Integer, primary_key=True)
date = Column(DateTime)
balance = Column(Integer)
engine = create_engine("sqlite:////tmp/so.db", future=True, echo=True)
Base.metadata.create_all(engine)
with Session(engine) as session:
session.add_all(
[
Loan(date=datetime.min, balance=10),
Loan(date=datetime.min, balance=11),
Loan(date=datetime.min, balance=12),
Loan(date=datetime.min, balance=13),
]
)
session.commit()
dd.read_sql_query(
select([Loan.date, Loan.balance]),
con="sqlite:////tmp/so.db",
index_col="date",
npartitions=3,
engine_kwargs={"echo": True},
)
```
**Anything else we need to know?**:
The issue appears to come from this block which calculates the limits before dividing them into partitions:
https://github.com/dask/dask/blob/8b95f983c232c1bd628e9cba0695d3ef229d290b/dask/dataframe/io/sql.py#L130-L137
Which essentially runs the following:
```py
pd.read_sql(select([func.min(Loan.date), func.max(Loan.date)]), con=engine.connect())
```
Whose output is
```none
min_1 max_1
0 0001-01-01 00:00:00 0001-01-01 00:00:00
```
and dtypes are objects since by default the `parse_dates` kwarg is None and datetimes don't get parsed
```none
min_1 object
max_1 object
dtype: object
```
With dtype being `object` then dask falls to
https://github.com/dask/dask/blob/8b95f983c232c1bd628e9cba0695d3ef229d290b/dask/dataframe/io/sql.py#L160-L164
and raises the exception.
**Environment**:
- Dask version: dask-2022.8.0
- Python version: 3.10.5
- Operating System: N/A
- Install method (conda, pip, source): pip
Contributor guide
Assessment
This issue has not been assessed yet.