Your right place for "Right to Know"


database administration

Python Django: Using multiple database, inspectdb and admin for existing database

One of the most powerful parts of Django is the automatic admin interface. It allows all users including developers to manipulated data inside database table. Often people use django admin tool to manage their database rather than using the console management tool provided by the database software itself or any other tool which are very limited and specific to one type of database only.

I have an existing setup mysql database with all the required schema and stored data. I want to use Django’s powerful administration tool to manage some of the stuffs on the database.

Lets get started on.

I have setup a django project called boad with necessary configurations updated in the file. I have added my mysql server as default database for the project.

Since running migration using django management command would create default django tables like django_sessions, django_migrations etc on my database which I dont like as I would like to keep my database very neat and clean as it was prior to django setup. So I decided to use django multiple database feature using database router that would route database operations of all other django related table to other database. I have setup a database router which will route read,write and syncdb operation of all tables not related to my existing database.

First in my I set up two databases as:

    'sqlite': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'HOST': 'localhost'
        'USER': 'root',
        'PASSWORD': '',
        'NAME': 'boad_database'

DATABASE_ROUTERS = ['boad.router.NonBoadAttributeRouter',] #Router's module path

I have used two database sqlite and mysql (default). I would be using sqlite to store data that are specific to django tables.

I created a file in the project module directory as:

class NonBoadAttributeRouter:

    non_boad_attribute_tables = ['auth', 'admin', 'contenttypes', 'sessions', 'messages', 'staticfiles', 'migrations']
    def db_for_read(self, model, **hints):
        if model._meta.app_label in self.non_boad_attribute_tables:
            return 'sqlite'
        return None

    def db_for_write(self, model, **hints):
        if model._meta.app_label in self.non_boad_attribute_tables:
            return 'sqlite'
        return None

    def allow_relation(self, obj1, obj2, **hints):
        if obj1._meta.app_label in self.non_boad_attribute_tables or obj1._meta.app_label in self.non_boad_attribute_tables:
            return True
        return None

    def allow_migrate(self, db, app_label, model_name=None, **hints):
        if app_label in self.non_boad_attribute_tables:
            return db=='sqlite'
        return None

Here I have stated that if application label is either of labels defined in non_boad_attribute_tables python variable, they should operate by “sqlite” database. Each router’s function would check for app label and route to “sqlite” database as per required or would return “None” which would tell django to fall back to “default” database for database operation.

Django have an “inspectdb” management command which is very useful to generate Django model from the existing database. As an example here I have use inspectdb to generate model for my course_category table in mysql.

venv) D:\Learnings\Django\boad\src>python inspectdb course_category
# This is an auto-generated Django model module.
# You'll have to do the following manually to clean this up:
# * Rearrange models' order
# * Make sure each model has one field with primary_key=True
# * Make sure each ForeignKey has `on_delete` set to the desired behavior.
# * Remove `managed = False` lines if you wish to allow Django to create, modify, and delete the table
# Feel free to rename the models, but don't rename db_table values or field names.
from __future__ import unicode_literals

from django.db import models

class CourseCategory(models.Model):
 course_cat_id = models.AutoField(primary_key=True)
 coursename = models.CharField(max_length=100)
 code = models.CharField(max_length=10, blank=True, null=True)
 project = models.CharField(max_length=30, blank=True, null=True)
 created_by = models.CharField(max_length=60)
 created_date = models.DateTimeField()
 deleted_by = models.CharField(max_length=60)
 deleted_date = models.DateTimeField(blank=True, null=True)
 deleted = models.IntegerField()

class Meta:
 managed = False
 db_table = 'course_category'

I have generated an model from existing database. I can do same for other mysql tables and create put it inside their own respective django app created using

python startapp courses


Now finally in file of each django app folder, I have register each model in admin as:

# -*- coding: utf-8 -*-
from __future__ import unicode_literals

from django.contrib import admin

from course.models import CourseCategory

class CourseCategoryAdmin(admin.ModelAdmin):
    list_display = ['course_cat_id', 'coursename', 'code', 'project']
    list_filter = ['project',]
    list_editable = ['code',], CourseCategoryAdmin)


Running django’s migration command wont create any other tables in my mysql database but instead will be created in other sqlite database.

After successful migration I can test my django app using:

python runserver

course_admin_interfaceI can now successfully setup admin interface for managing my mysql database.

This approach of creating admin interface is applicable to most of popular database systems like Oracle, postgres etc. Django is going more popular day by day and equally getting more recognition for its quick and powerful admin interface generation.


Dumping(backing) and restoring database [PostgreSQL, MongoDB, MySQL]

A database backup and recovery plan is creating backup copies of data. When error (internal exception within the DBMS, Application (or transaction) failures, damage to disk storage devices, file system failures, tape degradation or damage, and deleted data files) occur that damages the integrity of the database, a backup copy of the data can be used as the basis to recover or restore the database. When problems impact the database, the DBA can use the copy backups and the database log to recover the database. Whatever the cause of the problem, the DBA must be able to recover data quickly so that the business can continue to operate. In this article, I will teach how simply we can backup and restore any particular database (PostgreSQL, MongoDB, MySQL)

PostgreSQL database backup and restore

pg_dump is a utility for backing up a PostgreSQL database. pg_dump can be used to backup an database or any specific table, then pg_restore can be used select which parts of the database are to be restored.

Scenario: I have a postgres database instance running at server with one of the database named as “my_dbname”.

We need to store postgres server password in PGPASSWORD variable of the console

PGPASSWORD=my_pass (In Linux)

set PGPASSWORD=my_pass (In Windows)

pg_dump -h -U my_username -Fc my_dbname > pg_my_dbname_backup

Make sure that the postgres binary pg_dump is in your environment variable. If not, you need to change your current directory to the postgres binary folder.

-Fc option denotes that we are using custom dump format. We can use following format according to the need

  1. -Fp for plain format
  2. -Fd for directory format
  3. -Ft for tar format

Also we can use -j <no_of_jobs> for parallel jobs executing which could be useful if dumping requires use executing time.

Instead of output redirection ” > pg_my_dbname_backup” we can specify dumped filename using -f option as

-f pg_my_dbname_backup

pg_dump also allows dumping of specific table using -t option as

-t my_tablename

For full options or full documentation on using pg_dump I recommend to go through official postgres pg_dump documentation page.

If you want to create a routine backup script this wiki has some tips on it which provides a demo script for windows user as:

@echo off
for /f “tokens=1-4 delims=/ ” %%i in (“%date%”) do (
set dow=%%i
set month=%%j
set day=%%k
set year=%%l
set datestr=%month%_%day%_%year%
echo datestr is %datestr%

set BACKUP_FILE=<NameOfTheFile>_%datestr%.backup
echo backup file name is %BACKUP_FILE%
echo on
pg_dump -h <HostName> -p 5432 -U <UserName> -F c -b -v -f %BACKUP_FILE% <DATABASENAME>

Let’s say we have some error or problem in our database “my_dbname” and we need to restore it back from our backup file.

First we need to delete the existing erroneous database and create a fresh empty database of same name.

dropdb -h -U my_username my_dbname

createdb -h -U my_username my_dbname

Now we got a fresh empty “my_dbname” database where we can restore our backup into it

pg_restore -h -U my_username -d my_dbname pg_my_dbname_backup

we need to make sure that “pg_my_dbname_backup” is in current directory or could be replace with the path of the database file.

pg_restore can be used with option -C which could allow creation of the database also before restoring. i.e we can use option -C which would eliminate the use of createdb command before pg_restore

MongoDB database backup and restore

mongodump is a utility for creating a binary export of the contents of a mongo database. mongodump can export data from either mongod or mongos instances. mongodump captures the documents in the database in its backup data

I can create a backup directory called “mongodb_backup” using mongodump utility as

mongodump –host –out mongodb_backup

We can add

–db database_name to backup only database_name database

–collection collection_name to backup only collection_name collection

–username my_username –password my_password if need authentication for instance connection

–excludeCollection Collection_name to exclude any specific collection to be dumped

–archive my_mongo_dump.archive for dump in archive format

–gzip to dump in gzip format rather than directory

More on mongodump can be found in official documentation

Same way we can restore of dumped (backed up) database into running mongo instance using mongorestore as

mongorestore mongodb_backup

mongorestore can be used with same options as provided above for mongodump

More on mongorestore can be found in official documentation

MySQL database backup and restore

MySQL too provides a tool mysqldump to dump/backup any database

mysqldump -h -u my_username -p my_password my_dbname > mysql_backup.sql

If we need a gzip format we can pipe it to our gzip command before output redirection as

mysqldump -h -u my_username -p my_password my_dbname | gzip -9 > mysql_backup.sql.gzip

For restoring mysql database we need to make sure that restoring database exist and is empty.

mysql -u my_username -p my_password my_dbname < mysql_backup.sql

mysqlimport can also be used to import dumped mysql file as

mysqlimport -u my_username -p my_password my_dbname mysql_backup.sql

If we have a gzip format of dumped file we need to extract it before restore as

gunzip < mysql_backup.sql.gzip | mysql -u my_username -p my_password my_dbname

Create a free website or blog at

Up ↑