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()