from db.db_conn import db_conn from sqlalchemy import Column, Integer, String, DATETIME, DOUBLE, BOOLEAN, TEXT from utils.date_utils import DateUtils class LoginInfo(db_conn.Base): """登录信息表""" __tablename__ = 'du_js_t_login_info' id = Column(Integer, primary_key=True, autoincrement=True, doc="主键") user_name = Column(String(50), doc="登录账号") password = Column(String(255), doc="登录密码") cookie = Column(String(500), doc="cookies") cookie_timestamp = Column(DATETIME, doc="cookie时间戳") region = Column(String(255), doc="区域") class RequestTaskSetting(db_conn.Base): """爬取任务配置参数表""" __tablename__ = 'du_js_t_request_task_setting' id = Column(Integer, primary_key=True, autoincrement=True, doc="主键") user = Column(String(50), doc="用户名称") update_time = Column(DATETIME, doc="配置更新时间") region = Column(String(50), doc="区县") version = Column(String(10), doc="版本号") request_start_time = Column(DATETIME, doc="数据爬取开始时间") request_end_time = Column(DATETIME, doc="数据爬取结束时间") request_interval_seconds = Column(Integer, doc="在爬取任务开始结束时间内，爬取的频率") request_range_hour = Column(Integer, doc="一次数据爬取任务中，按总时长分段执行的每段时间长度") request_range_interval_seconds = Column(Integer, doc="一次数据爬取任务中，分段执行时的每段的间隔时间") request_fail_wait_seconds = Column(Integer, doc="爬取任务异常时再次尝试间隔") request_retry_times = Column(Integer, doc="爬取失败时再次尝试的总次数") request_cookie_valid_duration = Column(Integer, doc="cookie存储有效时长") statics_daily_time = Column(DATETIME, doc="") statics_monthly_time = Column(DATETIME, doc="") login_fail_wait_seconds = Column(Integer, doc="单次登陆失败重新尝试等待时间") login_retry_times = Column(Integer, doc="登陆失败连续尝试次数") task_retry_wait_seconds = Column(Integer, doc="整个爬取任务重新开始尝试等待时间") first_request_offset_days = Column(Integer, doc="程序首次执行, 站点数据最新时刻表为空时, 数据获取开始时间相对当日往前偏移的天数") class DustDataInfo(db_conn.Base): """监测数据表""" __tablename__ = 'ja_t_dust_site_data_info' id = Column(Integer, primary_key=True, autoincrement=True, doc="主键") mn_code = Column(String(255), doc="站点编号") dust_value = Column(DOUBLE, doc="扬尘监测值") noise_value = Column(DOUBLE, doc="噪声监测值") lst = Column(DATETIME, doc="监测时间") quality = Column(String(10), doc="监测质量") grade = Column(Integer, doc="监测质量") flag = Column(String(10), doc="状态标识") def __init__(self, json_obj) -> None: """ Args: json_obj: { "longitude": "121.32", "latitude": "30.840834", "Address": "西新泾黄汶泾", "DustValue": 0.207, "Grade": 1, "GroupID": 16, "GroupName": "金山区", "LST": "2023-10-16 12:00:00", "LST1": "1697457600", "Name": "大信.中信海直华东无人机总部基地项目——1号点", "MNCode": "SHXH0JS0100306", "NoiseValue": 59.4, "ProjectID": 45879, "Quality": "好", "SName": "上海小慧", "TypeName": "建筑工地", "flag": "N" } """ self.mn_code = json_obj.get('MNCode') self.dust_value = json_obj.get('DustValue') self.noise_value = json_obj.get('NoiseValue') self.lst = DateUtils.str_to_time(json_obj.get('LST')) self.quality = json_obj.get('Quality') self.grade = json_obj.get('Grade') self.flag = json_obj.get('flag') def json_to_cls(json_list) -> list: m = map(lambda x: DustDataInfo(x), json_list) return list(m) class SiteLatestTime(db_conn.Base): """站点数据最新时刻表""" __tablename__ = 'du_js_t_site_latest_time' mn_code = Column(String(50), primary_key=True, doc="站点编号") latest_time = Column(DATETIME, doc="最新时间") device_status = Column(Integer, doc="站点状态, 0代表上线;1代表停运;2代表下线") class DustSiteInfo(db_conn.Base): """站点基本信息表""" __tablename__ = 'ja_t_dust_site_info' id = Column(Integer, primary_key=True, autoincrement=True, doc="项目编号") mn_code = Column(String(50), doc="设备编号") address = Column(String(255), doc="安装地址") name = Column(String(255), doc="站点名称") code = Column(String(50), doc="") begin_date = Column(DATETIME, doc="") duty_company = Column(String(255), doc="") duty_company_id = Column(String(50), doc="") end_date = Column(DATETIME, doc="") engineering_stage = Column(String(50), doc="") group_id = Column(String(10), doc="") group_name = Column(String(50), doc="") is_online = Column(String(10), doc="") is_trouble = Column(String(10), doc="") jhpt_update_time = Column(String(50), doc="") kindex = Column(String(50), doc="") latitude = Column(String(50), doc="") linkman = Column(String(50), doc="") longitude = Column(String(50), doc="") phone = Column(String(50), doc="") province = Column(String(20), doc="") ring_id = Column(String(20), doc="") ring_name = Column(String(50), doc="") type_id = Column(String(20), doc="") typename = Column(String(50), doc="") stop_time = Column(DATETIME, doc="") active = Column(String(10), doc="") trouble_num = Column(Integer, doc="") insert_time = Column(DATETIME, doc="") stage_begin_date = Column(DATETIME, doc="") tsp = Column(DOUBLE, doc="") union_type_id = Column(String(20), doc="") wall_height = Column(String(20), doc="") ywsj_date = Column(DATETIME, doc="") build_area = Column(String(20), doc="") construction_unit = Column(String(255), doc="") control_level = Column(String(50), doc="") developers = Column(String(100), doc="") do_time = Column(DATETIME, doc="") has_monitor = Column(String(20), doc="") data_time = Column(DATETIME, doc="") engineering_stage_code = Column(String(50), doc="") equipment_code = Column(String(50), doc="") floor_area = Column(String(20), doc="") jhpt_delete = Column(String(10), doc="") noise_region = Column(String(255), doc="") responsible = Column(String(50), doc="") def __init__(self, json_obj) -> None: """ Args: json_obj: { "Active": 1, "Address": "上海市金山区亭林镇周栅村荡新7组1056号", "Code": "HMHB00087", "DustValue": 0.065, "DutyCompany": "上海泓旻环保科技有限公司", "DutyCompanyID": 25, "GroupID": 16, "GroupName": "金山区", "ID": 41024, "IsOnline": 1, "Name": "上海阜阜建材有限公司", "IsTrouble": 0, "LST": "2023-10-27 08:15:00", "RingName": "外环外", "MNCode": "HMHB0JS0300084", "TypeName": "码头", "TypeID": 3, "NoiseValue": "68.9", "RingID": 4 } """ self.update(json_obj) def update(self, json_obj): self.id = json_obj.get('ID') self.mn_code = json_obj.get('MNCode') self.address = json_obj.get('Address') self.name = json_obj.get('Name') self.code = json_obj.get('Code') self.begin_date = json_obj.get('BeginDate') self.duty_company = json_obj.get('DutyCompany') self.duty_company_id = json_obj.get('DutyCompanyID') self.end_date = json_obj.get('EndDate') self.engineering_stage = json_obj.get('EngineeringStage') self.group_id = json_obj.get('GroupID') self.group_name = json_obj.get('GroupName') self.is_online = json_obj.get('IsOnline') self.is_trouble = json_obj.get('IsTrouble') # self.jhpt_update_time = json_obj.get('') self.kindex = json_obj.get('KIndex') # self.latitude = json_obj.get('') self.linkman = json_obj.get('Linkman') # self.longitude = json_obj.get('') self.phone = json_obj.get('Phone') self.province = "上海市" self.ring_id = json_obj.get('RingID') self.ring_name = json_obj.get('RingName') self.type_id = json_obj.get('TypeID') self.typename = json_obj.get('TypeName') # self.stop_time = json_obj.get('') self.active = json_obj.get('Active') self.trouble_num = json_obj.get('TroubleNum') # self.insert_time = json_obj.get('') # self.stage_begin_date = json_obj.get('') # self.tsp = json_obj.get('') # self.union_type_id = json_obj.get('') # self.wall_height = json_obj.get('') # self.ywsj_date = json_obj.get('') # self.build_area = json_obj.get('') # self.construction_unit = json_obj.get('') # self.control_level = json_obj.get('') # self.developers = json_obj.get('') # self.do_time = json_obj.get('') # self.has_monitor = json_obj.get('') # self.data_time = json_obj.get('') # self.engineering_stage_code = json_obj.get('') # self.equipment_code = json_obj.get('') # self.floor_area = json_obj.get('') # self.jhpt_delete = json_obj.get('') # self.noise_region = json_obj.get('') # self.responsible = json_obj.get('') class RequestTask(db_conn.Base): """爬取数据任务表""" __tablename__ = 'du_js_t_request_task' id = Column(Integer, primary_key=True, autoincrement=True, doc="任务编号") start_time = Column(DATETIME, doc="") end_time = Column(DATETIME, doc="") running_status = Column(BOOLEAN, doc="") success = Column(BOOLEAN, doc="") def __init__(self, id, start_time) -> None: self.id = id self.start_time = start_time self.running_status = True self.success = False class RequestTaskLog(db_conn.Base): """爬取数据任务日志表""" __tablename__ = 'du_js_t_request_task_log' id = Column(Integer, primary_key=True, autoincrement=True, doc="日志编号") request_id = Column(Integer, doc="任务编号") create_time = Column(DATETIME, doc="") log_type = Column(String(20), doc="") log = Column(TEXT, doc="")