Building a Simple Reporting App in Django: Part 2

In this post, I’m gonna dive right in and show you what happens every step of the way from the time you submit the form to when the graph gets rendered. BTW, Part 1 is here for anyone who is interested.

So previously, we have the following views file

def activation_signupcount(request):
    if request.method == 'POST':
        date_segment_form = DateAndSegmentForm(request.POST)
        date_segment_form = DateAndSegmentForm()
    return render_to_response('activation_signupcount.html',
                            {   'date_segment_form': date_segment_form,
                                'display_graph': date_segment_form.is_valid()   })

As you can see, an ’empty’ DateAndSegmentForm() is created upon request of the page. The user then inputs the relevant fields (dates and usersegment) and submits the form.

This is what the activation_signupcount.html looks like:

{% extends "base_activation.html" %}

{% block content %}
	<h2>Sign ups</h2>
	<p>Number of users who have registered to the site between the dates entered</p>
{% endblock %}

{% block graph %}
	{% if display_graph %}
	<p>This counts the number of <strong>{{ date_segment_form.cleaned_data.usersegment }}</strong>
	users who have successfully registered to the site (verified their email)
	who joined between <strong>{{ date_segment_form.cleaned_data.datestart }}</strong>
	and <strong>{{  date_segment_form.cleaned_data.dateend }}</strong></p>
	<img src="./activation_signupcount.png?usersegment={{ date_segment_form.cleaned_data.usersegment }}&datestart={{ date_segment_form.cleaned_data.datestart }}&dateend={{ date_segment_form.cleaned_data.dateend }}" />
	{% endif %}
{% endblock %}

You can see that the contents within block graph gets displayed only if display_graph is true. From the views file, display_graph is only true when the form submitted is valid. This is the most basic level of form validation. One of the cool things I learnt is that you can access the submitted data in the form directly as a variable within the template. Previously, I used to set each field as a variable in my view file and then call the variable from my template. That step is now skipped and the result are variables that look like this {{ date_segment_form.cleaned_data.usersegment }} where date_segment_form is the variable I’ve set in my views file, cleaned_data is a built in forms function that makes the html output look pretty and usersegment is the name of the field in the form that I want.

So now, the following image is constructed dynamically depending on the input variables.

<img src="./activation_signupcount.png?usersegment={{ date_segment_form.cleaned_data.usersegment }}&datestart={{ date_segment_form.cleaned_data.datestart }}&dateend={{ date_segment_form.cleaned_data.dateend }}" />

Taking a peek in the urls file shows that this url is mapped to the following view

(r'^activation/signupcount/percentchange/activation_signup_percentchange.png/$', 'activation_signup_percentchange_graph') 

def activation_signupcount_graph(request):

    return generate_linegraph(  activation_signupcount_graph.__name__,
                                request.GET['usersegment'] )

So this view calls a function generate_linegraph() which looks like this:

def generate_linegraph(func_name, datestart_string, dateend_string, usersegment=''):

    data = generate_data(func_name, datestart_string, dateend_string, usersegment)

    # insert dates with 0 values
    full_dates = generate_dates(datestart_string, dateend_string)

    extradates = []

    for x in full_dates:
        if x not in [y[1] for y in data]:
            extradates.append(('0',x)) #SQL Query returns count then date


    data = [(k[0],datetime.datetime.strptime(k[1],'%Y-%m-%d')) for k in data]

    data.sort(key=lambda x: x[1])

    # create classes
    fig = Figure(figsize=(10, 7), dpi=80, facecolor='w')
    ax = fig.add_subplot(111)

    # populate x and y coordinates
    x = []
    y = []

    x = [k[1] for k in data]
    y = [k[0] for k in data]

    # plot x and y coordinates
    ax.plot_date(x, y, color='blue', ls='--', marker='o',
                markerfacecolor='green', markersize=6)

    # format how the dates on x-axis look

    # name x and y axis

    # set x and y axis

    canvas = FigureCanvas(fig)
    response = HttpResponse(content_type='image/png')

    return response

This is mostly Matplotlib but what I have done is separated the data query and graph output into 2 parts. As you can see, another function called generate_data() is called from within generate_linegraph() which turns a list to the function. You can check out where I got most of the original Matplotlib code from here. I’ve also prepared a list of dates from date_start to date_end to fill in the gaps in the returned data. What happens is that if a particular day has zero sign ups, this will not be reflected in my SQL query. I could have inserted this logic in that layer but seems cleaner (and easier) to do it here. generate_dates() snippet can be found here.

generate_data() looks like this

def generate_data(func_name, datestart_string, dateend_string, usersegment=''):

    timepattern = '%Y-%m-%d'
    datestart_int = int(time.mktime(time.strptime(datestart_string, timepattern)))
    dateend_int = int(time.mktime(time.strptime(dateend_string, timepattern)))

    #SQL Queries
    activation_signupcount_graph = """
    SELECT IFNULL(COUNT(1),0), FROM_UNIXTIME(registered_at, %(timepattern)s)
    FROM users
    WHERE registered_at BETWEEN %(datestart_int)s AND %(dateend_int)s
    GROUP BY FROM_UNIXTIME(registered_at, %(timepattern)s);
    args =  {
            "timepattern" : timepattern,
            "datestart_int" : datestart_int,
            "dateend_int" : dateend_int

    query_map = {
                 'activation_signupcount_graph': activation_signupcount_graph,

    cursor = connection.cursor() 

    cursor.execute(query_map.get(func_name), args)

    data = list(cursor.fetchall())


    return data

I’m doing a few things here: I do some datetime formatting which is peculiar to my case as the dates are stored as unix time. The function cursor.execute(query_map.get(func_name),args) is the main point of this function. It traverses a dictionary called query_map (other objects omitted) via the func_name and executes the SQL I’ve written. Note that the order in which the data is being returned here is important. In this case, it is count followed by date. If you look back to generate_linegraph(), you can see the setting of the x and y axis based on the index value of the list being returned as such:

x = [k[1] for k in data]
y = [k[0] for k in data]

So the data is then returned to generate_linegraph() which in turn returns a response of the image. At this point you can use Matplotlib functions to make the graphs look prettier which I have not done. Upon which, an image response is return and is finally shown on the webpage itself.

That concludes my walkthrough on how to create a basic reporting app in Django.


One thought on “Building a Simple Reporting App in Django: Part 2

  1. Pingback: Building a Simple Reporting App in Django: Part 1 | pragmaticstartup

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s