I'm setting up a Django app on Heroku and need to convert from an sqlite3 db backend to postgresql. Unfortunately, I'm using a shared database and so cannot get direct access to the db shell with psql, and also cannot execute a COPY command with a file.
For instance, this does not work:
from django.db import connection, transaction
cursor = connection.cursor()
cursor.execute("copy table_name from 'table_dump.dmp' delimiters ',' csv;")
I get this error:
DatabaseError: must be superuser to COPY to or from a file
HINT: Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.
So I tried to read the file into the COPY command as a string, like so:
cursor.execute("copy tabe_name from '%s' delimiters ',' csv header;" % f.read())
(For the most part, these are pretty small files 131kb at the max.)
And I get the same error (in addition to a lot of grief for not escaping my ' properly -- how many backslashes do I need?)
So, what's the easiest way to COPY to a postgresql within the Django shell? I thought that it would be as simple as serving up a string instead of a file reference.
