How to make an external database query iterable?

Question

I have the following code:

settings.py

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql', 
        'NAME': 'tectcom',                     
        'USER': 'test',                    
        'PASSWORD': '***146***',                 
        'HOST': '',                     
        'PORT': '',                      
    },

    'cdr': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'ast',                     
        'USER': '123',                      
        'PASSWORD': '654',                 
        'HOST': '',                      
        'PORT': '',                     
    }

views.py

def cdr_user(request):
        cursor = connections['cdr'].cursor()
        calls = cursor.execute('SELECT * FROM cdr')
        return render_to_response("cdr_user.html",
                {'result':calls }, context_instance=RequestContext(request))

cdr_user.html

{% for res in result %}

{{ res.billsec }}<br />

{% endfor %}

The table is like that:

+-------------+--------------+------+-----+---------------------+-------+
| Field       | Type         | Null | Key | Default             | Extra |
+-------------+--------------+------+-----+---------------------+-------+
| calldate    | datetime     | NO   | MUL | 0000-00-00 00:00:00 |       | 
| clid        | varchar(80)  | NO   |     |                     |       | 
| src         | varchar(80)  | NO   |     |                     |       | 
| dst         | varchar(80)  | NO   | MUL |                     |       | 
| dcontext    | varchar(80)  | NO   |     |                     |       | 
| channel     | varchar(80)  | NO   |     |                     |       | 
| dstchannel  | varchar(80)  | NO   |     |                     |       | 
| lastapp     | varchar(80)  | NO   |     |                     |       | 
| lastdata    | varchar(80)  | NO   |     |                     |       | 
| duration    | int(11)      | NO   |     | 0                   |       | 
| billsec     | int(11)      | NO   |     | 0                   |       | 
| disposition | varchar(45)  | NO   |     |                     |       | 
| amaflags    | int(11)      | NO   |     | 0                   |       | 
| accountcode | varchar(20)  | NO   | MUL |                     |       | 
| userfield   | varchar(255) | NO   |     |                     |       | 
| uniqueid    | varchar(32)  | NO   |     |                     |       | 
| linkedid    | varchar(32)  | NO   |     |                     |       | 
| sequence    | varchar(32)  | NO   |     |                     |       | 
| peeraccount | varchar(32)  | NO   |     |                     |       | 
+-------------+--------------+------+-----+---------------------+-------+

The problem is that I get a "Exception Value: 'long' object is not iterable"

TypeError at /cdr_user/
'long' object is not iterable
Request Method: GET
Request URL:    http://localhost:8000/cdr_user/
Django Version: 1.4.1
Exception Type: TypeError
Exception Value:    
'long' object is not iterable
Exception Location: /usr/local/lib/python2.7/site-packages/django/template/defaulttags.py in render, line 144
Python Executable:  /usr/local/bin/python
Python Version: 2.7.0
Python Path:    
['/home/tectadmin/cdr/billing',
 '/usr/local/lib/python2.7/site-packages/setuptools-0.6c11-py2.7.egg',
 '/usr/local/lib/python2.7/site-packages/pip-1.0-py2.7.egg',
 '/usr/local/lib/python2.7/site-packages/django_endless_pagination-1.1-py2.7.egg',
 '/usr/local/lib/python27.zip',
 '/usr/local/lib/python2.7',
 '/usr/local/lib/python2.7/plat-linux2',
 '/usr/local/lib/python2.7/lib-tk',
 '/usr/local/lib/python2.7/lib-old',
 '/usr/local/lib/python2.7/lib-dynload',
 '/usr/local/lib/python2.7/site-packages']
Server time:    Sab, 1 Set 2012 19:56:10 -0300
Error during template rendering

In template /home/tectadmin/cdr/billing/config/templates/cdr_user.html, error at line 21
'long' object is not iterable
11    text-indent: 6em;
12  }
13  </style>
14  {% extends "index_cliente.html" %}
15  {% load endless %}
16  {% block title %}CDR{% endblock %}
17  {% block content %}
18  
19  
20  
21  {% for res in result %}
22  
23  {{ res.billsec }}<br />
24  
25  {% endfor %}
26  
27  
28  
29  
30  <br />
31  <form name="input" action="/user_cdr/" method="et" >
Traceback Switch to copy-and-paste view

/usr/local/lib/python2.7/site-packages/django/core/handlers/base.py in get_response
                        response = callback(request, *callback_args, **callback_kwargs) ...
▶ Local vars
/home/tectadmin/cdr/billing/config/views.py in cdr_user
                {'result':result }, context_instance=RequestContext(request)) ...
▶ Local vars

How do I make result iterable to show it in my template? I've seen https://docs.djangoproject.com/en/dev/topics/db/sql/ and also other documentation, but I'm still lost in the code.

Thank you.

Answer 1

To iterate over the result of a SQL query in Python, use cursor.fetchall() to turn it into a list of lists. There's a very handy recipe here for turning those results into an object you can easily access:

class SQLRow(object):
    def __init__(self, cursor, row):
        for (attr, val) in zip((d[0] for d in cursor.description), row) :
            setattr(self, attr, val)

Once you have that class, this is simple:

def cdr_user(request):
    cursor = connections['cdr'].cursor()
    calls = cursor.execute('SELECT * FROM cdr')
    result = [SQLRow(cursor, r) for r in cursor.fetchall()]
    return render_to_response("cdr_user.html",
            {'result': result }, context_instance=RequestContext(request))

This way, the billsec attribute (and all other attributes) will still be accessible in your template.