PDD/pdd1/省市回填脚本/Pdd_add_province.py

import os

def get_mysql():
    """
    建立并返回一个到数据库的连接对象
    """
    import pymysql
    # return pymysql.connect(
    #     host='localhost',  # 修改后的主机
    #     port=3306,  # 添加端口号
    #     user='root',  # 修改后的用户名
    #     password='dfwy2025',  # 修改后的密码
    #     db='drug_data',  # 修改后的数据库名
    #     charset='utf8mb4'
    # )
    return pymysql.connect(
            host='47.119.164.65',  # 修改后的主机
            port=3306,  # 添加端口号
            user='test_c',  # 修改后的用户名
            password='Dfwy@2025',  # 修改后的密码
            db='test2',  # 修改后的数据库名
            charset='utf8mb4'
        )

def main():
    # 连接数据库
    conn = get_mysql()

    # 创建游标对象
    cur = conn.cursor()

    query = f"""
    SELECT id, business_license_address
    FROM pdd_shop_info_middle
    WHERE province = '' AND business_license_address != ''
    """

    cur.execute(query)

    results = cur.fetchall()

    print(f"results={results}")

    # 逐条处理每条数据

    for row in results:
        addr_id = row[0]          
        address = row[1]          
        print(f"address={address}")

        # address = '湖南省长沙市岳麓区西湖街道金星中路398号玛依拉山庄6栋113号'

        if '北京市' in address:
            province = '北京'
            city     = '北京'
        elif '天津市' in address:
            province = '天津'
            city     = '天津'
        elif '上海市' in address:
            province = '上海'
            city     = '上海'
        elif '重庆市' in address:
            province = '重庆'
            city     = '重庆'
        else :
            if '省' in address:
                province, _, rest_address = address.partition('省')
                #print('province ->', province)  
                # print('rest_address     ->', rest_address)    

                if '市' in rest_address:
                    city, _, rest_rest_address = rest_address.partition('市')
                    # print('rest_rest_address     ->', rest_rest_address)  
                else:
                    city = ''

            elif "县" in address:
                # 1. 提取出县的名字，比如 "海丰"
                county, _, _ = address.partition('县')

                # 2. 针对特殊的县进行处理
                if '海丰' in county:
                    province = '广东'
                    city = '汕尾'
                elif '灵川' in county:
                    province = '广西'
                    city = '桂林'
                elif '丰顺' in county:
                    province='广东'
                    city = '梅州'
                else:
                    # 如果是其他不认识的县，先记录名字，省份设为空
                    province = ''
                    city = county

            elif address.startswith('许昌'):
                province = '河南'
                city = '许昌'

            elif '新区' in address:
                # 1. 提取新区前的名字
                area, _, _ = address.partition('新区')

                # 2. 针对性判断
                if '西咸' in area:
                    province = '陕西'
                    city = '西安'

            elif '自由贸易试验区' in address:
                # 1. 提取括号里的省份名 (例如从 "中国（辽宁）" 中提取 "辽宁")
                if '（' in address and '）' in address:
                    _, _, temp = address.partition("（")
                    province, _, _ = temp.partition("）")
                else:
                    province = ''

                #中国（辽宁）自由贸易试验区营口片区青花大街西107号，即科技城孵化器二期26号楼301房41号工位
                if '片区' in address:
                    _, _, part_after_test = address.partition('试验区')
                    city, _, _ = part_after_test.partition("片区")
                else:
                    city = ''

                if city == '营口片区':  # 防止切得不干净
                    city = '营口'



            else:
                province = ''
                if '市' in address:
                    city, _, rest_address = address.partition('市')
                    #print('city ->', city)  
                    # print('rest_address     ->', rest_address)   
                    if (city == '深圳' or city == '广州' or city == '普宁' or city == '中山' or 
                        city == '惠州' or city == '茂名' or city == '韶关' or city == '佛山' or 
                        city == '汕头' or city == '东莞' or city == '揭阳' or city == '化州' or 
                        city == '清远' or city == '河源' or city == '湛江' or city == '英德' or 
                        city == '兴宁' or city == '开平' or city == '珠海' or city == '潮州' or
                        city == '四会' or city == '肇庆' or city == '阳春' or city == '阳江' or
                        city == '梅州' or city == '台山' or city == '江门' or city == '陆丰' or
                        city == '汕尾' or city == '高州' or city == '雷州' or city == '南雄'):
                        province = '广东'  
                    elif (city == '合肥' or city == '淮南' or city == '天长' or city == '滁州' or
                          city == '芜湖' or city == '蚌埠' or city == '马鞍山' or city == '淮北' or 
                          city == '铜陵' or city == '安庆' or city == '黄山' or city == '阜阳' or 
                          city == '宿州' or city == '六安' or city == '亳州' or city == '池州' or 
                          city == '宣城' or city == '广德' or city == '桐城' or city == '界首'):
                        province = '安徽'
                    elif (city == '成都' or city == '阆中' or city == '内江' or city == '彭州' or
                          city == '南充' or city == '自贡' or city == '攀枝花' or city == '泸州' or 
                          city == '德阳' or city == '绵阳' or city == '广元' or city == '遂宁' or 
                          city == '乐山' or city == '眉山' or city == '宜宾' or city == '广安' or 
                          city == '达州' or city == '雅安' or city == '巴中' or city == '资阳' or
                          city == '江油'):
                        province = '四川' 
                    elif (city == '盖州' or city == '营口' or city == '沈阳' or city == '大连' or
                          city == '鞍山' or city == '抚顺' or city == '本溪' or city == '丹东' or
                          city == '锦州' or city == '阜新' or city == '辽阳' or city == '盘锦' or
                          city == '铁岭' or city == '朝阳' or city == '葫芦岛' or city == '海城' or
                          city == '灯塔'):
                        province = '辽宁'
                    elif (city == '南京' or city == '无锡' or city == '苏州' or city == '徐州' or 
                          city == '宿迁' or city == '淮安' or city == '泰州' or city == '扬州' or
                          city == '盐城' or city == '连云港' or city == '丹阳' or city == '镇江' or 
                          city == '东台' or city == '江阴' or city == '靖江' or city == '昆山' or 
                          city == '启东' or city == '南通' or city == '扬中' or city == '宜兴' or 
                          city == '张家港' or city == '常州' or city == '兴化' or city== '太仓' or
                          city == '泰兴' or city == '新沂' or city == '海安' or city == '溧阳' or
                          city == '邳州' or city == '常熟'):
                        province = '江苏'
                    elif (city == '济南' or city == '滕州' or city == '枣庄' or city == '青岛' or
                          city == '淄博' or city == '东营' or city == '烟台' or city == '潍坊' or
                          city == '济宁' or city == '泰安' or city == '威海' or city == '日照' or
                          city == '临沂' or city == '德州' or city == '聊城' or city == '滨州' or
                          city == '菏泽' or city == '曲阜'):
                        province = '山东' 
                    elif (city == '杭州' or city == '宁波' or city == '温州' or city == '嘉兴' or
                          city == '湖州' or city == '绍兴' or city == '金华' or city == '衢州' or
                          city == '舟山' or city == '台州' or city == '丽水' or city == '义乌' or
                          city == '桐乡' or city == '余姚' or city == '慈溪'):
                        province = '浙江' 
                    elif (city == '长沙' or city == '衡阳' or city == '吉首' or city == '株洲' or
                          city == '湘潭' or city == '邵阳' or city == '岳阳' or city == '常德' or
                          city == '张家界' or city == '益阳' or city == '郴州' or city == '永州' or
                          city == '怀化' or city == '娄底' or city == '冷水江'):
                        province = '湖南'
                    elif (city == '厦门' or city == '福州' or city == '晋江' or city == '泉州' or 
                          city == '永安' or city == '三明' or city == '石狮' or city == '福安' or 
                          city == '宁德' or city == '莆田' or city == '漳州' or city == '南平' or 
                          city == '龙岩' or city == '福清' or city == '建瓯' or city == '南安'):
                        province = '福建'
                    elif (city == '郑州' or city == '禹州' or city == '南阳' or city == '永城' or 
                          city == '洛阳' or city == '焦作' or city == '济源' or city == '汝州' or
                          city == '平顶山' or city == '商丘' or city == '新密' or city == '禹州' or 
                          city == '许昌' or city == '开封' or city == '安阳' or city == '鹤壁' or
                          city == '新乡' or city == '濮阳' or city == '漯河' or city == '三门峡' or
                          city == '周口' or city == '驻马店' or city == '卫辉' or city == '灵宝' or
                          city == '辉县' or city == '项城' or city == '林州' or city == '荥阳' or
                          city == '巩义'):
                        province = '河南'
                    elif (city == '石家庄' or city == '唐山' or city == '秦皇岛' or city == '邯郸' or 
                          city == '廊坊' or city == '邢台' or city == '保定' or city == '张家口' or
                          city == '承德' or city == '沧州' or city == '衡水' or city == '南宫' or
                          city == '新乐' or city == '河间' or city == '辛集' or city == '迁安' or
                          city == '定州'):
                        province = '河北'
                    elif (city == '太原' or city == '长治' or city == '大同' or city == '阳泉' or 
                          city == '晋城' or city == '朔州' or city == '晋中' or city == '运城' or
                          city == '忻州' or city == '临汾' or city == '吕梁' or city == '汾阳' or
                          city == '介休'):
                        province = '山西'
                    elif (city == '呼和浩特' or city == '包头' or city == '乌海' or city == '赤峰' or
                          city == '通辽' or city == '鄂尔多斯' or city == '呼伦贝尔' or city == '巴彦淖尔' or
                          city == '乌兰察布'):
                        province = '内蒙古'
                    elif (city == '武汉' or city == '潜江' or city == '当阳' or city == '大冶' or
                          city == '黄石' or city == '当阳' or city == '宜昌' or city == '汉川' or 
                          city == '孝感' or city == '十堰' or city == '襄阳' or city == '鄂州' or
                          city == '荆门' or city == '荆州' or city == '黄冈' or city == '咸宁' or
                          city == '随州' or city == '恩施' or city == '利川' or city == '仙桃' or
                          city == '枣阳' or city == '潜江' or city == '应城'):
                        province = '湖北'  
                    elif (city == '西安' or city == '铜川' or city == '宝鸡' or city == '咸阳' or
                          city == '渭南' or city == '延安' or city == '汉中' or city == '榆林' or
                          city == '安康' or city == '商洛'):
                        province = '陕西'  
                    elif (city == '南昌' or city == '景德镇' or city == '萍乡' or city == '九江' or
                          city == '新余' or city == '鹰潭' or city == '赣州' or city == '吉安' or
                          city == '宜春' or city == '抚州' or city == '上饶' or city == '樟树' or
                          city == '瑞金'):
                        province = '江西'
                    elif (city == '哈尔滨' or city == '齐齐哈尔' or city == '鸡西' or city == '鹤岗' or
                          city == '双鸭山' or city == '大庆' or city == '伊春' or city == '佳木斯' or
                          city == '七台河' or city == '牡丹江' or city == '黑河' or city == '绥化' or
                          city == '海林'):
                        province = '黑龙江'
                    elif (city == '长春' or city == '梅河口' or city == '通化' or city == '图们' or
                          city == '吉林' or city == '四平' or city == '辽源' or city == '白山' or
                          city == '松原' or city == '白城' or city == '延吉'):
                        province = '吉林' 
                    elif (city == '弥勒' or city == '昆明' or city == '曲靖' or city == '玉溪' or
                          city == '保山' or city == '昭通' or city == '丽江' or city == '普洱' or
                          city == '临沧' or city == '大理' or city == '楚雄'):
                        province = '云南'
                    elif (city == '贵阳' or city == '六盘水' or city == '遵义' or city == '安顺' or
                          city == '毕节' or city == '铜仁'):
                        province = '贵州'
                    elif (city == '乌鲁木齐' or city == '克拉玛依' or city == '吐鲁番' or city == '哈密'):
                        province = '新疆'
                    elif (city == '兰州' or city == '嘉峪关' or city == '金昌' or city == '白银' or
                          city == '天水' or city == '武威' or city == '张掖' or city == '平凉' or
                          city == '酒泉' or city == '庆阳' or city == '定西' or city == '陇南'):
                        province = '甘肃'
                    elif city == '西宁' or city == '海东':
                        province = '青海'
                    elif (city == '银川' or city == '石嘴山' or city == '吴忠' or city == '固原' or city == '中卫'):
                        province = '宁夏'
                    elif (city == '南宁' or city == '玉林' or city == '柳州' or city == '防城港' or
                          city == '桂平' or city == '贵港' or city == '桂林' or city == '梧州' or
                          city == '北海' or city == '钦州' or city == '百色' or city == '贺州' or
                          city == '河池' or city == '来宾' or city == '崇左' or city == '岑溪' or
                          city == '北流'):
                        province = '广西'                                                        
    
                else :
                    city = ''   

        #对部分县级市或代管市进行城市值的变更：
        
        if province == '广东' and city == '普宁':
            city = '揭阳'
        elif province == '福建' and city == '晋江':
            city = '泉州'
        elif province == '福建' and city == '永安':
            city = '三明'
        elif province == '广东' and city == '四会':
            city = '肇庆'
        elif province == '广东' and city == '英德':
            city = '清远'
        elif province == '广西' and city == '桂平':
            city = '贵港'
        elif province == '河南' and city == '济源':
            city = '河南省直辖县'
        elif province == '河南' and city == '汝州':
            city = '平顶山'
        elif province == '河南' and city == '永城':
            city = '商丘'
        elif province == '湖北' and city == '大冶':
            city = '黄石'
        elif province == '湖北' and city == '当阳':
            city = '宜昌'
        elif province == '湖北' and city == '汉川':
            city = '孝感'
        elif province == '吉林' and city == '梅河口':
            city = '通化'
        elif province == '江苏' and city == '丹阳':
            city = '镇江'
        elif province == '江苏' and city == '东台':
            city = '盐城'
        elif province == '江苏' and city == '江阴':
            city = '无锡'
        elif province == '江苏' and city == '靖江':
            city = '泰州'
        elif province == '江苏' and city == '昆山':
            city = '苏州'
        elif province == '江苏' and city == '启东':
            city = '南通'
        elif province == '江苏' and city == '扬中':
            city = '镇江'
        elif province == '江苏' and city == '宜兴':
            city = '无锡'
        elif province == '江苏' and city == '张家港':
            city = '苏州'
        elif province == '辽宁' and city == '盖州':
            city = '营口'
        elif province == '山东' and city == '滕州':
            city = '枣庄'
        elif province == '云南' and city == '弥勒':
            city = '红河哈尼族彝族自治州'
        elif province == '河南' and city == '新密':
            city = '郑州'
        elif province == '河南' and city == '禹州':
            city = '许昌'
        elif province == '广东' and city == '化州':
            city = '茂名'
        elif province == '福建' and city == '石狮':
            city = '泉州'
        elif province == '湖南' and city == '吉首':
            city = '湘西土家族苗族自治州'
        elif province == '福建' and city == '福安':
            city = '宁德'
        elif province == '广东' and city == '阳春':
            city = '阳江' 
        elif province == '吉林' and city == '图们':
            city = '延边朝鲜族自治州' 
        elif province == '广东' and city == '兴宁':
            city = '梅州'
        elif province == '广东' and city == '台山':
            city = '江门'
        elif province == '广东' and city == '开平':
            city = '江门'
        elif province == '广东' and city == '陆丰':
            city = '汕尾'
        elif province == '广东' and city == '高州':
            city = '茂名'
        elif province == '安徽' and city == '天长':
            city = '滁州'
        elif province == '安徽' and city == '广德':
            city = '宣城'
        elif province == '安徽' and city == '桐城':
            city = '安庆'
        elif province == '安徽' and city == '界首':
            city = '阜阳'
        elif province == '四川' and city == '彭州':
            city = '成都'
        elif province == '四川' and city == '阆中':
            city = '南充'
        elif province == '辽宁' and city == '海城':
            city = '鞍山'
        elif province == '辽宁' and city == '灯塔':
            city = '辽阳'
        elif province == '江苏' and city == '兴化':
            city = '泰州'        
        elif province == '江苏' and city == '太仓':
            city = '苏州'
        elif province == '江苏' and city == '泰兴':
            city = '泰州' 
        elif province == '江苏' and city == '新沂':
            city = '徐州' 
        elif province == '江苏' and city == '海安':
            city = '南通' 
        elif province == '江苏' and city == '溧阳':
            city = '常州' 
        elif province == '江苏' and city == '邳州':
            city = '徐州' 
        elif province == '浙江' and city == '义乌':
            city = '金华' 
        elif province == '浙江' and city == '桐乡':
            city = '嘉兴' 
        elif province == '浙江' and city == '余姚':
            city = '宁波' 
        elif province == '河南' and city == '卫辉':
            city = '新乡' 
        elif province == '河南' and city == '灵宝':
            city = '三门峡' 
        elif province == '河南' and city == '辉县':
            city = '新乡' 
        elif province == '河南' and city == '项城':
            city = '周口' 
        elif province == '河南' and city == '林州':
            city = '安阳' 
        elif province == '河北' and city == '南宫':
            city = '邢台'
        elif province == '河北' and city == '新乐':
            city = '石家庄'
        elif province == '河北' and city == '河间':
            city = '沧州'
        elif province == '河北' and city == '辛集':
            city = '石家庄'
        elif province == '河北' and city == '迁安':
            city = '唐山'
        elif province == '山西' and city == '汾阳':
            city = '吕梁'
        elif province == '湖北' and city == '恩施':
            city = '恩施土家族苗族自治州' 
        elif province == '湖北' and city == '利川':
            city = '恩施土家族苗族自治州' 
        elif province == '湖北' and city == '仙桃':
            city = '湖北省直辖县' 
        elif province == '湖北' and city == '大治':
            city = '黄石'
        elif province == '湖北' and city == '枣阳':
            city = '襄阳'
        elif province == '湖北' and city == '潜江':
            city = '湖北省直辖县'
        elif province == '江西' and city == '樟树':
            city = '宜春'
        elif province == '江西' and city == '瑞金':
            city = '赣州'
        elif province == '吉林' and city == '延吉':
            city = '延边朝鲜族自治州'
        elif province == '云南' and city == '大理':
            city = '大理白族自治州'
        elif province == '云南' and city == '楚雄':
            city = '楚雄彝族自治州'
        elif province == '湖南' and city == '冷水江':
            city = '娄底'
        elif province == '河南' and city == '荥阳':
            city = '郑州'
        elif province == '广东' and city == '雷州':
            city = '湛江'
        elif province == '湖北' and city == '应城':
            city = '孝感'
        elif province == '山东' and city == '曲阜':
            city = '济宁'
        elif province == '广西' and city == '岑溪':
            city = '梧州'
        elif province == '山西' and city == '介休':
            city = '晋中'
        elif province == '广西' and city == '北流':
            city = '玉林'
        elif province == '河南' and city == '巩义':
            city = '郑州'
        elif province == '福建' and city == '福清':
            city = '福州'

        elif province == '广东' and city == '南雄':
            city = '韶关'
        elif province == '黑龙江' and city == '海林':
            city = '牡丹江'
        elif province == '河北' and city == '定州':
            city = '保定'
        elif province == '浙江' and city == '慈溪':
            city = '宁波'
        elif province == '福建' and city == '建瓯':
            city = '南平'
        elif province == '福建' and city == '南安':
            city = '泉州'
        elif province == '江苏' and city == '常熟':
            city = '苏州'
        elif province == '四川' and city == '江油':
            city = '绵阳'


        #将省市的值写入到数据表中
        update_sql = """
        UPDATE pdd_shop_info_middle
        SET province = %s,
            city     = %s
        WHERE id = %s
        """
        cur.execute(update_sql, (province, city, addr_id))
        conn.commit()




if __name__ == '__main__':
    main()