Copying from a CSV to Postgres database on Heroku

Question

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 COPY FROM 'filename' Postgres command cannot be run by my script. I have tried using COPY FROM STDIN instead, to no avail.

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.

#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)

You could always parse the file and run insert statements. It'll be slow, but if performance isn't critical... — Nick Bailey
– Nick Bailey, Commented Apr 21, 2015 at 2:15
@Nick this is the approach I decided to go with and it works, but I worry about the performance as the db grows large — travace
– travace, Commented Apr 22, 2015 at 6:03

Martin Krämer · Accepted Answer · 2015-04-21 02:39:20Z

0

Just get the SQL URL (postgres://...) out of the Heroku web interface. Then you can run the script locally and connect to Heroku Postgres. That's how I solved the problem for my case.

answered Apr 21, 2015 at 2:39

Martin Krämer

5773 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

travace Over a year ago

Does running the script locally bypass the permission problem if you are still using Heroku Postgres? Also, do you run your script automatically via a scheduler like cron or some other way?

Collectives™ on Stack Overflow

Copying from a CSV to Postgres database on Heroku

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related