Fun with Oracle and External Data

I’m playing around today with Django and Oracle, connecting my application to data from another application.

First thing I realized is that I cannot use a table or view that does not have a single column primary key.  Without a primary key, the Django Admin system complains loudly.  For those that do have a good key, it’s pretty easy to declare it to Django:

id_number = models.CharField(max_length=10,primary_key=True)

This will override Django’s default behavior of using ‘ID’ as the primary key field name.

Next, there’s a specific way that one declares the schema and table/view name for the existing structure.  Each has to be in quotes, with the entire name also in quotes.  Something like this:

    class Meta:
        db_table = u'"SCHEMA"."TABLE"'

Something else that I needed to do was to not allow changes to the external data.  This is enforced by Oracle security, but I also wanted to make it clear in my code that saves and deletes are not going to happen.  To do this, I create an abstract class upon which my other models are based, which includes the redefined save() and delete() methods that raise a NotImplemeted error.  These produce a nice, big error page when DEBUG=TRUE.

class AdvanceBase(models.Model):
    Abstract base class for accessing tables and views in the external schema

    class Meta:
        abstract = True

    def save(self): # No saving allowed
        raise NotImplementedError

    def delete(self): # No deletes allowed
        raise NotImplementedError

I decided to try setting up a relationship between two of the external tables.  This wasn’t really different than any other foreign key, with one exception.  I wanted to rename the field to follow Django conventions.  Orginally called SCHOOL_CODE, I changed it to SCHOOL – same as the foreign key object.  In order to still map to the correct DB column, the db_column attribute is used to declare the original name:

school = models.ForeignKey(School,blank=True,null=True,db_column='SCHOOL_CODE')

One more experiment – using a manager.  In this case I wanted to filter a table to include only living people.   The fact that I was using an external table did not change the method for this:

class PersonManager(models.Manager):
    def get_query_set(self):
        return super(PersonManager, self).get_query_set().filter(status='A')

Something else I learned was that the Django Admin will use the first manager listed for a model.  By placing the reference to the PersonManager ahead of the normal objects, the Admin only showed living people.

I’m sure there will be more later as I dig deeper into using external data.  I have an actual application related goal driving my interest, so I should get this solved soon.

As always, leave your comments below.