def RetureIllegalRecord(self): mysql = Mysql() # 获取系统时间 systime = datetime.datetime.now().strftime('%Y-%m-%d') #返回超过系统时间还未还的图书借阅记录 sql = "select * from borrow where actretdate < '" + systime + "' and presretdate = '0000-00-00'" result = mysql.getAll(sql) mysql.dispose() return result
def InsertBorrowRecord(self, List): mysql = Mysql() sql = "insert into borrow(borrowid,userid,bookid,borrowdate,presretdate,actretdate) values(%s,%s,%s,%s,%s,%s)" try: mysql.insertMany(sql, List) mysql.end('commit') print("insert success!") except Exception as e: print("except") mysql.end(None) mysql.dispose()
def InsertIllegal(self,List): mysql = Mysql() sql = "insert into illegal(illegalid,userid,bookid,amount,isprocessed,illegaldate,illegaltype) " + \ "values(%s,%s,%s,%s,%s,%s,%s)" # val try: mysql.insertMany(sql, List) mysql.end('commit') print("insert success!") except Exception as e: mysql.end(None) mysql.dispose()
def UpdateRecord(self, table, key1, val1, key2, val2): # key1和val1是修改键和值,val1和val2是条件键和值,如果是val是非数字,则需要写成'"数"'传入 mysql = Mysql() sql = "update " + table + " set " + key1 + "='" + val1 + "' where " + key2 + "='" + val2 + "'" try: mysql.update(sql, None) # mysql.update("update book") mysql.end('commit') print("update succes!") except Exception as e: print("except") mysql.end(None) mysql.dispose()
def GetAllIlegal(self,num): mysql = Mysql() if(str(num) == '0'): sqlAll = "select * from illegal" else: sqlAll= "select * from illegal limit " + str(num) result = mysql.getAll(sqlAll) if result : for row in result : print("%s\t%s\t%s\t%s\t%s\t%s\t%s" %\ (row[0],row[1],row[2],row[3],row[4],row[5],row[6])) mysql.dispose()
def GetBorrowRecord(self, num): mysql = Mysql() if (str(num) == '0'): sqlAll = "select * from borrow" else: sqlAll = "select * from borrow limit " + str(num) result = mysql.getAll(sqlAll) print("borrowid\tuserid\tbookid\tborrowdate\tpresretdate\tactretdate") if result: for row in result: print("%s\t%s\t%s\t%s\t%s\t%s" %\ (row[0],row[1],row[2],row[3],row[4],row[5])) mysql.dispose()
def GetUserIllegal(self,Ile): mysql = Mysql() sql = "select * from illegal where " keys = tuple(Ile.keys()) vals = tuple(Ile.values()) Len = len(Ile) for i in range(Len): if (i != Len-1): sql = sql + keys[i] + "='" + str(vals[i]) + "' and " else: sql = sql + keys[i] + "='" + str(vals[i]) + "'" Ilegal = mysql.getAll(sql) if len(Ilegal) == 0: print("No illegal record!") else: print("Illegal records:") if Ilegal: for row in Ilegal: print("%s\t%s\t%s\t%s\t%s\t%s\t%s" % (row[0],row[1],row[2],row[3],row[4],row[5],row[6])) mysql.dispose()
def GetBorrowRecordByField(self, Dict): mysql = Mysql() sql = "select * from borrow where " keys = tuple(Dict.keys()) vals = tuple(Dict.values()) Len = len(Dict) for i in range(Len): if (i != Len - 1): sql = sql + keys[i] + "='" + str(vals[i]) + "' and " else: sql = sql + keys[i] + "='" + str(vals[i]) + "'" Book = mysql.getAll(sql) if len(Book) == 0: print("No borrow record found!") else: print("The borrow record you found:") if Book: for row in Book: print("%s\t%s\t%s\t%s\t%s\t%s" %\ (row[0],row[1],row[2],row[3],row[4],row[5])) mysql.dispose() return Book