The table:
categories = Table("categories", metadata,
Column("id", Integer, primary_key=True),
Column("name", String),
Column("parent_id", Integer, ForeignKey("categories.id"),
CheckConstraint('id!=parent_id'), nullable=True),
)
A category can have many children, but only 1 parent. I have got the list of dictionary values as follows using CTE: eg. For id :14, parent is 13 and traversed from parent 8->10->12->13->14 where parent 8 has no parent id.
[
{
"id": 14,
"name": "cat14",
"parent_id": 13,
"path_info": [
8,
10,
12,
13,
14
]
},
{
"id": 15,
"name": "cat15",
"parent_id": 13,
"path_info": [
8,
10,
12,
13,
15
]
}
]
I would like to get the attributes of the parent also embedded as subcategories in the list as:
{
"id": 14,
"name": "cat14",
"parent_id": 13,
"subcats": [
{
"id: 8",
"name": "cat8",
"parent_id":null
},
{
"id: 10",
"name": "cat10",
"parent_id":8
},
{
"id: 12",
"name": "cat12",
"parent_id":10
},
and similarly for ids 13 and 14.....
]
},
{
"id": 15,
"name": "cat15",
"parent_id": 13,
"subcats": [
{
"id: 8",
"name": "cat8",
"parent_id":null
},
{
"id: 10",
"name": "cat10",
"parent_id":8
},
{
"id: 12",
"name": "cat12",
"parent_id":10
},
and similarly for ids 13, 14, 15.....
]
}
] Notice that 'path_info' has been deleted from the dictionary and each id has been displayed with its details. I want json dumps with the above indented format. How to go about? Using flask 0.10, python 2.7