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?

[jreback]

fyi...need to add SQLAlchemy as a build dep (in the requirements file) for full deps, @cpcloud ? (its alread in ci/print_versions.py thought)

[cpcloud]

fyi u can add to the requirements file "at will" (meaning, of course, when it makes sense) and things will be pulled from pypi unless i put up a wheel. wheels should be used only if the package is slow to install.

[mangecoeur]

@danielballan sadly because of workload i haven't had time to look into this much - i know pretty much what needs doing but i just don't have time to actually implement+test it. At the simplest level reading a frame using SQLAlchmey as a db abstraction looks like this:
https://gist.github.com/mangecoeur/5383970

(That's code i actually needed in production, so it works and is neater than what's in my branch)

The code for writing is in my branch, the principle is simple: map each Pandas Dtype to an SQLAlchemy supported type (most python builtins included), create a Table, write the values.

Testing is the kicker, i have very little experience with TDD but i do know that running tests that require DBs is generally a pain. On the other hand, SQLAlchemy's cross DB support is very good, so if it works with one DB it should work for them all, with SQLite as a kind of lowest common denominator.

Finally, I would recommend making SQLAlchemy a soft dependency (like a lot of the other IO) with a fallback to SQLite-support only since that's built into python - there's no point preventing people accessing SQLite just because they don't have SQLAlchemy installed. It's a simple try/except import, set an "sqlalchemy mode" flag, choose either genric or SQLite functions accordingly.

[hayd]

@danielballan I didn't get the travis build working anyway so feel free throw away my commit (I'd have to rebase after we pull in your mysql travis testing anyway).

[jreback]

I think that read_frame/write_frame should become internal only methods (and instead use the external facing read_sql/to_sql for consistency with the rest of the i/o (currently read_sql is just aliased to read_frame. I know that you will almost certainly just read/write frames, but leaves open then the possibility of dealing with Panel/Series (Series is sort of trivial), and e.g. in HDFStore, Panel is essentially converted to a mi-frame (though storing meta-data is different in SQL so not sure that is an option here).

[hayd]

Perhaps get SQLAlchemy working (passing the same tests as MySQL) first and then worry about precisely how to call from read_sql and to_sql... Saying that other operations use an engine argument (maybe this makes sense here).

[mangecoeur]

@jreback or maybe it should be more explicit: read_sqlite and read_sqlalchemy
Sqlalchemy supports raw SQL as well as its own "expression syntax" (which abstracts from the different SQL flavours used by different DBs) - so it would be nice in the future if read_sqlalchemy could also accept a query object as well as an SQL string.
At the moment if you use SQLAlchemy to read a DB using a raw SQL query even though it connects to all DBs transparently it's up to the user to make sure their SQL is formatted for the DB they're using - there's nothing you can really do about that for reading but it would be nice to make it possible to use sqlalchemy's abstractions (though this doesn't need to be done just yet)

[jreback]

added extra cool SQL label for these (CSV/HDF5) also exist as well

[jreback]

@mangecoeur I disagree, I believe write_frame as flavor= arg....so should read_sql
then this becomes straightforward, e.g. try this using say flavor=sqlite, or use the default (SQLAlchemy if installed, otherwise sqlite?)

you could also provide a module level set_default_flavor maybe

[cpcloud]

that could go in config

[jreback]

ahh...yes makes more sense, and fallback really doesn't make sense, and people rarely use more than one SQL backend

[cpcloud]

probably some of the other module level variables floating around could go in config as well

[mangecoeur]

@jreback thought about it some more and actually I agree with you now.

The SQL string given to read_sql has to be adapted by the user to fit the DB they know they are using, there's no point having different functions for different DBs. The function should accept both DBAPI connections and SQLAlchemy engines. There's no need to specify the flavor for read since pandas just needs to run the provided query through the given DB connection or engine, it doesn't need to modify it - therefore read will work so long as you provide a valid SQL for your DB and either a DBAPI compliant connection or an SQLAlchemy engine for that DB.

I think however we need to distinguish between 2 distinct user stories (use cases):

1. "I want to read an arbitrary dataset from a database using an arbitrarily complex SQL query" plus "I want to store dataframe values in an SQL table"

This case is served by read_sql and to_sql, which just needs to be able to save values, a user will write an appropriate SQL query to turn them back into a Dataframe if needed. There is no need for a "flavor" for to_sql because we can infer the correct column type mapping and let SQLAlchemy generate the correct SQL syntax based on the supplied engine. However we would like to have SQLite available as an option even if SQLAlchemy is not installed (this is what i meant by fallback, not that it would try to write to one database and fall back to writing to sqlite).

1. "I want to store the Dataframes I'm working in an SQL DB and retrieve them transparently later."

This is different from 1. because we have to assume a save format convention, or save additional metadata. You don't want to have to specify an SQL string when loading because the save/load layer should deal with it for you. This is akin to an ORM mapping, while 1. is dealing with raw SQL.

I believe the read_sqland to_sql feature under discussion should only attempt to address use case 1. Use case 2. is a different feature and might deserve its own read_ and to_ functions.

I think it's important to keep this difference in mind in order to have a clear understanding of what the IO functions should and should not attempt to do, and what problems they're actually trying to address.

It should also make the api simpler since, as I said above, there's no need for the user to specify SQL flavours as it can all be done automatically, and we don't need any additional machinery for defining save conventions because we assume they do not apply for this specific feature.

[jreback]

@mangecoeur

but if sqlalchemy is the flavor, then you dont't need to specify DB specific sql (which is a good thing), but you DO need to know the backend (I agree conn already has some of this info, so maybe the flavor can be inferred)

just trying to keep api as simple as possible (but not too simple!)

[mangecoeur]

@jreback I think you slightly misunderstood how sqlalchemy works. It provides you with a variety of abstractions in order to generate DB specific SQL (e.g. you create a Table object, SQLAlchemy figures out the correct CREATE TABLE syntax for the DB you're using). It also deals with things like transactions and connection pools for you. However if you tell the engine to execute a plain SQL string, it will not attempt to "translate" that string - how could it seeing as there is in practice no "strictly correct" SQL syntax and feature set for it to translate from in the first place.
What you can do is use their expression api to write queries in python which will be translated to the correct SQL flavour.

So if you are supply raw sql, you do need to know the DB you are using and to write SQL specific for that DB. SQLAlchemy also knows the db you're using, since you have to specify it when you create the engine with the connection URI, and it deals with all the communication business for you. The main strength of using SQLAlchemy in this case is not for reading using a provided SQL string (where it just provides a light abstraction from DBAPI) but for writing to the DB, because we can programatically generate an SQLAlchemy Table and let it generate the correct SQL based on the db you provided it through engine. All the information needed for this to happen is contained in the engine, so no flavour is required.

The only condition is see is if you have flavour= {'sqlite', 'sqlalchemy'}, rather than having two functions to_sqliteand to_sqlalchemy (or some other name).
However:

1. its easy to detect if sqlalchemy support is present through a try import... If it's not present you can't even try to use it because you can't supply an sqlalchemy engine. Setting the flag just raises an error, which you could have done simply during the try import...
2. If you set sqlite the function would accept a DBAPI connection and work with Sqlite featureset only, while setting sqlalchemy would accept an sqlalchemy engine. So the flavour is already implied by the arguments.
3. That said, I personally don't like the idea of a single to_sql function that could have very different output depending on whether it was using sqlalchemy (with advanced type mapping) vs basic built in mapping.

[mangecoeur]

Pull request: #5950

[jreback]

@jorisvandenbossche want to close this and open another issue with things that are not covered by #6292 (if they are at all relevant), but maybe want to address 'later'

[jorisvandenbossche]

@hayd You opened this issue, what do you think? Do you have a clear view what of this list is still relevant?

[hayd]

Let's close and move parts into 6292, this issue has got very long.