title: Creating transactions with with statements in psycopg2
body: <p>I am trying to use <a href="http://initd.org/psycopg/docs/usage.html" rel="noreferrer">psycopg2</a> to add some new columns to a table. PostgreSQL lacks a <code>ALTER TABLE table ADD COLUMN IF NOT EXISTS</code>, so I am adding each column in it's own transaction. If the column exists, there will be a python &amp; postgres error, that's OK, I want my programme to just continue and try to add the next column. The goal is for this to be idempotent, so it can be run many times in a row.</p>
  
  <p>It currently looks like this:</p>
  
  <pre><code>def main():
      # &lt;snip&gt;
      with psycopg2.connect("") as connection:
          create_columns(connection, args.table)
  
  def create_columns(connection, table_name):
      def sql(sql):
          with connection.cursor() as cursor:
              cursor.execute(sql.format(table_name=table_name))
  
      sql("ALTER TABLE {table_name} ADD COLUMN my_new_col numeric(10,0);")
      sql("ALTER TABLE {table_name} ADD COLUMN another_new_col INTEGER NOT NULL;")
  </code></pre>
  
  <p>However, if <code>my_new_col</code> exists, there is an exception <code>ProgrammingError('column "parent_osm_id" of relation "relations" already exists\n',)</code>, which is to be expected, but when it tried to add <code>another_new_col</code>, there is the exception <code>InternalError('current transaction is aborted, commands ignored until end of transaction block\n',)</code>.</p>
  
  <p>The <a href="http://initd.org/psycopg/docs/usage.html#with-statement" rel="noreferrer">psycogpg2 document for the <code>with</code> statement</a> implies that the <code>with connection.cursor() as cursor:</code> will wrap that code in a transaction. This is clearly not happening. Experimentation has shown me that I need 2 levels of <code>with</code> statements, to including the <code>pscyopg2.connect</code> call, and then I get a transaction.</p>
  
  <p>How can I pass a <a href="http://initd.org/psycopg/docs/connection.html#connection" rel="noreferrer"><code>connection</code> object</a> around and have queries run in their own transaction to allow this sort of "graceful error handling"? I would like to keep the postgres connection code separate, in a "clean architecture" style. Is this possible?</p>