update_loc.py

#!/usr/bin/python3
'''
        作用：更新点位

'''

import os
import sys
import re
import time
import pymysql
import logging
import pandas as pd
import requests
import signal
from clickhouse_driver import Client
from multiprocessing import Process
#class sub_process(Process):
#    def __init__(self,localtion_id,img_url,cursor):
#        super().__init__()
#        self.localtion_id = localtion_id
#        self.cursor = cursor
#    def run(self):
#        add_loc(self.localtion_id,img_url,self.cursor)
def add_loc(localtion_id,img_url,cursor):
    #print(img_url)
    comand = 'python3 /home/mypy/search_url_get_msg.py ' + img_url
    result_cmd = os.popen(comand)
    res = result_cmd.read()
    j =re.search("{.*\n",res)
    print(res)
    if not j:
    return 
    j_dict = eval(j.group())
    pn = j_dict['NAME_OF_PASSED_ROAD']
    pro_id = j_dict['TOLLGATE_ID']
    lon = ''
    lat = ''
    if 'LONGITUDE' in j_dict.keys():
        lon = j_dict['LONGITUDE']
    if 'LATITUDE' in j_dict.keys():
        lat = j_dict['LATITUDE']
    region_code = j_dict['TOLLGATE_ID'][:6]
    if lon or lat:
        sql = "insert into location(id,pointname,PROVIDER,latitude,longitude,regioncode)  values({},'{}','{}',{},{},{});".format(int(localtion_id),pn,pro_id,float(lat),float(lon),int(region_code))
    else:
        sql = "insert into location(id,pointname,PROVIDER,regioncode)  values({},'{}','{}',{});".format(int(localtion_id),pn,pro_id,int(region_code))
    try:
        cursor.execute(sql)
    except:
        logging.info(sql)
        logging.error("mysql语句执行错误！")
        sys.exit(1)
    cursor.close()

def handler(signum,frame):
        raise AssertionError

if __name__ == '__main__':
    logging.basicConfig(filename=os.path.dirname(os.path.abspath(__file__)) + "/update_loc.log",level=logging.DEBUG)
    try:
        cursor = Client(host='68.109.211.36', port=9001, password='Yisa_fs_2021')
    except:
        logging.info("lightingdb连接失败！")
        sys.exit(1)
    time2 = '2022-07-26 06:00:00'
    time3 = '2022-07-26 23:59:59'
    sql = "select license_plate2,xgbdp,ambdp,plate_type_id2,location_id,capture_time,image_url1,location_id  from yisa_oe.vehicle_all where toDateTime(capture_time) >= '"+ time2+"' and toDateTime(capture_time) <= '"+ time3+"'"
    try:
        results = cursor.execute(sql)
    except:
        logging.error("lighting语句执行错误！")
        sys.exit(1)
    try:
        mysql_db = pymysql.connect(host='68.109.211.67',user='yisa_oe',password='Yisa_fs_2021',database='yisa_oe')
    except:
        logging.info("mysql连接失败！")
        sys.exit(1)
    data_list = [] #查询某天数据的行
    not_pn_c = 0
    def handler(signum,frame):
        raise AssertionError
    for row in results:
        row_list = list(row)
        localtion_id = int(row_list[7])
        img_url = row_list[6]
        cursor = mysql_db.cursor()
        try:
            sql = "select  id,pointname,PROVIDER from location where id = {} ;".format(localtion_id)
        except:
            logging.error("mysql语句执行错误！")
            sys.exit(1)
        cursor.execute(sql)
        result = cursor.fetchall()
        if not result:
            #print(result)
            not_pn_c = not_pn_c + 1
            print("add_loc loc_id: {},img_url: {}".format(localtion_id,img_url,cursor))
            logging.info("add_loc loc_id: {},img_url: {}".format(localtion_id,img_url,cursor))
            try:
                signal.signal(signal.SIGALRM,handler)
                signal.alarm(360)
                add_loc(localtion_id,img_url,cursor)
            except AsserttionError:
                print("add timeout!")
                logging.info("add timeout!")
            finally:
                signal.alarm(0)
    print('total :{}'.format(not_pn_c))