Tag Archives: fixtures

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:

./manage.py 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):

./manage.py 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 ./manage.py 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 ./manage.py 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.


Django – virtualenv and South

Following on from my last post, I have two basic Django sites up and running.

So now I want to tackle the next round of topics. Note that there’s a great list of useful python packages in pythonanywhere’s “batteries included” page.

  • virtualenv.  virtualenv is a great package for making sure your apps continue to function when you update the underlying components’ versions… e.g. I’m using some features of Django 1.4 that I know will be deprecated in Django 1.5.
  • South. I have already been frustrated when I add a new field to a table (or object), only to find Django cannot update the database tables for me. South sets out to solve this problem, and is as simple as adding another installed app to your settings file.

What follows is my record of how I got on with each, in the hope that I might help others who trip over the same pitfalls.

I found a great post that record some of the same discoveries here, covering git, virtualenv, south and fabric.  I’m already using git, and plan to use fabric as my needs grow.


I just grabbed the single file virtualenv.py from the website, put it in my own Python directory and ran it by literally typing:

python virtualenv.py ENV

I wasn’t sure if ENV was meant to be ENV or actually represented an environment name of my choice, so to be safe I used ENV. It turns out to be the environment name of your choice.

I discovered later that what I should have done was:

python virtualenv.py ENV --system-site-packages

so that I could still access MySQLdb, for example, without having to install this separately into ENV.  Unfortunately I don’t see a way to retroactively do this, even for individual packages, though there is virtualenvwrapper that may give some of this functionality (e.g. see this Google groups post). I might just go straight to using virtualenvwrapper.

OK, so now I have a subdirectory named ENV in the same place I put virtualenv.py, with subdirectories bin/, include/, and lib/.  I typed

source bin/activate

and understand that everything I do now is using this virtual environment.  I tried to deactivate it in the same way, but actually all you need to type is:


With ENV activated, I installed Django-CMS into ENV via

sudo pip install Django==1.4 django-cms south

(And because I did not do the --system-site-packages thing earlier, I also had to install MySQLdb pointing at my MAMP’s MySQL installation as follows… pip install mysql-python  . This gave the same error I found before when I tried to get this working – see this post. So I opened up ~/Python/virtualenv/ENV/build/mysql-python/site.cfg and added the line: mysql_config = /Applications/MAMP/Library/bin/mysql_config . Then I retyped pip install mysql-python, and it installed fine.)

Note you can use virtualenvwrapper at pythonanywhere using the advice here.


I added “south” to my existing project’s list of installed apps (in my Django project’s settings.py file), and typed:

python manage.py syncdb

This set up the new South data table south_migrationhistory.

Initial data

This link explains how to load initial data into your tables.

Standard data you want in your model is called a fixture in Django.  You can make a fixture by first entering data (e.g. through the admin interface), then using the dumpdata command.  E.g. To turn the data in the model myapp.Colour into a fixture, you can just do this:

python manage.py dumpdata myapp.Colour --indent 2 > myapp/fixtures/colours.json

Later, use the loaddata command to load this in.
There is actually a gotcha here – you need to specify an extension on your fixtures filename – i.e. do not just use colours. When you load the data, you can leave the extension off.