librelist archives

« back to archive

Creating nested list from one database table

Creating nested list from one database table

From:
Jerry Mccreary (Bixley)
Date:
2015-05-18 @ 22:24
I'm looking for an example of creating a nested list from one database table.

Example table: 
-----------------------------
Cars
-----------------------------
make | model | lotnum | color
-----------------------------
Ford | Focus | A1 | blue
Ford | Fusion | A2 | blue
Ford | Fusion | A3 | red
Honda | Accord | H1 | blue
Honda | Civic | H2 | white
-----------------------------

Final output to HTML that I want to achieve:
Ford:
Focus:
A1: blue
Fusion:
A2: blue
A3: red
Honda:
Accord:
H1: blue
Civic:
H2: white


Normally, I would have separate related Models and use SqlAlchemy to query
top parent:
"Make"
"Model"
"Car"

But in this case, I can only pull from one database table  ... "Cars" 
which has "make" and "model" fields.

I feel like this should be an easier task than I'm making it, but am stumped.
I started with query using DISTINCT on "make", to get first level,  but 
not sure how to join.
I've also looked at Marshmallow to serialize.

Any suggestions appreciated.

-- Jerry McCreary

Re: [flask] Creating nested list from one database table

From:
Jerry Mccreary (Bixley)
Date:
2015-05-19 @ 14:22
Thank you.  Using collections defaultdicts is the direction I needed.

In your example, would "table" (line 5) be the returned data from initial query?


Jerry McCreary

----------------------


On May 19, 2015, at 10:05 AM, David Baumgold <david@davidbaumgold.com> wrote:

> I think you’re better off solving this with a data structure in Python, 
rather than using complex SQL queries. You could use nested dictionaries, 
and it would be easier if you used defaultdicts. Here’s an example:
> 
> from collections import defaultdict
> 
> data = defaultdict(lambda: defaultdict(dict))
> # populate the data structure
> for row in table:
>     data[row.make][row.model][row.lotnum] = color
> # read the data structure
> for make, models in data.items():
>     for model, lots in models.items():
>         for lotnum, color in lots.items():
>             # do whatever
> 
> I’m not sure if something in the email chain will strip the whitespace 
in this email, so I’ve also made a Github Gist: 
https://gist.github.com/singingwolfboy/2fdc586072af86981fcd
> 
> David Baumgold
> 
> From: Jerry Mccreary (Bixley) <jerry@bixley.com>
> Reply: flask@librelist.com <flask@librelist.com>>
> Date: May 18, 2015 at 6:27:39 PM
> To: flask@librelist.com <flask@librelist.com>>
> Subject:  [flask] Creating nested list from one database table 
> 
>> I'm looking for an example of creating a nested list from one database table.
>> 
>> Example table: 
>> -----------------------------
>> Cars
>> -----------------------------
>> make | model | lotnum | color
>> -----------------------------
>> Ford | Focus | A 1 | blue
>> Ford | Fusion | A2 | blue
>> Ford | Fusion | A3 | red
>> Honda | Accord | H1 | blue
>> Honda | Civic  | H2 | white
>> -----------------------------
>> 
>> Final output to HTML that I want to achieve:
>> Ford:
>> Focus:
>> A1: blue
>> Fusion:
>> A2: blue
>> A3: red
>> Honda:
>> Accord:
>> H1: blue
>> Civic:
>> H2: white
>> 
>> 
>> Normally, I would have separate related Models and use SqlAlchemy to 
query top parent:
>> "Make"
>> "Model"
>> "Car"
>> 
>> But in this case, I can only pull from one database table  ... "Cars" 
whi ch has "make" and "model" fields.
>> 
>> I feel like this should be an easier task than I'm making it, but am stumped.
>> I started with query using DISTINCT on "make", to get first level,  but
not sure how to join.
>> I've also looked at Marshmallow to serialize.
>> 
>> Any suggestions appreciated.
>> 
>> -- Jerry McCreary

Re: [flask] Creating nested list from one database table

From:
David Baumgold
Date:
2015-05-19 @ 14:26
Correct.


From: Jerry Mccreary (Bixley) <jerry@bixley.com>
Reply: flask@librelist.com <flask@librelist.com>>
Date: May 19, 2015 at 10:24:42 AM
To: flask@librelist.com <flask@librelist.com>>
Subject:  Re: [flask] Creating nested list from one database table  

Thank you.  Using collections defaultdicts is the direction I needed.

In your example, would "table" (line 5) be the returned data from initial query?


Jerry McCreary

----------------------


On May 19, 2015, at 10:05 AM, David Baumgold <david@davidbaumgold.com> wrote:

I think you’re better off solving this with a data structure in Python, 
rather than using complex SQL queries. You could use nested dictionaries, 
and it would be easier if you used defaultdicts. Here’s an example:

from collections import defaultdict

data = defaultdict(lambda: defaultdict(dict))
# populate the data structure
for row in table:
    data[row.make][row.model][row.lotnum] = color
# read the data structure
for make, models in data.items():
    for model, lots in models.items():
        for lotnum, color in lots.items():
            # do whatever

I’m not sure if something in the email chain will strip the whitespace in 
this email, so I’ve also made a Github 
Gist: https://gist.github.com/singingwolfboy/2fdc586072af86981fcd

David Baumgold

From: Jerry Mccreary (Bixley) <jerry@bixley.com>
Reply: flask@librelist.com <flask@librelist.com>>
Date: May 18, 2015 at 6:27:39 PM
To: flask@librelist.com <flask@librelist.com>>
Subject:  [flask] Creating nested list from one database table 

I'm looking for an example of creating a nested list from one database table.

Example table: 
-----------------------------
Cars
-----------------------------
make | model | lotnum | color
-----------------------------
Ford | Focus | A 1 | blue
Ford | Fusion | A2 | blue
Ford | Fusion | A3 | red
Honda | Accord | H1 | blue
Honda | Civic  | H2 | white
-----------------------------

Final output to HTML that I want to achieve:
Ford:
Focus:
A1: blue
Fusion:
A2: blue
A3: red
Honda:
Accord:
H1: blue
Civic:
H2: white


Normally, I would have separate related Models and use SqlAlchemy to query
top parent:
"Make"</ li>
"Model"
"Car"

But in this case, I can only pull from one database table  ... "Cars" whi 
ch has "make" and "model" fields.

I feel like this should be an easier task than I'm making it, but am stumped.
I started with query using DISTINCT on "make", to get first level,  but 
not sure how to join.
I've also looked at Marshmallow to serialize.

Any suggestions appreciated.

-- Jerry McCreary