[ENH] Allow passing MetaData (or meta kwargs) to high-level SQL functions

[aldanor]

Use case: I want to query an Oracle table but I'm not the owner, so meta.reflect(engine) would do nothing and always return an empty list of tables rendering read_sql_table useless. This works: meta.reflect(engine, schema='the_real_owner'), however there's no way of passing the metadata / schema to the high-level pandas.io.sql functions. One may also want to use a meta.reflect(engine, only=[...]) or meta.reflect(engine, oracle_resolve_synonyms=True) or any other dialect-specific argument.

PandasSQLAlchemy class already support passing meta in the constructor, so it's just a matter of a adding an extra keyword argument to the three io.sql.read_sql* functions. Or maybe allow passing through arbitrary kwargs to meta's reflect method, like read_sql_table(table, engine, reflect=dict(schema='my_schema', oracle_resolve_synonyms=True)) -- ugly but functional.

[jorisvandenbossche]

@aldanor on the only keyword you mentioned, this is already filed as #7396.

For passing a MetaData object to the read_sql_query/table functions, this was implemented partially in the past, but removed from the functional API (see discussion here: #6300 (comment) and commit ad97d27) to keep that as simple as possible for a first release (easier to add than to remove keywords). In any case, it is available in the OO API with PandasSQLAlchemy.

So basically, we just have to decide if we want to provide this functionality in both (read_* functions and object-interface), or only via the object (so that when you need this functionality, you should use the OO API).

[aldanor]

@jorisvandenbossche It may be just me, but I think it might make sense to allow the metadata object to be passed to the higher-level functions (or a set of arbitrary kwargs for reflect since there may be dialect-specific options) for those functions to be useful in setups more involved than the most basic ones.

In some environments (e.g. a typical corporate Oracle setup), you would absolutely have to pass the schema argument for reflect to work. And you may want to pass only in case your schema hosts ten thousand tables.

[jorisvandenbossche]

What would be the most common things you would want to adapt in the meta object? Of course the schema, but are there also other things? Can you give some examples?

Just asking as if the schema argument would be used a lot, I think we should provide it as a real keyword and not only through passing a MetaData object (most users should not start adapting a MetaData object themselves, so if a lot of users need the schema argument, we should provide it seperately of meta=..).

@mangecoeur @danielballan @hayd

[aldanor]

@jorisvandenbossche Do we consider the only argument being a special case here (as in, don't query the entire schema in read_sql functions)? As for the schema being a separate keyword, should the users be able to pass meta as well, so the function signature would contain schema=None, meta=None? If that doesn't clutter the API too much, that'd be nice.

As for myself, I sometimes have to use oracle_resolve_synonyms=True in MetaData#reflect or Table#__init__ when working with dblinks. I guess there exist a bunch of other useful dialect-specific options that I'm not aware of.

[jorisvandenbossche]

As I said above, the only keyword is already discussed in #7396, and it will be used by default (reflect only the database you are reading, and none for read_sql_query) so no seperate keyword needed I think? If you have further comments or remarks on that, you can comment there.

For the API, that seems a good option. Another one would be to provide a way to pass options directly to reflect with something like reflect_kwds={...}, but that seems a bit more ugly (and limits it to reflect).
Passing things to Table I think is too advanced for read_sql, but maybe we can see how we can do this in the OO API.

Would you be interested to do a PR to add this?

[aldanor]

I'm by no means sqlalchemy expert, so if someone more skilled in this would do it, it would be a better idea :) Elsewise I can give it a try on the weekend, will have to figure out how to set up test environment etc for the sql test suite then.

Re: API, schema and meta keywords look fine, what about the only keyword when you pass the meta in? In this case the user is fully responsible for reflecting whatever he needs, right?