Python MySQLDB: Get the result of fetchall in a list

Solution 1:

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]

Solution 2:

I'm sure that after all this time, you've 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"]

Solution 3:

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)

Solution 4:

Make your cursor object in this manner:

db = MySQLdb.connect("IP", "user", "password", "dbname")

cursor = db.cursor(MySQLdb.cursors.DictCursor)

Then when you perform cursor.fetchall() on a query, a tuple of dictionaries will be obtained, which you can later convert to a list.

data = cursor.fetchall()

data = list(data)

Solution 5:

list= [list[0] for list in cursor.fetchall()]

this will render results in one list like - list = [122,45,55,44...]