title: how are autoflush and flush different
body: <p>my user is interacting with a gtk2 GUI (Presenter/View/Model), and I want to let them import several records in the database, keep the interface responsive, let the user cancel the operation at any time, and when the longish operation is complete, let the user decide whether or not to commit the whole import.</p>
  
  <p>I'm doing this by running the import action in a separate thread, the GUI stays responsive, in the separate thread I create database objects, add them to the session (owned by the presenter), the importing thread adds log records to a <code>gtk.ListStore</code>, linked to a <code>gtk.TreeView</code>, and this is fine as far as the GUI is concerned. When the thread finalises, the 'ok' button is enabled.</p>
  
  <p>Since this is part of a <a href="http://github.com/Ghini/ghini.desktop/" rel="nofollow noreferrer">fairly large program I've adopted</a>, there's settings I can't change without reviewing the whole lot. One of them is autoflush, which is disabled in all sessions.</p>
  
  <p>now I have this smallish example with a surprising behaviour:</p>
  
  <pre><code>from sqlalchemy import Column, Unicode, Integer
  
  from sqlalchemy.ext.declarative import declarative_base
  Base = declarative_base()
  
  class Location(Base):
      __tablename__ = 'location'
      code = Column(Unicode(64), index=True, primary_key=True)
      def __init__(self, code=None):
          self.code = code
      def __repr__(self):
          return self.code
  
  from sqlalchemy import create_engine
  engine = create_engine('sqlite:///joindemo.db')
  
  Base.metadata.create_all(engine)
  
  from sqlalchemy.orm import sessionmaker
  Session = sessionmaker(bind=engine, autoflush=False)
  session = Session()
  
  session.query(Location).delete()
  session.commit()
  us = session.query(Location).all()
  print us
  
  def run_me_in_other_thread_1():
      session.autoflush = True
      u1 = Location(code=u'mario')
      u2 = Location(code=u'silla')
      session.add_all([u1, u2])
      session.autoflush = False
  
  def run_me_in_other_thread_2():
      u1 = Location(code=u'mario')
      u2 = Location(code=u'silla')
      session.add_all([u1, u2])
      session.flush()
  
  import threading
  t = threading.Thread(target=run_me_in_other_thread_1)
  t.start()
  t.join()
  
  session.commit()
  us = session.query(Location).all()
  print us
  </code></pre>
  
  <p>If you use as thread target <code>run_me_in_other_thread_1</code> (temporarily enable autoflush in the session), all goes as I also expected:</p>
  
  <pre><code>[]
  [mario, silla]
  </code></pre>
  
  <p>(Unfortunately, enabling autoflush, even just for one import, is something I cannot do within ghini/bauble and I don't have the energies to look into that.  Needless to say, if you want to have a look, <a href="http://github.com/Ghini/ghini.desktop/" rel="nofollow noreferrer">fork the project</a> and remove that nasty <code>autoflush=False</code>, and manage to solve those 10 consequent erroring tests, be my guest! I'll be glad to add new contributors to the authors list.)</p>
  
  <p>If you leave autoflush disabled and do a <code>session.flush</code> before returning from the target function (see <code>run_me_in_other_thread_2</code>), the flush will cause a ProgrammingError:</p>
  
  <p><code>ProgrammingError: (sqlite3.ProgrammingError) SQLite objects created in a thread can only be used in that same thread.The object was created in thread id 140403696158464 and this is thread id 140403623274240</code></p>
  
  <p>why this difference?</p>