TST: enable MySQL tests
State: closed

PR to fix and enable the MySQL tests for the new sql functionality.
- floats (decimal.Decimal) were not converted due to a small bug in the `coerce_float` arg. This is now OK.
- MySQL has no real BOOL type (it is just an alias for tiny int). So I changed the tests for that (so a bool column is converted to int64 (or float if there are NA's)

We should maybe add somewhere in the sql docs an overview of the limitations of the type conversion.


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:
@jreback Is there a way to see if the tests are not skipped anymore on Travis? (because before they were) I just see 'Ran 4980 tests in 266.519s OK (SKIP=107)' Can I see which are the 107 skipped?

go to the build
at the very bottom are little arrows to the skipped tests

ah yes, I see, the print_skipped. Thanks! And they are not skipped anymore, and passing!

looks good

also enabled on the windows builds.....

merge when ready

Nice! 
For me this is ready. @mangecoeur OK for you?

@jorisvandenbossche all good with me

I got this to work on windows (and all tests pass).

Oracle makes this EXTRA hard to download setup the server!

postgres so much easier!

I think you should catch these tests and skip
e.g. pymysql is installed but no server is running (or can't login)

secondarily these tests take a LONG time on windows! 150s!
no tests seems that slow, maybe a server setting (its a stock install)

```
======================================================================
ERROR: test_to_sql_replace (pandas.io.tests.test_sql.TestPostgreSQLAlchemy)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/mnt/home/jreback/pandas/pandas/io/tests/test_sql.py", line 778, in setUp
    self.pandasSQL = sql.PandasSQLAlchemy(self.conn)
  File "/mnt/home/jreback/pandas/pandas/io/sql.py", line 615, in __init__
    meta.reflect(self.engine)
  File "/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.7.7-py2.7-linux-x86_64.egg/sqlalchemy/schema.py", line 2474, in reflect
    conn = bind.contextual_connect()
  File "/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.7.7-py2.7-linux-x86_64.egg/sqlalchemy/engine/base.py", line 2489, in contextual_connect
    self.pool.connect(),
  File "/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.7.7-py2.7-linux-x86_64.egg/sqlalchemy/pool.py", line 224, in connect
    return _ConnectionFairy(self).checkout()
  File "/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.7.7-py2.7-linux-x86_64.egg/sqlalchemy/pool.py", line 387, in __init__
    rec = self._connection_record = pool._do_get()
  File "/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.7.7-py2.7-linux-x86_64.egg/sqlalchemy/pool.py", line 739, in _do_get
    con = self._create_connection()
  File "/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.7.7-py2.7-linux-x86_64.egg/sqlalchemy/pool.py", line 188, in _create_connection
    return _ConnectionRecord(self)
  File "/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.7.7-py2.7-linux-x86_64.egg/sqlalchemy/pool.py", line 270, in __init__
    self.connection = self.__connect()
  File "/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.7.7-py2.7-linux-x86_64.egg/sqlalchemy/pool.py", line 330, in __connect
    connection = self.__pool._creator()
  File "/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.7.7-py2.7-linux-x86_64.egg/sqlalchemy/engine/strategies.py", line 80, in connect
    return dialect.connect(*cargs, **cparams)
  File "/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.7.7-py2.7-linux-x86_64.egg/sqlalchemy/engine/default.py", line 281, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/usr/local/lib/python2.7/site-packages/psycopg2/__init__.py", line 179, in connect
    connection_factory=connection_factory, async=async)
OperationalError: (OperationalError) could not connect to server: Connection refused
        Is the server running on host "localhost" and accepting
        TCP/IP connections on port 5432?
 None None

----------------------------------------------------------------------
Ran 80 tests in 0.837s

FAILED (SKIP=26, errors=16)
```

Yes, catching the errors is on the to do list in #6292.

For the time, I experienced it as very varying, but the last time I ran them on Windows (all sql tests), it was 26 s for 80 tests.

I think one way to speed up the tests is maybe just making the connection and test tables once for one test class, and not for every test seperately (using `setup_class` instead of `setup`), but then should look that no test collide with table names.

Are there examples in the code base how the OperationalError can be catched in the tests?

look in utils/testing.py at the `@network` decorator. needs something like this for sql (or could just do it in the connect method (where you try except around it and skip if appropraite)

Something funny going on...

I though its only the MySQLAlchemy tests that take an exceptional amount of time, the other (and postgres) are pretty fast.

I though tit was 0.8.3 that was not compiled with c-extensions (as I had originally did pip install), but I just installed the binaries of 0.9.3

```
INSTALLED VERSIONS
------------------
commit: 6899ed662bdec20aaa3b9f9563236a0c4385395e
python: 2.7.5.final.0
python-bits: 64
OS: Windows
OS-release: 7
machine: AMD64
processor: Intel64 Family 6 Model 42 Stepping 7, GenuineIntel
byteorder: little
LC_ALL: None
LANG: None

pandas: 0.12.0
Cython: 0.19.2
numpy: 1.8.0
scipy: 0.13.0
statsmodels: 0.5.0
IPython: 1.1.0
sphinx: None
patsy: 0.2.1
scikits.timeseries: None
dateutil: 2.2
pytz: 2013.8
bottleneck: 0.7.0
tables: 3.0.0
numexpr: 2.2.2
matplotlib: 1.3.1
openpyxl: 1.7.0
xlrd: 0.9.2
xlwt: 0.7.5
xlsxwriter: 0.5.0
lxml: None
bs4: 4.3.2
html5lib: 1.0b3
bq: None
apiclient: None
rpy2: None
sqlalchemy: 0.9.3
pymysql: 0.6.1.None
psycopg2: 2.5.2 (dt dec pq3 ext)
```

```
C:\Users\Jeff Reback\Documents\GitHub\pandas>c:\python27-64\Scripts\nosetests.exe build\lib.win-amd64-2.7\pandas\io\tests\test_sql.py:TestMySQL -v
test_create_and_drop_table (pandas.io.tests.test_sql.TestMySQL) ... ok
test_execute_sql (pandas.io.tests.test_sql.TestMySQL) ... ok
test_invalid_flavor (pandas.io.tests.test_sql.TestMySQL) ... ok
test_read_sql (pandas.io.tests.test_sql.TestMySQL) ... ok
test_roundtrip (pandas.io.tests.test_sql.TestMySQL) ... ok
test_to_sql (pandas.io.tests.test_sql.TestMySQL) ... ok
test_to_sql_append (pandas.io.tests.test_sql.TestMySQL) ... ok
test_to_sql_fail (pandas.io.tests.test_sql.TestMySQL) ... ok
test_to_sql_replace (pandas.io.tests.test_sql.TestMySQL) ... ok
test_tquery (pandas.io.tests.test_sql.TestMySQL) ... ok
----------------------------------------------------------------------
Ran 10 tests in 11.836s


C:\Users\Jeff Reback\Documents\GitHub\pandas>c:\python27-64\Scripts\nosetests.exe build\lib.win-amd64-2.7\pandas\io\tests\test_sql.py:TestSQLApi
............
----------------------------------------------------------------------
Ran 12 tests in 0.306s
```

```
C:\Users\Jeff Reback\Documents\GitHub\pandas>c:\python27-64\Scripts\nosetests.exe build\lib.win-amd64-2.7\pandas\io\tests\test_sql.py:TestMySQLAlchemy -v
test_create_table (pandas.io.tests.test_sql.TestMySQLAlchemy) ... ok
test_date_parsing (pandas.io.tests.test_sql.TestMySQLAlchemy) ... ok
test_default_date_load (pandas.io.tests.test_sql.TestMySQLAlchemy) ... ok
test_default_type_conversion (pandas.io.tests.test_sql.TestMySQLAlchemy) ... ok
test_drop_table (pandas.io.tests.test_sql.TestMySQLAlchemy) ... ok
test_execute_sql (pandas.io.tests.test_sql.TestMySQLAlchemy) ... ok
test_mixed_dtype_insert (pandas.io.tests.test_sql.TestMySQLAlchemy) ... ok
test_read_sql (pandas.io.tests.test_sql.TestMySQLAlchemy) ... ok
test_read_table (pandas.io.tests.test_sql.TestMySQLAlchemy) ... ok
test_read_table_absent (pandas.io.tests.test_sql.TestMySQLAlchemy) ... ok
test_read_table_columns (pandas.io.tests.test_sql.TestMySQLAlchemy) ... ok
test_roundtrip (pandas.io.tests.test_sql.TestMySQLAlchemy) ... ok
test_to_sql (pandas.io.tests.test_sql.TestMySQLAlchemy) ... ok
test_to_sql_append (pandas.io.tests.test_sql.TestMySQLAlchemy) ... ok
test_to_sql_fail (pandas.io.tests.test_sql.TestMySQLAlchemy) ... ok
test_to_sql_replace (pandas.io.tests.test_sql.TestMySQLAlchemy) ... ok

----------------------------------------------------------------------
Ran 16 tests in 134.062s
```

@jreback What if you only run the PostgreSQLAlchemy tests? Are these faster than the MySQLAlchemy?

It seems to be going faster after some runs of the tests, but now (on Windows) I get 25s for both PostgreSQL and MySQL tests, and 8-9 s for without MySQL.

Postgres is fine about 10s on my machine

that's why I am thinking its a setting on the MySQL server but nothing obvious 

it's just a stock setup

Is it a problem for the test suite? (for testing on travis, that it takes to long)?

For speeding up the tests, possible things to explore:
- not make the connection for every test, but just once for the test class (although I don't know if this is the time consuming step, should profile it, as in all other actions (reading/writing tables, etc) also always connections are made)
- Maybe not all tests that are now tested in the flavor test classes should be tested for all flavors? Eg if to_sql `if_exists` works is maybe not flavor dependent? And only leave the real flavor issues (type conversions etc) in those test classes?

it's quite fast on Travis and since Postgres is fast I am thinking maybe it's some setting specific to MySQL 

seems really odd

your suggestions are good but prob don't need to optimize the testing as a high priority