def clearConnec(self): sql = "update connection set is_alive=0" db_lock.acquire() cursor.execute(sql) # 互斥访问,预防并发访问时游标被占用,结果出错 db.commit() db_lock.release() # 释放锁
def insertItem(self): data = self.RoomNo sql = "select room_no,switch_cnt from servent_stat where room_no=%d and date=CURRENT_DATE " % data # 互斥访问,预防并发访问时游标被占用,结果出错 print("database??", sql) db_lock.acquire() cursor.execute(sql) res = cursor.fetchall() print(res) if len(res) == 0: now = datetime.datetime.now().strftime('%Y-%m-%d') sql = "insert into servent_stat values (%d,%d,%f,%d,%f,%f,'%s')" data = (self.RoomNo, self.switchcnt, self.temp, self.velocity, self.cost, self.energy, now) cursor.execute(sql % data) db.commit() print("插入数据") else: #已存在,由于从机被重新初始化,除了开机次数和房间号、日期、消费,剩下字段都更新 self.switchcnt = res[0][1] sql = "update servent_stat set wind_level=%d,temp=%f where room_no='%d' and date=curdate()"\ %(self.velocity,self.temp,self.RoomNo) cursor.execute(sql) db.commit() print("更新数据") db_lock.release() # 释放锁
def setConnec(self): self.connec = [] sql = "select room_no from connection where is_alive=1" db_lock.acquire() cursor.execute(sql) # 互斥访问,预防并发访问时游标被占用,结果出错 for row in cursor.fetchall(): #print(row) self.connec.append(row[0]) db_lock.release() # 释放锁
def addCost(self, delmonye, deleng): self.cost += delmonye self.energy += deleng db_lock.acquire() sql = "UPDATE servent_stat SET cost=cost+%f, energy=energy+%f WHERE room_no='%d' and date=curdate()"\ % (delmonye,deleng,self.RoomNo) print(sql) cursor.execute(sql) db.commit() db_lock.release()
def newRequest(self, roomNo, temp, windLevel): time = datetime.now() #终止上个请求 self.endRequest(roomNo, temp, windLevel) sqlquery = "insert into request(room_no,s_temp,s_wind_level,s_time,cost) values(%s,%s,%s,'%s',0)" % ( str(roomNo), str(temp), str(windLevel), str(time)) print(sqlquery) db_lock.acquire() cursor.execute(sqlquery) db_lock.release()
def setTemp(self, temp): self.temp = temp # 互斥访问,预防并发访问时游标被占用,结果出错 print("change temp of room %d" % (self.RoomNo)) db_lock.acquire() sql = "update servent_stat set temp=%f where room_no='%d' and date=curdate()" \ % (self.temp, self.RoomNo) cursor.execute(sql) db.commit() db_lock.release() # 释放锁 print("change temp of room %d, complete" % (self.RoomNo))
def setID(self): sql = "SELECT name,is_alive FROM connection where room_no='%d'" data = self.RoomNo # 互斥访问,预防并发访问时游标被占用,结果出错 db_lock.acquire() cursor.execute(sql % data) for row in cursor.fetchall(): self.ID = row[0] self.isalive = row[1] print(row) db_lock.release() # 释放锁
def isdie(self): self.isalive = 0 date = datetime.datetime.now() start = datetime.datetime(date.year, date.month, date.day, 0, 0, 0) end = datetime.datetime(date.year, date.month, date.day, 23, 59, 59) db_lock.acquire() sql = "UPDATE connection SET is_alive=0 WHERE room_no=%d and login_time between '%s' and '%s'" % ( self.RoomNo, start, end) cursor.execute(sql) db.commit() db_lock.release()
def insertmes(self, roomNo, name, pwd): sql = "insert into usr(roomNo,name,pwd) value (%d,'%s','%s')" % ( int(roomNo), name, pwd) print(sql) # 互斥访问,预防并发访问时游标被占用,结果出错 db_lock.acquire() cursor.execute(sql) db_lock.release() #释放锁 db.commit() print("插入数据") return '登记成功'
def getRoomNo(self): sqlquery = "select distinct room_no from request order by room_no" print(sqlquery) db_lock.acquire() cursor.execute(sqlquery) row = cursor.fetchone() room = [] while (row != None): room.append(row[0]) row = cursor.fetchone() db_lock.release() return room
def getRequest(self, roomNo, sdate, edate): sqlquery = "SELECT * FROM REQUEST WHERE ROOM_NO = %s AND S_TIME BETWEEN '%s 00:00:00' AND '%s 23:59:59'" % ( str(roomNo), sdate, edate) print(sqlquery) db_lock.acquire() cursor.execute(sqlquery) row = cursor.fetchone() request = [] while (row != None): request.append(row) row = cursor.fetchone() db_lock.release() return request
def addSwitch_cnt(self): self.isdie() self.cost = 0.00 self.energy = 0 sql = "update servent_stat set switch_cnt = switch_cnt+1 where room_no='%d' and date=curdate()" % ( self.RoomNo) # 互斥访问,预防并发访问时游标被占用,结果出错 print(sql) db_lock.acquire() cursor.execute(sql) db.commit() db_lock.release() # 释放锁 print("关机次数更新完成")
def setWindLev(self, velocity): self.velocity = velocity # 互斥访问,预防并发访问时游标被占用,结果出错 print("change velocity of room %d as %d" % (self.RoomNo, self.velocity)) db_lock.acquire() sql = "update servent_stat set wind_level=%d where room_no='%d' and date=curdate()" \ % (self.velocity, self.RoomNo) cursor.execute(sql) db.commit() db_lock.release() # 释放锁 print("change velocity of room %d complete" % (self.RoomNo))
def getLatestRequest(self, roomNo): sqlquery = "select max(a.s_time) from (select * from request where room_no = %s) as a" % ( str(roomNo)) print(sqlquery) db_lock.acquire() cursor.execute(sqlquery) row = cursor.fetchone() db_lock.release() time = "" if (row[0] == None): print("no request from this room") else: time = str(row[0]) return time
def getSwitchCnt(self, roomNo, sdate, edate): sqlquery = "SELECT SUM(SWITCH_CNT) FROM SERVENT_STAT WHERE ROOM_NO = %s AND DATE BETWEEN '%s' AND '%s' GROUP BY ROOM_NO" % ( str(roomNo), sdate, edate) print(sqlquery) db_lock.acquire() cursor.execute(sqlquery) row = cursor.fetchone() db_lock.release() switchCnt = 0 if (row is None): print("No such room") else: switchCnt = int(row[0]) return switchCnt
def setOther(self): if self.isalive == 0: self.temp = 0.00 self.velocity = 0 else: sql = "SELECT temp,wind_level FROM servent_stat where room_no='%d' and date=curdate()" data = self.RoomNo # 互斥访问,预防并发访问时游标被占用,结果出错 db_lock.acquire() cursor.execute(sql % data) for row in cursor.fetchall(): self.temp = row[0] self.velocity = row[1] print(row) db_lock.release() # 释放锁
def getCost(self, roomNo, sdate, edate): sqlquery = "SELECT SUM(a.COST) FROM (select cost from servent_stat where room_no = %s and date between '%s' and '%s') as a" % ( str(roomNo), sdate, edate) print(sqlquery) db_lock.acquire() cursor.execute(sqlquery) row = cursor.fetchone() print(row) db_lock.release() cost = 0 if (row[0] is None): print("No such room") else: cost = row[0] return cost
def select(self, id, pwd): sql = "SELECT * FROM " + self.__tablename__ + " WHERE id='%s' and pwd='%s'" % ( id, pwd) print(sql) # 互斥访问,预防并发访问时游标被占用,结果出错 db_lock.acquire() cursor.execute(sql) row = cursor.fetchone() db_lock.release() #释放锁 print(row) res = True message = '登录成功' if row == None: #没查到了管理员号 res = False message = '账号或密码错误,请检查后重新登录' return res, message
def costUpdate(self, roomNo, addCost): sqlquery = "select max(s_time) from request where room_no = %s group by room_no" % ( str(roomNo)) print(sqlquery) db_lock.acquire() cursor.execute(sqlquery) row = cursor.fetchone() db_lock.release() if (row == None): print("No request from this room") else: time = row[0] sqlquery = "update request set cost = cost + %s where s_time = '%s' and room_no = %s" % ( str(addCost), str(time), str(roomNo)) print(sqlquery) db_lock.acquire() cursor.execute(sqlquery) db_lock.release()
def endRequest(self, roomNo, temp, windLevel): sqlquery = "select max(s_time) from request where room_no = %s group by room_no" % ( str(roomNo)) print(sqlquery) db_lock.acquire() cursor.execute(sqlquery) row = cursor.fetchone() db_lock.release() if (row == None): print("No request from this room") else: time = row[0] endtime = datetime.now() sqlquery = "update request set e_time = '%s',e_temp = %s,e_wind_level = %s where room_no = %s and s_time = '%s'" % ( str(endtime), str(temp), str(windLevel), str(roomNo), str(time)) db_lock.acquire() cursor.execute(sqlquery) db_lock.release()
def Check(self, Room_No, Name, Password): print("check:", Room_No, Name, Password) #检查房间号/用户名/身份证是否含有非法字符 numMatch = re.match(r'^[0-9][0-9]*$', Room_No, re.S | re.I) idMatch = re.match(r'.*[\'\"\\].*', Name, re.S | re.I) pwdMatch = re.match(r'.*[\'\"\\].*', Password, re.S | re.I) #含有非法字符则弹回去,防止崩溃 if numMatch == None: self.log_sig.emit(Room_No, 0, Name, Password) return False if idMatch != None: self.log_sig.emit(Room_No, 0, Name, Password) return False elif pwdMatch != None: self.log_sig.emit(Room_No, 0, Name, Password) return False db_lock.acquire() sql = "SELECT * FROM " + self.__tablename__ + " WHERE roomNo=%d and name='%s' and pwd='%s'" % ( int(Room_No), Name, Password) print(sql) cursor.execute(sql) row = cursor.fetchone() db_lock.release() print(row) if row == None: print("not connected") self.log_sig.emit(Room_No, 0, Name, Password) return False #return False else: print("connected") date = datetime.now() start = datetime(date.year, date.month, date.day, 0, 0, 0) end = datetime(date.year, date.month, date.day, 23, 59, 59) db_lock.acquire() sql = "SELECT is_alive FROM connection WHERE room_no=%d and login_time between '%s' and '%s'" % ( int(Room_No), start, end) cursor.execute(sql) row = cursor.fetchone() db_lock.release() if row == None: self.log_sig.emit(Room_No, 1, Name, Password) print("new room") sql = "INSERT INTO connection values(%d,'%s','%s','%s',1)" % ( int(Room_No), Name, Password, date) print(sql) db_lock.acquire() cursor.execute(sql) db.commit() db_lock.release() print("insert done") return True elif row[0] == 0: self.log_sig.emit(Room_No, 1, Name, Password) print("room exists and not connect") sql = "UPDATE connection SET is_alive=1 WHERE room_no=%d and name='%s' and pwd='%s'and " \ "login_time between '%s' and '%s'" % (int(Room_No), Name, Password, start, end) print(sql) db_lock.acquire() cursor.execute(sql) db.commit() db_lock.release() print("update done") return True else: #(房间已经被登录) self.log_sig.emit(Room_No, 0, Name, Password) return False