Esempio n. 1
0
 def InsertUserRecord(self, obj):
     # 将所有value加入一个list
     listforinsert = [
         obj.getid(),
         obj.getusername(),
         obj.getpassword(),
         obj.getfaculty(),
         obj.getdepartment(),
         obj.getage(),
         obj.getgendar(),
         obj.getuseridentity(),
         obj.getuserconnection(),
         obj.getstatus()
     ]
     # 转化为元组
     listforinsert = (tuple(listforinsert))
     # 加入list
     List = []
     List.append(listforinsert)
     mysql = Mysql()
     sql = "insert into user(userid,username,password,faculty,department,age,gender,useridentity,userconnection" \
           ",status) " + "values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
     try:
         mysql.insertMany(sql, List)
         mysql.end('commit')
         print("insert success!")
         return True
     except Exception as e:
         print("insert failed!")
         mysql.end(None)
     mysql.dispose()
     return None
Esempio n. 2
0
 def InsertBookRecord(self, obj):
     # obj->list
     List = [obj.get_bookid(), obj.get_bookname(), obj.get_author(), obj.get_pages(), obj.get_collecttime(),
             obj.get_version(), obj.get_major(), obj.get_discipline(), obj.get_isbn(), obj.get_booklanguage(),
             obj.get_publisher(), obj.get_status(), obj.get_abstract(), obj.get_stack(), obj.get_shelf(),
             obj.get_floor(), obj.get_bookvalue()]
     Tum = tuple(List)
     List = []
     List.append(Tum)
     print(List)
     mysql = Mysql()
     # 插入图书
     # sql
     sql = "insert into book(bookid,bookname,author,pages,collecttime,version,\
     major,discipline,isbn,booklanguage,publisher,status,abstract,stack,shelf,floor,bookvalue) " + \
           "values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
     # val
     try:
         mysql.insertMany(sql, List)
         mysql.end('commit')
         print("insert success!")
     except Exception as e:
         print('error')
         mysql.end(None)
     mysql.dispose()
Esempio n. 3
0
 def GetUserRecord(self, Dict):
     mysql = Mysql()
     sql = "select * from user 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]) + "'"
     try:
         User = mysql.getAll(sql)
         if len(User) == 0:
             print("No user found!")
         else:
             print("The user you found:")
             if User:
                 for row in User:
                     print("%s\t%s\t%s\t%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], row[7], row[8], row[9]))
                 return User
     except Exception as e:
         print(e)
     mysql.dispose()
Esempio n. 4
0
 def InsertIllegal(self, obj):
     # obj->list
     List = [
         obj.get_illegalid(),
         obj.get_userid(),
         obj.get_bookid(),
         obj.get_amount(),
         obj.get_isprocessed(),
         obj.get_illegaldate(),
         obj.get_illegaltype()
     ]
     Tum = tuple(List)
     List = []
     List.append(Tum)
     print(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!")
         return True
     except Exception as e:
         mysql.end(None)
     mysql.dispose()
     return False
Esempio n. 5
0
 def InsertBorrowRecord(self, obj):
     # obj->list
     List = [
         obj.get_borrowid(),
         obj.get_userid(),
         obj.get_bookid(),
         obj.get_borrowdate(),
         obj.get_presretdate(),
         obj.get_actretdate()
     ]
     Tum = tuple(List)
     List = []
     List.append(Tum)
     print(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!")
         return True
     except Exception as e:
         print("except")
         mysql.end(None)
     mysql.dispose()
     return False
 def GetRecommandShow(self, id):
     mysql = Mysql()
     sql = "select * from recommand where userid='" + id + "'"
     try:
         User = mysql.getAll(sql)
         if len(User) == 0:
             print("No recommand found!")
         else:
             #print("The recommand you found:")
             if User:
                 rs = []
                 for row in User:
                     user = Recommand()
                     user.setbookname(row[1])
                     user.setauthor(row[2])
                     user.setpublisher(row[3])
                     user.setversion(row[4])
                     user.setrecomreason(row[5])
                     user.setstatue(row[6])
                     rs.append(user)
                 rs = tuple(rs)
                 return rs
     except Exception as e:
         print("query error!")
     mysql.dispose()
 def GetUserTypeRecord(self):
     mysql = Mysql()
     sql = "select * from usertype"
     rs = mysql.getAll(sql)
     for row in rs:
         print("身份:%s\t同时可借最多图书数目:%s\t一次性最多可推荐图书数目:%s\t免费借阅期限:%s\t" %
               (row[0], row[1], row[2], row[3]))
     return rs
Esempio n. 8
0
 def GetMaxBorrowid(self):
     mysql = Mysql()
     sql = "select max(borrowid) from borrow"
     result = mysql.getAll(sql)
     mysql.dispose()
     result = result[0]
     result = result[0]
     return result
Esempio n. 9
0
 def GetMaxIllegalid(self):
     mysql = Mysql()
     sql = "select max(illegalid) from illegal"
     result = mysql.getAll(sql)
     mysql.dispose()
     result = result[0]
     result = result[0]
     return result
 def GetAdminPermission(self, adminid):
     '''
     :param adminid:
     :return: 管理员权限编号
     '''
     mysql = Mysql()
     sql = "select adminpri from admin where adminid ='" + adminid + "'"
     adminpermission = mysql.getOne(sql)
     return list(adminpermission)
 def GetUserType(self, id):
     mysql = Mysql()
     sql = "select useridentity from user where userid = '" + id + "'"
     s = mysql.getOne(sql)
     sql1 = "select * from usertype where useridentity = '" + s[0] + "'"
     result = mysql.getOne(sql1)
     print("最多可同时借阅书籍数目:%s\t最多可一次性推荐书籍数目:%s\t一本书免费借阅期限:%s" %
           (result[1], result[2], result[3]))
     return result
Esempio n. 12
0
 def GetBookByName(self, keyword):
     mysql = Mysql()
     book_list = mysql.getAll("select * from book where bookname like '%" + keyword + "%'")
     mysql.dispose()
     if book_list == False:
         print('no record')
         return 0
     show = MiddleLayer()
     tums = show.ShowBook(book_list)
     return tums
Esempio n. 13
0
 def DeleteRecord(self, table, key, val):  # key字段名 val值
     mysql = Mysql()
     sql = "delete from " + table + " where " + str(key) + "=" + str(val)
     try:
         mysql.delete(sql, None)
         mysql.end('commit')
         print("delete success!")
     except Exception as e:
         print("error")
         mysql.end(None)
     mysql.dispose()
 def GetAllAdminRecord(self):
     '''
     :param Dict:
     输出admin表所有字段
     '''
     mysql = Mysql()
     sqlAll = "select * from admin"
     result = mysql.getAll(sqlAll)
     mysql.dispose()
     show = MiddleLayer()
     tums = show.ShowAdmin(result)
     return tums
 def matchIsAdmin(self, adminid, passwd):
     '''
     :param adminid:
     :param passwd:
     根据id、passwd字段查询admin表,匹配管理员成功返回adminid
     '''
     mysql = Mysql()
     sql = "select * from admin where adminid = '" + adminid + "' and passwd = '" + passwd + "'"
     if (mysql.getOne(sql) != False):
         print("login success!")
         adminpermission = self.GetAdminPermission(adminid)
         return [adminid, adminpermission]
Esempio n. 16
0
 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(e)
         mysql.end(None)
     mysql.dispose()
Esempio n. 17
0
 def GetUserIllegal(self, obj):
     # 拼接
     Seq = []
     Val = []
     if obj.get_illegalid() != '':
         Seq.append('illegalid')
         Val.append(obj.get_illegalid())
     if obj.get_userid() != '':
         Seq.append('userid')
         Val.append(obj.get_userid())
     if obj.get_bookid() != '':
         Seq.append('bookid')
         Val.append(obj.get_bookid())
     if obj.get_amount() != '':
         Seq.append('amount')
         Val.append(obj.get_amount())
     if obj.get_isprocessed() != '':
         Seq.append('isprocessed')
         Val.append(obj.get_isprocessed())
     if obj.get_illegaldate() != '':
         Seq.append('illegaldate')
         Val.append(obj.get_illegaldate())
     if obj.get_illegaltype() != '':
         Seq.append('illegaltype')
         Val.append(obj.get_illegaltype())
     Ile = dict(zip(Seq, Val))
     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 Ilegal == False:
         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()
     show = MiddleLayer()
     tums = show.ShowIllegal(Ilegal)
     return tums
Esempio n. 18
0
 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()
     show = MiddleLayer()
     tums = show.ShowIllegal(result)
     return tums
Esempio n. 19
0
 def GetBorrowRecordByField(self, obj):
     # 拼接
     Seq = []
     Val = []
     if obj.get_borrowid() != '':
         Seq.append('borrowid')
         Val.append(obj.get_borrowid())
     if obj.get_userid() != '':
         Seq.append('userid')
         Val.append(obj.get_userid())
     if obj.get_bookid() != '':
         Seq.append('bookid')
         Val.append(obj.get_bookid())
     if obj.get_borrowdate() != '':
         Seq.append('borrowdate')
         Val.append(obj.get_borrowdate())
     if obj.get_presretdate() != '':
         Seq.append('presretdate')
         Val.append(obj.get_presretdate())
     if obj.get_actretdate() != '':
         Seq.append('actretdate')
         Val.append(obj.get_actretdate())
     Dict = dict(zip(Seq, Val))
     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]) + "'"
     Bor = mysql.getAll(sql)
     if Bor == False:
         print('no record')
     # Book = mysql.getAll(sql)
     # if Book == False:
     #     print('no record')
     # 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()
     show = MiddleLayer()
     tums = show.ShowBorrow(Bor)
     return tums
Esempio n. 20
0
 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'"
     sql = "select * from borrow where actretdate < '" + "1996-01-01" + "' and presretdate = '0000-00-00'"
     result = mysql.getAll(sql)
     mysql.dispose()
     if (result == False):
         print('no record')
         return 0
     show = MiddleLayer()
     tums = show.ShowBorrow(result)
     return tums
Esempio n. 21
0
 def GetBookByDis(self, desci):
     mysql = Mysql()
     Dis_List = mysql.getAll("select * from book where discipline = '" + desci + "'")
     # print("Classification of " + desci + ":\n")
     # print("bookid\tbookname\tauthor\tpages\tcollecttime\tversion\tmajor\tdiscipline\tisbn\tbooklanguage\tpublisher\tstatus\tabstract\tstack\tshelf\tfloor\tbookvalue")
     # if Dis_List:
     #     for row in Dis_List:
     #         print("%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%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],row[7],row[8],row[9],row[10],row[11],row[12],row[13],row[14],row[15],row[16]))
     mysql.dispose()
     if Dis_List == False:
         print('no record')
         return 0
     show = MiddleLayer()
     tums = show.ShowBook(Dis_List)
     return tums
Esempio n. 22
0
 def GetUserShow(self, id):
     mysql = Mysql()
     sql = "select * from user where userid = '" + id + "'"
     temp = mysql.getOne(sql)
     # print("%s\t\t%s\t\t%s\t\t%s\t\t%s\t\t%s\t\t%s\t\t%s\t\t%s" % (User[0], User[1], User[3], User[4], User[5], User[6], User[7], User[8], User[9]))
     # return User
     user = User()
     user.username = temp[1]
     user.password = temp[2]
     user.faculty = temp[3]
     user.department = temp[4]
     user.age = temp[5]
     user.gendar = temp[6]
     user.useridentity = temp[7]
     user.userconnection = temp[8]
     user.status = temp[9]
     return user
Esempio n. 23
0
 def GetAllBookRecord(self, num=0):
     mysql = Mysql()
     if (str(num) == '0'):
         sqlAll = "select * from book"
     else:
         sqlAll = "select * from book limit " + str(num)
     result = mysql.getAll(sqlAll)
     # print("bookid\tbookname\tauthor\tpages\tcollecttime\tversion\tmajor\tdiscipline\tisbn\tbooklanguage\tpublisher\tstatus\tabstract\tstack\tshelf\tfloor\tbookvalue")
     # if result :
     #     for row in result :
     # print("%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%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],row[7],row[8],row[9],row[10],row[11],row[12],row[13],row[14],row[15],row[16]))
     mysql.dispose()
     if result == False:
         print('no record')
         return 0
     show = MiddleLayer()
     tums = show.ShowBook(result)
     return tums
Esempio n. 24
0
 def GetBorrowRecord(self, num=0):
     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()
     if result == False:
         print('no record')
         return 0
     show = MiddleLayer()
     tums = show.ShowBorrow(result)
     return tums
 def InsertRecommandRecord(self, obj, id):
     recom = []
     recom.append(id)
     recom.append(obj.getbookname())
     recom.append(obj.getauthor())
     recom.append(obj.getpublisher())
     recom.append(obj.getversion())
     recom.append(obj.getrecomreason())
     recom.append('待定')
     reco = tuple(recom)
     List = []
     List.append(reco)
     mysql = Mysql()
     try:
         sql = "insert into recommand(userid,bookname,author,publisher,version,recomreason,statue)" + \
               "values(%s, %s, %s, %s, %s, %s, %s)"
         # sql2 = "select * from recommand where userid='" + id + "'"
         # print(sql2)
         # rs = mysql.getAll(sql2)
         # print(rs)
         # count = len(rs)
         # print(count)
         # sql3 = "select useridentity from user where userid = '" + id + "'"
         # print(sql3)
         # s = mysql.getOne(sql3)
         # print(s[0])
         # sql4 = "select * from usertype where useridentity = '" + s[0] + "'"
         # print(sql4)
         # result = mysql.getOne(sql4)
         # print(result[2])
         # if(count >= result[2]):
         #     print("can't more recommand")
         # else:
         mysql.insertMany(sql, List)
         mysql.end('commit')
         print("insert success!")
         return True
     except Exception as e:
         print(e)
         mysql.end(None)
     mysql.dispose()
     return False
    def GetRecommandRecord(self, Dict):
        mysql = Mysql()
        sql = "select * from recommand 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]) + "'"
        try:
            User = mysql.getAll(sql)
            if len(User) == 0:
                print("No recommand found!")
            else:
                print("The recommand you found:")
                if User:
                    for row in User:
                        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]))
        except Exception as e:
            print(e)
        mysql.dispose()

# 插入推荐
#test = RecommandAPI()
#user = Recommand()
#user.setbookname('哈哈p')
#user.setauthor('ni')
#user.setpublisher('ni')
#user.setversion('98')
#user.setrecomreason('good')
#test.InsertRecommandRecord(user, '11002')

# 查询推荐
#test = RecommandAPI()
#rs = test.GetRecommandShow('11000')
#for i in rs:
#    print(i.getbookname())
Esempio n. 27
0
    def GetBookByField(self, obj):
        # 拼接成Dict函数
        # 例:{'bookid': '100014', 'author': '韩强军'}
        Seq = []
        Val = []
        if obj.get_bookid() != '':
            Seq.append('bookid')
            Val.append(obj.get_bookid())
        if obj.get_bookname() != '':
            Seq.append('bookname')
            Val.append(obj.get_bookname())
        if obj.get_author() != '':
            Seq.append('author')
            Val.append(obj.get_author())
        if obj.get_pages() != '':
            Seq.append('pages')
            Val.append(obj.get_pages())
        if obj.get_collecttime() != '':
            Seq.append('collecttime')
            Val.append(obj.get_collecttime())
        if obj.get_version() != '':
            Seq.append('version')
            Val.append(obj.get_version())
        if obj.get_major() != '':
            Seq.append('major')
            Val.append(obj.get_major())
        if obj.get_discipline() != '':
            Seq.append('discipline')
            Val.append(obj.get_discipline())
        if obj.get_isbn() != '':
            Seq.append('isbn')
            Val.append(obj.get_isbn())
        if obj.get_booklanguage() != '':
            Seq.append('booklanguage')
            Val.append(obj.get_booklanguage())
        if obj.get_publisher() != '':
            Seq.append('publisher')
            Val.append(obj.get_publisher())
        if obj.get_status() != '':
            Seq.append('status')
            Val.append(obj.get_status())
        if obj.get_abstract() != '':
            Seq.append('abstract')
            Val.append(obj.get_abstract())
        if obj.get_stack() != '':
            Seq.append('stack')
            Val.append(obj.get_stack())
        if obj.get_shelf() != '':
            Seq.append('shelf')
            Val.append(obj.get_shelf())
        if obj.get_floor() != '':
            Seq.append('floor')
            Val.append(obj.get_floor())
        if obj.get_bookvalue() != '':
            Seq.append('bookvalue')
            Val.append(obj.get_bookvalue())

        Dict = dict(zip(Seq, Val))

        mysql = Mysql()
        sql = "select * from book 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 Book == False:
            print('no record')
            return 0
        # if len(Book) == 0:
        #     print("No books found!")
        # else:
        #     print("The book you found:")
        #     if Book:
        #         for row in Book:
        #
        #             print("%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%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],row[7],row[8],\
        #                  row[9],row[10],row[11],row[12],row[13],row[14],row[15],row[16]))
        mysql.dispose()
        show = MiddleLayer()
        tums = show.ShowBook(Book)
        return tums
Esempio n. 28
0
 def matchIsUser(self, userid, password):
     mysql = Mysql()
     sql = "select * from user where userid = '" + userid + "' and password = '******'"
     if (mysql.getOne(sql) != False):
         # print("login success!")
         return True