<p dir="auto">Hi,</p>
<p dir="auto">I put data from dataframe to SQL Server:</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="engine = create_engine('mssql+pyodbc://localhost/...')
# ...
df.to_sql(name='data', con=engine, if_exists='append', index=False)"><pre class="notranslate"><span class="pl-s1">engine</span> <span class="pl-c1">=</span> <span class="pl-en">create_engine</span>(<span class="pl-s">'mssql+pyodbc://localhost/...'</span>)
<span class="pl-c"># ...</span>
<span class="pl-s1">df</span>.<span class="pl-en">to_sql</span>(<span class="pl-s1">name</span><span class="pl-c1">=</span><span class="pl-s">'data'</span>, <span class="pl-s1">con</span><span class="pl-c1">=</span><span class="pl-s1">engine</span>, <span class="pl-s1">if_exists</span><span class="pl-c1">=</span><span class="pl-s">'append'</span>, <span class="pl-s1">index</span><span class="pl-c1">=</span><span class="pl-c1">False</span>)</pre></div>
<p dir="auto">With version 0.21.1 and previous version this is successfully executed, but with version 0.23 I get error:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError)

('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]
    The incoming request has too many parameters. 
    The server supports a maximum of 2100 parameters. 
    Reduce the number of parameters and resend the request."><pre class="notranslate"><code class="notranslate">sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError)

('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]
    The incoming request has too many parameters. 
    The server supports a maximum of 2100 parameters. 
    Reduce the number of parameters and resend the request.
</code></pre></div>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="
#let's import packages to use
import numpy as np
import pandas as pd
from sqlalchemy import create_engine


# #use pandas to import data
df = pd.DataFrame(np.arange(0,20000,1))
#create the engine to connect pandas with sqlite3
engine = create_engine('sqlite://')
#create connection
conn = engine.connect()
#convert df to sql table
df.to_sql('test',engine, if_exists='replace',chunksize=1000)
#print results
result = conn.execute(&quot;select * from test&quot;)
for row in result:
    print(row['index'])
conn.close()

"><pre class="notranslate"><span class="pl-c">#let's import packages to use</span>
<span class="pl-k">import</span> <span class="pl-s1">numpy</span> <span class="pl-k">as</span> <span class="pl-s1">np</span>
<span class="pl-k">import</span> <span class="pl-s1">pandas</span> <span class="pl-k">as</span> <span class="pl-s1">pd</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span> <span class="pl-k">import</span> <span class="pl-s1">create_engine</span>


<span class="pl-c"># #use pandas to import data</span>
<span class="pl-s1">df</span> <span class="pl-c1">=</span> <span class="pl-s1">pd</span>.<span class="pl-v">DataFrame</span>(<span class="pl-s1">np</span>.<span class="pl-en">arange</span>(<span class="pl-c1">0</span>,<span class="pl-c1">20000</span>,<span class="pl-c1">1</span>))
<span class="pl-c">#create the engine to connect pandas with sqlite3</span>
<span class="pl-s1">engine</span> <span class="pl-c1">=</span> <span class="pl-en">create_engine</span>(<span class="pl-s">'sqlite://'</span>)
<span class="pl-c">#create connection</span>
<span class="pl-s1">conn</span> <span class="pl-c1">=</span> <span class="pl-s1">engine</span>.<span class="pl-en">connect</span>()
<span class="pl-c">#convert df to sql table</span>
<span class="pl-s1">df</span>.<span class="pl-en">to_sql</span>(<span class="pl-s">'test'</span>,<span class="pl-s1">engine</span>, <span class="pl-s1">if_exists</span><span class="pl-c1">=</span><span class="pl-s">'replace'</span>,<span class="pl-s1">chunksize</span><span class="pl-c1">=</span><span class="pl-c1">1000</span>)
<span class="pl-c">#print results</span>
<span class="pl-s1">result</span> <span class="pl-c1">=</span> <span class="pl-s1">conn</span>.<span class="pl-en">execute</span>(<span class="pl-s">"select * from test"</span>)
<span class="pl-k">for</span> <span class="pl-s1">row</span> <span class="pl-c1">in</span> <span class="pl-s1">result</span>:
    <span class="pl-en">print</span>(<span class="pl-s1">row</span>[<span class="pl-s">'index'</span>])
<span class="pl-s1">conn</span>.<span class="pl-en">close</span>()
</pre></div>
<h4 dir="auto">Problem description</h4>
<p dir="auto">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 (based on their docs). This can apparently be changed by recompiling sqlite and adjusting this variable accordingly. I can confirm that for a df of length (rows) 499 this works. I can also confirm that this test version works with a row length of 20k and a chunksize of 499 inputted with df_to_sql works. In my real case the limit is 76. These numbers are clearly dependent on data size of each row so a method is required to estimate this based on data type and number of columns. <a class="issue-link js-issue-link" data-error-text="Failed to load title" data-id="296501550" data-permission-text="Title is private" data-url="https://github.com/pandas-dev/pandas/issues/19664" data-hovercard-type="pull_request" data-hovercard-url="/pandas-dev/pandas/pull/19664/hovercard" href="https://github.com/pandas-dev/pandas/pull/19664">#19664</a></p>
<h4 dir="auto">Expected  #Output</h4>
<p dir="auto">runfile('H:/Tests/Pandas_0.23_test.py', wdir='H:/Tests')<br>
0<br>
1<br>
2<br>
3<br>
4<br>
5<br>
6<br>
7<br>
8<br>
9<br>
10<br>
11<br>
12<br>
13<br>
14<br>
15<br>
16<br>
17<br>
18<br>
19</p>
<p dir="auto">####Trace###########</p>
<p dir="auto">runfile('H:/Tests/Pandas_0.23_test.py', wdir='H:/Tests')<br>
Traceback (most recent call last):</p>
<p dir="auto">File "", line 1, in <br>
runfile('H:/Tests/Pandas_0.23_test.py', wdir='H:/Tests')</p>
<p dir="auto">File "C:\Users\kane.hill\AppData\Local\Continuum\anaconda3\lib\site-packages\spyder\utils\site\sitecustomize.py", line 705, in runfile<br>
execfile(filename, namespace)</p>
<p dir="auto">File "C:\Users\kane.hill\AppData\Local\Continuum\anaconda3\lib\site-packages\spyder\utils\site\sitecustomize.py", line 102, in execfile<br>
exec(compile(f.read(), filename, 'exec'), namespace)</p>
<p dir="auto">File "H:/Tests/Pandas_0.23_test.py", line 19, in <br>
df.to_sql('test',engine, if_exists='fail',chunksize=500)</p>
<p dir="auto">File "C:\Users\kane.hill\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\core\generic.py", line 2127, in to_sql<br>
dtype=dtype)</p>
<p dir="auto">File "C:\Users\kane.hill\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\io\sql.py", line 450, in to_sql<br>
chunksize=chunksize, dtype=dtype)</p>
<p dir="auto">File "C:\Users\kane.hill\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\io\sql.py", line 1149, in to_sql<br>
table.insert(chunksize)</p>
<p dir="auto">File "C:\Users\kane.hill\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\io\sql.py", line 663, in insert<br>
self._execute_insert(conn, keys, chunk_iter)</p>
<p dir="auto">File "C:\Users\kane.hill\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\io\sql.py", line 638, in _execute_insert<br>
conn.execute(*self.insert_statement(data, conn))</p>
<p dir="auto">File "C:\Users\kane.hill\AppData\Local\Continuum\anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 948, in execute<br>
return meth(self, multiparams, params)</p>
<p dir="auto">File "C:\Users\kane.hill\AppData\Local\Continuum\anaconda3\lib\site-packages\sqlalchemy\sql\elements.py", line 269, in _execute_on_connection<br>
return connection._execute_clauseelement(self, multiparams, params)</p>
<p dir="auto">File "C:\Users\kane.hill\AppData\Local\Continuum\anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 1060, in _execute_clauseelement<br>
compiled_sql, distilled_params</p>
<p dir="auto">File "C:\Users\kane.hill\AppData\Local\Continuum\anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 1200, in _execute_context<br>
context)</p>
<p dir="auto">File "C:\Users\kane.hill\AppData\Local\Continuum\anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 1413, in _handle_dbapi_exception<br>
exc_info</p>
<p dir="auto">File "C:\Users\kane.hill\AppData\Local\Continuum\anaconda3\lib\site-packages\sqlalchemy\util\compat.py", line 203, in raise_from_cause<br>
reraise(type(exception), exception, tb=exc_tb, cause=cause)</p>
<p dir="auto">File "C:\Users\kane.hill\AppData\Local\Continuum\anaconda3\lib\site-packages\sqlalchemy\util\compat.py", line 186, in reraise<br>
raise value.with_traceback(tb)</p>
<p dir="auto">File "C:\Users\kane.hill\AppData\Local\Continuum\anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 1193, in _execute_context<br>
context)</p>
<p dir="auto">File "C:\Users\kane.hill\AppData\Local\Continuum\anaconda3\lib\site-packages\sqlalchemy\engine\default.py", line 507, in do_execute<br>
cursor.execute(statement, parameters)</p>
<p dir="auto">OperationalError: (sqlite3.OperationalError) too many SQL variables [SQL: 'INSERT INTO test ("index", "0") VALUES (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?,</p>
<h4 dir="auto">Output of <code class="notranslate">pd.show_versions()</code></h4>
<details>
<p dir="auto">pd.show_versions()</p>
<h2 dir="auto">INSTALLED VERSIONS</h2>
<p dir="auto">commit: None<br>
python: 3.6.5.final.0<br>
python-bits: 64<br>
OS: Windows<br>
OS-release: 7<br>
machine: AMD64<br>
processor: Intel64 Family 6 Model 60 Stepping 3, GenuineIntel<br>
byteorder: little<br>
LC_ALL: None<br>
LANG: en<br>
LOCALE: None.None</p>
<p dir="auto">pandas: 0.23.0<br>
pytest: 3.5.1<br>
pip: 10.0.1<br>
setuptools: 39.1.0<br>
Cython: 0.28.2<br>
numpy: 1.14.3<br>
scipy: 1.1.0<br>
pyarrow: None<br>
xarray: None<br>
IPython: 6.4.0<br>
sphinx: 1.7.4<br>
patsy: 0.5.0<br>
dateutil: 2.7.3<br>
pytz: 2018.4<br>
blosc: None<br>
bottleneck: 1.2.1<br>
tables: 3.4.3<br>
numexpr: 2.6.5<br>
feather: None<br>
matplotlib: 2.2.2<br>
openpyxl: 2.5.3<br>
xlrd: 1.1.0<br>
xlwt: 1.3.0<br>
xlsxwriter: 1.0.4<br>
lxml: 4.2.1<br>
bs4: 4.6.0<br>
html5lib: 1.0.1<br>
sqlalchemy: 1.2.7<br>
pymysql: None<br>
psycopg2: None<br>
jinja2: 2.10<br>
s3fs: None<br>
fastparquet: None<br>
pandas_gbq: None<br>
pandas_datareader: None</p>
</details>