django – Python MySQLDB: Get the result of fetchall in a list

django – Python MySQLDB: Get the result of fetchall in a list

And what about list comprehensions? If result is ((123,), (234,), (345,)):

>>> row = [item[0] for item in cursor.fetchall()]
>>> row
[123, 234, 345]

If result is ({id: 123}, {id: 234}, {id: 345}):

>>> row = [item[id] for item in cursor.fetchall()]
>>> row
[123, 234, 345]

Im sure that after all this time, youve solved this problem, however, for some people who may not know how to get the values of a cursor as a dictionary using MySQLdb, you can use this method found here:

import MySQLdb as mdb

con = mdb.connect(localhost, testuser, test623, testdb)

with con:

    cur = con.cursor(mdb.cursors.DictCursor)
    cur.execute(SELECT * FROM Writers LIMIT 4)

    rows = cur.fetchall()

    for row in rows:
        print row[Id], row[Name]

django – Python MySQLDB: Get the result of fetchall in a list

This old Q comes up on Google while searching for flattening db queries, so here are more suggestions…

Consider a fast list-flattening iterator.

Others answers use fetchall() which first loads all rows in memory, then iterates over that to make a new list. Could be inefficient. Could combine with MySQL so-called server side cursor:

# assume mysql on localhost with db test and table bs
import itertools
import MySQLdb
import MySQLdb.cursors

conn = MySQLdb.connect(host=localhost,db=test, 
          cursorclass=MySQLdb.cursors.SSCursor ) 
cursor = conn.cursor()
# insert a bunch of rows
cursor.executemany(INSERT INTO bs (id) VALUES (%s),zip(range(1,10000)) )
conn.commit()
# retrieve and listify
cursor.execute(select id from bs)
list_of_ids = list(itertools.chain.from_iterable(cursor))
len(list_of_ids)
#9999
conn.close()

But the question is also tagged Django, which has a nice single field query flattener

class Bs(models.Model):
    id_field = models.IntegerField()

list_of_ids = Bs.objects.values_list(id_field, flat=True)

Leave a Reply

Your email address will not be published.