title: sqlite - works with file, dies with :memory:
body: <p>My script fails tests when working with <a href="http://en.wikipedia.org/wiki/SQLite" rel="noreferrer">SQLite</a> base (via <a href="http://www.sqlalchemy.org/" rel="noreferrer">sqlalchemy</a>) created in <code>:memory:</code> and passes tests when working with a base created with physical file.</p>
  
  <p>The script is multi-threaded. I know it is not the best idea to use SQLite with multiple threads (locking, etc.), but I use SQLite to test script's DB interface only.</p>
  
  <p>When I use <code>:memory:</code>, the script dies complaining that there is no table:</p>
  
  <pre><code>OperationalError: (OperationalError)
      no such table: content_info u'INSERT INTO content_info ...
  </code></pre>
  
  <p>The testing procedure (with <a href="http://nose.readthedocs.org/en/latest/" rel="noreferrer">nose</a>) is as follows:</p>
  
  <pre><code>def setup_database():
      global global_engine
      # create database columns
      engine = sqlalchemy.create_engine(...)
      Base.metadata.create_all(engine)
      global_engine = engine
  
  @with_setup(setup_database)
  def test_task_spoolers():
      check_database_content_from_another_thread()
  
  def check_database_content_from_another_thread():
      global global_engine
      # from within other thread
      # create new session using global_engine
      # do some inserts
  </code></pre>
  
  <p>So in the setup I <em>do</em> create the database and the columns. I can see it also in logs (<code>echo=True</code>):</p>
  
  <pre><code>12:41:08 INFO sqlalchemy.engine.base.Engine 
  CREATE TABLE content_info (...
  
  12:41:08 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
  12:41:08 INFO sqlalchemy.engine.base.Engine INSERT INTO
      content_info (base_id, request_id, timestamp, ...
  12:41:08 INFO sqlalchemy.engine.base.Engine (0, 0, 0, 'dummy_variant',
      None, 0)
  12:41:08 INFO sqlalchemy.engine.base.Engine ROLLBACK
  Exception in thread Thread-1:
  Traceback (most recent call last):
  OperationalError: (OperationalError)
      no such table: content_info u'INSERT INTO ...
  </code></pre>
  
  <p>My guess was that when I create the base in thread A, and then I use it in thread B, then B starts to operate on the base before it was actually created. But, I added <code>time.sleep(3)</code> after <code>create_all</code> and it didn't work.</p>
  
  <p>And, as mentioned before, it works when I use a file instead of <code>:memory:</code>, even if the file is placed on virtual partition (so, in fact in memory). Any idea why is that?</p>