dask / dask/dask

dataframe.read_sql_query does not work with datetime index

Open
#9,383 2 comments 0 reactions 0 assignees View on GitHub
bug dataframe needs attention
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.