`read_sql_table` fails on Geopackge SQLite file due to DATETIME field with string value

[DavidMStraub]

Code Sample, a copy-pastable example if possible

import sqlite3
import pandas as pd

con = sqlite3.connect("tmp.db")
c = con.cursor()
c.execute("CREATE TABLE gpkg_contents (last_change DATETIME);")
c.execute("INSERT INTO gpkg_contents VALUES ('2019-01-01T00:00:00.000Z');")
con.commit()
con.close()
df = pd.read_sql_table(
    "gpkg_contents",
    "sqlite:///tmp.db",
    parse_dates=["last_change"]
)
df["last_change"][0]

Problem description

The above minimal example raises the following error:

---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-172-a1e2291991c6> in <module>
      6 con.commit()
      7 con.close()
----> 8 df = pd.read_sql_table("gpkg_contents", "sqlite:///tmp.db", parse_dates=["last_change"])
      9 df["last_change"][0]

~/.venv/lib/python3.7/site-packages/pandas/io/sql.py in read_sql_table(table_name, con, schema, index_col, coerce_float, parse_dates, columns, chunksize)
    252         parse_dates=parse_dates,
    253         columns=columns,
--> 254         chunksize=chunksize,
    255     )
    256 

~/.venv/lib/python3.7/site-packages/pandas/io/sql.py in read_table(self, table_name, index_col, coerce_float, parse_dates, columns, schema, chunksize)
   1143             parse_dates=parse_dates,
   1144             columns=columns,
-> 1145             chunksize=chunksize,
   1146         )
   1147 

~/.venv/lib/python3.7/site-packages/pandas/io/sql.py in read(self, coerce_float, parse_dates, columns, chunksize)
    803             data = result.fetchall()
    804             self.frame = DataFrame.from_records(
--> 805                 data, columns=column_names, coerce_float=coerce_float
    806             )
    807 

~/.venv/lib/python3.7/site-packages/pandas/core/frame.py in from_records(cls, data, index, exclude, columns, coerce_float, nrows)
   1611             arr_columns = columns
   1612         else:
-> 1613             arrays, arr_columns = to_arrays(data, columns, coerce_float=coerce_float)
   1614 
   1615             arr_columns = ensure_index(arr_columns)

~/.venv/lib/python3.7/site-packages/pandas/core/internals/construction.py in to_arrays(data, columns, coerce_float, dtype)
    485     else:
    486         # last ditch effort
--> 487         data = [tuple(x) for x in data]
    488         return _list_to_arrays(data, columns, coerce_float=coerce_float, dtype=dtype)
    489 

~/.venv/lib/python3.7/site-packages/pandas/core/internals/construction.py in <listcomp>(.0)
    485     else:
    486         # last ditch effort
--> 487         data = [tuple(x) for x in data]
    488         return _list_to_arrays(data, columns, coerce_float=coerce_float, dtype=dtype)
    489 
ValueError: Couldn't parse datetime string: '2019-01-01T00:00:00.000Z

Surprisingly, using

pd.read_sql_query("SELECT * FROM gpkg_contents", ...)

instead of read_sql_table does give exactly the expected result.

As the table name suggests, I encountered this bug when working on a Geopackage file, that uses a CREATE statement with DATETIME type and string values as per the format specification and this is thus implemented as such in many GIS tools.

Expected Output

Timestamp('2019-01-01 00:00:00+0000', tz='UTC')

Output of pd.show_versions()

INSTALLED VERSIONS

commit : None
python : 3.7.5.final.0
python-bits : 64
OS : Linux
OS-release : 5.3.0-24-generic
machine : x86_64
processor : x86_64
byteorder : little
LC_ALL : None
LANG : de_DE.UTF-8
LOCALE : de_DE.UTF-8

pandas : 0.25.3
numpy : 1.18.1
pytz : 2019.3
dateutil : 2.8.1
pip : 19.3.1
setuptools : 44.0.0
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : 2.10.3
IPython : 7.11.1
pandas_datareader: None
bs4 : None
bottleneck : None
fastparquet : None
gcsfs : None
lxml.etree : None
matplotlib : 3.1.2
numexpr : None
odfpy : None
openpyxl : 3.0.2
pandas_gbq : None
pyarrow : None
pytables : None
s3fs : None
scipy : 1.4.1
sqlalchemy : 1.3.12
tables : None
xarray : None
xlrd : None
xlwt : None
xlsxwriter : None

[kastkeepitjumpinlikekangaroos]

as of 0a1577f this is returning the following stack trace for me:

[nav] In [6]: import sqlite3                                                                                                                              
         ...: import pandas as pd                                                                                                                         
         ...:                                                                                                                                             
         ...: con = sqlite3.connect("tmp.db")                                                                                                             
         ...: c = con.cursor()                                                                                                                            
         ...: c.execute("CREATE TABLE gpkg_contents (last_change DATETIME);")                                                                             
         ...: c.execute("INSERT INTO gpkg_contents VALUES ('2019-01-01T00:00:00.000Z');")                                                                 
         ...: con.commit()                                                                                                                                
         ...: con.close()                                                                                                                                 
         ...: df = pd.read_sql_table(                                                                                                                     
         ...:     "gpkg_contents",                                                                                                                        
         ...:     "sqlite:///tmp.db",                                                                                                                     
         ...:     parse_dates=["last_change"]                                                                                                             
         ...: )                                                                                                                                           
         ...: df["last_change"][0]                                                                                                                        
---------------------------------------------------------------------------                                                                               
ValueError                                Traceback (most recent call last)                                                                               
Cell In[6], line 10                                                                                                                                       
      8 con.commit()                                                                                                                                      
      9 con.close()  
---> 10 df = pd.read_sql_table(                                                                                                                           
     11     "gpkg_contents",                                                                                                                              
     12     "sqlite:///tmp.db",                                                                                                                           
     13     parse_dates=["last_change"]                                                                                                                   
     14 )                                                                                                                                                 
     15 df["last_change"][0]                                                                                                                              
                                                                                                                                                          
File ~/pandas/pandas/io/sql.py:352, in read_sql_table(table_name, con, schema, index_col, coerce_float, parse_dates, columns, chunksize, dtype_backend)   
    349     if not pandas_sql.has_table(table_name):                                                                                                      
    350         raise ValueError(f"Table {table_name} not found")                                                                                         
--> 352     table = pandas_sql.read_table(                                                                                                                
    353         table_name,                                                                                                                               
    354         index_col=index_col,                                                                                                                      
    355         coerce_float=coerce_float,                                                                                                                
    356         parse_dates=parse_dates,                                                                                                                  
    357         columns=columns,                                                                                                                          
    358         chunksize=chunksize,                                                                                                                      
    359         dtype_backend=dtype_backend,                                                                                                              
    360     )                                                                                                                                             
    362 if table is not None:                                                                                                                             
    363     return table                                                                                                                                  
                                                                                                                                                          
File ~/pandas/pandas/io/sql.py:1727, in SQLDatabase.read_table(self, table_name, index_col, coerce_float, parse_dates, columns, schema, chunksize, dtype_b
ackend)                                                                                                                                                   
   1725 if chunksize is not None:                                                                                                                         
   1726     self.returns_generator = True                                                                                                                 
-> 1727 return table.read(                                                                                                                                
   1728     self.exit_stack,                                                                                                                              
   1729     coerce_float=coerce_float,                                                                                                                    
   1730     parse_dates=parse_dates,                                                                                                                      
   1731     columns=columns,                                                                                                                              
   1732     chunksize=chunksize,                                                                                                                          
   1733     dtype_backend=dtype_backend,                                                                                                                  
   1734 )
File ~/pandas/pandas/io/sql.py:1184, in SQLTable.read(self, exit_stack, coerce_float, parse_dates, columns, chunksize, dtype_backend)                     
   1174     return self._query_iterator(                                                                                                                  
   1175         result,                                                                                                                                   
   1176         exit_stack,                                                                                                                               
   (...)                                                                                                                                                  
   1181         dtype_backend=dtype_backend,                                                                                                              
   1182     )                                                                                                                                             
   1183 else:                                                                                                                                             
-> 1184     data = result.fetchall()                                                                                                                      
   1185     self.frame = _convert_arrays_to_dataframe(                                                                                                    
   1186         data, column_names, coerce_float, dtype_backend                                                                                           
   1187     )                                                                                                                                             
   1189     self._harmonize_columns(                                                                                                                      
   1190         parse_dates=parse_dates, dtype_backend=dtype_backend                                                                                      
   1191     )                                                                                                                                             
                                                                                                                                                          
File ~/pandas/venv/lib/python3.10/site-packages/sqlalchemy/engine/result.py:1317, in Result.fetchall(self)                                                
   1314 def fetchall(self) -> Sequence[Row[_TP]]:                                                                                                         
   1315     """A synonym for the :meth:`_engine.Result.all` method."""                                                                                    
-> 1317     return self._allrows()                                                                                                                        
                                                                                                                                                          
File ~/pandas/venv/lib/python3.10/site-packages/sqlalchemy/engine/result.py:551, in ResultInternal._allrows(self)                                         
    549 made_rows: List[_InterimRowType[_R]]                                                                                                              
    550 if make_row:                                                                                                                                      
--> 551     made_rows = [make_row(row) for row in rows]                                                                                                   
    552 else:                                                                                                                                             
    553     made_rows = rows  # type: ignore                                                                                                              
                                                                                                                                                          
File ~/pandas/venv/lib/python3.10/site-packages/sqlalchemy/engine/result.py:551, in <listcomp>(.0)                                                        
    549 made_rows: List[_InterimRowType[_R]]                                                                                                              
    550 if make_row:                                                                                                                                      
--> 551     made_rows = [make_row(row) for row in rows]                                                                                                   
    552 else:                                                                                                                                             
    553     made_rows = rows  # type: ignore                                                                                                              
                                                                                                                                                          
File lib/sqlalchemy/cyextension/resultproxy.pyx:22, in sqlalchemy.cyextension.resultproxy.BaseRow.__init__()                                              
                                                                                                                                                          
File lib/sqlalchemy/cyextension/resultproxy.pyx:79, in sqlalchemy.cyextension.resultproxy._apply_processors()                                             
                                                                                                                                                          
File lib/sqlalchemy/cyextension/processors.pyx:40, in sqlalchemy.cyextension.processors.str_to_datetime()                                                 
                                                                                                                                                          
ValueError: Invalid isoformat string: '2019-01-01T00:00:00.000Z'      

and in the function that's raising this exception lib/sqlalchemy/cyextension/processors.pyx:40, in sqlalchemy.cyextension.processors.str_to_datetime() the datetime function fromisoformat is used:

def str_to_datetime(value):
    if value is not None:
        value = datetime_cls.fromisoformat(value)
    return value

which when called with the ISO string in the example raises the same exception that read_sql_table is:

>>> import datetime
>>> datetime.datetime.fromisoformat('2019-01-01T00:00:00.000Z')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ValueError: Invalid isoformat string: '2019-01-01T00:00:00.000Z'

So I think this issue can maybe be closed because its a result of sqlalchemy and datetime not supporting the Z format for ISO strings with a UTC timezone. The equivalent 2019-01-01T00:00:00.000-00:00 works as expected:

[ins] In [1]: import sqlite3
         ...: import pandas as pd
         ...: 
         ...: con = sqlite3.connect("tmp.db")
         ...: c = con.cursor()
         ...: c.execute("CREATE TABLE gpkg_contents (last_change DATETIME);")
         ...: c.execute("INSERT INTO gpkg_contents VALUES ('2019-01-01T00:00:00.000-00:00');")
         ...: con.commit()
         ...: con.close()
         ...: df = pd.read_sql_table(
         ...:     "gpkg_contents",
         ...:     "sqlite:///tmp.db",
         ...:     parse_dates=["last_change"]
         ...: )
         ...: df["last_change"][0]
Out[1]: Timestamp('2019-01-01 00:00:00+0000', tz='UTC')

[ins] In [2]: import datetime

[ins] In [3]: datetime.datetime.fromisoformat('2019-01-01T00:00:00.000-00:00')
Out[3]: datetime.datetime(2019, 1, 1, 0, 0, tzinfo=datetime.timezone.utc)