Reporting with Django Multi-DB Support

Oct 15, 2012 Django

The premise

I needed to build a reporting tool that reads from multiple databases, but that doesn't write to any of them. There should be only one database that's writable from this report app, and that's the default database set up when the project is created, for managing users and user groups with access to the tool.

Getting started

The first step is creating a Django project - I'm not going to discuss that in detail as you can find everything you need here and here. What I will point out is that I only ran syncdb once, to create that one writable default database.

Settings

After the initial syncdb, I added my reporting app to INSTALLED_APPS, then added the databases I'll be reporting on:

    # settings.py

    DATABASES = {
        'default': {
            'ENGINE': 'django.db.backends.postgresql_psycopg2',
            'NAME': 'reports_db',
            'HOST': '',
            'USER': '',
            'PASSWORD': ''
        },
        'dbone': {
            'ENGINE': 'django.db.backends.postgresql_psycopg2',
            'NAME': 'artist_one',
            'HOST': '',
            'USER': '',
            'PASSWORD': ''
        },
        'dbtwo': {
            'NAME': 'artist_two',
            'HOST': '',
            'USER': '',
            'PASSWORD': ''
        },
    }
    DATABASE_ROUTERS = ['report.router.ReportRouter']

    ...

    INSTALLED_APPS = (
        ...
        'reporting.report',
    )

The default database is a Django db with all of the auth/user information for logins and access to the reports; the second and third example databases are those from which we'll draw reporting information.

Models

The models for reporting were built using Django's inspectdb:

    python manage.py inspectdb --database=artist_one > report/models_one.py
    python manage.py inspectdb --database=artist_two > report/models_two.py

(A few years ago, I wrote a two-parter on using inspectdb to integrate legacy databases into a Django project - those are here and here if you'd like to take a look.)

For this reporting project, I ported them to separate model files for ease of management. One thing to be aware of - if your databases have similarly named tables, you'll want to make sure the model class names are different to avoid namespace collisions:

    # models_one.py

    class ArtistOne(models.Model):
        ...
    
        class Meta:
            db_table = u'artist'


    # models_two.py
    
    class ArtistTwo(models.Model):
        ...
    
        class Meta:
            db_table = u'artist'

Also note that there is currently no support for foreign key relationships across multiple databases. This will come back to bite you if you're reading from multiple Django databases - for one thing, you'll see some unintended crossover between the user tables in the admin. This isn't a problem for this particular project, where the app is built to generate reports on tables that are not based on Django models.

Router

Here's where multi-db really gets interesting. This router uses various model._meta attributes to determine which db to read from, and limits writes to the default database.

    # report/router.py

    class ReportRouter(object):
    
      def db_for_read(self, model, **hints):
        """
        Send reads on a specific model to the appropriate db
        """
        db = 'default'

        """
        This snippet is particular to my own use case - the databases I'm reporting on 
        have tables with unique prefixes, so I'm able to find that prefix in the 
        model._meta.db_table value to determine which database I need to point to.

        But you'll probably need to solve this problem in a different way.  Take a 
        look at this page for some other model._meta attributes you can use:
        https://docs.djangoproject.com/en/dev/ref/models/options/
        """
        import re
        one = re.search('firstartist_', model._meta.db_table)
        two = re.search('secondartist_', model._meta.db_table)
        if g:
          db = 'dbone'
        if p:
          db = 'dbtwo'
        return db
    

      def db_for_write(self, model, **hints):
        """
        Limit writes to the default db only (for managing users and groups).

        There are a few different ways you could do this. My preference 
        was to prioritize writes to the 'default' db over disabling
        writes to the other two databases.    
        """
        if model._meta.app_label == 'report':
            return None
        return 'default'

    
      def allow_syncdb(self, db, model):
        """
        Since both of the reporting databases support other apps, 
        we're not allowing syncdb on either of them.
        """
        return False

Forms

There's nothing special you need to do to create forms on top of these new models - just open forms.py, import your models, and go to town.

    from reporting.report.models_one import ArtistOne
    from reporting.report.models_two import ArtistTwo

One thing I did include, for the start page of my reporting tool, is a form allowing the user to choose which database they want to pull from:

    class DatabaseForm(forms.Form):
        dbs = []
        for db in settings.DATABASES:
            if db != 'default':
                dbs.append(db)
        choice_list = [('', 'CHOOSE A DATABASE')]
        dlist = [(str(d),str(d)) for d in dbs]
        for choice in dlist:
            choice_list.append(choice)
        DATABASES = choice_list
    
        database = forms.ChoiceField(choices=DATABASES, required=True)

Admin

It may also be useful to disable writes, edits and deletes to some objects from the admin. In that case, you'll want to import your model, create your admin class, and overwrite a few methods.

    from reporting.report.models_one import ArtistOne

    class ArtistOneAdmin(admin.ModelAdmin):
        list_display = ('id', 'name', 'field1', 'field2')
    
        # This disables edit links on individual records.
        fieldsets = [
            (None, {'fields':()}),
            ]
        def __init__(self, *args, **kwargs):
            super(ArtistOneAdmin, self).__init__(*args, **kwargs)
            self.list_display_links = (None, )
    
        # Disable individual delete and add
        def has_add_permission(self, request, obj=None):
            return False
        def has_delete_permission(self, request, obj=None):
            return False
    
        # Disable bulk delete
        def get_actions(self, request):
            actions = super(ArtistOneAdmin, self).get_actions(request)
            if 'delete_selected' in actions:
                del actions['delete_selected']
            return actions

admin.site.register(ArtistOne, ArtistOneAdmin)

Additional reading

Tutorial: Using Django's Multiple Database Support [Marakana]
Exposing multiple databases in Django's admin interface [Scribd]

Using managers with multiple databases [Django docs]
Writing custom django-admin commands [Django docs]