title: Using UPDATE in SQLite for Many Rows with a Python List
body: <p>I using SQLite (sqlite3) interfaced with Python, to hold parameters in a table which I use for processing a large amount of data.  Suppose I have already populated the table initially, but then change the parameters, and I want to update my table.  If I create a Python list holding the updated parameters, for every row and column in the table, how do I update the table?</p>
  
  <p>I have looked <a href="https://stackoverflow.com/questions/3926606/examining-then-updating-rows-in-sqlite-with-python">here</a> and <a href="https://stackoverflow.com/questions/17406438/sqlite-update-of-many-rows-is-very-slow">here</a> (though the latter refers to C++ as opposed to Python) but these don't really answer my question.</p>
  
  <p>To make this concrete, I show some of my code below:</p>
  
  <pre><code>import sqlite3 as sql
  import numpy as np
  
  db = sql.connect('./db.sq3')
  cur = db.cursor()
  
  #... Irrelevant Processing Code ...#
  
  cur.execute("""CREATE TABLE IF NOT EXISTS process_parameters (
                   parameter_id            INTEGER PRIMARY KEY,
                   exciton_bind_energy     REAL,
                   exciton_bohr_radius     REAL,
                   exciton_mass            REAL,
                   exciton_density_per_QW  REAL,
                   box_trap_side_length    REAL,
                   electron_hole_overlap   REAL,
                   dipole_matrix_element   REAL,
                   k_cutoff                REAL)""")
  
  #Parameter list
  process_params = [(E_X/1.6e-19, a_B/1e-9, m_exc/9.11e-31, 1./(np.sqrt(rho_0)*a_B), D/1e-6, phi0/1e8, d/1e-28, k_cut/(1./a_B)) for i in range(0,14641)]
  
  #Check to see if table is populated or not
  count = cur.execute("""SELECT COUNT (*) FROM process_parameters""").fetchone()[0]
  
  #If it's not, fill it up
  if count == 0:
      cur.executemany("""INSERT INTO process_parameters VALUES(NULL, ?, ?, ?, ?, ?, ?, ?, ?);""", process_params)
      db.commit()
  </code></pre>
  
  <p>Now, suppose that on a subsequent processing run, I change one or more of the parameters in <code>process_params</code>.  What I'd like is for on any subsequent runs that Python will update the database with the most recent version of the parameters.  So I do</p>
  
  <pre><code>else:
      cur.executemany("""UPDATE process_parameters SET exciton_bind_energy=?, exciton_bohr_radius=?, exciton_mass=?, exciton_density_per_QW=?, box_trap_side_length=?, electron_hole_overlap=?, dipole_matrix_element=?, k_cutoff=?;""", process_params)
      db.commit()
  db.close()
  </code></pre>
  
  <p>But when I do this, the script seems to hang (or be going very slowly) such that Ctrl+C doesn't even quit the script (being run via <code>ipython</code>).</p>
  
  <p>I know in this case, updating using a huge Python list may be irrelevant, but it's the principle here which I want to clarify, since at another time, I may not be updating every row with the same values.  If someone could help me understand what's happening and/or how to fix this, I'd really appreciate it.  Thank-you.</p>