flightinfo_sever/Functions/DBUtils4PG_.py

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 + '&timestamp=' + 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
                   )