title: How to share single SQLite connection in multi-threaded Python application
body: <p>I am trying to write a multi-threaded Python application in which a single SQlite connection is shared among threads.  I am unable to get this to work.  The real application is a cherrypy web server, but the following simple code demonstrates my problem.</p>
  
  <p>What change or changes to I need to make to run the sample code, below, successfully?</p>
  
  <p>When I run this program with THREAD_COUNT set to 1 it works fine and my database is updated as I expect (that is, letter "X" is added to the text value in the SectorGroup column).</p>
  
  <p>When I run it with THREAD_COUNT set to anything higher than 1, all threads but 1 terminate prematurely with SQLite related exceptions.  Different threads throw different exceptions (with no discernible pattern) including:</p>
  
  <pre><code>OperationalError: cannot start a transaction within a transaction 
  </code></pre>
  
  <p>(occurs on the <code>UPDATE</code> statement)</p>
  
  <pre><code>OperationalError: cannot commit - no transaction is active 
  </code></pre>
  
  <p>(occurs on the .commit() call)</p>
  
  <pre><code>InterfaceError: Error binding parameter 0 - probably unsupported type. 
  </code></pre>
  
  <p>(occurs on the <code>UPDATE</code> and the <code>SELECT</code> statements)</p>
  
  <pre><code>IndexError: tuple index out of range
  </code></pre>
  
  <p>(this one has me completely puzzled, it occurs on the statement <code>group = rows[0][0] or ''</code>, but only when multiple threads are running)</p>
  
  <p>Here is the code:</p>
  
  <pre><code>CONNECTION = sqlite3.connect('./database/mydb', detect_types=sqlite3.PARSE_DECLTYPES, check_same_thread = False)
  CONNECTION.row_factory = sqlite3.Row
  
  def commands(start_id):
  
      # loop over 100 records, read the SectorGroup column, and write it back with "X" appended.
      for inv_id in range(start_id, start_id + 100):
  
          rows = CONNECTION.execute('SELECT SectorGroup FROM Investment WHERE InvestmentID = ?;', [inv_id]).fetchall()
          if rows:
              group = rows[0][0] or ''
              msg = '{} inv {} = {}'.format(current_thread().name, inv_id, group)
              print msg
              CONNECTION.execute('UPDATE Investment SET SectorGroup = ? WHERE InvestmentID = ?;', [group + 'X', inv_id])
  
          CONNECTION.commit()
  
  if __name__ == '__main__':
  
      THREAD_COUNT = 10
  
      for i in range(THREAD_COUNT):
          t = Thread(target=commands, args=(i*100,))
          t.start()
  </code></pre>