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 ?

• Tim Roberts: What you're showing as your result is PHP output, not Python. If you want the three tables in separate records, then do three fetches: one to select LocationModel, one to fetch the LocationType from the location_type_id, and one to fetch the Parent from the parent_id. It's certainly possible to separate them from your single big record, but it may be more trouble.

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

• Parth Shah: Thanks a lot @Tim Roberts for helping me to resolve issue. your solutions helped me to get out of problem