ENH: full SQL support for datetime64 values (original) (raw)

See the tests I added for datetime, NaT and NaN values in PRs #7082 and #7100.

Postrgresql (psycopg2)

• writing and reading works

SQLite

• writing and reading works with sqlalchemy
• reading works with read_sql_table, and you get strings with read_sql_query (sqlite has no native datetime format -> nothing to do about that)
• writing fails with fallback mode 'Unsupported type' (datetime64 columns not supported with sqlite fallback #7567)

MySQL (pymysql / MySQLdb / mysql.connector)

• Reading works with all drivers
• Writing datetime64 columns depends on driver:
  • pymysql: fails with "KeyError: < class 'pandas.tslib.Timestamp' >"

  In [65]: import pymysql  
  In [66]: engine_mysql = sqlalchemy.create_engine('mysql+pymysql://root@localhost/pandas_nosetest')  
  In [67]: df = pd.DataFrame({'A': pd.date_range('2013-01-01 09:00:00', periods=3),  
  ...:                    'B': np.arange(3.0)})  
  In [68]: df.to_sql('test_datetime', engine_mysql, if_exists='replace')  
  Traceback (most recent call last):  
  File "<ipython-input-68-95961c7ce232>", line 1, in <module>  
  df.to_sql('test_datetime', engine_mysql, if_exists='replace')  
  ...  
  File "C:\Anaconda\envs\devel\lib\site-packages\pymysql\converters.py", line 24, in escape_item  
  encoder = encoders[type(val)]  
  KeyError: <class 'pandas.tslib.Timestamp'>  

  • MySQLdb: works
  • mysql.connector: fails with "ProgrammingError: Failed processing pyformat-parameters; Python 'timestamp' cannot be converted to a MySQL type" or with " 'MySQLConverter' object has no attribute '_timestamp_to_mysql' " (see 'MySQLConverter' object has no attribute '_timestamp_to_mysql' error with datetime64[ns], MySQL and MySQL-connector #7936 for more details)

The data are feeded to the database using df.itertuples() and then np.asscalar(), and this gives data of the Timestamp class. And for some reason most drivers can handle this (because it is a subclass of datetime.datetime?), but pymysql and mysql.connector not.

For now, all the tests are skipped for MySQL / pymysql.

Oracle / SQL Server ?

There are also some issues with NaT handling (see the tests I added for datetime, NaT and NaN values in PRs #7082 and #7100), but to track this there is also an issue specific on NaN handling (#2754)