SQL: error when reading postgres table with timezone · Issue #7139 · pandas-dev/pandas (original) (raw)

I have a postgresql table with a column with type timestamp with time zone. Just reading this table (sql.read_sql_table("everyaware_by_passage", engine)) did not work, because of a datetime column: ValueError: Tz-aware datetime.datetime cannot be converted to datetime64 unless utc=True.

I can import it with read_sql, and there the imported datetime column is of type datetime.datetime with tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=120, name=None)

---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-9-13ac3c111575> in <module>()
----> 1 sql.read_sql_table("everyaware_by_passage", engine)

c:\users\vdbosscj\scipy\pandas-joris\pandas\io\sql.pyc in read_sql_table(table_name, con, meta, index_col, coerce_float, parse_dates, columns)
    268     table = pandas_sql.read_table(
    269         table_name, index_col=index_col, coerce_float=coerce_float,
--> 270         parse_dates=parse_dates, columns=columns)
    271 
    272     if table is not None:

c:\users\vdbosscj\scipy\pandas-joris\pandas\io\sql.pyc in read_table(self, table_name, index_col, coerce_float, parse_dates, columns)
    795         table = PandasSQLTable(table_name, self, index=index_col)
    796         return table.read(coerce_float=coerce_float,
--> 797                           parse_dates=parse_dates, columns=columns)
    798 
    799     def read_sql(self, sql, index_col=None, coerce_float=True,

c:\users\vdbosscj\scipy\pandas-joris\pandas\io\sql.pyc in read(self, coerce_float, parse_dates, columns)
    609             data, columns=column_names, coerce_float=coerce_float)
    610 
--> 611         self._harmonize_columns(parse_dates=parse_dates)
    612 
    613         if self.index is not None:

c:\users\vdbosscj\scipy\pandas-joris\pandas\io\sql.pyc in _harmonize_columns(self, parse_dates)
    691                 if col_type is datetime or col_type is date:
    692                     if not issubclass(df_col.dtype.type, np.datetime64):
--> 693                         self.frame[col_name] = _handle_date_column(df_col)
    694 
    695                 elif col_type is float:

c:\users\vdbosscj\scipy\pandas-joris\pandas\io\sql.pyc in _handle_date_column(col, format)
     52             return to_datetime(col, coerce=True, unit=format)
     53         else:
---> 54             return to_datetime(col, coerce=True, format=format)
     55 
     56 

c:\users\vdbosscj\scipy\pandas-joris\pandas\tseries\tools.pyc in to_datetime(arg, errors, dayfirst, utc, box, format, coerce, unit, infer_datetime_format)
    313         return arg
    314     elif isinstance(arg, Series):
--> 315         values = _convert_listlike(arg.values, False, format)
    316         return Series(values, index=arg.index, name=arg.name)
    317     elif com.is_list_like(arg):

c:\users\vdbosscj\scipy\pandas-joris\pandas\tseries\tools.pyc in _convert_listlike(arg, box, format)
    306                 return DatetimeIndex._simple_new(values, None, tz=tz)
    307             except (ValueError, TypeError):
--> 308                 raise e
    309 
    310     if arg is None:

ValueError: Tz-aware datetime.datetime cannot be converted to datetime64 unless utc=True