大数据清洗二之增加行政编码和规范到去区或县

我因为准备考研，所有就算抽空整整建民老师的大数据作业，今天在选修课上把第二阶段的规范到区和县还有增加行政区域编码。

代码如下：

import json
import pymysql
import pandas as pd
import requests
from sqlalchemy import create_engine

# 连接MySQL数据库
conn = pymysql.connect(host='localhost', user='root', password='你的密码', db='你的数据库', charset='utf8')

# 编写SQL查询语句
sql = "SELECT * FROM table3 "

# 使用pandas读取数据
df = pd.read_sql(sql, conn)

new_addresses = []

# 更改循环变量名，避免与 DataFrame 对象名冲突
for i, address in enumerate(df['单位']):
    if address:
        url = 'https://restapi.amap.com/v3/geocode/geo?address=' + address + '&output=JOSON&key=你的key'
        request = requests.get(url).text
        try:
            result = json.loads(request)
        except json.JSONDecodeError as e:
            new_address=address
        if result['status'] == "1":
            adcode = result['geocodes'][0]['adcode']
            sql2 = "UPDATE table3 SET 行政区划编码 = '%s' where 单位 = '%s' " % (adcode, address)
            # 执行数据库插入
            cursor = conn.cursor()
            cursor.execute(sql2)
            conn.commit()
            if result['geocodes'][0]['district']:
                new_address = str(result['geocodes'][0]['province']) + str(result['geocodes'][0]['city']) + str(result['geocodes'][0]['district'])
            else:
                if result['geocodes'][0]['city']:
                    new_address = result['geocodes'][0]['province'] + result['geocodes'][0]['city']
                else:
                    new_address = result['geocodes'][0]['province']
        else:
            new_address = address
    else:
        new_address = ""
        address = ""
    if new_address != address:
        sql1 = "UPDATE table3 SET 单位 = '%s' WHERE 单位 = '%s'" % (new_address, address)
        #向table3插入行政区划编码
        # 使用cursor执行SQL语句
        cursor = conn.cursor()
        cursor.execute(sql1)
        conn.commit()
        cursor.close()
    print(address + "---->" + new_address + " --" + str(i))

conn.close()

同理其他表也是这样操作，然后再合并。