"""用户信息管理系统：基于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
python基于wx的GUI教务系统（模板）.rar
需积分: 0 159 浏览量
2020-12-26
13:03:35
上传
评论 6
收藏 68KB RAR 举报
python基于wx的GUI教务系统.rar （34个子文件） 
教务系统 
ui_main.py 5KB ui_change_password.py 2KB __pycache__ ui_user.cpython-37.pyc 7KB ui_jxb.cpython-37.pyc 7KB ui_main.cpython-36.pyc 4KB ui_change_password.cpython-37.pyc 2KB ui_teacher.cpython-37.pyc 5KB ui_student.cpython-37.pyc 7KB data.cpython-36.pyc 12KB ui_teacher.cpython-36.pyc 5KB ui_course.cpython-37.pyc 6KB ui_student.cpython-36.pyc 6KB ui_login.cpython-36.pyc 2KB ui_change_password.cpython-36.pyc 2KB ui_main.cpython-37.pyc 4KB data.cpython-37.pyc 12KB ui_user.cpython-36.pyc 7KB ui_jxb.cpython-36.pyc 7KB ui_course.cpython-36.pyc 6KB ui_login.cpython-37.pyc 2KB ui_course.py 8KB ui_user.py 10KB jwxt.db 44KB .idea misc.xml 191B modules.xml 260B workspace.xml 14KB jwxt.iml 437B ui_teacher.py 6KB jwxt_main.py 278B ui_jxb.py 11KB ui_student.py 9KB ui_user_bak.py 6KB ui_login.py 3KB data.py 14KB
评论0