MT/mt1/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'
#     )

from config import Config

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


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

    # 创建游标对象
    cur = conn.cursor()
    ##jd_shop_info_copy1
    query = f"""
    SELECT id, business_license_address
    FROM mt_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 = ''
                
                #print('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 == '珠海'):
                        province = '广东'  
                    elif city == '济南':
                        province = '山东' 
                    elif (city == '成都' or city == '阆中' or city == '内江'):
                        province = '四川'  
                    elif city == '武汉' or city == '潜江'  or city == '当阳':
                        province = '湖北'
                    elif city == '合肥' or city == '淮南':
                        province = '安徽'
                    elif city == '太原' or city == '长治':
                        province = '山西'
                    elif city == '南宁' or city == '玉林' or city == '柳州' or city == '防城港':
                        province = '广西'
                    elif city == '长沙' or city == '衡阳':
                        province = '湖南'
                    elif (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 == '西安':
                        province = '陕西'
                    elif city == '贵阳':
                        province = '贵州'
                    elif city == '厦门':
                        province = '福建'
                    elif city == '邯郸' or city == '廊坊':
                        province = '河北'
                    elif city == '哈尔滨':
                        province = '黑龙江'
                    elif city == '长春':
                        province = '吉林'
                else :
                    city = ''   
        #将省市的值写入到数据表中  #jd_shop_info_copy1
        update_sql = """
        UPDATE mt_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()