ENH: Infer dtype from non-nulls when pushing to SQL
State: closed

Closes  #8778

This infers dtype from non-null values for insertion into SQL database.  See #8778 .  I had to alter @tiagoantao test a bit.

Like @tiagoantao, I skipped writing tests for legacy MySQL. Support for this will be removed soon, right?

As a side note, `lib.infer_dtype` throws an exception for categorical data -- probably shouldn't happen, right?

@jorisvandenbossche 
@jahfet
@tiagoantao


Labels:
name: Dtype Conversions
description: Unexpected or buggy dtype conversions
name: IO SQL
description: to_sql, read_sql, read_sql_query

Comments:
can u show what u r passing that raises on Categorical
infer_dtypes is internal and cannot be called with certain types of things
but sounds like a bug

Yes, here's the snippet.  Interestingly, it works for a dataframe but not for a series.

```
In [11]: import pandas as pd

In [12]: import pandas.lib as lib

In [13]: df = pd.DataFrame({'a':pd.Series(['A','B'], dtype='category')})

In [14]: lib.infer_dtype(df)
Out[14]: 'string'

In [15]: lib.infer_dtype(df['a'])
---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-15-20fdf746850b> in <module>()
----> 1 lib.infer_dtype(df['a'])

/Users/artemy/dev/pandas/pandas/lib.so in pandas.lib.infer_dtype (pandas/lib.c:41462)()

TypeError: Cannot convert Categorical to numpy.ndarray
```

that's not really valid input, it has to be an ndarray (though we have relaxed it a bit). But I will add this as it should be able to infer (its easy to do), it just checks the dtype (and doesn't do anything more)

@artemyk ok, fixed up by #8975 

@jreback Great, thanks!  Simplified this PR a bit (no longer doing special check for categorical type)

@artemyk put some comments, but in general it looks very good! 
ok for skipping the tests on mysql legacy

@jorisvandenbossche Thanks, made the fixes.

Regarding `lib.infer_dtype(com._ensure_object(col))` --- this is from #6932 , not sure why `_ensure_object` was used , I thought that code path would occur when `col` is already dtype object (@jreback may know) .  In any case, the new dtype inference code takes care of inferring date and time.

And yes, I removed the previous testing for dtype, lines like `elif issubclass(pytype, np.integer):` &c., and now rely on `lib.infer_dtype` to do this.  Not sure what you mean by `should we also do that for sqlalchemy + test it`.

@jorisvandenbossche Oh, that makes sense!  Added check / test for complex numbers .

@jorisvandenbosscher ready to go?

yep, looking good! @artemyk can you add a release note and squash?

@jorisvandenbossche @jreback Done

looks ok to me, any docs need to be updated for this? (maybe a note somewhere explaining what this is doing), e.g. maybe add a 'dtypes' sub-section for SQL (as getting more traction in this area).

Could be done in separate issue.

@artemyk hmm, can you rebase again?

Docs can go in a seperate PR I think (more general than this)

@jreback did a PR for your doc suggestion

@jorisvandenbossche  Rebased on master, let me if I still need to fix something.  And thanks for doing the docs!

@artemyk Thanks a lot!