TST: sql add more tests (NaN handling, ..)
State: closed

This PR just adds some tests, not fixes to get them all running.

Summary:
- NaN writing not working for MySQL (or pymysql), but does work for sqlite and postgresql
- NaN reading does work for float columns. But not for string columns (converted to None or u'NaN') or for sqlite full float columns
- NaT in datetime columns only works for sqlite for writing. Writing postgresql gives an error on inserting the date `0001-255-255T00:00:00`. 


Labels:
name: Testing
description: pandas testing functions or related to the test suite
name: IO SQL
description: to_sql, read_sql, read_sql_query

Comments:
ok....maybe an issue specifically to track the fixing of these (and sub-link to the issues with checkboxes?)

and if you want to push some to 0.14.1 ok too

Added it to the issue #2754 

these are currently breaking on windows because the `sqlite` is not skipping

should it be?

its having a parse error on the NaT

```
========================================================================
ERROR: test_datetime_NaT (pandas.io.tests.test_sql.TestSQLiteAlchemy)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "C:\Users\Jeff Reback\Documents\GitHub\pandas\build\lib.win32-3.4\pandas\io\tests\test_sql.py", line 912, in test_datetime_NaT
    result = sql.read_sql_table('test_datetime', self.conn)
  File "C:\Users\Jeff Reback\Documents\GitHub\pandas\build\lib.win32-3.4\pandas\io\sql.py", line 270, in read_sql_table
    parse_dates=parse_dates, columns=columns)
  File "C:\Users\Jeff Reback\Documents\GitHub\pandas\build\lib.win32-3.4\pandas\io\sql.py", line 797, in read_table
    parse_dates=parse_dates, columns=columns)
  File "C:\Users\Jeff Reback\Documents\GitHub\pandas\build\lib.win32-3.4\pandas\io\sql.py", line 609, in read
    data, columns=column_names, coerce_float=coerce_float)
  File "C:\Users\Jeff Reback\Documents\GitHub\pandas\build\lib.win32-3.4\pandas\core\frame.py", line 798, in from_records
    coerce_float=coerce_float)
  File "C:\Users\Jeff Reback\Documents\GitHub\pandas\build\lib.win32-3.4\pandas\core\frame.py", line 4684, in _to_arrays
    data = lmap(tuple, data)
  File "C:\Users\Jeff Reback\Documents\GitHub\pandas\build\lib.win32-3.4\pandas\compat\__init__.py", line 87, in lmap
    return list(map(*args, **kwargs))
  File "C:\Python34-32\lib\site-packages\sqlalchemy-0.9.2-py3.4.egg\sqlalchemy\engine\result.py", line 63, in __iter__
    yield processor(value)
  File "C:\Python34-32\lib\site-packages\sqlalchemy-0.9.2-py3.4.egg\sqlalchemy\processors.py", line 39, in process
    "'%s'" % (type_.__name__, value))
ValueError: Couldn't parse datetime string: '-001--1--1 -1:-1:-1.-00001'

----------------------------------------------------------------------
Ran 23 tests in 3.896s

FAILED (errors=1)
```

Strange, I tested this on Windows (python 2.7) and it works. Something with the python version?

its happenin on several version 26-64,33-32,33-64 (where I have things installed)

I can't reproduce this on windows, python 2.7, 64bit, sqlalchemy 0.8.2, so difficult to debug. But I suppose something is different in the datetime parsing between different versions, leading to an error or to NaN. 
But maybe just skip this test for now? As it is not really supported? (and it is already skipped for postgresql and mysql)

yes
just skip for now (and in any event NaT support issue should remain open)
but can deal with in later releases