title: session.rollback deletes data previously queried from the database.
body: <h1>The Problem</h1>
  
  <p>Calling SQLAlchemy's <code>session.rollback()</code> after hitting a DB integrity error (requirement of SQLAlchemy) causes all session objects to be disposed. This includes objects created by previous selects.</p>
  
  <p>Looking at the SQL generated our query data is caught in an implicit transaction that isn't finished until after we do <code>session.close</code>.</p>
  
  <h2>Simplified SQL Example</h2>
  
  <pre><code>Begin (implicit) transaction
  SELECT Data
  # Should be end transaction here
  # Should Start new Transaction
  INSERT New Data
  # hit integrity error
  rollback
  </code></pre>
  
  <h1>Problem Section</h1>
  
  <pre><code>try:
      self.session.add(insertable['cluster'])
      self.session.commit()
  except IntegrityError:
      print('Prevented from inserting duplicate to cluster table')
      self.session.rollback()
  # insertable is now an empty object and can't be used without repopulating
  </code></pre>
  
  <p>What this means is that in the worst case we'll have an integrity error on every insert and we'll need to re-select the row IDs for each insert.</p>
  
  <h1><code>session.commit</code> After Select</h1>
  
  <pre><code>def find_entry(self, inpath):
      inpath = inpath.rstrip('/')
      inpath = '/'.join(inpath.split('/')[:-1])
      entry = self.session.query(
          models.Gather).filter_by(path=inpath).first()
      self.session.commit()
      return entry
  </code></pre>
  
  <p>But this doesn't actually commit the session transaction and so when we rollback we still lose the data.</p>
  
  <h1>Reasons And Other Effects</h1>
  
  <p>For performance reasons we have a set of code that selects data from a database, gets row IDs from two tables in said DB, and then uses the selected row IDs as foreign keys when we insert.</p>
  
  <p>This is mainly done so that we don't have to query on each insert.</p>
  
  <p>The problem is that if we run into a constraint or integrity errors while we're inserting than we have to do a <code>session.rollback</code>. We're finding that this <code>session.rollback</code> is killing our prior query even though they should logically be on different transactions.</p>
  
  <p>In addition to our selected data if we have objects that inserted successfully that we'd like to reference for their IDs these are also removed after a <code>sessions.rollback</code>.</p>
  
  <h1>Full Code</h1>
  
  <pre class="lang-py prettyprint-override"><code>class DBInserter:
  
      def __init__(self):
          # connection info here
          Session = sessionmaker(bind=self.engine)
          self.session = Session()
  
      def __del__(self):
          self.session.close()
          self.engine.dispose()
  
      def find_entry(self, inpath):
          inpath = inpath.rstrip('/')
          inpath = '/'.join(inpath.split('/')[:-1])
          entry = self.session.query(
              models.Gather).filter_by(path=inpath).first()
          return entry
  
      def build_insertable(self, jsonin, gather, lnn):
          """
          Build sqlalchemy object that is ready to be inserted into the db
          """
          object_dict = {}
  
          cluster = models.Cluster(
              guid = jsonin.get('guid'),
              )
  
          gather_id = None
          if gather:
              gather_id = gather.gather_id
              cluster.gather_id = gather_id
              gather.cluster_guid = jsonin.get('guid')
              gather.cluster_name = jsonin.get('name'),
              object_dict['gather'] = gather
  
          node_gather = models.NodeGather(
              gather_id = gather_id,
              lnn = lnn,
              checksum = jsonin.get('checksum'),
              checksum_valid = jsonin.get('checksum_valid'),
              compliance = jsonin.get('compliance'),
              encoding = jsonin.get('encoding'),
              joinmode = jsonin.get('joinmode'),
              master = jsonin.get('master'),
              maxid = jsonin.get('maxid'),
              timezone = jsonin.get('timezone'),
              )
  
          object_dict['cluster'] = cluster
          object_dict['node_gather'] = node_gather
          return object_dict
  
      def insert(self, insertable):
          """
          insert prepared sqlalchemy object into the db
          """    
          # Not doing batch inserts until we get single case to work properly.
          try:
              self.session.add(insertable['cluster'])
              self.session.commit()
          except IntegrityError:
              print('Prevented from inserting duplicate to cluster table')
              self.session.rollback()
  
          if insertable.get('gather'):
              try:
                  self.session.add(insertable.get('gather'))
                  self.session.commit()
              except IntegrityError:
                  print('Prevented from inserting duplicate to gather table')
                  self.session.rollback()
  
      def __call__(self, jsonin, path):
          path = path.rstrip('/')
          lnn = int(path.split('/')[-1].split('-')[-1])
          out = self.build_insertable(jsonin, self.find_entry(path), lnn)
          return out
  </code></pre>
  
  <h1>SQL</h1>
  
  <pre><code>2016-03-08 01:38:52,438 INFO sqlalchemy.engine.base.Engine SELECT gather.gather_id AS gather_gather_id, gather.cluster_guid AS gather_cluster_guid, gather.path AS gather_path, gather
  .cluster_name AS gather_cluster_name, gather.gather_date AS gather_gather_date, gather.unfurl_start AS gather_unfurl_start, gather.unfurl_end AS gather_unfurl_end, gather.upload_date
  AS gather_upload_date, gather.source_lnn AS gather_source_lnn, gather.last_full AS gather_last_full, gather.path_exists AS gather_path_exists, gather.type AS gather_type 
  FROM gather 
  WHERE gather.path = %(path_1)s 
  LIMIT %(param_1)s
  2016-03-08 01:38:52,438 INFO sqlalchemy.engine.base.Engine {'param_1': 1, 'path_1': '/mnt/logs/REALPAGE/2015-12-14-005'}
  2016-03-08 01:38:52,440 INFO sqlalchemy.engine.base.Engine COMMIT
  2016-03-08 01:38:52,441 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
  2016-03-08 01:38:52,441 INFO sqlalchemy.engine.base.Engine SELECT gather.gather_id AS gather_gather_id, gather.cluster_guid AS gather_cluster_guid, gather.path AS gather_path, gather
  .cluster_name AS gather_cluster_name, gather.gather_date AS gather_gather_date, gather.unfurl_start AS gather_unfurl_start, gather.unfurl_end AS gather_unfurl_end, gather.upload_date
  AS gather_upload_date, gather.source_lnn AS gather_source_lnn, gather.last_full AS gather_last_full, gather.path_exists AS gather_path_exists, gather.type AS gather_type 
  FROM gather 
  WHERE gather.gather_id = %(param_1)s
  2016-03-08 01:38:52,441 INFO sqlalchemy.engine.base.Engine {'param_1': 'd284c3f7983f94bac95e024038820f05475feddb2f24aec2cb52d42c343194dd'}
  2016-03-08 01:38:52,444 INFO sqlalchemy.engine.base.Engine INSERT INTO cluster (guid, site_id) VALUES (%(guid)s, %(site_id)s)
  2016-03-08 01:38:52,444 INFO sqlalchemy.engine.base.Engine {'guid': '00074309e06ace7817523b06c7cbf76f7c08', 'site_id': None}
  2016-03-08 01:38:52,445 INFO sqlalchemy.engine.base.Engine ROLLBACK
  </code></pre>
  
  <p>Notice that there isn't a commit between the select and the rollback.</p>
  
  <p>Halp.</p>