title: use try/except with psycopg2 or "with closing"?
body: <p>I'm using Psycopg2 in Python to access a PostgreSQL database. I'm curious if it's safe to use the <code>with closing()</code> pattern to create and use a cursor, or if I should use an explicit <code>try/except</code> wrapped around the query. My question is concerning inserting or updating, and transactions.</p>
  
  <p>As I understand it, all Psycopg2 queries occur within a transaction, and it's up to calling code to commit or rollback the transaction. If within a <code>with closing(...</code> block an error occurs, is a rollback issued? In older versions of Psycopg2, a rollback was explicitly issued on <code>close()</code> but this is not the case anymore (see <a href="http://initd.org/psycopg/docs/connection.html#connection.close" rel="noreferrer">http://initd.org/psycopg/docs/connection.html#connection.close</a>).</p>
  
  <p>My question might make more sense with an example. Here's an example using <code>with closing(...</code></p>
  
  <pre><code>with closing(db.cursor()) as cursor:
       cursor.execute("""UPDATE users                    
               SET password = %s, salt = %s
               WHERE user_id = %s""",
               (pw_tuple[0], pw_tuple[1], user_id))
       module.rase_unexpected_error()
       cursor.commit()
  </code></pre>
  
  <p>What happens when module.raise_unexpected_error() raises its error? Is the transaction rolled back? As I understand transactions, I either need to commit them or roll them back. So in this case, what happens?</p>
  
  <p>Alternately I could write my query like this:</p>
  
  <pre><code>cursor = None
  try:
      cursor = db.cursor()
      cursor.execute("""UPDATE users                    
              SET password = %s, salt = %s
              WHERE user_id = %s""",
              (pw_tuple[0], pw_tuple[1], user_id))
      module.rase_unexpected_error()
      cursor.commit()
  except BaseException:
      if cursor is not None:
          cursor.rollback()
  finally:
      if cursor is not None:
          cursor.close()
  </code></pre>
  
  <p>Also I should mention that I have no idea if Psycopg2's connection class <code>cursor()</code> method could raise an error or not (the documentation doesn't say) so better safe than sorry, no?</p>
  
  <p>Which method of issuing a query and managing a transaction should I use?</p>