import sqlite3
# 1.连接数据库
def connect_sql():
return sqlite3.connect('students.db')
# 创建表
def create_table():
# 1.连接数据库
conn = connect_sql()
# 2.获取游标
cursor = conn.cursor()
# 3.准备sql语句
sql = 'CREATE TABLE IF NOT EXISTS students(s_id INTEGER PRIMARY KEY ,name CHAR , phone INTEGER )'
# 4.执行sql语句
cursor.execute(sql)
# 5.关闭游标
cursor.close()
# 6.关闭数据库连接
conn.close()
# 添加学员函数
def add():
# 1.连接数据库
conn = connect_sql()
cursor = conn.cursor()
# 2.输入学员信息
while True:
try:
s_id = int(input('* 请输入学员学号:'))
name = input('* 请输入学员姓名:')
phone = int(input('* 请输入学员电话:'))
except Exception as e:
print('* 输入的学员信息不合法,请重新输入!')
# continue 跳过当前次循环,继续执行下一次循环
continue
try:
# 3.拼接sql语句
sql = "INSERT INTO students(s_id,name,phone)VALUES({},'{}',{})".format(s_id, name, phone)
# 4.执行sql
cursor.execute(sql)
# 5.提交操作
conn.commit()
except sqlite3.IntegrityError as e:
print('* 您输入的数据已存在,请检查后重新输入!')
continue
is_next = input('* 按回车继续,输入q结束:')
if is_next == 'q':
break
# 6.关闭游标
cursor.close()
# 7.关闭数据库
conn.close()
# 查询所有数据
def query_all():
# 1.连接数据库
conn = connect_sql()
# 2.获取游标
cursor = conn.cursor()
# 3.准备sql语句
sql = 'SELECT * FROM students'
# 4.执行sql语句,接收查询结果
result = cursor.execute(sql)
# 遍历查询结果
for stu in result:
print('* 学号:{:>6} 姓名:{} 电话:{}'.format(stu[0], stu[1], stu[2]))
# 5.关闭游标
cursor.close()
# 6.关闭数据库连接
conn.close()
# 修改学员信息
def update_stu():
# 1.查询所有学员信息
query_all()
# 2.连接数据库
conn = connect_sql()
# 3.获取游标
cursor = conn.cursor()
while True:
try:
# 4.选择要修改的学号
s_id = int(input('* 请输入要修改的学员学号:'))
except Exception as e:
print('* 输入的学号不合法,请重新输入!')
continue
else:
break
# 5.检测输入的学号是否存在
# COUNT() 统计符合条件的数据个数
sql = 'SELECT COUNT(*),s_id,name,phone FROM students WHERE s_id={}'.format(s_id)
result = cursor.execute(sql)
for x in result:
# x就是查询到的结果
if x[0] == 0:
print('* 您输入的学号不存在,请检查后重新输入!')
continue
else:
name = input('* 请输入修改后的姓名({}):'.format(x[2]))
# 检测输入的电话是否合法
while True:
try:
phone = int(input('* 请输入修改后的电话({}):'.format(x[3])))
except Exception as e:
print('* 您输入的电话不合法,请检查后重新输入!')
continue
else:
break
# 准备sql语句
sql = "UPDATE students SET name='{}',phone={} WHERE s_id={}".format(name, phone, s_id)
# 执行sql语句
cursor.execute(sql)
conn.commit()
cursor.close()
conn.close()
break
# 删除学员信息
def delete_stu():
print('* a.删除所有信息')
print('* b.根据学号删除')
conn = connect_sql()
cursor = conn.cursor()
select = input('* 请选择删除方式:')
while select != 'a' and select != 'b':
select = input('* 选项有误,请重选:')
if select == 'a':
is_del = input('* 确定要删除所有信息?y/n:')
if is_del == 'y':
cursor.execute('DELETE FROM students')
conn.commit()
cursor.close()
conn.close()
else:
# 1.展示所有学员信息
query_all()
# 2.选择要删除的学号
while True:
s_id = int(input('* 请输入要删除的学员学号:'))
# 3.判断学号是否存在
result = cursor.execute("SELECT COUNT(*),s_id,name,phone FROM students WHERE s_id={}".format(s_id))
for x in result:
if x[0] == 0:
print('您输入的学号不存在,请检查后重新输入!')
break
else:
is_del = input('* 确定要删除({})y/n:'.format(x[2]))
if is_del == 'y':
# 执行删除的sql语句
cursor.execute('DELETE FROM students WHERE s_id={}'.format(s_id))
print('删除成功!')
conn.commit()
cursor.close()
conn.close()
# retuen结束整个函数的执行
return
def run():
create_table()
while True:
print('* 1.添加学员')
print('* 2.修改学员')
print('* 3.查询学员')
print('* 4.删除学员')
print('* 0.退出程序')
# 用来检测输入的选项是否正确
while True:
select = input('* 请选择您的操作:')
try:
select = int(select)
except Exception as e:
print('* 选项有误,请重选!')
else:
if select < 0 or select > 4:
print('* 选项有误,请重选!')
else:
# 结束循环
break
if select == 1:
add()
elif select == 2:
update_stu()
elif select == 3:
query_all()
elif select == 4:
delete_stu()
else:
print('* 感谢您的使用,下次再会!')
break
run()