Sorting values in large dictionary fetched from mysql in python

Question

I connected to mysql database and fetched two columns from mysql with python with

    query  = ('select test_id, a_id from b.atc_id_TO_cID ')


    cursor.execute(query)
    rows = cursor.fetchall()



    CountinmyDi = {}

    for row in rows:

            if not row[0] in CountinmyDi.keys():
                    CountinmyDi[row[0]] = 1
            else :
                    CountinmyDi[row[0]] += 1
    for key in CountinmyDi.keys():
                    print str(key) + ":" ,CountinmyDi[key]

and created a dictionary CountinmyDi to count the unique items inside the dictionary. Now i have for example :

testA : 2 testB : 82 testC:102 test D : 3 test E:1 and so on .. about 220 items

I want to sort the items inside my dictionary for example increasing to decreasing order based upon their values for instance testC : 102, testB : 82 , testD :3 , test A :2 , test E : 1. I would like to get an idea how i could do that . Thank you and if you need further infos, i could gladly provide.

This question has nothing to do with MySQL, so please remove the tag. — FDavidov, Nov 14 '16 at 13:54
Why don't you just write the proper query so you don't have to do anything in Python? — Stefan Pochmann, Nov 14 '16 at 13:58
First, the OP states that he wants to sort the _received_ data; second, I wouldn't re-issue the query just to get the data in a different order. Bad practice (at least in my opinion). — FDavidov, Nov 14 '16 at 13:58
@StefanPochmann i am trying to learn python and make statistical analysis with it. — Acerace.py, Nov 14 '16 at 14:00
This question is actually not a duplicate of the question it has been flagged a duplicate of... — Guillaume, Nov 14 '16 at 14:19

Guillaume · Accepted Answer · 2016-11-14T14:16:08.793

1

I would ask the database to do it for you, SQL query would be:

select test_id, count(*) as count from b.atc_id_TO_cID group by test_id order by count

and it will return a 2 column table with test_id, number of tests with this test_id

If you can't do it this way, I would use a Python built-in Counter:

from collections import Counter

query  = ('select test_id, a_id from b.atc_id_TO_cID ')
cursor.execute(query)

test_counter = Counter(row[0] for row in cursor.fetchall())

for test_id, count in test_counter.most_common():
    print(test_id, ":", count)

edited Nov 14 '16 at 14:16

answered Nov 14 '16 at 14:05

Guillaume

5,497
3
24
42

Perfect . Thank you so much Guillaume. It works – Acerace.py Nov 14 '16 at 14:10

Sorting values in large dictionary fetched from mysql in python

1 Answers1