Python MySQLDB: Get the result of fetchall in a list

Question

I would like to get the result of the fetchall operation in a list instead of tuple of tuple or tuple of dictionaries. For example,

cursor = connection.cursor() #Cursor could be a normal cursor or dict cursor
query = "Select id from bs"
cursor.execute(query)
row = cursor.fetchall()

Now, the problem is the resultant row is either ((123,),(234,)) or ({'id':123}, {'id':234}) What I am looking for is (123,234) or [123,234]. Be best if I can save on parsing the resulset.

Probably have to provide a custom Cursor class to do this. The django db backends do this, so you could look for inspiration there. For example, `django.db.backends.mysql.base.CursorWrapper` is used on top of the MySQL Cursor, but I'm not sure where that's registered. It may mean providing a custom db backend that returns your custom Cursor. It will likely be easier to parse the data you need when accessing it. — dokkaebi, Oct 12 '12 at 21:23

score 93 · Answer 1 · answered Oct 12 '12 at 21:35

93

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]

answered Oct 12 '12 at 21:35

César

9,939
6
53
74

1

So if this scenario is just an example, how are you planning to that with queries that have more than one field? That's why you get a tuple of tuples in the first place – César Oct 12 '12 at 22:03
Who said its an example? I just want to fetch a single column of ids and row count is in millions. I want to do a post processing on the data which is why I am trying to figure out a way to avoid this expensive operation. – Raunak Agarwal Oct 12 '12 at 22:11
12

@RaunakAgarwal - please be civil - you didn't mention the constraints you were operating under in your question so it is perfectly reasonable for someone to suggest unrolling the nesting client-side. You should have mentioned you were dealing with millions of rows. – scytale Oct 12 '12 at 22:18
2

@RaunakAgarwal maybe you can use generators instead of list comprehensions. I guess it wouldn't be so expensive since generators have lazy evaluation – César Oct 12 '12 at 22:57
@César: Yeah, that could be one thing to do. But generators would only help me to control the amount of items I want to load that could be achieved from cursor itself. Probably, I have to do the computation at the database level itself. But Thanks anyways. – Raunak Agarwal Oct 12 '12 at 23:44

score 20 · Answer 2 · answered Sep 03 '13 at 18:06

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"]

I get an error where trying to fetch a property `tuple indices must be integers or slices, not str` — Somangshu Goswami, May 26 '20 at 06:32

score 11 · Answer 3 · edited May 23 '17 at 11:47

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)

A couple years late, but this is what OP was asking - create a list without using fetch all() due to the number of his data points. — Kevin Bott, Sep 14 '17 at 18:53

score 5 · Answer 4 · edited Jun 20 '18 at 04:40

5

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)

edited Jun 20 '18 at 04:40

Sangam Belose

4,262
8
26
48

answered Jun 20 '18 at 03:35

Sahana Joshi

59
1
2

score 3 · Answer 5 · answered May 20 '20 at 08:35

3

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

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

answered May 20 '20 at 08:35

Iceman

157
1
9

score 1 · Answer 6 · answered Feb 28 '13 at 08:25

If there is only one field, i can use this to make a list from database:

def getFieldAsList():
    kursor.execute("Select id from bs")
    id_data = kursor.fetchall()
    id_list = []
    for index in range(len(id_data)):
        id_list.append(id_data[index][0])
    return id_list

score -9 · Answer 7 · edited May 17 '13 at 23:15

-9

cursor.execute("""Select * From bs WHERE (id = %s)""",(id))

cursor.fetchall()

edited May 17 '13 at 23:15

Jon Lin

142,182
29
220
220

answered May 17 '13 at 22:55

user2395682

1

Python MySQLDB: Get the result of fetchall in a list

7 Answers7

Linked

Related