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 clearConnec(self): sql = "update connection set is_alive=0" db_lock.acquire() cursor.execute(sql) # 互斥访问,预防并发访问时游标被占用,结果出错 db.commit() 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 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 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 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 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