python测试开发详细记录【第一期】(二)
图书管理系统
依赖封装函数
import pymysql
from pymysql.cursors import DictCursor
class MysqlHandler():
def __init__(
self,
host=None,
port=3306,
user=None,
password=None,
charset='utf8',
cursorclass=DictCursor,
database=None
):
self.conn = pymysql.connect(
host=host,
port=port,
user=user,
password=password,
charset=charset,
cursorclass=cursorclass,
database=database
)
self.cursor = self.conn.cursor()
def query(self, sql, one=True):
self.conn.commit() # 把最新的数据进行更新(提交事务。)
self.cursor.execute(sql)
self.conn.commit()#对于增删改的数据需要进行(提交事务)
if one:
return self.cursor.fetchone()
return self.cursor.fetchall()
def query_all(self):
pass
def close(self):
self.cursor.close()
self.conn.close()
if __name__ == '__main__':
pass
主函数(待优化)
from comm.mysql_handler import MysqlHandler
db = MysqlHandler(host="192.168.174.133", user="root", password="123456", port=3338, database="test")
def book():
while True:
print("""
---------------欢迎进入图书管理系统-------------
【1】:添加图书
【2】:修改图书
【3】:删除图书
【4】:查询图书
【5】:图书列表
【6】:出借图书
【7】:归还图书
【8】:退出
""")
choose=int(input("请输入选项:"))
if choose==8:
print("退出图书系统成功")
break
elif choose==1:
while True:
print("*****************添加图书*****************")
name=input("请输入书名:")
position=input("请输入图书位置:")
#链接数据库
#数据库中插入数据
sql="insert into books(name,position) values('{}','{}')".format(name,position)
data=db.query(sql)
print("图书添加成功")
choose2 = input("继续添加请输入1,回车退回主菜单")
if choose2=="1":
continue
else:
break
elif choose==2:
while True:
print("*****************修改图书*****************")
id=int(input("请输入修改图书的ID:"))
sql = "select * from books where id={} ".format(id)
data = db.query(sql)
print("当前数据为:{}".format(data))
rename=input("请重写输入书名:(不修输入回车)")
reposition=input("请重写数据位置:(不修改输入回车)")
if rename and reposition:
sql="update books set name='{}',position='{}' where id={}".format(rename,reposition,id)
db.query(sql)
elif rename:
sql = "update books set name='{}' where id={}".format(rename,id)
db.query(sql)
elif reposition:
sql = "update books set position='{}' where id={}".format(reposition, id)
db.query(sql)
else:
pass
print("图书修改成功")
choose2 = input("继续修改请输入1,回车退回主菜单")
if choose2 == "1":
continue
else:
break
elif choose==3:
while True:
print("*****************查阅图书*****************")
id = input("请输入书籍编号:")
if id:
sql = "delete from books where id='{}'".format(id)
data = db.query(sql)
else:
continue
print("书籍删除成功")
choose2 = input("继续请输入1,回车退回主菜单")
if choose2 == "1":
continue
else:
break
elif choose==4:
while True:
print("*****************查阅图书*****************")
name = input("请输入书名:")
if name:
sql = "select * from books where name='{}'".format(name)
data = db.query(sql)
else:
continue
print(data)
choose2 = input("继续请输入1,回车退回主菜单")
if choose2 == "1":
continue
else:
break
elif choose==5:
while True:
print("*****************图书列表*****************")
sql = "select * from books"
data = db.query(sql, one=False)
for dic in data:
print("编号:{}, 书名:{},位置:{},状态:{},借阅人:{}".format(dic['id'],dic['name'], dic['position'], dic['status'],dic['borrower']))
print("查看所有图书成功")
choose2 = input("继续请输入1,回车退回主菜单")
if choose2 == "1":
continue
else:
break
elif choose==6:
while True:
print("****************出借图书*****************")
id = input("请输入书籍编号:")
borrower = input("请输入借阅人名称:")
if id:
sql = "update books set status='{}',borrower='{}' where id={}".format("出库",borrower,id)
data = db.query(sql)
else:
continue
print("书籍借阅成功")
choose2 = input("继续请输入1,回车退回主菜单")
if choose2 == "1":
continue
else:
break
elif choose==7:
while True:
print("****************归还图书*****************")
id = input("请输入书籍编号:")
if id:
sql = "update books set status='{}',borrower='' where id={}".format("在库",id)
data = db.query(sql)
else:
continue
print("书籍归还成功")
choose2 = input("继续请输入1,回车退回主菜单")
if choose2 == "1":
continue
else:
break
if __name__ == "__main__":
book()