flightinfoV3_sever/Functions/flaskDBUtils4PG.py

import datetime
import time
from .DButilsFunction import flightDB

loginsts = 'loginsts'
logintoken = 'logintoken'
infolog = 'infolog'
IpPhoneip='IpPhoneip'
callingPhone='callingPhone'
vin="vin"


class flaskDB(flightDB) :
    def __init__(self , host , port , user , password , database) :
        super(flaskDB , self).__init__(host = host , port = port , user = user , password = password ,
                                       database = database)
        # self._dir = './DATABASE'
        # self.conn = sqlite3.connect(os.path.join(self._dir,DBName))
        # self.c = self.conn.cursor()

    def insertManyData(self , sql: str , data_list: list) :
        self.c.executemany(sql , data_list)
        self.conn.commit()


def initIpPhoneip(db: flaskDB) :
    db.initTable(IpPhoneip , 'ID' , 'INTEGER PRIMARY KEY NOT NULL' , { 'ip' : 'text' , 'token' : 'text' })
    result = db.getAlldata(IpPhoneip)
    if result == [ ] :
        db.insertData('IpPhoneip' , { 'ID' : 1 , 'ip' : 'None' , 'token' : 'None' })


def initVin(db: flaskDB) :
    db.initTable(vin , 'ID' , 'INTEGER PRIMARY KEY NOT NULL' , { 'VINA' : 'text' , 'VINB' : 'text' })
    result = db.getAlldata(vin)
    if result == [ ] :
        db.insertData('vin' , { 'ID' : 1 , 'VINA' : '%s' % time.time() , 'VINB' : '%s' % time.time() })
        db.insertData('vin' , { 'ID' : 2 , 'VINA' : '%s' % time.time() , 'VINB' : '%s' % time.time() })
        db.insertData('vin' , { 'ID' : 3 , 'VINA' : '%s' % time.time() , 'VINB' : '%s' % time.time() })


def initcallingPhone(db: flaskDB) :
    db.initTable(callingPhone , 'ID' , 'INTEGER PRIMARY KEY NOT NULL' , { 'phonenumber' : 'text' , 'time' : 'text' })
    result = db.getAlldata(callingPhone)
    if result == [ ] :
        db.insertData('callingPhone' , { 'ID' : 1 , 'phonenumber' : 'None' , 'time' : 'None' })


def initLoginsts(db: flaskDB) :
    db.initTable(loginsts , 'ID' , 'INTEGER PRIMARY KEY NOT NULL' , { 'sts' : 'int' })
    result = db.getAlldata(loginsts)
    if len(result) != 2 :
        try:
            db.insertData('loginsts' , { 'ID' : 1 , 'sts' : 0 })
        except:
            pass
        try:
            db.insertData('loginsts' , { 'ID' : 2 , 'sts' : 0 })
        except:
            pass


def initLoginToken(db: flaskDB) :
    db.initTable(logintoken , 'ID' , 'INTEGER PRIMARY KEY NOT NULL' , { 'username' : 'text' , 'token' : 'text' })
    result = db.getAlldata(logintoken)
    if len(result) != 2 :
        try:
            db.insertData(logintoken , { 'ID' : 1 , 'username' : 'None' , 'token' : 'None' })
        except:
            pass
        try:
            db.insertData(logintoken , { 'ID' : 2 , 'username' : 'None' , 'token' : 'None' })
        except:
             pass


def initInfolog(db: flaskDB) :
    db.initTable(infolog , 'ID' , 'SERIAL PRIMARY KEY' , { 'datatime' : 'text' , 'sts' : 'text' })


def checkLogin(db: flaskDB) :
    initLoginsts(db)
    result = db.queryTabel(loginsts , 'sts' , 'ID=1')
    if result[0][0] == 1 :
        # print('已登录')
        return 1
    else :
        return 0


def checkLoginflightplan(db: flaskDB) :
    initLoginsts(db)
    result = db.queryTabel(loginsts , 'sts' , 'ID=2')
    if result[ 0 ][ 0 ] == 1 :
        # print('已登录')
        return 1
    else :
        return 0


def logout(db: flaskDB) :
    initLoginsts(db)
    initLoginToken(db)
    db.upDateItem(loginsts , { 'sts' : 0 } , 'ID=1')
    db.upDateItem(logintoken , { 'username' : "'None'" , 'token' : "'None'" } , 'ID=1')


def logoutflightplan(db: flaskDB) : #飞机排班掉线会直接摧毁amro登录
    initLoginsts(db)
    initLoginToken(db)
    db.upDateItem(loginsts , { 'sts' : 0 } , 'ID=2')
    db.upDateItem(loginsts , { 'sts' : 0 } , 'ID=1')
    db.upDateItem(logintoken , { 'username' : "'None'" , 'token' : "'None'" } , 'ID=1')
    db.upDateItem(logintoken , { 'username' : "'None'" , 'token' : "'None'" } , 'ID=2')


def setIpPhone(db: flaskDB , ip , token) :
    initIpPhoneip(db)
    db.upDateItem(IpPhoneip , { 'ip' : "'%s'" % ip , 'token' : "'{}'".format(token) } , 'ID=1')


def callingphoneUpdate(db: flaskDB , phonenumber , time) :
    initcallingPhone(db)
    db.upDateItem(callingPhone , { 'phonenumber' : "'%s'" % phonenumber , 'time' : "'%s'" % time } , 'ID=1')


def getPhoneNum(db: flaskDB) :
    initcallingPhone(db)
    res = db.queryTabel(callingPhone , "phonenumber" , "ID = '1'")[ 0 ][ 0 ]
    return res


def getVin(db: flaskDB , mode , num) :
    initVin(db)

    if num == 1 :  # 昨天
        res = db.queryTabel(vin , "VIN%s" % mode , "ID = '1'")[ 0 ][ 0 ]
    elif num == 2 :  # 今天
        res = db.queryTabel(vin , "VIN%s" % mode , "ID = '2'")[ 0 ][ 0 ]
    elif num == 3 :  # 明天
        res = db.queryTabel(vin , "VIN%s" % mode , "ID = '3'")[ 0 ][ 0 ]
    return res


def getallVin(db: flaskDB) :
    initVin(db)
    res = [ ]
    for mode in [ "A" , "B" ] :
        for num in [ 1 , 2 , 3 ] :
            res.append(db.queryTabel(vin , "VIN%s" % mode , "ID = '%s'" % num)[ 0 ][ 0 ])
    return res


def updateVin(db: flaskDB , mode , num) :
    initVin(db)
    Vin = time.time()
    if num == 1 :
        db.upDateItem(vin , { 'VIN%s' % mode : "'%s'" % Vin } , 'ID=1')
    elif num == 2 :
        db.upDateItem(vin , { 'VIN%s' % mode : "'%s'" % Vin } , 'ID=2')
    elif num == 3 :
        db.upDateItem(vin , { 'VIN%s' % mode : "'%s'" % Vin } , 'ID=3')
    return Vin


def updateALlVin(db: flaskDB) :
    initVin(db)
    Vin = time.time()
    for mode in [ "A" , "B" ] :
        for num in [ 1 , 2 , 3 ] :
            db.upDateItem(vin , { 'VIN%s' % mode : "'%s'" % Vin } , 'ID=%s' % num)


def getIpPhoneip(db: flaskDB) :
    initIpPhoneip(db)
    res1 = { "ip" : "" , "token" : "" }
    res = db.queryTabel(IpPhoneip , "*" , "ID = '1'")
    if res[ 0 ][ 2 ] != None :
        res1 = { "ip" : "%s" % res[ 0 ][ 1 ] , "token" : "%s" % res[ 0 ][ 2 ] }
        return res1
    else :
        return res1


def login(db: flaskDB , user , token , x_access_token) :
    initLoginsts(db)
    initLoginToken(db)
    db.upDateItem(loginsts, { 'sts' : 1 } , 'ID=1')
    db.upDateItem(loginsts, { 'sts' : 1 } , 'ID=2')
    db.upDateItem(logintoken, { 'username' : user , 'token' : "'{}'".format(token) } , 'ID=1')
    db.upDateItem(logintoken, { 'username' : user , 'token' : "'{}'".format(x_access_token) } , 'ID=2')


def getToken(db: flaskDB) :
    initLoginToken(db)
    result = db.getAlldata(logintoken)
    # print(result)
    return result[ 0 ][ 2 ] , result[ 1 ][ 2 ]


def insertinfolog(db: flaskDB , datatime , sts) :
    initInfolog(db)
    db.insertData(infolog , { 'datatime' : "{}".format(datatime) , 'sts' : "{}".format(sts) })


def updatecheck(db: flaskDB) :
    now = datetime.datetime.now()
    testtime = (now - datetime.timedelta(minutes = 6)).strftime("%Y%m%d %H:%M:%S")
    log_str = db.queryTabel(infolog , "ID" , "datatime >'%s' and sts = '200'" % testtime)
    if log_str == [ ] :
        updatests = "0"
    else :
        updatests = "1"
    return updatests


def clearinfolog(db: flaskDB) :
    initInfolog(db)
    now = datetime.datetime.now()
    passtime = (now - datetime.timedelta(days = 1)).strftime("%Y%m%d %H:%M:%S")
    log_str = db.queryTabel(infolog , "ID" , "datatime <'%s'" % passtime)
    if log_str != [ ] :
        for i in log_str :
            db.deleteSingledata(infolog , "ID='%s'" % i[ 0 ])


def getinfolog(db: flaskDB) :
    initLoginsts(db)
    result = db.getAlldata(infolog)
    # print(result)
    return result


def testInsertMany(db: flaskDB) :
    initInfolog(db)
    sql = "insert into infolog (datatime, sts) values (?, ?);"
    data_list = [ ]
    for i in range(1000) :
        nowDay = datetime.datetime.now()
        nowDayStr = nowDay.strftime("%Y%m%d")
        nowStr = nowDay.strftime("%Y%m%d %H:%M:%S")
        data_list.append((nowStr , '200'))
    time1 = time.time()
    db.insertManyData(sql , data_list)
    # print("插入多条数据：{}".format(time.time()-time1))


def testInsert(db: flaskDB) :
    for i in range(1000) :
        nowDay = datetime.datetime.now()
        nowDayStr = nowDay.strftime("%Y%m%d")
        nowStr = nowDay.strftime("%Y%m%d %H:%M:%S")
        insertinfolog(db , nowStr , '200')


if __name__ == '__main__' :
    flaskDB = flaskDB(host = '192.168.2.65' ,
                      port = '9527' ,

                      # host='101.42.5.211', #测试服务器
                      # port='5444',

                      user = 'user1' ,
                      password = 'a123456s' ,
                      database = "flaskDatabase"
                      )
    initLoginsts(flaskDB)
    # flaskDB.insertData('loginsts', {'ID':1,'sts':1})
    # checkLogin(flaskDB)
    # login(flaskDB, '022673', '7de7ce62-e02f-4f9e-bcec-03b9084e890d')
    # checkLogin(flaskDB)
    # logout(flaskDB)
    #
    # getToken(flaskDB)

    testInsertMany(flaskDB)

    time1 = time.time()
    testInsert(flaskDB)
    print('插入时间：{}'.format(time.time() - time1))