ENH: 'to_sql()' add param 'method' to control insert statement (#21103)
State: closed

Also revert default insert method to NOT use multi-value.

This is WIP. I would like to gather feedback on API change before going on.

- support callables as parameter?
- support to_sql() when used on SQLite3 (without SQLAlchemy)

Sample file for performance benchmarking
```
import time

import numpy as np
import pandas as pd
from sqlalchemy import create_engine


N_COLS = 20
N_ROWS= 200000
# one of to_sql insert methods: None/'default', 'multi', 'copy'
METHOD = 'copy'

engine = create_engine('postgresql://postgres:@localhost/pandas_perf')
conn = engine.connect()

start = time.time()
df = pd.DataFrame({n: np.arange(0, N_ROWS, 1) for n in range(N_COLS)})

# convert df to sql table
df.to_sql('test', conn, index=False, if_exists='replace',
          chunksize=1000, method=METHOD)

print('WRITE: {}'.format(time.time() - start))
```

- [x] closes #21103 & closes #21146 & #8953
- [x] tests added / passed
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [ ] whatsnew entry
- [ ] update docstrings/docs


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

Comments:
Hello @schettino72! Thanks for updating the PR.







Cheers ! There are no PEP8 issues in this Pull Request. :beers: 

##### Comment last updated on May 30, 2018 at 03:40 Hours UTC
For 200,000 rows, 20 columns, chunksize 1000. Running python 3.6, postgres: 

Method | time(seconds)
-----------|-------------------
default  | 36.6 
multi | 56.4 
copy | 2.2 
Does ``COPY`` work for all sqlalchemy flavors?

In the past, we always have limited the pandas implementation to sqlachemy core constructs that are backend agnostic, within its limitations. 
But given the big difference, we maybe should reconsider that.
> Does COPY work for all sqlalchemy flavors?

No. I do not have enough knowledge of various DB systems. I just know it works with Postgresql and does NOT work with SQLite.

I think the main point is to allow the insertion method to be modified without requiring any monkey-patching. 

I can drop the COPY handling from the patch if you guys wish... I included it to make sure the API was good enough to really use other methods. As the default and multi-value implementation are very similar.

IMO it does not hurt to include some code for a specific backend as long as the user needs to explicitly choose to do so, but I understand you guys dont want the burden to support code for every different backend.


# [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/21199?src=pr&el=h1) Report
> Merging [#21199](https://codecov.io/gh/pandas-dev/pandas/pull/21199?src=pr&el=desc) into [master](https://codecov.io/gh/pandas-dev/pandas/commit/f91e28c3fdd4e0708e4cc2ec45a96b068ed0a44b?src=pr&el=desc) will **increase** coverage by `<.01%`.
> The diff coverage is `n/a`.

[![Impacted file tree graph](https://codecov.io/gh/pandas-dev/pandas/pull/21199/graphs/tree.svg?width=650&src=pr&token=eZ4WkYLtcO&height=150)](https://codecov.io/gh/pandas-dev/pandas/pull/21199?src=pr&el=tree)

```diff
@@            Coverage Diff             @@
##           master   #21199      +/-   ##
==========================================
+ Coverage   91.84%   91.84%   +<.01%     
==========================================
  Files         153      153              
  Lines       49506    49538      +32     
==========================================
+ Hits        45467    45499      +32     
  Misses       4039     4039
```

| Flag | Coverage Δ | |
|---|---|---|
| #multiple | `90.24% <ø> (ø)` | :arrow_up: |
| #single | `41.87% <ø> (-0.02%)` | :arrow_down: |

| [Impacted Files](https://codecov.io/gh/pandas-dev/pandas/pull/21199?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [pandas/core/generic.py](https://codecov.io/gh/pandas-dev/pandas/pull/21199/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZ2VuZXJpYy5weQ==) | `96.12% <ø> (ø)` | :arrow_up: |
| [pandas/core/indexes/base.py](https://codecov.io/gh/pandas-dev/pandas/pull/21199/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvaW5kZXhlcy9iYXNlLnB5) | `96.61% <0%> (-0.08%)` | :arrow_down: |
| [pandas/core/frame.py](https://codecov.io/gh/pandas-dev/pandas/pull/21199/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZnJhbWUucHk=) | `97.22% <0%> (ø)` | :arrow_up: |
| [pandas/core/arrays/categorical.py](https://codecov.io/gh/pandas-dev/pandas/pull/21199/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvYXJyYXlzL2NhdGVnb3JpY2FsLnB5) | `95.68% <0%> (ø)` | :arrow_up: |
| [pandas/core/indexes/datetimelike.py](https://codecov.io/gh/pandas-dev/pandas/pull/21199/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvaW5kZXhlcy9kYXRldGltZWxpa2UucHk=) | `96.89% <0%> (+0.09%)` | :arrow_up: |
| [pandas/core/tools/datetimes.py](https://codecov.io/gh/pandas-dev/pandas/pull/21199/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvdG9vbHMvZGF0ZXRpbWVzLnB5) | `84.98% <0%> (+0.54%)` | :arrow_up: |
| [pandas/io/formats/printing.py](https://codecov.io/gh/pandas-dev/pandas/pull/21199/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2Zvcm1hdHMvcHJpbnRpbmcucHk=) | `93.08% <0%> (+3.7%)` | :arrow_up: |

------

[Continue to review full report at Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/21199?src=pr&el=continue).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/21199?src=pr&el=footer). Last update [f91e28c...66cec62](https://codecov.io/gh/pandas-dev/pandas/pull/21199?src=pr&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).

So as I said on the issue (https://github.com/pandas-dev/pandas/issues/21103#issuecomment-393333398), I think a good start might be to have this option and allow users to pass a custom function, but not to actually implement such a 'copy' method ourselves (but it is a good test case). 
(if we include the copy method, we should have much more extensive testing for different platforms and datatypes etc)

sure. no problems. I will remove the "copy" implementation.
Be sure to keep it as a test case in the tests, and maybe also a good example for the documentation
for 0.23.1 ? 
At least something, if not this, then a revert of the original PR.
fair enough. there are 2 or 3 issues open in 0.23.1. pls adjust to what makes sense.
I opened the PR to simply revert: https://github.com/pandas-dev/pandas/pull/21355, we can keep this PR for 0.23.2 or 0.24.0
Sorry, I rebased this to master and force pushed... It seems github decided to automatically close this. Re-opened as #21401