“Advanced SQLAlchemy queries” in Pandas 1.0.1 Document #32147
Description
Code Sample, a copy-pastable example if possible
import sqlalchemy as sa
from sqlalchemy import create_engine
engine = create_engine('sqlite:///:memory:')
data = pd.DataFrame({'id':[26,42,63],
'Date':['2012-10-18','2012-10-19', '2012-10-20'],
'Col_1':['X','Y','Z'],
'Col_2':[25.7,-12.4,5.73],
'Col_3':[True,False,True]})
data.to_sql('data', engine, if_exists='replace', index=False)
sa.MetaData().clear()
metadata = sa.MetaData(bind=engine)
data_table = sa.Table('data', metadata,
sa.Column('id', sa.Integer),
sa.Column('Date', sa.DateTime),
sa.Column('Col_1', sa.String),
sa.Column('Col_2', sa.Float),
sa.Column('Col_3', sa.Boolean)
)
pd.read_sql(sa.select([data_table]).where(data_table.c.Col_3 == True),engine)
Problem description
~/anaconda3/lib/python3.7/site-packages/pandas/core/internals/construction.py in (.0)
482 else:
483 # last ditch effort
--> 484 data = [tuple(x) for x in data]
485 return _list_to_arrays(data, columns, coerce_float=coerce_float, dtype=dtype)
486
ValueError: Couldn't parse datetime string: '2012-10-18'
The original version "where(data_table.c.Col_3 is True)" do not work , so i change to "where(data_table.c.Col_3 == True)" .
When I change "sa.Column('Date', sa.DateTime)" to "sa.Column('Date', sa.Date)" , it work well.
I want to know what happen ? And Why ?
Expected Output
| id | Date | Col_1 | Col_2 | Col_3 |
|---|---|---|---|---|
| 26 | 2012-10-18 | X | 25.70 | True |
| 63 | 2012-10-20 | Z | 5.73 | True |
Output of pd.show_versions()
INSTALLED VERSIONS
commit : None
python : 3.7.6.final.0
python-bits : 64
OS : Darwin
OS-release : 19.3.0
machine : x86_64
processor : i386
byteorder : little
LC_ALL : None
LANG : zh_CN.UTF-8
LOCALE : zh_CN.UTF-8
pandas : 1.0.1
numpy : 1.18.1
pytz : 2019.3
dateutil : 2.8.1
pip : 20.0.2
setuptools : 45.2.0.post20200210
Cython : 0.29.15
pytest : 5.3.5
hypothesis : 5.5.4
sphinx : 2.4.0
blosc : None
feather : None
xlsxwriter : 1.2.7
lxml.etree : 4.5.0
html5lib : 1.0.1
pymysql : None
psycopg2 : None
jinja2 : 2.11.1
IPython : 7.12.0
pandas_datareader: None
bs4 : 4.8.2
bottleneck : 1.3.1
fastparquet : 0.3.2
gcsfs : None
lxml.etree : 4.5.0
matplotlib : 3.1.3
numexpr : 2.7.1
odfpy : None
openpyxl : 3.0.3
pandas_gbq : None
pyarrow : 0.16.0
pytables : None
pytest : 5.3.5
pyxlsb : None
s3fs : None
scipy : 1.4.1
sqlalchemy : 1.3.13
tables : 3.4.4
tabulate : None
xarray : None
xlrd : 1.2.0
xlwt : 1.3.0
xlsxwriter : 1.2.7
numba : 0.48.0