title: How should I handle multiple threads accessing a sqlite database in Python?
body: <p>It appears that SQLite has supported multithreaded access since 3.7.13, and Python's <code>sqlite3</code> module has supported it since 3.4. To use it, you might write code like this:</p>
  
  <pre><code>import sqlite3
  dburi = "file:TESTING_MEMORY_DB?mode=memory&amp;cache=shared"
  connection = sqlite3.connect(dburi, uri=True, check_same_thread=False)
  with connection:
      cursor = conneciton.cursor()
      cursor.execute("SQL")
  </code></pre>
  
  <p>This works, but the first thing you'll discover is that you need to lock access to the database or else another thread could corrupt your data. That might look like this:</p>
  
  <pre><code>import threading
  import sqlite3
  dburi = "file:TESTING_MEMORY_DB?mode=memory&amp;cache=shared"
  connection = sqlite3.connect(dburi, uri=True, check_same_thread=False)
  # NOTE: You'll need to share this same lock object with every other thread using the database
  lock = threading.Lock()
  with lock:
      with connection:
          cursor = connection.cursor()
          cursor.execute("SQL")
          connection.commit()
  </code></pre>
  
  <p>Now, if one thread acquires the lock, another thread cannot acquire it until the first thread closes it, and so long as all threads use the same <code>lock</code> object and remember to <code>with lock:</code> before they <code>with connection:</code>, your data will not be corrupted.</p>
  
  <p>However, now I need a way to pass the lock around with the connection. You could do this with separate arguments, or perhaps with a custom class:</p>
  
  <pre><code>import threading
  import sqlite3
  
  class LockableSqliteConnection(object):
      def __init__(self, dburi):
          self.lock = threading.Lock()
          self.connection = sqlite3.connect(dburi, uri=True, check_same_thread=False)
  
  dburi = "file:TESTING_MEMORY_DB?mode=memory&amp;cache=shared"
  lsc = LockableSqliteConnection(dburi)
  with lsc.lock:
      with lsc.connection:
          cursor = lsc.connection.cursor()
          cursor.execute("SQL")
          lsc.connection.commit()
  </code></pre>
  
  <p>This is pretty good, since the name of the class reminds me of what I have, so at least I'm not likely to forget the lock and corrupt my data. But is there a way to do away with the two <code>with</code> statements? Ideally I'd like to combine them into a single <code>with</code>, since I shouldn't ever use the connection without the lock anyway.</p>