1. 编写stu表信息操作类:内有方法:构造方法实现数据库连接;析构方法关闭数据连接;
findAll( )--查询方法 、del(id)-- 删除方法 insert(data)--添加方法
2. 使用使用上面自定义stu表操作类,做出增,删,查询操作。
# -*- coding:utf-8 -*-
"""
@Time : 2018/6/8 16:38
@Author: Cai Jinzhi
@File : Student_Manage_System-mysql.py
"""
'''
import pymysql
db = pymysql.connect(host="localhost",user='root',password='fgh000561',db='cai_database',charset="utf8")
cursor = db.cursor()
#数据查询
sql = "select * from stulist where classid = '%s'" % ('python03')
try:
cursor.execute(sql)
print("本次查询条数:",cursor.rowcount)
alist = cursor.fetchall()
for i in alist:
print(i)
except Exception as error:
print("SQL执行错误,原因:",error)
#数据添加
data = ('python06','caio',25)
sql = "insert into stulist values('%s','%s','%d')" % data
try:
m = cursor.execute(sql)
db.commit()
print("成功操作条数:",m)
except Exception as error:
db.rollback()
print("SQL执行错误,原因:",error)
#数据删除
sql = "delete from stulist where classid='python06'"
try:
cursor.execute(sql)
db.commit() #提交事务
print("成功删除条数:",cursor.rowcount)
except Exception as error:
db.rollback() #事务回滚
print("SQL执行错误,原因:",error)
#关闭数据库
db.close()
'''
import pymysql
#定义stu表信息操作类
class Stu(object):
#构造方法实现数据库连接
def __init__(self):
try:
self.db = pymysql.connect(host="localhost",user='root',password='fgh000561',db='cai_database',charset="utf8")
self.cursor = self.db.cursor()
#try:
#self.sql = "show tables"
#self.cursor.execute(self.sql)
#self.list1 = self.cursor.fetchall()
#if ('stulist',) in self.list1:
#self.sql1 = "select * from stulist"
#self.cursor.execute(self.sql1)
#self.alist = self.cursor.fetchall()
#for i in self.alist:
#print(i)
except Exception as error:
self.db.rollback()
print("SQL连接失败,原因是:",error)
#定义一个查询学员信息方法
def Find_All(self):
try:
self.cursor.execute("select * from stulist")
self.alist =self.cursor.fetchall()
if self.cursor.rowcount=='0':
print("========== 没有学员信息可以输出!=============")
return
print("|{0:<3}| {1:<10}| {2:<3}| {3:<5}| {4:<10}|".format("id", "name", "sex","age", "classid"))
print("-" * 40)
for j in (self.alist):
print("|{0:<3}| {1:<10}| {2:<3}| {3:<5}| {4:<10}|".format(j[0],j[1],j[2],j[3],j[4]))
except Exception as error:
self.db.rollback()
print("SQL查询失败,原因是:", error)
#定义一个添加成员方法
def Insert_New(self,new_stu):
try:
self.cursor.execute("insert into stulist values(null,'{0}','{1}','{2}','{3}')".format(new_stu["name"],new_stu["sex"],new_stu["age"],new_stu["classid"]))
self.db.commit()
except Exception as error:
self.db.rollback()
print("SQL添加失败,原因是:",error)
#定义一个删除成员的方法
def Del_Data(self,id):
try:
self.cursor.execute("delete from stulist where id={}".format(id))
self.db.commit()
except Exception as error:
self.db.rollback()
print("SQL删除失败,原因是:",error)
#定义一个析构方法关闭数据库
def __del__(self):
self.cursor.close()
self.db.close()
#类操作对象
stu = Stu()
#程序主函数
while True:
# 输出初始界面
print("="*12,"学员管理系统","="*14)
print("{0:1} {1:13} {2:15}".format(" ","1. 查看学员信息","2. 添加学员信息"))
print("{0:1} {1:13} {2:15}".format(" ","3. 删除学员信息","4. 退出系统"))
print("="*40)
key = input("请输入对应的选择:")
# 根据键盘值,判断并执行对应的操作
if key == "1":
print("="*12,"学员信息浏览","="*14)
stu.Find_All()
input("按回车键继续:")
continue
elif key == "2":
print("="*12,"学员信息添加","="*14)
new_stu={}
new_stu['name']=input("请输入要添加的姓名:")
new_stu['sex']=input("请输入要添加的姓别:")
new_stu['age']=input("请输入要添加的年龄:")
new_stu['classid']=input("请输入要添加的班级号:")
stu.Insert_New(new_stu)
stu.Find_All()
input("按回车键继续:")
continue
elif key == "3":
print("="*12,"学员信息删除","="*14)
stu.Find_All()
id = int(input("请输入你要删除的信息id号:"))
stu.Del_Data(id)
stu.Find_All()
input("按回车键继续:")
continue
elif key == "4":
print("="*12,"再见","="*14)
break
else:
print("======== 无效的键盘输入! ==========")