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