enable multivalues insert
State: closed

### Summary
Currently when pushing a dataframe to a database, lines are inserted one by one. This change enables multivalues inserts. 

TODO
 - [x] release note
 - [ ] address chunksize behavior

### Reference
http://docs.sqlalchemy.org/en/rel_0_9/core/dml.html?highlight=insert%20values#sqlalchemy.sql.expression.Insert.values

Labels:
name: Performance
description: Memory or execution speed performance
name: IO SQL
description: to_sql, read_sql, read_sql_query

Comments:
xref https://github.com/pandas-dev/pandas/issues/14315 and https://github.com/pandas-dev/pandas/issues/8953

I think the previous issues were with sqlalchemy dialects that don't support multi-row inserts, so we'll need to test that.

+ docs, release note, and tests.
For reference, I'm using a SQLAlchemy dialect that supports_multivalues_inserts and inserts still happen line by line. Will add tests though.
# [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/19664?src=pr&el=h1) Report
> :exclamation: No coverage uploaded for pull request base (`master@f33e84c`). [Click here to learn what that means](https://docs.codecov.io/docs/error-reference#section-missing-base-commit).
> The diff coverage is `n/a`.

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

```diff
@@            Coverage Diff            @@
##             master   #19664   +/-   ##
=========================================
  Coverage          ?   91.71%           
=========================================
  Files             ?      150           
  Lines             ?    49104           
  Branches          ?        0           
=========================================
  Hits              ?    45035           
  Misses            ?     4069           
  Partials          ?        0
```

| Flag | Coverage Δ | |
|---|---|---|
| #multiple | `90.09% <ø> (?)` | |
| #single | `41.87% <ø> (?)` | |


------

[Continue to review full report at Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/19664?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/19664?src=pr&el=footer). Last update [f33e84c...f298de1](https://codecov.io/gh/pandas-dev/pandas/pull/19664?src=pr&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).

Ideally, in the tests we'll be able to introspect the sqlalchemy engine somehow to assert that multi-row inserts are actually happening.
Hello @danfrankj! Thanks for updating the PR.





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

##### Comment last updated on March 07, 2018 at 21:54 Hours UTC
@jreback @TomAugspurger added first stab at a test. Let me know what you think!
@jreback @TomAugspurger I believe I've addressed your concerns above, when you get a chance PTAL
Some profiling 

### Presto

```

In [5]: df = pd.DataFrame(np.random.randn(1000, 4), columns=list('ABCD'))

In [6]: %time df.to_sql('multi_insert_profile', presto_engine, schema='dan_frank', index=False)
INFO:pyhive.presto:SHOW COLUMNS FROM "dan_frank"."multi_insert_profile"
INFO:pyhive.presto:
CREATE TABLE "dan_frank"."multi_insert_profile" (
    "A" DOUBLE,
    "B" DOUBLE,
    "C" DOUBLE,
    "D" DOUBLE
)


INFO:pyhive.presto:INSERT INTO "dan_frank"."multi_insert_profile" ("A", "B", "C", "D") VALUES (-0.4425108530531213, -0.4582021047086419, 0.17242001384630398, -1.2917653645626361), (-0.9715964127007015, -0.1458055798883143, 0.3444250700373072, -0.35869901840257923), (0.6732070093449385, 0.3371601918897362, -0.49645678476330574, -0.8241023338536242), (-0.4845513289740901, -1.4860936235542728, 0.19123940403655423, -0.32166319533058985), (2.72221337305179, 0.31572155167450705, -0.5522159042533455, -0.28023622560479866), (-2.2406710854261345, 0.8005522925313067, -0.5762370339886204, 1.1784968768877826), (-0.06826129801094293, 0.2760723638718846, 0.526970720133034, 
... LOG TRUNCATED


CPU times: user 131 ms, sys: 9.06 ms, total: 140 ms
Wall time: 13.1 s



In [7]: presto_engine.dialect.supports_multivalues_insert = False

In [8]: %time df.to_sql('sequential_insert_profile', presto_engine, schema='dan_frank', index=False)
INFO:pyhive.presto:SHOW COLUMNS FROM "dan_frank"."sequential_insert_profile"
INFO:pyhive.presto:
CREATE TABLE "dan_frank"."sequential_insert_profile" (
    "A" DOUBLE,
    "B" DOUBLE,
    "C" DOUBLE,
    "D" DOUBLE
)


INFO:pyhive.presto:INSERT INTO "dan_frank"."sequential_insert_profile" ("A", "B", "C", "D") VALUES (-0.4425108530531213, -0.4582021047086419, 0.17242001384630398, -1.2917653645626361)
INFO:pyhive.presto:INSERT INTO "dan_frank"."sequential_insert_profile" ("A", "B", "C", "D") VALUES (-0.9715964127007015, -0.1458055798883143, 0.3444250700373072, -0.35869901840257923)
INFO:pyhive.presto:INSERT INTO "dan_frank"."sequential_insert_profile" ("A", "B", "C", "D") VALUES (0.6732070093449385, 0.3371601918897362, -0.49645678476330574, -0.8241023338536242)
INFO:pyhive.presto:INSERT INTO "dan_frank"."sequential_insert_profile" ("A", "B", "C", "D") VALUES (-0.4845513289740901, -1.4860936235542728, 0.19123940403655423, -0.32166319533058985)
INFO:pyhive.presto:INSERT INTO "dan_frank"."sequential_insert_profile" ("A", "B", "C", "D") VALUES (2.72221337305179, 0.31572155167450705, -0.5522159042533455, -0.28023622560479866)
INFO:pyhive.presto:INSERT INTO "dan_frank"."sequential_insert_profile" ("A", "B", "C", "D") VALUES (-2.2406710854261345, 0.8005522925313067, -0.5762370339886204, 1.1784968768877826)
... LOG TRUNCATED 


CPU times: user 15.7 s, sys: 1.44 s, total: 17.1 s
Wall time: 14min 57s
``` 

### MySQL
Comparable insert times 
@TomAugspurger did some brief profiling and added additional documentation. Anything else you think is needed for this PR?
@jorisvandenbossche any thoughts? This seems harmless to me, and the results are... impressive :) 
@jreback backquotes fixed and note added to `io.rst`
thanks!
Thank you guys for the reviews! Excited for my first pandas contribution :)
Hi all, In pandas 0.22 I could write a dataframe to sql of reasonable size without error. Now I receive this error "OperationalError: (sqlite3.OperationalError) too many SQL variables". I am converting a dataframe with ~20k+ rows to sql. After looking around I suspect the problem lies in the limit set by sqlite3: SQLITE_MAX_VARIABLE_NUMBER which is set to 999 by default. This can apparently be changed by recompiling sqlite and adjusting this variable accordingly. I also noticed that adjusting the chunksize in DataFrame.to_sql has no effect perhaps confirming this is the root cause. 
@tripkane maybe make a new issue (link back here) with a reproducible example.
@TomAugspurger: ok thanks, will do