Use Django fixtures to export and import a database

I have some Django sites, and I’ve often wondered about how to make my dev database faithfully mirror the production database (at a point in time), i.e. copy all the info from my prod database back to my dev environment.

Approach 1: Export and import the data

You would think it is simple: just export the data from one and import it into the other. If you use the same type of database (e.g. MySQL) in both environments, this should work fine.  But I use sqlite in dev and MySQL in prod. Using sqlite on the development server is quick and easy, with no need to set up MySQL (see my earlier post on some of the hoops you may need to jump through for that to work). But then there are subtle differences between the import and export formats, and you can waste a lot of time mucking around with the files trying to get it to work.

Approach 2: Fixtures

So, the second idea is: use fixtures, e.g. on the production machine:

./ dumpdata --indent 2 > all.json

and load it in again on the dev server (I use git to transfer it but there’s probably a cleaner approach):

./ loaddata all.json

Ah – another problem. The dumpdata command, with no apps supplied, dumps everything including Users, User profiles, Sites, etc. Your dev database, even if you only just set it up a second ago, will have some content already. So you will get error messages like this:

IntegrityError: Could not load myapp.UserProfile(pk=18): column user_id is not unique

You could go back and only list the apps you want to transfer the data of after the dumpdata command, but this is painful and you may not know them all if you’re using lots of third party apps (South, Django-CMS, etc).

The solution: Fixtures + some delete statements

But I can give you good news: there is a way to do it by deleting all that initial content from your dev database, and potentially making one small deletion from the above json file.

Start with a newly created database, with the tables installed (e.g. using ./ syncdb). These are the tables you need to wipe clean:

  • django_site
  • django_content_type
  • auth_permission
  • auth_user
  • south_migrationhistory if you’re using South.  Note: I am using South but did not delete the contents of this table, and it all worked. But I suspect you should delete the contents.
  • any user profiles you define (at least in Django 1.4, I’m not sure how they work in Django 1.5 yet)

In sqlite you’d do this by typing (assuming your database file is named sqlite.db):

sqlite3 sqlite.db
  delete from django_site;
  delete from django_content_type;
  delete from auth_permission;
  delete from auth_user;
  delete from south_migrationhistory;
  delete from ...;

Looks scary, but this is a brand new database anyway, right? You could easily recreate this content by starting over with ./ syncdb.

The last line is where you delete your user profiles. If you do define a user profile, the act of creating a user will also create a profile. So when the users get loaded from the fixture file, user profiles will be automatically created; when the user profiles’ turn comes to be loaded from the fixture, there can be a potential clash. If you are OK to lose all your user’s profile data on your dev machine, there is a simple solution: just delete these entries from the json file.

That’s it! I hope that helps someone out there.


6 thoughts on “Use Django fixtures to export and import a database”

  1. Great post !

    In my case when ever I run
    ./ dumpdata –indent 2 > all.json
    I got this error , and I do not no how to solve it
    Unable to serialize database: ‘utf8′ codec can’t decode byte 0xd0 in position 0: invalid continuation byte. You passed in ‘\xd0\xcf\x11\xe0\xa1\xb1\x1a\xe1\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00>\x00\x03\x00\xfe\xff\t\x00\x06\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x01\x00\x00\x00\x01\x00\x00\x00\x00\x00\x00\x00\x00\x10\x00\x001\x00\x00\x00\x01\x00\x00\x00\xfe\xff\xff\xff\x00\x00\x00\x00\x00\x00\x00\x00\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xf

    1. Looks like you used “indent” with single dash, it is double.
      (that or the comment here merged your dashes)

      try using –indent

  2. I had a situation where I was duplicating my production environment and didn’t realize that fixtures cause signals to be sent. If you have any signal based actions that loading a fixture will hit signals. Special attention should be paid to the documentation unless you want to end up sending a thousand emails or whatever action on post_save signal you have setup!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>