Tag Archives: MySQL

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.


Access MAMP’s MySQL database from Python

I posted a while ago about running a locally hosted server using MAMP, and I have been successfully using a PHP script on this server to read and write to a database.

Then a friend suggested I could use Python instead of PHP, e.g. using “CherryPy” as a light-weight server.  I am a big fan of python, so I want to try this.

The basic overview is:

  • Use the CherryPy python library to run the server (though more recently I have switched to Django)
  • Use the MySQLdb python library to interface with MySQL
  • Use the MAMP-installed instance of MySQL

Here’s how I made it work:

    • Download CherryPy (or Django or any other Python-based framework) from the link above.  I had to use sudo in front of the python setup.py build command.
    • Download MySQLdb from the link above.
    • Check it works by going to the terminal, typing python and then import MySQLdb
    • It doesn’t work, for several reasons:
      • It can’t find the “mysql_config” file associated with MAMP.  To fix this, open site.cfg (in the directory you installed MySQLdb) and add the line below. Note this points to the MAMP installation of my_sql, not the default of /usr/local/bin:
          mysql_config = /Applications/MAMP/Library/bin/mysql_config
      • It couldn’t find llvm. To solve this, open XCode, go to Preferences, choose the Components tab, and install the command line tools.
      • MAMP does not include the required .h files.  This post describes how to get around this: I downloaded the 64bit MySQL from here, and copied files from its include directory into the new directory MAMP/Library/include, and from its lib directory into MAMP/Library/lib. (Don’t use the 32bit files.)
      • The final magic touch is you need to type
          export DYLD_LIBRARY_PATH=/Applications/MAMP/Library/lib
        into the terminal before you go into python. (But note – this will mess up git.  So when you need git, type export DYLD_LIBRARY_PATH=''. Urrggh!)
    • Now both import MySQLdb and import _mysql work in python.
    • To access MySQL from the terminal, open MAMP and start the servers.  Then type
        /Applications/MAMP/Library/bin/mysql --host localhost -uroot -proot
      You can now do things like show databases; to look at the databases you have available.
    • So far so good, but when I try to connect to a database, I got the error “Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)” (error number 2002).  The solution is nicely explained in this post, and is:
        sudo ln -s /Applications/MAMP/tmp/mysql/mysql.sock /tmp/mysql.sock
    • Strangely, when I came back to this a week later, I got an error: “Library not loaded: libmysqlclient.18.dylib”.  I found this works:
        sudo ln -s /Applications/MAMP/Library/lib/libmysqlclient.18.dylib /usr/lib/libmysqlclient.18.dylib
      I also found I had to repeat the previous point to recreate the /tmp/mysql.sock file.

To summarise the on-going usage:

  • Before using python, you need to type export DYLD_LIBRARY_PATH=/Applications/MAMP/Library/lib
  • In order to use MySQL in the terminal, you need to type /Applications/MAMP/Library/bin/mysql --host -localhost -uroot -proot

In Django, you will need to use the following DATABASE values in settings.py:

    'HOST': '/Applications/MAMP/tmp/mysql/mysql.sock',
    'PORT': '8888',

One more thing. When I write apps that interface with the MAMP localhost, I need to replace localhost with the local IP address (from the Mac Network Utility program), and append port :8888.  However the CherryPy localhost has the local IP address (and  :8080). Why are the two local IP addresses different?

From here:

Any thoughts?