python convert sql result to tree hierarchy

Question

i have a sql query that returns 3 levels of a data chain on each row (ie. Country, County, City). There may be more than 1 county per country and more than 1 city per county.

resultset: "UK" "Lancashire" "Burnley" "UK" "Lancashire" "Blackburn" "UK" "Merseyside" "Liverpool" "UK" "West Yorkshire" "Leeds" "UK" "West Yorkshire" "York"

how do i iterate the resultset in python to create something like:

UK
->Lancashire
->->Burnley
->->Blackburn
->Merseyside
->->Liverpool
->West Yorkshre
->->Leeds
->->York

in php i would do something like:

while($row = $rec->fetch_object()) {
    $var[$row->country]['county'][$row->county]['city'][$row->city] = $row->city;
}

Please provide a better clue as to what the table structure is. The PHP code isn't the ideal way to show a SQL table structure. — S.Lott
– S.Lott, Commented Jan 11, 2011 at 18:33
the table structure is complex as it uses 3 INNER JOIN's and I didn't really want to explain all the relationships, hence the simplified question. Its not even geographic but i used areas as they are easy to relate to. — khany
– khany, Commented Jan 11, 2011 at 18:48

S.Lott · Accepted Answer · 2011-01-11 18:41:02Z

2

This isn't ideal because it's kind of obscure.

ccc = defaultdict( lambda: defaultdict( list ) )
for row in cursor.fetchall():
    country, county, city = row
    ccc[country][county].append(city)

There are probably cleaner ways to do this.

answered Jan 11, 2011 at 18:41

S.Lott

393k83 gold badges521 silver badges791 bronze badges

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

2 Comments

khany Over a year ago

i would tick this but i have no idea what lambda does - and I have tried to understand it. +1 anyways.

S.Lott Over a year ago

@khany: Don't understand lambda? Step 1. search. It's been asked here. Step 2. search. There are long essays available on Google. Step 3. try it. It's pretty clear how it works and you can play with code in Python to see how things work.

Collectives™ on Stack Overflow

python convert sql result to tree hierarchy

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related