python - 如何使外部数据库查询可迭代？

我有以下代码:

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 %}

该表是这样的:

+-------------+--------------+------+-----+---------------------+-------+
| 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   |     |                     |       |
+-------------+--------------+------+-----+---------------------+-------+

问题是我得到了“Exception Value:'long'对象是不可迭代的”

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

如何使结果可迭代以将其显示在模板中？
我看过https://docs.djangoproject.com/en/dev/topics/db/sql/和其他文档，但是我仍然迷失在代码中。

谢谢你。

最佳答案

要遍历Python中SQL查询的结果，请使用cursor.fetchall()将其转换为列表列表。有一个非常方便的食谱here可以将这些结果转换为您可以轻松访问的对象:

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)

有了该类(class)后，这很简单:

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))

这样，仍然可以在模板中访问billsec属性(和所有其他属性)。

关于python - 如何使外部数据库查询可迭代？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/12232244/