How to make a dictionary within a dictionary in MySQLPython?

Question

I have the below code:

import MySQLdb  
import sys
import pprint

connect = MySQLdb.connect(host = "127.8.2.3", port=3377, user = "root",  db="data1")
with connect:

    cur = connect.cursor()
    cur.execute("SELECT familynames,names FROM data1.files")

    rows = cur.fetchall()
pprint.pprint(rows)

I want to create a big dictionary which has "family names" as key and "names" as the value.Further, the value of the big dict(names), should again be a dict itself with key=names and value= number of identical names under the same family name.

I have tried the DictCursor function of MySQLPython but it is not capable of creating dict within dict.

I also have tried to make a loop over each item of the big dict to split and create another smaller dict(for values of the big dict) but since the returned result of the cur.execute is a Tuple that is not possible as well.

Can someone help me in this regards?

Martijn Pieters · Accepted Answer · 2013-03-27 12:12:11Z

1

You need to do some processing to get that structure. I'd use itertools.groupby to get your rows grouped by family name, then a collections.Counter() to create the inner mapping you need.

groupby requires that you sorted the data, so the query needs a ORDER BY clause too:

from collections import Counter
from itertools import groupby
from operator import itemgetter
import pprint

import MySQLdb  

familynames = {}

connection = MySQLdb.connect(host="127.8.2.3", port=3377, user="root",  db="data1")
with connection:
    cur = connect.cursor()
    cur.execute("SELECT familynames, names FROM data1.files ORDER BY familynames")

    for familyname, names in groupby(cur, key=itemgetter(0)):
        familynames[familyname] = Counter(row[1] for row in names)

pprint.pprint(familynames)

The groupby function groups the rows on the first element of each row (the familynames column), so looping over the names iterable will only yield rows where familynames has the same value.

We then feed the names (second column in each row) to the Counter(), which results in name keys with a count for each name as the value.

edited Mar 27, 2013 at 12:12

answered Mar 27, 2013 at 12:02

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

UserYmY Over a year ago

Thank you for the help.The problem with the above code is that the counter is only counting its key, so basically the number of the counter is always 1. What I want to see is how many times I get same names with one family name(see below):for instance:

UserYmY Over a year ago

{Nickelson:{sam:2 Jack:5 Joe:10 Wiliam:1} Gabrielii: {Maria:2 Sami:3}}

Martijn Pieters Over a year ago

@user2058811: names is not the name then? If the second column contains sam, Jack, Joe, etc. then the Counter will count those just like you want.

UserYmY Over a year ago

yes 'names' is name like sam,Jack etc. but now the code only count number of Sam s not the number of Sam s with Nickelson family name

Martijn Pieters Over a year ago

@user2058811: No, the code groups rows by family name, that is what groupby(..., key=itemgetter(0)) does. It groups rows by the first column (so row[0]).

|

Collectives™ on Stack Overflow

How to make a dictionary within a dictionary in MySQLPython?

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related