title: what is the most efficient way to iterate over a very large table and update the rows?
body: <p><strong>Problem:</strong></p>
  
  <p>When using the built-in SQLite3 database in Python (3.6), what are the considerations for iterating over a very large database table and updating it row by row?</p>
  
  <p><strong>Requirements:</strong></p>
  
  <p>I'll need to iterate each row and <em>use information from a column to perform an action</em> and then update a second column for that row with the result. I can't do this on the fly, I need to have the table fully updated.</p>
  
  <p>Additionally, based on the size of the database and the amount of time it will take to perform each action, this is expected to run for multiple days.  With that in mind, <em>it needs to be fault tolerant with the ability to periodically commit the changes.</em></p>
  
  <p><strong>Question</strong></p>
  
  <p>I am looking at the different fetch methods first but I'd like to know what the memory considerations are with the different methods. I've seen for external databases on another server, when you do a <code>fetchmany()</code> the database is generating the select statement with all of your results but only returning <code>arraysize</code> results at a time -- is this the case with SQLite as well?  If so, is there any benefit to using <code>fetchmany()</code> vs <code>fetchall()</code> vs <code>fetchone()</code>?</p>
  
  <p>I plan to use a generator function with <code>executemany()</code> in order to update the database and commit periodically, assuming that I can fetch in chunks in order to break the generator function in intervals to cause it to commit.</p>
  
  <p><strong>Example:</strong></p>
  
  <pre><code>table:
    columns: id, value1, updated_value
  </code></pre>
  
  <pre class="lang-py prettyprint-override"><code>
  def action_function():
      next_id = next_row()
      updated_value = compute_value(id)
      yield (updated_value, next_id)
  
  def next_row():
      while True:
          results = cur.fetchmany(arraysize)
          if not results:
              break
          for result in results:
              yield result[0]
  
  cur.executemany('''UPDATE table SET updated_value = ? WHERE id = ?''', action_function())
  </code></pre>
  
  <p>Is that around what this should look like? I think that possibly the functions should be reversed in order to cause the chunking to commit every <code>arraysize</code>?</p>
  
  <p>In the end, the database has millions of rows and needs to compute one action per row (using a value from that same row) and then update a column.  Loading the database into memory will likely not work so <code>fetchall()</code> seems out, but considering how standard SQL databases work where a <code>SELECT</code> statement is loading the results into memory anyway, is that happening with SQLite3 in Python?  If not, what is happening?</p>
  
  <p>Is there a better way I should be doing this such as simply holding variables for start_rowid and stop_rowid for each chunk and then calculating the next chunk size and doing a <code>SELECT</code> statement just for those indexes?</p>
  
  <p>Thanks for the help!</p>
  
  <p><strong>Edit:</strong></p>
  
  <p>As an alternative option, there is the <code>create_function</code> which goes row by row.</p>
  
  <p>Something like this:</p>
  
  <pre class="lang-py prettyprint-override"><code>con.create_function("action", 1, compute_value)
  cur.execute("UPDATE table SET updated_value = action(id)")
  </code></pre>
  
  <p><a href="https://stackoverflow.com/a/42102396/10366273">This answer</a> suggests to use this method, but considering that it needs to be at least somewhat fault tolerant I don't think this method will work, and the time to complete will still be large if only due to the time for <code>compute_value</code> to do its thing.</p>
  
  <p>If I used this method and the process was interrupted, would the values computed so far be committed to the database or would they be lost?</p>