nested dictionary from database python

Question

The following code in python extracts all the rows from DATA table:

import MySQLdb

db = MySQLdb.connect("localhost","root","","myDB" )
cursor = db.cursor()
sql = "SELECT user,item,rate FROM data"
cursor.execute(sql)
results = cursor.fetchall()

for row in results:
    name = row[0]
    title = row[1]
    vote = row[2]
    print name,title,vote

db.close()

I want to construct the results according to the following dictionary form. How I can do this?

critics={'Lisa Rose': {'Lady in the Water': 2.5, 'Snakes on a Plane': 3.5,
'Just My Luck': 3.0, 'Superman Returns': 3.5, 'You, Me and Dupree': 2.5,
'The Night Listener': 3.0},
'Gene Seymour': {'Lady in the Water': 3.0, 'Snakes on a Plane': 3.5,
'Just My Luck': 1.5, 'Superman Returns': 5.0, 'The Night Listener': 3.0,
'You, Me and Dupree': 3.5},
'Michael Phillips': {'Lady in the Water': 2.5, 'Snakes on a Plane': 3.0,
'Superman Returns': 3.5, 'The Night Listener': 4.0},
'Claudia Puig': {'Snakes on a Plane': 3.5, 'Just My Luck': 3.0,
'The Night Listener': 4.5, 'Superman Returns': 4.0,
'You, Me and Dupree': 2.5},
'Mick LaSalle': {'Lady in the Water': 3.0, 'Snakes on a Plane': 4.0,
'Just My Luck': 2.0, 'Superman Returns': 3.0, 'The Night Listener': 3.0,
'You, Me and Dupree': 2.0},
'Jack Matthews': {'Lady in the Water': 3.0, 'Snakes on a Plane': 4.0,
'The Night Listener': 3.0, 'Superman Returns': 5.0, 'You, Me and Dupree': 3.5},
'Toby': {'Snakes on a Plane':4.5,'You, Me and Dupree':1.0,'Superman Returns':4.0}}

Nicolas Heimann · Accepted Answer · 2016-01-21 14:48:58Z

1

data = {}
for row in results:
   if row[0] not in data:
      data[row[0]] = {}

   data[row[0]][row[1]] = row[2]

answered Jan 21, 2016 at 14:48

Nicolas Heimann

2,59118 silver badges27 bronze badges

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

Comments

Vahid Msm · Accepted Answer · 2016-01-21 15:06:17Z

0

If your rows have same name with different values do this:

res = {}
for row in results:
    name = row[0]
    title = row[1]
    vote = row[2]
    res.get(name, res.update({name: {title: vote}}).update(title: vote})

edited Jan 21, 2016 at 15:06

answered Jan 21, 2016 at 14:57

Vahid Msm

1,1027 silver badges16 bronze badges

Collectives™ on Stack Overflow

nested dictionary from database python

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related