title: Executing a sql file in psycopg2
body: <p>More or less this function is part of a utility we use for our help desk technicians and is meant to provide them with an easy way to extract a table from a postgres backup, rename the table and reinsert it into another database. My function works great up until the actual insertion of the table into the chosen database, with cur.execute(). The sed(yes, I want to stick with sed) and extraction portions work great. I would like to execute the file somehow with psycopg2, but I am open to other suggestions if someone has an idea that uses subprocess and psql maybe.
  This function is called with a list of table names, a target database, and a sql file backup. I've seen other examples using .read(). I also know I should turn the filename into a variable to clean up the code a bit. If possible it also needs to work with python 2.6.</p>
  
  <pre><code>for tbl in selected_tables:
      # Open a file each iteration, dump the table in to it. Use .wait() to make sure the command finishes.
      table_file = open(str(tbl) + backup_suffix + ".sql", "w")
      p1 = subprocess.Popen(["/usr/bin/pg_restore", backup_picked, "-t", tbl], stdout=table_file)
      p1.wait()
  
      # Rename every instance of the table in the restore file. Format: _backup_YYYYMMDD
      sed_guts = "s/" + str(tbl) + "/"  + str(tbl) + backup_suffix + "/g"
      p2 = subprocess.Popen(["/bin/sed", "-i", sed_guts, str(tbl) + backup_suffix + ".sql"])
      p2.wait()
      table_file.close()
  
      # Restore the tables.
      try:
          # Gets the proper connection information. Works fine.
          site_config = site.ParseConfig(target_site)
  
          contents = open(str(tbl) + backup_suffix + ".sql", "r")
  
  
          con = psycopg2.connect(user=site_config['dbusername'],host=site_config['host'], \
                              password= site_config['password'], port=site_config['port'],\
                              dbname=site_config['dbname'])
  
          con.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
  
          # Connect to postgres or to the db and then execute the query.
          cur = con.cursor()
          # Fails here
          cur.execute(contents.read())
          os.remove(str(tbl) + backup_suffix + ".sql")
  
      except:
          os.remove(str(tbl) + backup_suffix + ".sql")
  </code></pre>