Django: RunSQL: use the PosGreSQL COPY command

Question

I am trying to migrate with the following RunSQL command:

 class Migration(migrations.Migration): operations = [ RunSQL( r''' COPY auth_group (id, name) FROM stdin; 1 TEST-GROUP \. ''')] 

This is not true:

 File "/home/foo/local/lib/python2.7/site-packages/django/db/backends/utils.py", line 63, in execute return self.cursor.execute(sql) django.db.utils.ProgrammingError: syntax error at or near "1" LINE 3: 1 TEST-GROUP 

Does COPY exist in RunSQL ?

We use psycopg2

Answer 1

The psycopg2 driver provides copy_to and copy_from methods that can be used to implement the same behavior as the psql client; the key is to use the RunPython operation instead of the RunSQL operation.

You will need:

• A function in your migration to open a file and interact with copy methods
• A RunPython in your operations migration list to call a function

Example using Django 1.8.4, Python 2.7.10, psycopg2 2.6.1 -

 from django.db import models, migrations def forwards(apps, schema_editor): with open('./path/to/file.csv', 'r') as infile: with schema_editor.connection.cursor() as stmt: #for finer control, use copy_expert instead stmt.copy_from(infile, 'your_table', sep=',') class Migration(migrations.Migration): dependencies = [ ] operations = [ #this runs your function migrations.RunPython(forwards) ] 

Some notes:

• The file object passed to copy is essentially STDIN in the instruction.
• The copy command can be legible in columns; using copy_expert , you can control all the same parameters as the command: format, headers, delimiter, etc.

For more information on copy_* methods, see the psycopg2 docs: http://initd.org/psycopg/docs/cursor.html