How to Get individual array format result using python sql query?

Question

I want to convert my python sql result to array format but currently i'm getting my python-query result is as below but i want to get it as different array for each joined table columns so anyone have idea regarding this then pls help me to resolve this

Below is python query result which i want to convert in array format

[{'id': 54522, 'location_type_id': 5, 'parent_id': 7544, 'name': 'Koln - Zollstock', 'name_deu': 'Köln - Zollstock', 'lat': '50.905591', 'lng': '6.95257', 'is_active': 1, 'is_use_for_avg': 0, 'created': datetime.datetime(2018, 9, 18, 9, 54, 21), 'modified': datetime.datetime(2018, 9, 18, 9, 54, 21), 'LocationType.id': 5, 'LocationType.name': 'Municipality', 'LocationType.is_active': 1, 'priority': 5, 'LocationType.is_use_for_avg': 0, 'LocationType.created': datetime.datetime(2014, 2, 10, 23, 22, 27), 'LocationType.modified': datetime.datetime(2014, 2, 10, 23, 22, 27), 'Parent.id': 7544, 'Parent.location_type_id': 4, 'Parent.parent_id': 297, 'Parent.name': 'Koln', 'Parent.name_deu': 'Köln', 'Parent.lat': '52.5200', 'Parent.lng': '13.4050', 'Parent.is_active': 1, 'Parent.is_use_for_avg': 0, 'Parent.created': datetime.datetime(2018, 9, 18, 8, 17, 15), 'Parent.modified': datetime.datetime(2018, 9, 18, 8, 17, 15)}]

And i want result as different array as below

    Array
(
    [LocationModel] => Array
        (
            [id] => 3
            [location_type_id] => 1
            [parent_id] => 
            [name] => Berlin
            [name_deu] => Berlin
            [lat] => 52.5170365
            [lng] => 13.3888599
            [is_active] => 1
            [is_use_for_avg] => 0
            [created] => 2018-09-18 08:02:16
            [modified] => 2018-09-18 08:02:16
        )

    [LocationType] => Array
        (
            [id] => 1
            [name] => State
            [is_active] => 1
            [priority] => 1
            [is_use_for_avg] => 0
            [created] => 2014-02-10 23:22:27
            [modified] => 2014-02-10 23:22:27
        )

    [Parent] => Array
        (
            [id] => 
            [location_type_id] => 
            [parent_id] => 
            [name] => 
            [name_deu] => 
            [lat] => 
            [lng] => 
            [is_active] => 
            [is_use_for_avg] => 
            [created] => 
            [modified] => 
        )
)

And sql query is which i'm hitting as

location_sql_query = """SELECT LocationModel.id, LocationModel.location_type_id, LocationModel.parent_id, LocationModel.name,LocationModel.name_deu, 
        LocationModel.lat, LocationModel.lng, LocationModel.is_active, LocationModel.is_use_for_avg, LocationModel.created,
        LocationModel.modified, LocationType.id, LocationType.name, LocationType.is_active, LocationType.priority, 
        LocationType.is_use_for_avg, LocationType.created, LocationType.modified, Parent.id, Parent.location_type_id, Parent.parent_id, 
        Parent.name, Parent.name_deu, Parent.lat, Parent.lng, Parent.is_active, Parent.is_use_for_avg, Parent.created,
        Parent.modified FROM locations AS LocationModel LEFT JOIN location_types AS LocationType ON 
        (LocationModel.location_type_id = LocationType.id AND LocationType.is_active = '1')
        LEFT JOIN locations AS Parent ON (LocationModel.parent_id = Parent.id) 
        WHERE LocationModel.id = {id}""".format(id=zip_code)

Can anyone help me get sql result as i had attached ?

Answer 1

This will do what you asked.

def reformat(rec):
    dct = {
        'LocationModel': {},
        'LocationType': {},
        'Parent': {}
    }
    for key, value in rec.items():
        if '.' not in key:
            dct['LocationModel'][key] = value
        else:
            tbl,fld = key.split('.')
            dct[tbl][fld] = value
    return dct