Python学员信息管理系统mysql

	 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("======== 无效的键盘输入！ ==========")