Convert result from mysql to list in python

Question

How can I convert the results from Mysql query to a list in python? Here is what I've tried so far

import MySQLdb as mdb

con = mdb.connect('localhost', 'root', 'root', 'sample_db');
with con:
cur = con.cursor()
cur.execute("SELECT site_id FROM positive_outcomes")

for i in range(cur.rowcount):

    row = cur.fetchone()
    print row[0]

Saif Asif · Accepted Answer · 2016-08-22T08:10:07.207

1

You just have to listify the entire result set from the cursor object and you should be good

con = mdb.connect('localhost', 'root', 'root', 'sample_db');
with con:
cur = con.cursor()
cur.execute("SELECT site_id FROM positive_outcomes")

result_set = list(cursor.fetchall())

for result in result_set:
    # do something here

edited Aug 22 '16 at 08:10

answered Aug 22 '16 at 08:08

Saif Asif

5,516
3
31
48

`fetchall` already returns a list. no need to call `list` on it. – DeepSpace Aug 22 '16 at 08:09
True, but then comes the decision to whether call all results at once on the client end by `list(...)` or iterate over it row by row. Just as you mentioned in your comment on the question – Saif Asif Aug 22 '16 at 08:11
@DeepSpace 'fetchall' did the trick. Thank you. – Ahmad Waseem Aug 22 '16 at 08:13

score 1 · Answer 2 · answered Aug 22 '16 at 08:10

1

If you really need to use fetchone:

def yield_rows():
    for i in range(cur.rowcount):

        row = cur.fetchone()
        yield row[0]

print list(yield_rows())

answered Aug 22 '16 at 08:10

turkus

4,637
2
24
28

Convert result from mysql to list in python

2 Answers2