CODEWITHSUNDEEP
pythondjangodjango-modelsteradata
chaylins
chaylins

Reputation: 245

Integrating a teradata database with django models framework

We have an external teradata reporting database which we want to create a frontend for generating and querying using django.

I've gotten relatively far on just using a direct ODBC connection to the DB and running queries and displaying them on the website, but the further I get into it the more evident it is becoming that I really need to be using the django models framework.

My question is does anyone have any experience or insight in how to integrate a teradata database into a django web app?

For instance, I am displaying data using django-tables2 as it allows the tables to be generated not only using a queryset, but also a list of dictionaries which works great for me. Now I have come to adding filtering using django-filters package, and it seems it can only be done using an queryset object and nothing else.

Would be grateful for any advice you may have to offer!

Upvotes: 0

Views: 881

Answers (1)

ravioli
ravioli

Reputation: 3833

Here is a "database utilities" library I put together for my Django app. The back-end was Postgres, but it should be adaptable to Teradata.

Take a look at the logic in the get_data_qs() function. That's the one I used to try to create QuerySets using the __in filter trick, which should convert the result set to a QuerySet. Not sure how far this will take you, but something to play around with.

dbUtils.py 

from django.db import connection
from collections import namedtuple

# TO-DO: Handle non-ASCII characters (Python default encoding is ASCII)

# Return data as a RawQuerySet object (list of objects)
def get_data(myobjects, sp_signature, params):
    # IMPORTANT: Model field names must match column names in DB
    resultset = myobjects.raw('select 1 as id, * from ' + sp_signature, params)

    # Check if resultset has any rows
    try:
        test = resultset[0] 
    except IndexError:
        return myobjects.none() # return empty queryset ("not myobjects.none()" == True)

    # Return result set
    return resultset

# Return data as a single model object
def get_data_pk(myobjects, sp_signature, params):
    resultset = get_data(myobjects, sp_signature, params)

    # If there is a result set, return the first instance
    if(resultset):
        return resultset[0] 

# Return data as a QuerySet object (first column in resultset must be the model PK
def get_data_qs(self, sp_signature, params, pk_fieldname = None):
    cursor = connection.cursor()
    try:
        # Use default PK if not provided
        if(not pk_fieldname):
            pk_fieldname = self.model._meta.pk.name

        # Create "IN" filter
        myfilter = pk_fieldname + '__in' 

        # Execute query and apply filter to convert to QuerySet        
        cursor.execute("select * from " + sp_signature, params)
        return self.filter(**{ myfilter: (x[0] for x in cursor) }) 
          # TO-DO: This (**) may need looking at due to Python 3 conversion

    finally:
        cursor.close()

# Return data as a non-objectified result set (list of rows)
def get_data_raw(sp_name, params):
    with connection.cursor() as cursor:        
        cursor.callproc(sp_name, params)
        return_data = namedtuplefetchall(cursor) # Format as result set (list of tuples)
        cursor.close()

    return return_data

def save_data(sp_name, params):
    with connection.cursor() as cursor:        
        cursor.callproc(sp_name, params)
        return_data = cursor.fetchone() # Store any output
        cursor.close()

    return return_data

# Return all rows from a cursor as named tuples (i.e. rows with field names)
def namedtuplefetchall(cursor):
    columns = [col[0] for col in cursor.description]
    nt_result = namedtuple('Result', columns)

    return [
        nt_result(*row) 
        for row in cursor.fetchall()
    ]

Upvotes: 1

Related Questions