Chado Django HOWTO

From GMOD
Revision as of 11:07, 1 September 2008 by Vdejager (Talk | contribs)

Jump to: navigation, search

--Vdejager 11:07, 1 September 2008 (UTC)

Chado access with Django HOWTO

Abstract

this HOWTO describes how to use the Django (Python based) framework for accessing a Chado database. The Django framework can be used to create web interfaces and command line tools using the Python language.

Introduction

During the first GMOD Summer school and GMOD User meeting a great deal was learned about Chado and the surrounding GMOD Tools. Specifically that one should try not to change the Chado scheme (althouhgh some do with very good reasons) and secondly not to change code of third party tools, Perl modules etc in order to make them work with Chado. (or at least if they are bug fixes, give them back to the community). This will break upgradability and platform independence of those tools.

== Why Django ==.

high performance

Django is a high-level Python Web framework that encourages rapid development and clean, pragmatic design and adheres the DRY (Don't Repeat Yourself) principle. Developed and used over two years by a fast-moving online-news operation, Django was designed to handle two challenges: the intensive deadlines of a newsroom and the stringent requirements of the experienced Web developers who wrote it. Although most genome annotation databases probably won't have to endure a million hits per hour they will be able to benefit from a lot of optimization strategies applied to high traffic sites like query caching and lazy querying methods.

structure

Django lets you structure the design of a site to a high degree without giving up any flexibility or portability. Django certainly does not give you an out of the box website, but gives you a flexible and highly documented framework that is well maintained by a large community.

This makes Django a nice choice for data disclosure projects like a website on top of a Chado database. There are other such frameworks like Turbogears (Python), Hibernate(Java), Ruby on Rails and Catalyst(Perl). Choose what you like and write a HOWTO as well. Python is the most used language in our lab and thus an obvious first choice. (and the inventor is Dutch, Guido van Rossum, employed by Google.)

our goal

We will use the Django framework as showcase for disclosing our microbial genome information.


Prerequisites

  • A working Chado database. It should work with most recent versions. This howto was created using version 1.01 of the schema.
  • Python, at least 2.4, but preferably version 2.5, this is probably already installed during your Linux setup.
  • Apache 2 with mod_python installed. alternatively you may setup a mod_wsgi server as described in Django and mod-wsgi
  • psycopg2, the python postgres interface, which should be found in your Linux distribution or can be snatched from http://www.initd.org/
  • Django of course. This howto is written with the Django version 1.0 beta 2, actually revision 8791 from the Django SVN repository which should be virtually identical to version 1.0.
  • please make sure mod_python works as described in http://www.djangoproject.com/documentation/modpython/
  • If you are not familiar with Django, start reading the tutorial at http://docs.djangoproject.com (stable) or http://www.djangoproject.com/documentation/ (development)
  • try to get the Django welcome screen before continuing the project creation step.

important Django URLS


Preparations

From this point on it is assumed you have read the Django introduction and tutorial on the django project website. http://www.djangoproject.com/documentation/

In an ideal world one would be able to upgrade the Django framework code without breaking anything (a practice I have been doing for almost a year with some other sites under development, only the last major changes to Django broke a site (but how and why to fix those is well described in the Django documentation)

Also, since the Chado scheme is bigger than most schemes, the models should be generated ore regenerated automatically. Any model specific functionality should be attached to the model classes in such a way that the models can be upgraded independently without breaking the website code.


create a Django project

A Django project consists of a tree of files under a certain directory (preventing scattered code). this directory may be inside a user's home dir or inside a specific location where all Django projects are stored. When a Django website is created following the guidelines in the official documentation it should be a minimal task to change locations or even servers making deployment a breeze.

Inside your home directory we create a Django project with the following command:

   django-admin.py startproject <your project name>
   example /home/gmod/projects/django-admin startproject microgear

This will create a directory that contains several files:

   __init__.py
   manage.py
   settings.py
   urls.py

We start by changing the settings.py file and filling in some options:

   DATABASE_ENGINE = 'postgresql_psycopg2'            # 'postgresql_psycopg2', 'postgresql', 'mysql', 'sqlite3' or 'oracle'.
   DATABASE_NAME = 'dev_chado_03'	                   # Or path to database file if using sqlite3.
   DATABASE_USER = 'chado'                            # Not used with sqlite3.
   DATABASE_PASSWORD = '<no i'm not giving you mine>' # Not used with sqlite3.
   DATABASE_HOST =                                  # Set to empty string for localhost (uses sockets)
                                                      # Set to machine IP to force tcp connection. Not used with sqlite3.
   DATABASE_PORT =                                  # Set to empty string for default. Not used with sqlite3.
  • make sure you set MEDIA_ROOT, MEDIA_URL and ADMIN_MEDIA_PREFIX as described in the Django manual.
  • make site_media/ a symlink in your project dir pointing to a directory on your web server's document root. This is where all your static files go (pdf's, jpgs,pngs etc)

save the file and we are ready for the model generation part.

The Django Model Philosophy

A model is the single, definitive source of data about your data. It contains the essential fields and behaviors of the data you’re storing. Django follows the DRY Principle. The goal is to define your data model in one place and automatically derive things from it.

This is not going to work for a Chado database since the schema is predefined and works a lillte bit differnet than how Django normally would create it. Django also does not know how to create views and such although it can perfectly use then as we will notice later.

creating a Django app

First create a Django application inside you project directory. Switch to your project directory and create an application framework with the command:

  ./manage.py startapp <your application name>
  example /home/gmod/projects/microgear/manage.py startapp gmod


This will create a directory inside your project directory named gmod and contains all file scaffolds we will need later.


creating the models

Now we switch back to our project directory and enter the following command.

  ./manage.py inspectdb > unsortedmodels.py

This will create a raw models.py with a model for each table and view in the specified Postgres database. We will need to edit this file a bit with a Perl script. (

Each foreign key relation should have a unique name in Django to support reverse relationships. The following Perl code will create these unique names. The code rewrites the models in such a way that one could query reverse relations like this:(feel free to change and republish since my Perl is a bit rusty.)

The code will also create an admin.py file for linking the models to the admin site (handy for smaller size tables like the organism, db or cv table.

Perl code Gzip:sormodels.pl.gz


usage: perl sortmodels.pl unsortedmodels.py models.py <project> <app>

The resulting files, models.py and admin.py should be copied to the <app> directory. Have a look at these files. A model in Django representing a database table looks like this:

   class Feature(models.Model):
       feature_id = models.IntegerField(primary_key=True)
       dbxref = models.ForeignKey('Dbxref', related_name="feature_dbxref_set")
       organism = models.ForeignKey('Organism', related_name="feature_organism_set")
       name = models.CharField(max_length=255)
       uniquename = models.TextField()
       residues = models.TextField()
       seqlen = models.IntegerField()
       md5checksum = models.TextField() # This field type is a guess.
       type = models.ForeignKey('Cvterm', related_name="feature_type_set")
       is_analysis = models.BooleanField()
       is_obsolete = models.BooleanField()
       timeaccessioned = models.DateTimeField()
       timelastmodified = models.DateTimeField()
       class Meta:

app_label="<your app name>"

           db_table = u'feature'


creating model specific functions

in Django it is possible to specify so called model methods. These model methods describe the way a model behaves and can add certain functionality to a model. A special model method called __unicode__ describes how to display the standard name of a model instance (representing a record in the database). We use these methods to get something readable while playing with the command line further in this tutorial.

We could create this model definition by editing the classes in model.py, but instead we will use a common python pattern.

We create a new file called modeldefs.py. Inside this file we will create all our model methods and link them together inside the special __init__.py file that is used to initialize the package in Python (

modeldefs.py:

   #this file contains all the model methods we will attach to the specific models in the __init__.py file
   # one method may be attached to different model adhering to the DRY principle
   #
   #The line below imports all the Chado models
   from <project>.<app>.models import *
   #this is a generic method definition for model, returning the value of the field called 'name'
   def unicode_name(self):
       return self.name


   # this is a method definition for the 'Organism' model, returning the value of the field called
   # 'common_name'
   def unicode_common_name(self):
       return self.common_name


attaching the model method defnitions to specific models

__init__.py:

   # this file attaches defined methods to specific models
   #
   # import the model method definitions
   from <project>.<app>.modeldefs import *
   setattr(Organism, '__unicode__', unicode_common_name)
   setattr(Cv, '__unicode__', unicode_name)
   setattr(Db, '__unicode__', unicode_name)
   setattr(Cvterm, '__unicode__', unicode_name)
   setattr(Feature, '__unicode__', unicode_name)
   setattr(Featureloc, '__unicode__', location)



link everyling together

in settings.py: the INSTALLED_APPS section should contain besides the standard settings.

   'django.contrib.admin',
   '<project>.<app>.',
  • note the comma at the last item, this is a python requisite

finalizing

Once this has been inserted we need to run one other command. From the command line inside your <project> run

   ./manage syncdb

This will install all the tables necessary for the Django Admin application. You are now ready to continue building a website or run a scripts using the Django framework against a Chado database.


Using Django from the command line

(you may want to install Django commandline extentions from http://code.google.com/p/django-command-extensions/wiki/InstallationInstructions )

=== starting an interactive python shell


Inside your project dir

   ./manage shell
   >>>from <project>.<app>.models import *

querying the database

Show all Organisms in the database:

   >>>Organism.objects.all()

All Features from a specific organism (See http://www.djangoproject.com/documentation/db-api/ for an explanation of all database api methods):

   >>>Feature.objects.filter(Organism__common_name__iexact='Lactobacillus_plantarum')

All Features from a specific source feature between a start and stop location:

   >>>Feature.featureloc_feature_set.filter(strand__exact=1).filter(fmin__gte=10000).filter(fmax__lte=20000)

stacking queries

using Q objects

show me the generated SQL

It is possible to see the SQL Django generates using the following commands

Make sure your Django DEBUG setting is set to True. Then, just do this:

   >>> from django.db import connection
   >>> connection.queries

connection.queries is only available if DEBUG is True. It’s a list of dictionaries in order of query execution. Each dictionary has the following:

  • ``sql`` -- The raw SQL statement
  • ``time`` -- How long the statement took to execute, in seconds.

connection.queries includes all SQL statements — INSERTs, UPDATES, SELECTs, etc. Each time your app hits the database, the query will be recorded.


Running commandline python scripts using Django for database interaction

Tips and tricks

BioPython interaction

Example website