python-根据左右值获取城市区域编码

 

 

def get_adcode(city):
    """
    根据城市获取区域编码
    :param city:
    :return:
    """
    db = dbTool.conDB(dbTool.MYSQLDB, dbTool.MYSQLDBNAME)
    sql = "SELECT adcode,dispname,lft,rgt,dep FROM citycode_rela WHERE dispname LIKE '"+city+"%'"
    result = list(db.query(sql))
    if not result:
        return result
    else:
        adlist = []
        for addict in result:
            param1 = {'adcode': addict['adcode'], 'dispname': addict['dispname'], 'lft': addict['lft'], 'rgt': addict['rgt']}
            if addict['rgt'] - addict['lft'] > 1: #根据左右值判断是否是最后一级
                sql1 = ""
                
                if addict['dep'] == 3: #判断是否是第三级,如何济南市 淄博市,用来取省份
                    sql1 += " SELECT $adcode AS adcode,$dispname AS dispname,dispname AS supername " \
                            "FROM citycode_rela WHERE lft<$lft AND rgt>$rgt AND dep=2 UNION"

                #取出该级下的所有下一级
                sql1 += " SELECT adcode,dispname,$dispname AS supername FROM citycode_rela WHERE lft>$lft AND rgt<$rgt "
                adlist.extend(list(db.query(sql1, vars=param1)))
                
            else: # 最后一级
                
                #用来取省份
                sql2 = "SELECT dispname AS supername,$adcode AS adcode,$dispname AS dispname " \
                       "FROM citycode_rela WHERE lft<$lft AND rgt>$rgt AND dep=2"
                
                adlist.extend(list(db.query(sql2, vars=param1)))
        return sorted(adlist, key=lambda x: x['adcode'], reverse=False)

 

res = get_adcode('山东')
for item in res:
    print ''.join([item['supername'],'-',item['dispname'],'-',str(item['adcode'])])

结果：　　

山东省-济南市-370100
山东省-历下区-370102
山东省-市中区-370103
山东省-槐荫区-370104
山东省-天桥区-370105
山东省-历城区-370112
山东省-长清区-370113

.....

 

res = get_adcode('济南')
for item in res:
    print ''.join([item['supername'],'-',item['dispname'],'-',str(item['adcode'])])

结果：

山东省-济南市-370100
济南市-历下区-370102
济南市-市中区-370103
济南市-槐荫区-370104
济南市-天桥区-370105
济南市-历城区-370112

 

res = get_adcode('历下区')
for item in res:
    print ''.join([item['supername'],'-',item['dispname'],'-',str(item['adcode'])])

结果：

山东省-历下区-370102

 

参考数据：http://www.stats.gov.cn/tjsj/tjbz/xzqhdm/201401/t20140116_501070.html