ENH: dtype costumization on to_sql (GH8778)
State: closed

This is the proposed general gist of the changes. My ad-hoc testing suggests that this might work. If this is an acceptible design, I will proceed to make the formal test and change the docs (including docstrings)

Closes #8778


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

Comments:
@tiagoantao I think in general this is a good approach. Further needs:
- tests
- docstring + documentation
- release note (look at /doc/source/whatsnew/v0.15.2.txt)

We should also check that the user provides a SQLAlchemy type and not something else for now I think

@mangecoeur @hayd @artemyk OK with this addition?

@tiagoantao @jorisvandenbossche I realize this is late in the game, but just a short thought --- this whole situation (`to_sql` on a DataFrame with missing data) seems common enough that perhaps we should handle it without the user having to pass in their own datatypes.  What if instead of having to specify the types as in this PR, we iterated over the columns with dtype==object, and did `pandas.core.common._infer_dtype_from_scalar` on their first non-null entry?

@artemyk That is maybe a worthwile thing to look at for that specific issue, but apart from that I think the functionality in this PR is also useful for other things? (so maybe just not say it closes that specific nan issue). 
Eg #7957 could also use this PR to at least have it possible to solve the problem by the user for now.

continuing the discussion on the approach proposed by @artemyk in the issue: https://github.com/pydata/pandas/issues/8778#issuecomment-64968850

@jorisvandenbossche That makes sense how `dtypes` would be useful for various edge-cases.
@tiagoantao @jorisvandenbossche One minor comment -- unless I'm missing something, it seems like `dtypes` is not passed along in `SQLiteDatabase.to_sql`.  If dtypes is not implemented for `SQLite` (ie legacy) class, then I think the documentation should reflect this, and probably an exception should be thrown if dtypes are passed in.

I am taking care of implemeting this on SQLiteDatabase

I hope this sorts out the issue with SQLite

On the name of the argument, I was thinking it should be `dtype` instead of `dtypes` (for consistency with eg `read_csv`)

But is the name OK? Because it aren't exactly 'dtype's you specify, but sql types? (@jreback)

@jorisvandenbossche I think `dtype` is ok, this is on the `to_sql` so not confusing here.

OK, Tiago, can you:
- dtypes -> dtype
- squash
- change the bare assert (see https://github.com/pydata/pandas/pull/8926/files#r21059649)
- the test failure is for MySQL, because mysql has no boolean type, mysqlalchemy converts to Boolean into a TINYINT for mysql, so the check fails. You can do a custom test depending on the flavor (like for example: https://github.com/pydata/pandas/blob/master/pandas/io/tests/test_sql.py#L1457)

and then I think it should be ready to merge!

I have done my first rebase (quite ridiculous, but I have never done it in the past), so I am not sure if this was correctly done, could you please have a look and advise? All the rest is, hopefully, done.

@tiagoantao Not fully I think. You have a squashed commit here, but also still the old ones. Normally simply doing following steps should resolve it:

```
git fetch upstream
git rebase -i upstream/master  # and then change the 'pick' into 'squash' or 'fixup' for all your commits apart from the first one
git push -f origin
```

If you have problems with it, just ask!

I should have forced the push. I hope this will work now.

Thanks for all your help and patience with this.

Yep, that is better!
I will do a final review later, but it is looking good.

@tiagoantao Thanks a lot for this nice PR!