ENH: sql support

[hayd]

UPDATE: this is continued in #6292 after the refactor based on sqlalchemy.

---

This is a central issue to track sql support (migrated from the mysql support ticket - #2482 and #1662).

• sqlite
• mysql (mysql support #2482)
• oracle (not tested, partially working)
• postgres, (partial via ENH: add PostgreSQL support for pandas.io.sql.get_schema #4574) (not tested, some parts possibly working, [SO user suggests it "works"],(http://stackoverflow.com/questions/17156084/unpacking-a-sql-select-into-a-pandas-dataframe/17156233#17156233))
• ODBC
• any others?

I think we are agreed we will be moving to use SQLAlchemy, in the hope of comprehensive cross-platform support:

• SQLAlchemy (SQLAlchemy in io.sql to manage different SQL dialects #2717, SQLalchemy interface in pandas.io #191)

Todo:

• travis should not skip tests (e.g. mysql TST: Do not skip MySQL tests on Travis. #4177)
• add flavor argument to read_sql
• handling no row returns (BUG: handle no rows returns from a sql query #3745)
• Specify chunksize (and iterator) for read_frame (Feature request: specify chunksize for read_sql #2908)
• read and write the index (and multi-index)
• mangle_dup_columns for read_frame (Handling of duplicate columns in pandas.io.sql.read_frame #2738)
• bug in write_frame replace (BUG: in io.sql.write_frame (replace) #4110, BUG: io.sql.write_frame(if_exists='replace') not working as expected (with fix) #2971, PR BUG/TST: Fix io.sql.write_frame replace bug and complete test coverage of if_exists functionality #4304, 4304r if_exists == 'fail' should actually work #6164)
• option in config to specify default engine/connection for SQL (maybe?)
• refactor SQLAlchemy/sqliite tests (@hayd was thinking different flavors could inherit tests from a baseclass, so tests are only flavor explicit if obviously flavor explicit...)

Better Dtype coercion:

• infer dtypes more carefully (discussed in mysql support #2482)
• NaN/NaT conversions (ENH: sql support for NaN/NaT conversions #2754, NaN value in dataframe caused _write_mysql error #4199, PR BUG: convert nan to None before insert data into mysql #4200)
• datetime.date conversions https://groups.google.com/forum/#!topic/pydata/KEBKxj4cWNI
• support encoding Add option for default_charset to sql.write_frame #5008

Peformance

• Vbenches! (for various flavors even?)

cc @garaud @mangecoeur @danielballan @changhiskhan

[garaud]

I think it can be good if the "way to go" was defined before doing remaining tasks. I think SQLAlchemy is a good way:

• less code
• better maintainable
• less specific to a given SQL engine

I don't know well SQLAlchemy. I can't tell if all wanted features can be implemented with it, or if it can cause some tricky problems.

Damien G.

[hayd]

@garaud I agree, I suspect it'll create fewer tricky problems than maintaining every flavour. :)

I think I've pulled in all the open issues re sql (as they'll have to be addressed in either case).

[danielballan]

And while we're addressing the big picture, write_frame ignores the index. HDFStore, by contrast, saves it. It's easy enough to promote the index to a column if you want to write it to a SQL table, but would it better to write the index column by default? I have no good ideas for how to implement this cleanly; I'm raising the issue in case someone else can think of a way.

[jreback]

you should write the index for sure (and provide index_col=) on reading

I guess this is a bit tricky because you don't have control of the table format (in HDFStore its opaque to the user)

maybe provide an option on writing, maybe : index_col='in_table' or False for dont' write it ?

or have the user provide a column name (whichif its already being written won't then do anything)

[danielballan]

I guess the question is what to call it, then, if "index" is taken.

[danielballan]

...and whether to attempt to use it as the PRIMARY KEY

[hayd]

index.name or index.names (do we have MultiIndex support ?) otherwise 'index' (or if Multi: [index_0, index_1, ...]) ?

Can check if already a column name and raise, I guess that is also under the multiple columns with the same name.. raise (or mangle_dup_columns first)... :s

Not sure can PK as index may not be unique... does pk matter?

[hayd]

@danielballan what am I supposed to be adding to get mysql tests to pass? Does travis run them?

SKIP: Cannot connect to database. Create a group of connection parameters under the heading [pandas] in your system's mysql default file, typically located at ~/.my.cnf or /etc/.my.cnf.

[danielballan]

Travis does not run them. (I wrote those tests before I had any experience with Travis, but I will start a branch to install MySQL on the full_deps build.)

Add a block to your MySQL configuration file that provides valid connection parameters for pandas to use.

[pandas]
host = localhost
user = ...
password = ...
database = any_database_that_exists

[jreback]

I think #4110 should be in here/closed is it a dup?

[danielballan]

No, not a dup, and still open I believe. @hayd can add it to the checklist.

Half the issues on the list could be closed in an afternoon's work, but maybe we should first decide whether we are moving to SQLAlchemy. @mangecoeur?

[hayd]

I rebased @mangecoeur's branch cleanly (no conflicts!) to master a few days ago, but couldn't get the tests working on my system - haven't got time to play for a couple of days.

Do you mind having a go @danielballan ?

[danielballan]

Sure... To avoid git surgery down the road, I'll ask a dumb question now. I should add your fork of pandas as a remote and work on a local copy of this branch, correct?