Django change the database backend.

1482395863120 » Tagged as: odo , python , django , sqlite , mysql

In theory if you use django, changing your RDBS from say sqlite to mysql is a breeze. All you need is generate a dump of your existing data with ./manage.py dumpdata, change settings.py and do ./manage.py migrate on the new server. Then you can import the previously exported data through ./manage.py loaddata but it isn't always that simple.

The ./manage.py migrate step on the new database invariably through an error or two dozen. Squashing migrations often works, but the simplest thing is to delete all the migrations from each app's migrations/ folder. Then you do ./manage.py makemigrations followed by ./manage.py migrate and if you are lucky you have a newly created database on your hot new server. If you are unlucky you will find that some of the model fields you have used (example ArrayField) are incompatible with the new RDBMS you are moving to. I leave you to solve that yourself with the reminder that ArrayField should never have been used in the first place.

Anyone who has ever tried to use Django's loaddata command can tell you that it will choke on django Content Types. Once you get past that hurdle, you will still find all sorts of errors popping up. Of course you can use MySql's SELECT INTO OUTFILE or Postgresql's COPY command to make the import export process less painfull but the only catch is if you have a large number of models, you will find yourself doing a lot of typing. Enter Odo.

import os, sys

if __name__ == '__main__': #pragma nocover
    # Setup environ
    sys.path.append(os.getcwd())

    os.environ.setdefault("DJANGO_SETTINGS_MODULE", "myapp.settings")

    import django
    django.setup()

from odo import odo
from django.apps import apps

for m in apps.get_models():
    odo("sqlite://db.sqlite3::{0}".format(m._meta.db_table),"/tmp/{0}.csv".format(m._meta.db_table))

The above code will export data from every single table in your database into a set of CSV files using sqlite's .output command for the export, while most home brewed exporters would save the data line by line. Right now, you can import these into another database right? What if I told you that you can actually import export without intermediate CSV dumps? yep, quite possible but your milage might vary.

comments powered by Disqus