Login

model queries to Google Visualization DataTables

Author:
banderkat
Posted:
July 9, 2013
Language:
Python
Version:
1.5
Tags:
Google Visualization charts
Score:
0 (after 0 ratings)

Helper functions to use the Google Visualization API in Django. Module stores queries to use and creates a JSon representation of a Google Visualization DataTable for each by inspecting the query's model. Looks up any choices defined for the columns.

Depends on Google Visualization Python API: https://code.google.com/p/google-visualization-python/

To use, define the queries to use with the Visualization API at the head of views.py with:

add_query(queryset, 'query name')

then in view functions, get the JSon representation of a Google Visualization DataTable for the query with:

my_json = get_viz_json('query name')

Within the HTML template, pipe the JSon through safe:

var dataTable = new google.visualization.DataTable( {{ my_json|safe }} );

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
import gviz_api
from django.db.models.query import ValuesQuerySet
 
# mapping of model field types to google vizualization column types (all not 
# in list are 'string' type) valid google viz data types are:  'string' 
# 'number' 'boolean' 'date' 'datetime' 'timeofday'
fieldmap = {'DateField':'date','DateTimeField':'datetime','BooleanField':'boolean',
    'IntegerField':'number','DecimalField':'number','BigIntegerField':'number',
    'FloatField':'number','TimeField':'timeofday','NullBooleanField':'boolean'}
 
# { 'queryname' : [query object, { field_name : (datatype, descriptive_name) }, 
# { name of field with choices : { choices } }] }
queries = {}
 
def add_query(qry, qryName, makeStr=set()):
    """Takes a Django QuerySet or ValuesQuerySet and an (arbitrary, unique) name for it,
    and adds it to the collection of queries to use in Google Visualization DataTables.  Also
    takes an optional third argument of a set of column names to be put in the DataTable as 
    strings, ignoring their data type in the model."""
    
    if type(qry) is ValuesQuerySet:
        add_values_qry(qry, qryName, makeStr)
    else:
        add_full_qry(qry, qryName, makeStr)
    
 
def get_viz_json(qryName):
    """Takes a previously added query name and returns JSon representation of a 
    Google Visualization DataTable."""
    tbl = gviz_api.DataTable(queries[qryName][1])
    
    if type(queries[qryName][0]) is ValuesQuerySet:
        tbl.LoadData(get_viz_data_values(qryName))
    else:
        tbl.LoadData(get_viz_data_full(qryName))
    
    json = tbl.ToJSon()
    return json
 
def add_values_qry(qry, qryName, makeStr):
    model = qry.model
    desc = {} # { field_name : (datatype, descriptive_name) }
    choices = {}
    
    flds = qry.field_names
    for f in flds:
        fld = model._meta.get_field(f)
        t = fld.get_internal_type() # returns Django model field type as string
        gt = 'string' # default to string type
        
        if (not f in makeStr) and fieldmap.has_key(t):
            gt = fieldmap[t]
        
        if fld.choices:
            choices[f] = dict(fld.choices)
            desc[f] = ('string', fld.verbose_name)
        else:
            desc[f] = (gt, fld.verbose_name)
        
    
    if qry.aggregate_names:
        flds_agg = qry.aggregate_names # if query has annotations, add them as 'number' type
        for fa in flds_agg:
            descriptive_name = fa.replace('_', ' ')
            desc[fa] = ('number', descriptive_name)
        
    
    queries[qryName] = [qry, desc, choices]
 
def add_full_qry(qry, qryName, makeStr):
    model = qry.model
    desc = {} # { field_name : (datatype, descriptive_name) }
    choices = {}
    model_name = model.__name__
    flds = model._meta.get_all_field_names()
    
    for f in flds:
        try:
            fld = model._meta.get_field(f)
        except:
            continue # in case of FK field
        
        t = fld.get_internal_type() # returns Django model field type as string
        if t == 'ForeignKey':
            continue
        
        gt = 'string' # default to string
        
        if (not f in makeStr) and fieldmap.has_key(t):
            gt = fieldmap[t]
            
        desc[f] = (gt, fld.verbose_name)
        
        if fld.choices:
            choices[f] = dict(fld.choices)
            desc[f] = ('string', fld.verbose_name)
        else:
            desc[f] = (gt, fld.verbose_name)
        
    
    try:
        flds_agg = qry.query.aggregates.keys()
        for fa in flds_agg:
            descriptive_name = fa.replace('_', ' ')
            desc[fa] = ('number', descriptive_name)
        
    except:
        pass
    
    queries[qryName] = [qry, desc, choices]
 
def get_viz_data_full(qryName):
    """Takes a QuerySet name and returns a dataset to load into a Google Visualization DataTable."""
    data = []
    qry = queries[qryName][0]
    qry.update() # force query to re-evaluate
    flds = queries[qryName][1].keys()
    choices = queries[qryName][2]
    
    for itm in qry:
        row = {}
        for fld in flds:
            if choices.has_key(fld):
                row[fld] = choices[fld][itm.__getattribute__(fld)]
            else:
                row[fld] = itm.__getattribute__(fld)
            
        
        data.append(row)
    
    return data
 
def get_viz_data_values(qryName):
    """Takes a ValuesQuerySet name and returns a dataset to load into a Google Visualization DataTable."""
    data = []
    qry = queries[qryName][0]
    qry.update() # force query to re-evaluate
    flds = queries[qryName][1].keys()
    choices = queries[qryName][2]
    
    for itm in qry:
        row = {}
        for fld in flds:
            if choices.has_key(fld):
                row[fld] = choices[fld][itm[fld]]
            else:
                row[fld] = itm[fld]
            
                  
        data.append(row)
    
    return data

More like this

  1. JSON-compatible query filter specification by mhalle 6 years, 11 months ago
  2. UnicodeReprMixIn by ashcrow 6 years, 1 month ago
  3. compressing polygons for geodjango by showell 5 years, 8 months ago
  4. Django google maps v3 snipplet by summerisgone 4 years, 7 months ago
  5. pycallgraph by roppert 6 years ago

Comments

Please login first before commenting.