import pymysql
import requests
import json


db = pymysql.connect(
    # host='172.16.15.167',
    host='114.55.0.7',
    user='root',
    port=3306,
    password='yfkj@6020',
    database='bigdata_api',
    charset='utf8mb4'
)
cursor = db.cursor()

address_info_id_sql = """

    select cityid from address_info
"""

address_ids = cursor.execute(address_info_id_sql)
cityids = [i[0] for i in cursor.fetchall()]
print(len(cityids))

city_sql = """
    select id, cityZh, provinceZh, leaderZh, lat, lon from city
"""

city_ids = cursor.execute(city_sql)
ids = [(i[0])[2:] for i in cursor.fetchall()]
print(len(ids))
for i in ids:
    if i not in cityids:
        sql = """
            select id, cityZh, provinceZh, leaderZh, lat, lon from city where id = %s
        """
        cursor.execute(sql, ("CN" + id, ))
        c = cursor.fetchone()
        print(c)
        lat, lon = c[-2], c[-1]
        ret = requests.post("http://api.map.baidu.com/geocoder?location=%s,%s&coord_type=gcj02&output=json"%(lat,lon))
        ret_json = json.loads(ret.text)
        province, city, district = ret_json["result"]["addressComponent"]["province"], \
                                ret_json["result"]["addressComponent"]["city"], \
                                ret_json["result"]["addressComponent"]["district"]
        print(province, city, district)