Iterating over a database column in Django

Question

I would like to iterate a calculation over a column of values in a MySQL database. I wondered if Django had any built-in functionality for doing this. Previously, I have just used the following to store each column as a list of tuples with the name table_column:

import MySQLdb
import sys

try:
    conn = MySQLdb.connect (host = "localhost",
                            user = "user",
                            passwd="passwd",
                            db="db")
except MySQLdb.Error, e:
    print "Error %d: %s" % (e.args[0], e.args[1])
    sys.exit (1)

cursor = conn.cursor()

for table in ['foo', 'bar']:
    for column in ['foobar1', 'foobar2']:
        cursor.execute('select %s from %s' % (column, table))
        exec "%s_%s = cursor.fetchall()" % (table, column)

cursor.close()

conn.commit()
conn.close()

Is there any functionality built into Django to more conveniently iterate through the values of a column in a database table? I'm dealing with millions of rows so speed of execution is important.

[SOLVED] Thanks everyone. I used the built-in iterator, combined with the values_list() call for performance optimization. Note that calling values() will return dicts, which are slow to iterate over, whereas values_list() returns much faster tuples. So, for example, if I want to iterate over every row of the column 'foobar1', in the table 'foo', I can obtain an iterator in the following way:

foobar1_iterator = foo.objects.values_list('foobar1').iterator()

Suppose I want to iterate over i to produce a list of all row values of 'foobar1'. Then simply do this:

foobar1_list = [i for i in foobar1_iterator]

Answer 1

from django.db.models.loading import get_model
app_name = 'your_app_name'
for model_name in ['foo','bar']:
    model = get_model(app_name, model_name)
    model_values = model.objects.values('foorbar1','foobar2') # this is a ValuesQuerySet. 
                                                              # you can run your computation on it, 
                                                              # or store the values somewhere.

Answer 2

Database microoptimalisation is not a strong part of Django ORM. However, when speed is so important, I wonder if the exec is the right way to do it.

Anyways, You write "iterate through the values of a column", this means you have multiple values in single column separated by a separator (not seen in your code)?

Then just

for value in modelinstalnce.column.split('seprator'):
    print 'whatever'

As for connection, it's better to use

from django.db import connection

instead of doing it by hand.

As for pairs, I'd do something like:

pairs = []

for model in (MyModel, MyModel2,):
    for field in model.field_names:
        pairs.append((field, getattr(model, field))

Answer 3

Look into Django docs for only() and iterator():

http://docs.djangoproject.com/en/1.1/ref/models/querysets/#only-fields

http://docs.djangoproject.com/en/1.1/ref/models/querysets/#iterator