python-fastapi-mm-zhcs-yj-api/DataGovernance.py

# -*- coding: utf-8 -*-

from config import settings
from common.BigDataCenterAPI import *
from utils import *
import pymysql

def jwd_to_area(location):
    url = 'https://19.15.75.180:8581/GatewayMsg/http/api/proxy/invoke'
    service_code = 'YZT1685418808667'
    passid = 'C90-44004428'
    passtoken = 'f539f616b2834160bc47fda5f298512c'
    signTime = str(GetTime() // 1000)
    nonce = GetNonce(5)
    sign = GetSign(signTime, nonce, passtoken)
    headers = {
        'Content-Type': 'application/json',
        'x-tif-signature': sign,
        'x-tif-timestamp': signTime,
        'x-tif-nonce': nonce,
        'x-tif-paasid': passid,
        'x-tif-serviceId': service_code
    }
    response = requests.post(url=url, headers=headers, json=location, verify=False)
    if response.status_code == 200:
        try:
            data = response.json()['data'][0]
            district = data['district']
            township = data['township']
            return district,township
        except:
            return None,None
def put_area(table : str,longitude='longitude',latitude='latitude'):
    conn=None
    cur = None
    try:
        conn = pymysql.connect(host=settings.MYSQL_SERVER,
                               user=settings.MYSQL_USER,
                               password=settings.MYSQL_PASSWORD,
                               database=settings.MYSQL_DB_NAME,
                               port=settings.MYSQL_PORT,
                               charset='utf8mb4')
        cur = conn.cursor()
        sql = f'select id,{longitude},{latitude} from {table}'
        cur.execute(sql)
        resu = cur.fetchall()
        for info in resu:
            id_1 = info[0]
            location = [{"x":info[1],"y":info[2]}]
            area,township = jwd_to_area(location)
            update_sql = f"UPDATE {table} SET area = %s WHERE id = %s"
            cur.execute(update_sql, (area, id_1))
        conn.commit()  # 提交事务
    except pymysql.MySQLError as e:
        print(f"Error: {e}")
    finally:
        if cur:
            cur.close()
        if conn:
            conn.close()

def put_township(table : str,longitude='longitude',latitude='latitude'):
    conn=None
    cur = None
    try:
        conn = pymysql.connect(host=settings.MYSQL_SERVER,
                               user=settings.MYSQL_USER,
                               password=settings.MYSQL_PASSWORD,
                               database=settings.MYSQL_DB_NAME,
                               port=settings.MYSQL_PORT,
                               charset='utf8mb4')
        cur = conn.cursor()
        sql = f'select id,{longitude},{latitude} from {table}'
        cur.execute(sql)
        resu = cur.fetchall()
        for info in resu:
            id_1 = info[0]
            location = [{"x":info[1],"y":info[2]}]
            area,township = jwd_to_area(location)
            update_sql = f"UPDATE {table} SET township = %s WHERE id = %s"
            cur.execute(update_sql, (township, id_1))
        conn.commit()  # 提交事务
    except pymysql.MySQLError as e:
        print(f"Error: {e}")
    finally:
        if cur:
            cur.close()
        if conn:
            conn.close()
def put_uuid(table,column):
    conn = None
    cur = None
    try:
        conn = pymysql.connect(host=settings.MYSQL_SERVER,
                               user=settings.MYSQL_USER,
                               password=settings.MYSQL_PASSWORD,
                               database=settings.MYSQL_DB_NAME,
                               port=settings.MYSQL_PORT,
                               charset='utf8mb4')
        cur = conn.cursor()
        sql = f'select distinct {column} from {table}'
        cur.execute(sql)
        resu = cur.fetchall()
        for info in resu:
            column_data = info[0]
            uid = new_guid()
            update_sql = f"UPDATE {table} SET uid = %s  WHERE {column} = %s"
            cur.execute(update_sql, (uid,column_data))
        conn.commit()  # 提交事务
    except pymysql.MySQLError as e:
        print(f"Error: {e}")
    finally:
        if cur:
            cur.close()
        if conn:
            conn.close()


#put_uuid('rescue_materia','management_unit')

#put_area('emergency_expert')
# put_area('mid_waterlogged_roads','lng','lat')
# put_area('mid_school')
# put_area('mid_hospital')
# put_area('tower_list')
# put_area('govdata_real_time_address')
put_township('govdata_real_time_address')