title: Pandas errors in writing chunks to database with df.to_sql()
body: <p><strong>Existing Database and Desired Outcome:</strong></p>
  
  <p>I have a larger SQLite database (12gb, tables with 44 million+ rows) that I would like to modify using Pandas in Python3.</p>
  
  <blockquote>
    <p><em>Example Objective</em>: I hope to read one of these large tables (44 million rows) into a DF in chunks, manipulate the DF chunk, and write the result to a new table. If possible, I would like to replace the new table if it exists, and append each chunk to it. </p>
    
    <p>Because my manipulations only add or modify columns, <strong>the new table should have the same number of rows as the original table.</strong></p>
  </blockquote>
  
  <p><strong>Issues:</strong></p>
  
  <p>The primary issue seems to stem from the following line in the below code:</p>
  
  <p><code>df.to_sql(new_table, con=db, if_exists = "append", index=False)</code></p>
  
  <ol>
  <li>When this line is run in the below code, I seem to consistently get an additional chunk of size=N, plus one observation than I expected.</li>
  <li>The first time this code runs with a new table name, I get an error:</li>
  </ol>
  
  
  
  <pre class="lang-py prettyprint-override"><code> Traceback (most recent call last):
    File "example.py", line 23, in &lt;module&gt;
      for df in df_generator:
    File "/usr/local/lib/python3.5/site-packages/pandas/io/sql.py", line 1420, in _query_iterator
      data = cursor.fetchmany(chunksize)
  sqlite3.OperationalError: SQL logic error or missing database
  </code></pre>
  
  <ol start="3">
  <li><p>If I then rerun the script, with the same new table name, it runs for each chunk, and an extra chunk, +1 row.</p></li>
  <li><p>When the <code>df.to_sql()</code> line is commented out, the loop runs for the expected number of chunks.</p></li>
  </ol>
  
  <p><strong>Test Example of Issue with Complete Code:</strong></p>
  
  <p><strong>Complete Code: <em>example.py</em></strong>
  </p>
  
  <pre class="lang-py prettyprint-override"><code>import pandas as pd
  import sqlite3
  
  #Helper Functions Used in Example
  def ren(invar, outvar, df):
      df.rename(columns={invar:outvar}, inplace=True)
      return(df)
  
  def count_result(c, table):
      ([print("[*] total: {:,} rows in {} table"
          .format(r[0], table)) 
          for r in c.execute("SELECT COUNT(*) FROM {};".format(table))])
  
  
  #Connect to Data
  db = sqlite3.connect("test.db")
  c = db.cursor()
  new_table = "new_table"
  
  #Load Data in Chunks
  df_generator = pd.read_sql_query("select * from test_table limit 10000;", con=db, chunksize = 5000)
  
  for df in df_generator:
      #Functions to modify data, example
      df = ren("name", "renamed_name", df)
      print(df.shape)
      df.to_sql(new_table, con=db, if_exists = "append", index=False)
  
  
  #Count if new table is created
  try:
      count_result(c, new_table)
  except:
      pass
  </code></pre>
  
  <blockquote>
    <p><strong>1. Result when</strong>
     <code>#df.to_sql(new_table, con=db, if_exists = "append", index=False)</code> </p>
    
    <p>(the problem line is commented out):</p>
  </blockquote>
  
  <pre class="lang-py prettyprint-override"><code>$ python3 example.py 
  (5000, 22)
  (5000, 22)
  </code></pre>
  
  <p>Which I expect since the example code limits my large table to 10k rows. </p>
  
  <blockquote>
    <p><strong>2. Result when</strong>
     <code>df.to_sql(new_table, con=db, if_exists = "append", index=False)</code> </p>
    
    <p>a. the problem line is not commented out</p>
    
    <p>b. this is the <strong>first time</strong> the code is run with a new_table:</p>
  </blockquote>
  
  <pre class="lang-py prettyprint-override"><code>$ python3 example.py 
  (5000, 22)
  Traceback (most recent call last):
    File "example.py", line 23, in &lt;module&gt;
      for df in df_generator:
    File "/usr/local/lib/python3.5/site-packages/pandas/io/sql.py", line 1420, in _query_iterator
      data = cursor.fetchmany(chunksize)
  sqlite3.OperationalError: SQL logic error or missing database
  </code></pre>
  
  <blockquote>
    <p><strong>3. Result when</strong>
     <code>df.to_sql(new_table, con=db, if_exists = "append", index=False)</code> </p>
    
    <p>a. the problem line is not commented out</p>
    
    <p>b. the above code is run a <strong>second time</strong> with the new_table:</p>
  </blockquote>
  
  <pre class="lang-py prettyprint-override"><code>$ python3 example.py 
  (5000, 22)
  (5000, 22)
  (5000, 22)
  (1, 22)
  [*] total: 20,001 rows in new_table table
  </code></pre>
  
  <p>Thus, I have the issue of first the code breaking when run the first time (Result 2) and second, the total number of rows when run the second time (Result 3) is more than double what I expected.</p>
  
  <p><em>Any suggestions on how I can solve this issue would be greatly appreciated.</em></p>