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
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()
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()
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
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
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
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
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
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]
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()
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
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
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
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
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
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
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
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())
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
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