Jay Taylor | programmer notes

Apr/10

18

Django + Oracle = …

Django + Oracle = one error after another.

It took 3 hours just to get the Oracle DB driver installed and working (ended up linking all the Oracle xxx.so libs into /lib64/xxx.so, and that worked.

Then I created my models:

class Customer(models.Model):
 id = models.IntegerField(primary_key=True)
 name = models.CharField(max_length=100)
 phone_number = models.CharField(max_length=40)
 address = models.CharField(max_length=100)
 city = models.CharField(max_length=50)
 state = models.CharField(max_length=2)
 zipcode = models.CharField(max_length=10)
 date_created = models.DateField(auto_now_add=True)
 financial_info = models.CharField(max_length=255)

 def __unicode__(self):
 return self.name

 class Meta:
 db_table = 'CUSTOMER'
 db_tablespace = 'tables'

When I went into the AdminSite to create a customer, everything seemed to be working well.  I filled out the form, pressed “Save”, and then was greeted with a lovely error page:

DatabaseError at /admin/jjauto/customer/add/
ORA-00904: "CUSTOMER"."ID": invalid identifier
...

Googling this term + django, I found that there was a ticket which had the same error. At the bottom it said a fix had been integrated with the latest SVN version.

I figured out how to run different versions of Django concurrently so as not to break my other sites running fine with Django 1.1.1.  After all this, I still got the same error when creating a new customer.

At this point I started digging again and discovered the SQL query that is causing the error:

SELECT * FROM (SELECT ROWNUM AS "_RN", "_SUB".* FROM (SELECT "EMPLOYEE"."ID", "EMPLOYEE"."NAME", "EMPLOYEE"."PHONE_NUMBER", "EMPLOYEE"."ADDRESS", "EMPLOYEE"."CITY", "EMPLOYEE"."STATE", "EMPLOYEE"."ZIPCODE", "EMPLOYEE"."DATE_CREATED" FROM "EMPLOYEE" WHERE "EMPLOYEE"."ID" = :arg0 ) "_SUB" WHERE ROWNUM <= 21) WHERE "_RN" > 0 (,)

So I took the query and modified it to use lower-case column names:


SELECT * FROM (SELECT ROWNUM AS "_RN", "_SUB".* FROM (SELECT "EMPLOYEE"."id", "EMPLOYEE"."name", "EMPLOYEE"."phone_number", "EMPLOYEE"."address", "EMPLOYEE"."city", "EMPLOYEE"."state", "EMPLOYEE"."zipcode", "EMPLOYEE"."date_created" FROM "EMPLOYEE" WHERE "EMPLOYEE"."id" = 1 ) "_SUB" WHERE ROWNUM <= 21) WHERE "_RN" > 0;

I ran it in SQL Developer and it executed fine, returning no hits.

It is becoming apparent that the problem is Django taking each column (e.g. “id”), and creating the table with the column name (e.g. “id”, lower-cased), but then when it runs this query, the column names become upper-case (i.e. “ID”).  Ok, that is dumb and should probably not be happening, but I’ll try and work with what I’ve got here.  I found the option to override the column names, and used a little vimfu to make it painless:

%s/^(s+)([^ ]+)( = .*))$/123, db_column='U2')/ge | %s/(, /(/ge

yielding:

class Customer(models.Model):
 id = models.IntegerField(primary_key=True, db_column='ID')
 name = models.CharField(max_length=100, db_column='NAME')
 phone_number = models.CharField(max_length=40, db_column='PHONE_NUMBER')
 address = models.CharField(max_length=100, db_column='ADDRESS')
 city = models.CharField(max_length=50, db_column='CITY')
 state = models.CharField(max_length=2, db_column='STATE')
 zipcode = models.CharField(max_length=10, db_column='ZIPCODE')
 date_created = models.DateField(auto_now_add=True, db_column='DATE_CREATED')
 financial_info = models.CharField(max_length=255, db_column='FINANCIAL_INFO')

 def __unicode__(self):
 return self.name

 class Meta:
 db_table = 'CUSTOMER'

Finally, the inserts work :) What a PITA…

RSS Feed

No comments yet.

Leave a comment!

You must be logged in to post a comment.

<<

>>

Find it!