read_sql/to_sql should accept database URI as con parameter

[s-celles]

Hello,

it will be nice if read_sql (and so read_sql_query and read_sql_table could directly accept database URI as con parameter
http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_sql.html

So we could do

import pandas as pd
uri = 'dialect+driver://username:password@host:port/database'
query = 'SELECT * FROM table'
df = pd.read_sql(query, uri)

instead of :

import pandas as pd
from sqlalchemy import create_engine
uri = 'dialect+driver://username:password@host:port/database'
engine = create_engine(uri)
query = 'SELECT * FROM table'
df = pd.read_sql(query, engine)

Kind regards

PS: this kind of idea is also used by Blaze / odo
http://blaze.pydata.org/
http://odo.readthedocs.org/

PS2: A similar approach could maybe also apply to to_sql(...)

[s-celles]

I think _is_sqlalchemy_connectable in https://github.com/pydata/pandas/blob/80597dc2a8a943b710317d1f56990af20e7c8657/pandas/io/sql.py should be modified

def _is_sqlalchemy_connectable(con):
    ...
    if _SQLALCHEMY_INSTALLED:
        import sqlalchemy
        if isinstance(con, string_types):
            con = sqlalchemy.create_engine(con)
        return isinstance(con, sqlalchemy.engine.Connectable)
    else:
        return False

[jorisvandenbossche]

Yes, I think this would be nice, and not too difficult.

Do you want to give this a try? The suggestion you give seems good, only that the check is probably better done in pandasSQL_builder, so you an use the newly created engine to pass to SQLDatabase

[s-celles]

Should I also change doc, create unit tests ?

[jorisvandenbossche]

I possible yes, but whatever contribution would already be welcome. For the docs, just updating the docstring of the different functions would already be a start. And for the tests, a simple test that checks if the passing of a string uri works and the engine of the underlying SQLDatabase object is correctly created is OK.

[s-celles]

It may be a good idea to tell contributors how to run only ONE test in http://pandas.pydata.org/developers.html#running-the-test-suite

$ cd pandas
$ nosetests tests.test_frame:test_pivot -s -v

raises

Fatal Python error: Py_Initialize: can't initialize sys standard streams
AttributeError: 'module' object has no attribute 'OpenWrapper'
Abort trap: 6

[s-celles]

I also wonder in which class should I put tests https://github.com/pydata/pandas/blob/master/pandas/io/tests/test_sql.py

$ nosetests pandas.io.tests.test_sql -s -v

run all tests of test_sql.py file but how to run one.

[s-celles]

Same comment here than in #10664 about table URI vs database URI

Blaze uses table URI like 'dialect+driver://username:password@host:port/database::table' (instead of database URI like 'dialect+driver://username:password@host:port/database'
so passing table as parameter is not (always) necessary, as it may be contained inside a (table) URI
but It may be difficult to mimic this API without breaking eggs (con should be first parameter and sql should be second with a default value)

[jorisvandenbossche]

@scls19fr To run a single class of tests or even a single test, the following works for me:

nosetests pandas\io\tests\test_sql.py:TestSQLApi
................................
----------------------------------------------------------------------
Ran 32 tests in 1.536s

nosetests pandas\io\tests\test_sql.py:TestSQLApi.test_categorical
.
----------------------------------------------------------------------
Ran 1 test in 0.076s

OK

[jorisvandenbossche]

And I think putting it in TestSQLApi is fine

[s-celles]

My Mac doesn't like \ ;-)

$ nosetests pandas/io/tests/test_sql.py:TestSQLApi.test_to_sql_read_sql_with_database_uri -s -v