pythonsqldjangooracle-databasenvl

Use NVL with string in Django?

On a Django app, I'm using cx_Oracle to display a roster of sports players:

def get_players(self, sport_code='', term=0):
"""

:rtype: object
"""
con = cx_Oracle.Connection(settings.BANNER_CONNECTION_URL)
cursor = con.cursor()
query = 'select PREF_NAME, CLASS, ELIGIBLE_HOURS, CHECKED_IN, SEASONS_USED, MAJR1, NVL(MINR1, Null), CA_EMAIL, ID from swvsprt_web where ACTC = \'%s\' AND term = \'%s\' ORDER BY PREF_NAME' % (
    sport_code, term)
cursor.execute(query)
players = cursor.fetchall()
cursor.close()
con.close()
return players

I want minor to just be blank if they don't have a minor

NVL(MINR1, Null)

But I can't quite get NVL to behave. "Null" makes it print the word "None." If I do 

NVL(MINR1, 0)

it will display a 0, but if I try any of the following, they crash the site with a 500 error:

NVL(MINR1, )

NVL(MINR1, '')

NVL(MINR1, "")

Can we use NVL to make it show null values as just nothing?

Solution

  • In Python, the 'null' object is the singleton None

    If you want to display something else, you should post-process the data, and replace values of None in that field with an empty string.

    A better alternative might be to just leave the values of None as is in your backend representation. This is more representative of not having a minor than an empty string.

    Then, on your frontend template, you can check for a null value, and display an empty string if MINR1 is None.