flightinfo_sever/Functions/flaskDBUtils4PG.py

import datetime
import time
from Functions.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 result == []:
        db.insertData('loginsts', {'ID':1,'sts':0})

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

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 logout(db:flaskDB):
    initLoginsts(db)
    initLoginToken(db)
    db.upDateItem(loginsts, {'sts': 0}, 'ID=1')
    db.upDateItem(logintoken, {'username': "'None'", 'token': "'None'"}, 'ID=1')

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):
    initLoginsts(db)
    initLoginToken(db)
    db.upDateItem(loginsts,{'sts':1}, 'ID=1')
    db.upDateItem(logintoken,{'username':user,'token':"'{}'".format(token)}, 'ID=1')

def getToken(db:flaskDB):
    initLoginToken(db)
    result = db.getAlldata(logintoken)
    #print(result)
    return result[0][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))