python基于wx的GUI教务系统（模板）.rar

"""用户信息管理系统：基于sqlite3""" import os import sqlite3 DBFILE = 'jwxt.db' # sqlite3数据库，全局变量 def get_create_db(db_filename): """打开本地数据库文件db_filename，并返回数据库连接con 如果本地数据库文件db_filename，在创建数据库和UserInfo表""" if os.path.exists(db_filename): con = sqlite3.connect(db_filename) else: con = sqlite3.connect(db_filename) # 在该数据库下创建用户信息表 sql_create_UserInfo = '''CREATE TABLE UserInfo ( USERID VARCHAR(20) PRIMARY KEY, USERNAME VARCHAR(20) NOT NULL, GENDER VARCHAR(2), BIRTHDAY VARCHAR(11), DEPARTMENT VARCHAR(50), PHONE VARCHAR(20), USERTYPE VARCHAR(2), PASSWORD VARCHAR(20) NOT NULL);''' con.execute(sql_create_UserInfo) sql_insert_UserInfo = '''INSERT INTO UserInfo VALUES ('J001','张教务','女','1988/5/2','物理系','13912345678','教务','123456');''' con.execute(sql_insert_UserInfo) con.commit() # 在该数据库下创建课程信息表 sql_create_COURSE = '''CREATE TABLE Course ( COURSEID VARCHAR (20) PRIMARY KEY, COURSENAME VARCHAR (20) NOT NULL, CREDIT INT, DESCRIPTION VARCHAR (100));''' # con.execute(sql_create_Student) # 在该数据库下创建教学班号表 sql_create_JXB = ''' CREATE TABLE JXB( JXBID VARCHAR(20) PRIMARY KEY, COURSEID VARCHAR(20) NOT NULL, USERID VARCHAR(20) NOT NULL, DESCRIPTION VARCHAR(100));''' # 在该数据库下创建学生成绩表 sql_create_JXB = ''' CREATE TABLE Grades ( JXBID VARCHAR (20), USERID VARCHAR (20), SCORE INT, PRIMARY KEY ( JXBID, USERID));''' return con # 返回数据库连接 def check_login(userid, password, usertype): """检查用户登录信息是否正确""" con = get_create_db(DBFILE) try: sql_pattern = '''SELECT USERNAME FROM UserInfo WHERE USERID="{0}" AND PASSWORD="{1}" AND USERTYPE="{2}"''' sql = sql_pattern.format(userid, password, usertype) cur = con.execute(sql) row = cur.fetchone() if row: r = tuple(row) return r[0] else: return False finally: con.close() def change_password(userid, password): """修改用户密码""" con = get_create_db(DBFILE) try: sql_pattern = '''UPDATE UserInfo SET PASSWORD="{1}" WHERE USERID="{0}"''' sql = sql_pattern.format(userid, password) con.execute(sql) con.commit() finally: con.close() ############用户管理######################################################################### def check_user_id(userid): """检查UserInfo中是否存在userid""" con = get_create_db(DBFILE) try: sql_pattern = '''SELECT USERID, USERNAME FROM UserInfo WHERE USERID="{0}"''' sql = sql_pattern.format(userid) cur = con.execute(sql) row = cur.fetchone() if row: return row[1] #返回用户名 else: return False finally: con.close() def get_user_list(user_type): """查找数据库UserInfo表，获取类型为user_type的用户信息列表""" con = get_create_db(DBFILE) try: sql_pattern = '''SELECT USERID, USERNAME, GENDER, DEPARTMENT, PHONE, BIRTHDAY FROM UserInfo WHERE USERTYPE="{0}"''' sql = sql_pattern.format(user_type) results = con.execute(sql) users = results.fetchall() user_list = [] for user in users: user_list.append(user) return user_list finally: con.close() def insert_user(usertype,userid, username, gender, birthday, department, phone): """插入一条记录到UserInfo表""" con = get_create_db(DBFILE) try: sql = '''INSERT INTO UserInfo(USERID, USERNAME, GENDER, BIRTHDAY, DEPARTMENT, PHONE,USERTYPE, PASSWORD) VALUES (?,?,?,?,?,?,?,?)''' con.execute(sql, (userid, username, gender, birthday, department, phone, usertype, '123456')) con.commit() finally: con.close() def update_user(userid, username, gender, birthday, department, phone): """更新一条记录到UserInfo表""" con = get_create_db(DBFILE) try: sql = '''UPDATE UserInfo SET USERNAME = ? ,GENDER = ? ,BIRTHDAY = ? ,DEPARTMENT = ? ,PHONE = ? WHERE USERID = ?''' con.execute(sql, (username, gender, birthday, department, phone, userid)) con.commit() finally: con.close() def delete_user(userid): """从UserInfo表中删除一条记录""" con = get_create_db(DBFILE) try: sql = '''DELETE FROM UserInfo WHERE USERID = ?''' con.execute(sql, (userid, )) con.commit() finally: con.close() ############课程管理######################################################################### def check_course_id(courseid): """检查Course表中是否存在courseid""" con = get_create_db(DBFILE) try: sql_pattern = '''SELECT COURSEID, COURSENAME FROM COURSE WHERE COURSEID="{0}"''' sql = sql_pattern.format(courseid) cur = con.execute(sql) row = cur.fetchone() if row: return row[1] #返回课程名称 else: return False finally: con.close() def get_course_list(): """查找数据库Course表，获取课程信息列表""" con = get_create_db(DBFILE) try: sql = '''SELECT COURSEID, COURSENAME, CREDIT, DESCRIPTION FROM Course''' results = con.execute(sql) courses = results.fetchall() course_list = [] for course in courses: course_list.append(course) return course_list finally: con.close() def insert_course(courseid, coursename, credit, description): """插入一条记录到Course表""" con = get_create_db(DBFILE) try: sql = '''INSERT INTO COURSE(COURSEID, COURSENAME, CREDIT,DESCRIPTION) VALUES (?,?,?,?)''' con.execute(sql, (courseid, coursename, credit, description)) con.commit() finally: con.close() def update_course(courseid, coursename, credit, description): """更新一条记录到COURSE表""" con = get_create_db(DBFILE) try: sql = '''UPDATE COURSE SET COURSENAME = ? ,CREDIT = ? ,DESCRIPTION = ? WHERE COURSEID = ?''' con.execute(sql, (coursename, credit, description, courseid)) con.commit() finally: con.close() def delete_course(courseid): """从COURSE表中删除一条记录""" con = get