1

Pandas read_sql function is not working if there is a timestamp field to parse. Can someone please help on this DB: SQL server.

Timestamp field has value like this : 2021-04-22 06:27:18.6015279 -05:00

data_df = pd.read_sql("select * from tblA", cnxn, parse_dates={'last_updated_ts': '%Y-%m-%d, %H:%M:%S.%f')

The error I get is the following:

('ODBC SQL type -155 is not yet supported.  column-index=9  type=-155', 'HY106')

Any help will be very much appreciated.

Saawan
  • 363
  • 6
  • 24
  • Try passing `parse_dates=['last_updated_ts']`. Your date format is not correct. – deepAgrawal May 05 '21 at 05:10
  • Thanks for quick info.. parse_dates=['last_updated_ts'] doesn't work. Gives same error.. – Saawan May 05 '21 at 05:13
  • Check out [this previous thread](https://stackoverflow.com/questions/46405373/odbc-sql-type-155-is-not-yet-supported) with the same question. – deepAgrawal May 05 '21 at 05:20
  • I need to do select * from tbl but this field is not required to me.. Since I have a dynamic function which selects from different table, I dont want to provide column names but this field of timestamp is not required.. Is there a way to skip it or get it as string then I discard this column? – Saawan May 05 '21 at 05:30
  • This field is optional. You don't need to provide it. As this thread above mentioned the issue is "The error message says that this SQL Server datatype is currently unsupported by the Python ODBC API". Try defining the function `handle_datetimeoffset` and then call this `cnxn.add_output_converter(-155, handle_datetimeoffset)` before your `pd.read_sql` call – deepAgrawal May 05 '21 at 05:34
  • Another solution is to cast the column as string in your SQL statement and then read it as a string in python – deepAgrawal May 05 '21 at 05:38
  • But I dont want to give column names.. this function is called to execute query for 10-20 tables so need to keep column list in dictionary then.. – Saawan May 05 '21 at 05:58
  • You don't need to give column names with `handle_datetimeoffset`. It will use this function only for columns that give the error `-155`. Did you try it? – deepAgrawal May 05 '21 at 06:02

0 Answers0