BUG: after pandas 2.1.0 sqlite isnt able to auto parse dates based on schema

[jadeidev]

Pandas version checks

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

To recreate this issue, create two new python 3.11 envs one with pandas 2.0.3 and one with pandas 2.1.1

import sqlite3
import pandas as pd
import contextlib

with contextlib.closing(sqlite3.connect("test.sql")) as conn:
    data = {"date": [pd.to_datetime("2022-01-01T16:23:11")]}
    # Create a DataFrame and insert to table
    df = pd.DataFrame(data).to_sql(name="TEST_TABLE", con=conn, if_exists="replace")

with contextlib.closing(
    sqlite3.connect("test.sql", detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES)
) as conn:
    sql = "SELECT * FROM TEST_TABLE"
    print(pd.read_sql(sql, conn).dtypes)

results in pandas 2.0.3:

index             int64
date     datetime64[ns]
dtype: object

results in pandas 2.1.1:

Traceback (most recent call last):
  File "c:\Users\iballa\source\repos\wistaetl\pandas_case.py", line 19, in <module>
    print(pd.read_sql(sql, conn).dtypes)
          ^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\iballa\AppData\Local\Programs\Python\virtualenv\py311\Lib\site-packages\pandas\io\sql.py", line 654, in read_sql
    return pandas_sql.read_query(
           ^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\iballa\AppData\Local\Programs\Python\virtualenv\py311\Lib\site-packages\pandas\io\sql.py", line 2343, in read_query
    data = self._fetchall_as_list(cursor)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\iballa\AppData\Local\Programs\Python\virtualenv\py311\Lib\site-packages\pandas\io\sql.py", line 2358, in _fetchall_as_list
    result = cur.fetchall()
             ^^^^^^^^^^^^^^
  File "C:\Users\iballa\AppData\Local\Programs\Python\virtualenv\py311\Lib\site-packages\pandas\io\sql.py", line 2102, in <lambda>
    convert_timestamp = lambda val: datetime.fromtimestamp(int(val))
                                                           ^^^^^^^^
ValueError: invalid literal for int() with base 10: b'2022-01-01T16:23:11'

What does work for 2.1.1:

with contextlib.closing(
    sqlite3.connect("test.sql")
) as conn:
    sql = "SELECT * FROM TEST_TABLE"
    print(pd.read_sql(sql, conn).dtypes)

However this doesn't automatically recognize the datetime columns:

index     int64
date     object
dtype: object

The only resolution is to use the parse_dates kwarg when calling read_sql, but with my previous approach, it is automatically done through the SQLite column definition on the DB.

is there a way to override pandas behavior?

Issue Description

Unable to automatically parse SQLite datetime columns

Expected Behavior

When reading sql from SQLite DB expect pandas to identify datetime columns

Installed Versions

INSTALLED VERSIONS for pandas 2.0.3

commit : 0f43794
python : 3.11.4.final.0
python-bits : 64
OS : Windows
OS-release : 10
Version : 10.0.22621
machine : AMD64
processor : Intel64 Family 6 Model 154 Stepping 3, GenuineIntel
byteorder : little
LC_ALL : None
LANG : en_US.UTF-8
LOCALE : English_United States.1252

pandas : 2.0.3
numpy : 1.26.1
pytz : 2023.3.post1
dateutil : 2.8.2
setuptools : 68.2.2
pip : 23.2.1
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : None
IPython : None
pandas_datareader: None
bs4 : None
bottleneck : None
brotli : None
fastparquet : None
fsspec : None
gcsfs : None
matplotlib : None
numba : None
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : None
pyreadstat : None
pyxlsb : None
s3fs : None
scipy : None
snappy : None
sqlalchemy : None
tables : None
tabulate : None
xarray : None
xlrd : None
zstandard : None
tzdata : 2023.3
qtpy : None
pyqt5 : None

INSTALLED VERSIONS for pandas 2.1.1

commit : e86ed37
python : 3.11.4.final.0
python-bits : 64
OS : Windows
OS-release : 10
Version : 10.0.22621
machine : AMD64
processor : Intel64 Family 6 Model 154 Stepping 3, GenuineIntel
byteorder : little
LC_ALL : None
LANG : en_US.UTF-8
LOCALE : English_United States.1252

pandas : 2.1.1
numpy : 1.26.1
pytz : 2023.3.post1
dateutil : 2.8.2
setuptools : 68.2.2
pip : 23.2.1
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : None
IPython : None
pandas_datareader : None
bs4 : None
bottleneck : None
dataframe-api-compat: None
fastparquet : None
fsspec : None
gcsfs : None
matplotlib : None
numba : None
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : None
pyreadstat : None
pyxlsb : None
s3fs : None
scipy : None
sqlalchemy : None
tables : None
tabulate : None
xarray : None
xlrd : None
zstandard : None
tzdata : 2023.3
qtpy : None
pyqt5 : None

[jorisvandenbossche]

@jadeidev thanks for the report! This is indeed a regression, and caused by #53743 where we added custom sqlite adapaters/converters for Python 3.12 compat (cc @lithomas1 )