ENH: add schema support to sql functions
State: closed

Related #7441. Work in progress for now

Add support to read/write data from/to a specific database schema through a `schema` keyword. If this is `None`, use the default schema as before.
- [x] support via a `schema` arg in the `read_sql_table` and `to_sql` functions
- [x] not for `read_sql_query` -> is this needed? (this is a direct `execute`, and I don't directly see how this can be aware of schema's) -> indeed not
- [x] tested for postgresql
- [ ] test for other databases (mysql and sqlite don't support schema's -> so can't test on travis)
- [x] docs
- [x] Cause of the failure at the moment: `schema` as arg for `MetaData` is only introduced in sqlalchemy 0.7.4. We test on 0.7.1 => raise required version


Labels:
name: IO SQL
description: to_sql, read_sql, read_sql_query

Comments:
@jorisvandenbossche I would like to add a comment: I have done similar changes to get it working for a specific dialect (exasol dialect). Your changes work also fine for me, but I need one additional line in `get_table`:

```
def get_table(self, table_name):
    self.meta.reflect(only=[table_name])
    if self.meta.schema:
        table_name = self.meta.schema + '.' + table_name
    return self.meta.tables.get(table_name)
```

If I don't reflect the metadata it will not find this table and will crash at some later point. Can you try to add this line to your pull request?

@JoergRittinger Thanks for the feedback! 
How are you exactly using it? Normally through `read_sql_table` the metadata should already be reflected (in that function), and if you pass `meta` manually to PandasSQLAlchemy (like `pdsql = PandasSQLAlchemy (engine, meta=meta)`) I think we leave it to the user to provide a reflected meta object. 

If I use to_sql, now is the question what is the idea of using this function. I would like to use it, to insert data in an existing table.

You can certainly use `to_sql` to insert data in an existing table, therefore the `if_exists='append'` option exists.

But does it give an error without that one line extra? 
It is true that it would be logical that if you do `'append'` with `to_sql`, that is reflects that database. However, at the moment, it just creates the insert statement from a newly created `Table` object, instead of getting the `Table` from the reflected `meta`. But it works now for sqlite/mysql/postgresql. 

So using the functional interface:

```
df.to_sql('table', engine, schema='schema'
```

When you provide the `meta` manually (but this API can still change):

```
meta = sqlalchemy.MetaData(engine, schema='schema')
meta.reflect()
pdsql = pd.io.sql.PandasSQLAlchemy(engine, meta=meta)
pdsql.to_sql(df, 'table')
```

sry, forget my complains. Seems to work also for me (I forget to undo some changes from my side). Looking forward to 0.15 :)

No problem, thanks for trying out! 
Another question, you said that working with exasol without schemas does not make sense. How do you do it to execute a query with the pandas functions to retrieve data? `read_sql_query` does not have a clue about schemas at the moment (only `read_sql_table` does with this PR). Can you still use it? (using dotted names like `"SELECT * FROM schema.table"`?)

exactly: `"SELECT * from schema_name.table_name"`.

If I don't waste your time I would like to have another question. What is the design of the `sql` module. Are you supposed to use only the high level functions `read_sql_table`, `read_sql_query` and `to_sql` or should you also be able to work with an `PandasSQLAlchemy` object and use the `read_table`, `read_sql` and `to_sql` functions within. If it is the second case, your solution has still some drawbacks when using a database with more then one schema, because in the metadata object you can only specify one schema. I had made a fork of the sql module with some different changes to yours (not giving the schema to metadata but adding it as a parameter to more low level functions). If you are interested I could make a pull request, maybe?

@JoergRittinger No, that is a very good question! This is still work in progress, and if you want to contribute to that (in commenting on the design, or in implementation), very very welcome! 

The idea is that you have two possibilities: 1) the simpler functional API (the high-level functions) `read_sql_query/table` and `to_sql`, and 2) the more advanced object-oriented API with `PandasSQLAlchemy`. The high-level functions are already quite polished (but can use some enhancements, such as this PR to add `schema`), but the OO API should still be discussed and is not yet really public (will still change). See #6300 for an open issue on that. It is also not yet fully clear what this 'more advanced' means, besides you don't need to recreate the object and metadata again for each query. Also providing custom metadata is possible here. 
You are correct that now only one schema can be specifies. This could also be an enhancement for the object oriented API.

In any case, certainly interested to see your changes. You can point to the branch or open a PR.

@jorisvandenbossche you can see the changes in https://github.com/JoergRittinger/pandas/commit/8e7f41fccf4aaf571893217757498a1237978eb1 and https://github.com/JoergRittinger/pandas/commit/e4cdf312d5e2af6c2fcdcd87109bcbebdcb421c0 (the second also incorporating your bugfix https://github.com/jorisvandenbossche/pandas/commit/df32cff8833edf810000b50e50a40a809305d239)

This implementation works only when you use SQLalchemy. It has the major drawback that you would have to introduce the schema also in all legacy functions where it would have no meaning at all ...

@JoergRittinger Thanks! So basically, you provided a keyword arg to each function and pass this around, instead of adding it explicitely to the `MetaData.schema` (like I did). In this way you can write/read to/from different schema's with the same `PandasSQLAlchemy` object? 
Maybe we could combine both approaches: both add it to metadata (so in this way you can give a 'default' schema to the `PandasSQLAlchemy` object and don't need to specify it in each method call afterwards) and provide it as a keyword in the methods, to being able to overwrite this default value? (so you also have the ability to use different schema's?)

About it having no meaning for the legacy functions, I don't think that is a problem. We should just give an informative error message then.

Looks interesting, will try to test drive it on Oracle later this week.

@JoergRittinger I combined both approaches (your and mine commit). Can you have a look? So the idea is, if you provide `schema` to `PandasSQLAlchemy`, it uses this as default in all other methods (read_table/to_sql/has_table/get_table/drop_table), but in each of the methods you can also override this schema with again a `schema` keyword.

Two small things I saw:
-  `read_sql` also with schema parameter? (imho I would remove this function and be explicit with what you want, a table or a sql query)
- in the high level function in `to_sql` you give the schema to `pandasSQL_builder` and call `pandas_sql.to_sql` without, in `has_table` you do it vice versa

- `read_sql`: yes, I was also a little in doubt about that, but removing is not an option (backwards compatibility). Not providing it is a reason to push users to `read_sql_query/table`, but it also gives some inconsistency between the functions
- `has_table`/`to_sql`: good catch, I changed it in `to_sql` to pass it to `pandasSQL_builder` (so it is passed to the init function), but actually it does not (or it should not) really matter. But maybe I should be consistent in the implementation.

rebased and updated

@mangecoeur @danielballan @hayd any comments?

@jreback does this look OK to you?

OK, I merged this, if people could test this with oracle / mssql server (@aldanor, @aergener), that would be very welcome! 

@jorisvandenbossche Finally got to give it a spin on Oracle, the read functions seem to work for the simplest cases where schema is required (aside from several upstream Oracle-related sqlalchemy bugs which have been fixed and will make it to 0.9.8). So far so good, thanks!

Very minor thing: the only thing I find a bit unintuitive is the order of arguments in read_sql_table, but maybe it's just me :) (I'd probably expect engine and table name to be swapped, so engine comes first, same as in most other sqlalchemy functions).

Haven't checked the write functionality though yet.

@aldanor Thanks for the heads up!

On the keyword order of table name and engine, that is already too late to change I think (also in read_sql_query, it it first the query and then the engine, also in to_sql)

@aldanor the argument order is that way because it was in the original to_sql before sqlalchemy support was added, so not really possible to change it round :/ 

I am currently using pandas 0.17.1 and trying to upload a dataframe to an existing table on redshift using `partial.to_sql('table', conn, schema='db', index=False, if_exists='append')`, but still got `Relation "table" already exists`. However, when I did `conn.has_table('db.table')`, it returned `False`.