Reporting with Django Multi-DB Support
| Django | 2012-10-15 |
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]