ENH: sql support with SQLAlchemy - follow-up

[jorisvandenbossche]

#5950 is now merged! (Thanks to @mangecoeur)

But there were still some issue to flesh out/fix:

• release notes / 0.14.0 whatsnew (Here is the section in whatsnew for when you are ready): http://pandas-docs.github.io/pandas-docs-travis/whatsnew.html#whatsnew-0140-sql -> SQL: add release notes for refactor (GH6292) #7120
• figure out user API (API: user api for new sql functionality #6300) and update api.rst/docs accordingly
  • functional api (read_table/read_sql) (fixed in API: update SQL functional api (GH6300) #6867)
  • other public functions? (execute, has_table)` (see API: user api for new sql functionality #6300 and SQL: bunch of fixes based on the old tests #6987)
  • OO API (PandasSQLAlchemy?)
• get travis working (not skipping the tests)
  MySQL was taken out of the 2.7 build, for some reason was failing the build process: 043d165, and failure here (see install.sh): https://travis-ci.org/jreback/pandas/jobs/18477137 (note Joris: I think this is OK now with pymysql)
• get MySQL working (failing tests, see TEST: add basic postgresql tests #6316 (comment)) -> fixed in TST: enable MySQL tests #6613
• handle OperationsError (e.g. have PyMySQL installed but server is not running) -> TST: skip sql tests if connection to server fails #6651
• lower bar on sql alchemy version -> moved to BLD: test with an older version of sql alchemy #6340, BLD: use sqlalchemy 0.7.1 in 2.6 build (GH6340) #7022
• add postgresql tests (TEST: add basic postgresql tests #6316) (and other flavors?)
• move DataFrame.to_sql to generic and update it with new functionality -> fixed in SQL: move df.to_sql to generic + update with new functionality #6598
• timezone issues (see also ENH: sql support via SQLAlchemy, with legacy fallback #5950 (comment)) -> moved to SQL: error when reading postgres table with timezone  #7139
• check if relevant parts of old legacy tests pass with master -> SQL: bunch of fixes based on the old tests #6987
• check coverage of the test suite
• add vbench (esp. vs old code)
• columns argument in read_table not working
• use itertuples rather than iterrows BUG: SQL writing can lead to data loss #6509
• error when having integer column names (fixed in API: Stop modifying SQL column and names, and warn when pertinent. #6902)
• read and write a MultiIndex (so index_col should take a list as well as a scalar) (fixed in ENH: SQL multiindex support #6735)
• Allow mapping as parameters for SQL DBAPI2 DBAPI 2.0 broken when using dictionary as parameters #6708 (fixed in BUG: Allow mapping as parameters for SQL DBAPI2 #6709)
• Conversion of invalid column names is broken (you get column with None's) BUG/API: converting invalid column names in to_sql #6796, fixed in API: Stop modifying SQL column and names, and warn when pertinent. #6902
• Data written with to_sql legacy mode (sqlite/mysql) is not persistent (not written, only in memory) BUG: data written with to_sql legacy mode (sqlite/mysql) not persistent #6846, fixed in BUG: data written with to_sql legacy mode (sqlite/mysql) not persistent GH6846 #6875

There is also a todo list in #4163, but I thought it is cleaner to start a new one (but maybe we should copy some over).
Complement the list if you think of others!

[tony]

@jorisvandenbossche: Is there a minimum sqlalchemy version this is targeting?

Edit: I see v0.8.1 (https://github.com/pydata/pandas/pull/5950/files#diff-f2202167afc438a107cf9f73ac4f9fb1R98).

It may be helpful to know if it's a requirement / recommendation. If it's a larger project and the sqlalchemy version is locked and Pandas can be upgraded, they may still want to know if it's possible for them to utilize this at an older version.

[jreback]

@tony this should work for pretty much all versions of SQLAlchemy; it uses mainly the connection features

[stonebig]

Hello,

With the new sql interface, will the 'classic' DB2API still works with params of a sql being a dictionnary ?

typical example = "execute('SELECT * FROM ATABLE WHERE AFIELD=%(x)s', {'x':x})"

[TomAugspurger]

I added 'read and write a MultiIndex' to the list.

I (finally) have a need to use SQL so I'm going to be using sqlalchemy and the new pandas sql stuff pretty extensively in the next few weeks. I'll report what I find. Doing a df.to_sql() with a MultiIndex was the first thing I tried.

[jorisvandenbossche]

@TomAugspurger Super! Certainly report (and fix? :-) everything, bugs, annoyances, suggestions. There are still a lot of issue to report and fix.

@stonebig I think so. Would you be able to test it with?

[jreback]

@jorisvandenbossche

if you can move residual issues that are not likely to be closed in 0.14 to 0.14.1 (or later)

release notes / v0.14.0.txt needed as well

not pushing, but lmk when you think you are 'ready' enough on this

[jorisvandenbossche]

@jreback I will try to finish some things of this thursday of friday. Is that OK?

[jreback]

sure

thxs

[westurner]

As far as DOCs for SQLAlchemy in pandas.io.sql:

Docs

• https://pandas-docs.github.io/pandas-docs-travis/io.html#sql-queries
• https://pandas-docs.github.io/pandas-docs-travis/cookbook.html#cookbook-sql
• https://stackoverflow.com/questions/10065051/python-pandas-and-databases-like-mysql
• https://pandas-docs.github.io/pandas-docs-travis/search.html?q=sqlalchemy

Source, Tests:

• https://github.com/pydata/pandas/blob/master/pandas/io/sql.py
• https://github.com/pydata/pandas/blob/master/pandas/io/tests/test_sql.py