Fetch Oracle Sequence when Saving Django Model

August 24, 2010

I have some Django models on top of an Oracle database. Normally, when generating database tables from Django in Oracle, a sequence is created for the primary key and a trigger is added to automatically populate the serial column. In this case, I couldn't change the database to add the trigger so I needed to populate the serial using a seperate select from the sequence, before saving the Django model.

To complicate things a little more, I needed to use the multi-database support in Django and have this save happen on the non default database.

Here's the model save method. This overrides the default and populates the serial column if it is blank. It then calls up to do the save, passing in the database connection 'oracle'

def save(self):
  """
  Get next value for primary key, if required, then save
  """
  populate_serial_if_blank(self, 'primary_key_sequence_name')
  super(MyModelObject, self).save(using='oracle')

Here's the implementation of populate_serial_if_blank, which gets the django connection by name before running raw SQL on it.

from django.db import connections

def populate_serial_if_blank(pModelObject, pSequenceName):
  """
  Look to see if this object has a valid id.  If it does not, select from the sequence
  to get the next value, then populate the id field.
  """
  if pPdiModelObject.id == None:
    lCursor = connections['oracle'].cursor()
    lSql = "SELECT %s.nextval FROM dual" % pSequenceName
    lCursor.execute(lSql)
    lRows = lCursor.fetchall()
    pModelObject.id = lRows[0][0]
    lCursor.close()