import concurrent.futures import datetime import psycopg2 from pypinyin import pinyin, Style import traceback import openpyxl import msoffcrypto import io import requests import json import time import hmac import hashlib import base64 import urllib.parse from openpyxl import Workbook from Functions import utils from unitls.settings import DBServer, loginDB, flightDB4 host, port, user, password,online_host, online_port, online_user,online_password = DBServer() databaseloginDB=loginDB() databaseflightDB4=flightDB4() #headerLabel = utils.headerLabel #headerLabelKey=utils.headerLabelKey headerLabel = flightinfoLabel headerLabelKey=enflightifnoLabel taskType = utils.taskType taskSTS = utils.taskSTS flightSTS = utils.flightSTS flightSTSDic = utils.flightSTSDic class flightDB(): def __init__(self, host, port, user, password, database): self.conn = psycopg2.connect( host=host, port=port, user=user, password=password, database=database ) self.c = self.conn.cursor() def initTable(self, tableName:str,primarykey:str,primarykeyStr:str,keyDict:dict): try: keyStr = '' num = 1 for key in keyDict: if num !=len(keyDict): keyStr += '{} {},\n'.format(key,keyDict[key]) else: keyStr += '{} {}'.format(key, keyDict[key]) num +=1 curStr =""" create table if not exists {} ( {} {} , {} ) """.format(tableName, primarykey, primarykeyStr,keyStr) #print(curStr) self.c.execute(curStr) self.conn.commit() except Exception: #print(curStr) print(traceback.format_exc()) dingding_alert(traceback.format_exc()) def insertData(self, tableName:str, data:dict, *args): try: #curStr1 = 'insert into {} '.format(tableName) num = 1 curStr2 = '' for key in data: if num != len(data): curStr2+='{},'.format(key) else: curStr2+='{}'.format(key) num+=1 curStr3 = '' num = 1 for key in data: if num != len(data): curStr3+="'{}',".format(data[key]) else: curStr3+="'{}'".format(data[key]) num+=1 curStr = """ insert into {} ({}) values ({}) """.format(tableName, curStr2, curStr3) #print(curStr) self.c.execute(curStr) if args==(): self.conn.commit() except Exception: print(traceback.format_exc()) #print(curStr) #dingding_alert(traceback.format_exc()) def lazyInsertData(self,tableName:str, data:dict): self.insertData(tableName, data, 'lazy') def lazydeleteTable(self,tablename:str): curStr = """delete from {}""".format(tablename) self.c.execute(curStr) def lazyInsertData2(self,tableName:str, curStr2, curStr3): curStr = """insert into {} ({})values {}""".format(tableName, curStr2, curStr3) self.c.execute(curStr) #print(curStr) def lazyInsertData3(self,curStr1, curStr2, curStr3): curStr = """insert into display (ID, A, B)values (%s,'%s','%s')"""%(curStr1, curStr2, curStr3) self.c.execute(curStr) #print(curStr) def FunctionCommit(self): self.conn.commit() def FunctionRollback(self): self.conn.rollback() def deleteTable(self, tablename:str, *condition:str): try: #print(condition) if condition !=(): curStr = """ delete from {} where {} """.format(tablename, condition[0]) else: curStr = """ delete from {} """.format(tablename) self.c.execute(curStr) self.conn.commit() return "ok" except Exception: dingding_alert(traceback.format_exc()) dingding_alert(curStr) return "fail" def copyTable(self, oldtablename:str, newTablename:str): try: curStr = """ drop table {} """.format(newTablename) self.c.execute(curStr) self.conn.commit() except Exception: dingding_alert(traceback.format_exc()) dingding_alert(curStr) def sortTable(self, tablename:str, tableKey:str, fn): try: curStr = """ select * from {} order by {} {} """.format(tablename, tableKey, fn) #print(curStr) self.c.execute(curStr) return self.c.fetchall() except Exception: dingding_alert(traceback.format_exc()) def sort_queryTable(self, findkey:str,tablename:str, condition:str,tableKey:str, fn): try: curStr = """ select {} from {} where {} order by {} {} """.format(findkey,tablename, condition,tableKey, fn) #print(curStr) self.c.execute(curStr) return self.c.fetchall() except Exception: dingding_alert(traceback.format_exc()) dingding_alert(curStr) def sort_queryTable2(self, findkey:str,tablename:str, condition:str,tableKey:str, fn,tableKey1:str, fn1): try: curStr = """ select {} from {} where {} order by {} {},{} {} """.format(findkey,tablename, condition,tableKey, fn,tableKey1, fn1) #print(curStr) self.c.execute(curStr) return self.c.fetchall() except Exception: dingding_alert(traceback.format_exc()) dingding_alert(curStr) def queryTabel(self, tablename:str, key:str, condition:str): try: curStr1 = """ SELECT EXISTS ( SELECT * FROM pg_catalog.pg_tables WHERE tablename = '{}' AND schemaname = 'public' ); """.format(tablename.lower()) self.c.execute(curStr1) result = self.c.fetchall()[0][0] if result: curStr = """ select {} from {} where {} """.format(key, tablename, condition) #print(curStr) self.c.execute(curStr) return self.c.fetchall() else: return None except Exception: print(traceback.format_exc()) #print(curStr) #dingding_alert(traceback.format_exc()) #dingding_alert(curStr) self.conn.rollback() def getAlldata(self, tablename:str): try: curStr = """select * from {}""".format(tablename) self.c.execute(curStr) return self.c.fetchall() except Exception: dingding_alert(traceback.format_exc()) dingding_alert(curStr) def upDateItem(self, tablename:str, dateDic:dict, condition:str, *args): try: setStr = '' for key in dateDic: if tablename == 'display': setStr += '{}={},'.format(key, str(dateDic[key]).replace("'","''")) else: setStr += '{}={},'.format(key, dateDic[key]) if tablename == 'display': setStr = setStr[:-1].replace('"',"'") else: setStr = setStr[:-1] curStr = """ update {} set {} where {} """.format(tablename, setStr, condition) #print(curStr) if setStr !="": self.c.execute(curStr) if args == (): self.conn.commit() except Exception: print(traceback.format_exc()) #print(curStr) dingding_alert(traceback.format_exc()) def lazyUpdateItem(self,tablename:str, dateDic:dict, condition:str): self.upDateItem(tablename,dateDic,condition,'lazy') def getSingledata(self,findkey:str,tablename:str): try: curStr = """ select {} from {} """.format(findkey,tablename) #print(curStr) self.c.execute(curStr) return self.c.fetchall() except Exception: dingding_alert(traceback.format_exc()) def deleteSingledata(self,tablename:str,findkey:str): try: curStr = """ delete from {} where {} """.format(tablename,findkey) self.c.execute(curStr) #print(curStr) self.conn.commit() except Exception: dingding_alert(traceback.format_exc()) def close(self): try: self.conn.close() except Exception: dingding_alert(traceback.format_exc()) def dingding_alert(msg): dingding_webhook="https://oapi.dingtalk.com/robot/send?access_token=9c78c711f14ba3345d6dc492dc5ca8118c421516d611b5de46854fb8e158565f" timestamp = str(round(time.time() * 1000)) secret = 'SEC09f744f81b55c8a18f0d77a3ab60bf4e1ec3f16b85f025e6a4f75ddff00a8fd4' secret_enc = secret.encode('utf-8') string_to_sign = '{}\n{}'.format(timestamp, secret) string_to_sign_enc = string_to_sign.encode('utf-8') hmac_code = hmac.new(secret_enc, string_to_sign_enc, digestmod=hashlib.sha256).digest() sign = urllib.parse.quote_plus(base64.b64encode(hmac_code)) dingding_url = dingding_webhook + '×tamp=' + timestamp + "&sign=" + sign header = { "Content-Type": "application/json" } data = { "at": { "atMobiles":(123,456), # "isAtAll": True }, "text": { "content": msg }, "msgtype": "text" } res = requests.post(url=dingding_url, data=json.dumps(data), headers=header) if __name__ == '__main__': fdb = flightDB(host=host, port=port, user=user, password=password, database=databaseflightDB4 ) loginDB = flightDB(host=host, port=port, user=user, password=password, database=databaseflightDB4 )