Status of io.sql.get_schema as public function?

[jorisvandenbossche]

At this moment, pd.io.sql.get_schema is not documented (not in the API docs, and not in the io docs). But, it is a potential useful function, so I think it would be good to be more explicit about its status (by mentioning it in the docs).

However, there are some quirks about the function:

• The signature: pd.io.sql.get_schema(frame, name, flavor='sqlite', keys=None, con=Non e, dtype=None) -> flavor keyword in the third place, while we want to deprecate it (and this means you cannot do get_schema(df, 'name', engine), but always have to do get_schema(df, 'name', con=engine).
  Ideally this should have the same arguments (order) as to_sql (pd.io.sql.to_sql(frame, name, con, flavor='sqlite', schema=None, if_exists='fail', index=True, index_label=None, chunksize=None, dtype=None)) (only chunksize is not relevant)
• It should have all options to modify the resulting sql table as to_sql
• Maybe also the option to return the sqlalchemy Table instead of the string itself?

That we maybe should first solve before making it more explicitely public?

Triggered by http://stackoverflow.com/questions/29749356/python-pandas-export-structure-only-no-rows-of-a-dataframe-to-sql/

[jorisvandenbossche]

cc @artemyk @mangecoeur @danielballan @hayd

[TomAugspurger]

I've used this (or something similar) in my to_redshift(https://gist.github.com/TomAugspurger/1b6ed51edfbc17c26d85) method; so I think it would be good to document.

As far as returning a Table vs the schema as a string, the function name indicates a string. I haven't had any cases where having the SQLA Table would have been useful, especially if it isn't reflecting the destination (I'm not sure if we'd reflect or not).

[artemyk]

It seems like the more fundamental issue --- at least in regards to the stackoverflow question --- is that to_sql is not setting the right data types for an empty dataframe. As far as I can tell, this comes from the fact that:

>>> df = pd.DataFrame({'a':[1,2,3],'b':['b','b2','b3']})
>>> df.ix[[0,],:].ix[:,0].dtype
dtype('int64')

while

>>> df = pd.DataFrame({'a':[1,2,3],'b':['b','b2','b3']})
>>> df.ix[[],:].ix[:,0].dtype
dtype('O')

Is there a reason for this discrepancy?

[artemyk]

So confused.

In [1]: import pandas as pd
In [2]: df = pd.DataFrame({'a':[1,2,3],'b':['b','b2','b3']})

In [3]: df.ix[[],:].loc[:,'a']
Out[3]: Series([], name: a, dtype: int64)

In [4]: df.ix[[],:].iloc[:,0]
Out[4]: Series([], name: a, dtype: object)

Why the difference in dtypes?

[jorisvandenbossche]

@artemyk Can you open a new issue for that? (to not hijack this thread)

[jorisvandenbossche]

I think the point here is that there should be a way to create the database table from a DataFrame, without having to do a non-selection to obtain an empty dataframe to write.
And therefore, get_schema could possibly be helpful (as you can execute the output).

[artemyk]

@jorisvandenbossche OK, i will open a new issue. I did come across this because I think its at the root of the dtypes being lost on an empty dataframe. And I do think that inserting an empty dataframe should insert the dtypes correctly. Though get_schema could still be useful, I imagine.

[jorisvandenbossche]

@artemyk yes, I agree writing an empty frame should also work (but more an issue with indexing, than with the sql code)

@TomAugspurger I see you didn't use get_schema in the code you linked to, but the SQLTable.sql_schema() method? (but that uses the same code under the hood)

On returning an sqlalchemy Table, I was thinking this could be potentially useful if you want to adjust some things (eg constraints on columns), that are now not yet possible through our interface.

[TomAugspurger]

@jorisvandenbossche Yeah, I didn't know about the get_schema method, hence the need for docs :)

That's a fair point about making adjustments to the generated schema. And getting the actual schema should just be a str(Table) then right?

The order of arguments should be aligned with to_sql (maybe warn for a cycle).

[artemyk]

@jorisvandenbossche I would consider making a separate function to return a Table rather than the schema string. Seems a bit cleaner...

[jorisvandenbossche]

yes, I was also think that using a new name for get_schema could also solve the backwards-compatibility problem with its signature.

Name suggestions?

[jorisvandenbossche]

maybe something alluding on 'create statement', as 'schema' is actually a confusing name (in some databases this has another meaning, eg see also the schema kwarg in to_sql)

Some possibilities (but I am not yet convinced ..)

to_create_statement
get_create_statement