title: Using SQLAlchemy sessions and transactions
body: <p>While learning SQLAlchemy I came across two ways of dealing with SQLAlchemy's sessions.
  One was creating the session once globally while initializing my database like:</p>
  
  <pre><code>DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))
  </code></pre>
  
  <p>and import this DBSession instance in all my requests (all my insert/update) operations that follow.
  When I do this, my DB operations have the following structure:</p>
  
  <pre><code>with transaction manager:
      for each_item in huge_file_of_million_rows:
          DBSession.add(each_item)
          //More create, read, update and delete operations
  </code></pre>
  
  <p>I do not commit or flush or rollback anywhere assuming my Zope transaction manager takes care of it for me 
  (it commits at the end of the transaction or rolls back if it fails)</p>
  
  <p>The second way and the most frequently mentioned on the web way was:
   create a DBSession once like</p>
  
  <pre><code>    DBSession=sessionmaker(bind=engine)
      and then create a session instance of this per transaction:
      session = DBSession()
  
  for row in huge_file_of_million_rows:
      for item in row:
          try:
              DBsesion.add(item)
                  //More create, read, update and delete operations
                  DBsession.flush()
                  DBSession.commit()
          except:
              DBSession.rollback()
  DBSession.close()
  </code></pre>
  
  <ol>
  <li><p>I do not understand which is BETTER ( in terms of memory usage,
  performance, and healthy) and how?</p></li>
  <li><p>In the first method, I
          accumulate all the objects to the session and then the commit
          happens in the end.  For a bulky insert operation, does adding
          objects to the session result in adding them to the memory(RAM) or
          elsewhere? where do they get stored and how much memory is consumed?</p></li>
  <li>Both the ways tend to be very slow when I have about a
          million inserts and updates. Trying SQLAlchemy core also takes the
          same time to execute. 100K rows select insert and update takes about
          25-30 minutes. Is there any way to reduce this?</li>
  </ol>
  
  <p>Please point me in the right direction. Thanks in advance.</p>