COPY to POSTGRESQL from STDIN in Python / Django (on Heroku)

Question

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.

jerQ · Accepted Answer · 2012-01-19 18:24:35Z

3

Is it possible to just use

heroku db:push

http://devcenter.heroku.com/articles/taps

answered Jan 19, 2012 at 18:24

jerQ

311 bronze badge

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

1 Comment

tchaymore Over a year ago

Great suggestion but I can't get it to actually open the database.

araqnid · Accepted Answer · 2012-01-19 19:16:50Z

0

You need to send a command to the server like this:

COPY table_name FROM STDIN DELIMITERS ',' CSV HEADER

And then use a driver-specific call to "put copy data" to the server. If you're using psycopg2 and can get hold of the underlying connection handle from django, the documentation for using it for COPY statements is here: http://packages.python.org/psycopg2/cursor.html#cursor.copy_expert

answered Jan 19, 2012 at 19:16

araqnid

135k25 gold badges163 silver badges138 bronze badges

Comments

CraigKerstiens · Accepted Answer · 2012-01-19 20:54:08Z

0

Taps is a non ideal move for sqlite. You can actually get ingress with the new Shared Postgres available in Heroku's labs. You can find more information on it at: http://devcenter.heroku.com/articles/labs-heroku-shared-postgresql

This would allow you to copy as you expect to it.

answered Jan 19, 2012 at 20:54

CraigKerstiens

5,9891 gold badge27 silver badges29 bronze badges

2 Comments

tchaymore Over a year ago

Sounds like a nice solution, but unfortunately I can't use it: Adding heroku-shared-postgresql to evening-mountain-6907... failed That add-on is only available to selected users

CraigKerstiens Over a year ago

You must first be a part of heroku labs. You can add yourself to Heroku labs with: devcenter.heroku.com/articles/labs Then you can enable the addon.

Collectives™ on Stack Overflow

COPY to POSTGRESQL from STDIN in Python / Django (on Heroku)

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related