title: Copying from a CSV to Postgres database on Heroku
body: <p>I'm attempting to use a python script running on a Heroku app to copy data from a CSV file to a Postgres database (the database is attached to said Heroku app). The problem I am encountering is that Heroku does not allow for superuser access to Postgres, and thus the <strong>COPY FROM 'filename'</strong> Postgres command cannot be run by my script. I have tried using <strong>COPY FROM STDIN</strong> instead, to no avail. </p>
  
  <p>Is there a problem with the code below? Or perhaps another way I can achieve the task of copying from a CSV file to my Heroku Postgres database in my script? Manually running psql commands in the terminal is not an option since the whole point is to automate the copy process to keep my database up to date without me touching it. </p>
  
  <pre><code>#copysql is a string with the command for copying from the CSV file; newvals is the name of a temporary table I will use for the data imported from the CSV file
  copysql = """COPY newvals FROM STDIN (FORMAT csv, NULL 'NULL');"""
  #sqlquery is the SQL string for inserting new data found in the temporary table which is created from the CSV import into the existing Postgres database
  sqlquery = """INSERT INTO desttable SELECT newvals.id, newvals.column1, newvals.column2 FROM newvals LEFT OUTER JOIN desttable ON (desttable.id = newvals.id) WHERE desttable.id IS NULL;"""
  
  #my CSV file is called 'csvfile' and cur is the database cursor (using psycopg2 and I have already connected to the db elsewhere in my script)
  cur.execute("DROP TABLE IF EXISTS newvals;")
  cur.execute("CREATE TEMPORARY TABLE newvals AS SELECT * FROM desttable LIMIT 0;")
  cur.copy_expert(sql=copysql,file=csvfile)
  cur.execute(sqlquery)
  </code></pre>