如何使用python sql查询获得单个数组格式的结果？

Question

我想将我的python sql结果转换为数组格式，但目前我得到的python查询结果如下，但我想将其作为每个联接表列的不同数组，因此任何人对此都有想法，然后请帮助我解决此问题

下面是我想以数组格式转换的python查询结果

[{'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)}]

我想要的结果是不同的数组，如下所示

    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] => 
        )
)

而sql查询正是我所提到的

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)

有人能帮我得到我附加的sql结果吗

Answer 1

这会满足你的要求

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