# coding: utf-8
import sqlite3
# 导入数据库
def connect_sql():
connect = sqlite3.connect("test_sqlite.db")
cursor = connect.cursor()
cursor.execute("""
CREATE TABLE studentsab51
(
id INTEGER PRIMARY KEY,
name TEXT,
sex TEXT,
age INTEGER,
phone TEXT
);
""")
connect.commit()
cursor.close()
connect.close()
# 展示学生列表
def show_students():
print('ID',' ', '姓名'' ', '性别'' ', '年龄'' ', '电话')
print('---'*20)
connect = sqlite3.connect("test_sqlite.db")
cursor = connect.cursor()
cursor.execute("""
SELECT * FROM students;
""")
students_list = cursor.fetchall()
for index, student in enumerate(students_list):
print(f'{index+1} {student[1]} {student[2]} {student[3]} {student[4]}')
connect.commit()
cursor.close()
connect.close()
# 添加学生
def add_student():
name = input('新学生姓名：')
sex = input('新学生性别：')
age = input('新学生年龄：')
phone = input('新学生电话')
connect = sqlite3.connect("test_sqlite.db")
cursor = connect.cursor()
# sql=("""
# INSERT INTO students(name, sex, age, phone) VALUES ("%s", "%s",%s, "%s")
# """%(name, sex, age , phone))
sql = f"""
INSERT INTO students (name, sex, age, phone) VALUES ("{name}","{sex}",{age},"{phone}")
"""
print(sql)
cursor.execute(sql)
connect.commit()
connect.close()
# 修改
def update_student():
show_students()
connect = sqlite3.connect("test_sqlite.db")
cursor = connect.cursor()
new_id = input("请输入你想要修改的编号:")
new_name=input('请输入修改后的姓名:')
new_sex=input('请输入修改后的性别：')
new_age = input('请输入新的年龄：')
new_phone = input('请输入新电话')
# 先查询输入的学生id是否存在，存在的话更新，不存在的给出用户提示
sql = f"""
SELECT 1 FROM students WHERE id="{new_id}";
"""
cursor.execute(sql)
student = cursor.fetchall()
if student:
sql2 = f"""
UPDATE students SET name = "{new_name}",sex="{new_sex}",age="{new_age}",phone="{new_phone}" WHERE id="{new_id}";
"""
cursor.execute(sql2)
connect.commit()
else:
print('学生不存在，请重新操作。')
connect.close()
print('学生修改成功')
# 下面的方法也行 看你用哪种
# cursor.execute("""UPDATE students SET name=?, sex= ?,age=?,phone=? WHERE id="""+new_id,(new_name, new_sex, new_age, new_phone))
# connect.commit()
# connect.close()
# 删除
def delete_student():
connect = sqlite3.connect("test_sqlite.db")
cursor = connect.cursor()
print(""" 删除> 请输入子操作编号：
1）按学生编号删除
2）删除全部学生（clear）
""")
sub_select = int(input('请选择子操作：'))
if sub_select == 1:
show_students()
stu_name = input('要删除的学生姓名：')
delete_name = f""" delete from students where name='{stu_name}'"""
cursor.execute(delete_name)
connect.commit()
connect.close()
print('删除成功')
elif sub_select == 2:
connect = sqlite3.connect("test_sqlite.db")
cursor = connect.cursor()
confirm = input('要删除全部学生？( Y/y):')
if confirm == 'y':
clear_as = f"""DELETE FROM main.students;"""
cursor.execute(clear_as)
connect.commit()
connect.close()
print('删除全部成功')
def main():
# 主函数，程序入口
while True:
print("""
欢迎使用学生管理系统
1-查看学员姓名
2-添加学员姓名
3-修改学员姓名
4-删除学员姓名
0-退出程序
""")
num = int(input('请输入操作编号：'))
if num == 1:
show_students()
elif num == 2:
add_student()
elif num == 3:
update_student()
elif num == 4:
delete_student()
elif num == 0:
break
if __name__ == '__main__':
main()
# 可能出现的错误：
# 插入功能
# sql = """
# INSERT INTO students (name, sex, age, phone) VALUES (%s, %s, %d, %s);
# """ % (name, sex, int(age), phone)
# print(sql)
# 报错 sqlite3.OperationalError: no such column: aaa
# 原因 sql INSERT INTO students (name, sex, age, phone) VALUES (aaa, nan, 13, 13000); 值并不是sql解释器理解的字符串。
# 解决 %s两侧加引号。 INSERT INTO students (name, sex, age, phone) VALUES ("%s", %s, %d, %s) % (name, sex, int(age), phone);
# sql补充
# SELECT 1 FROM students WHERE name="{stu_name}"; -- 只关心一行数据是否存在。效率比select * 高。
# coding: utf-8
import sqlite3
# 导入数据库
def connect_sql():
connect = sqlite3.connect("test_sqlite.db")
cursor = connect.cursor()
cursor.execute("""
CREATE TABLE studentsab51
(
id INTEGER PRIMARY KEY,
name TEXT,
sex TEXT,
age INTEGER,
phone TEXT
);
""")
connect.commit()
cursor.close()
connect.close()
# 展示学生列表
def show_students():
print('ID',' ', '姓名'' ', '性别'' ', '年龄'' ', '电话')
print('---'*20)
connect = sqlite3.connect("test_sqlite.db")
cursor = connect.cursor()
cursor.execute("""
SELECT * FROM students;
""")
students_list = cursor.fetchall()
for index, student in enumerate(students_list):
print(f'{index+1} {student[1]} {student[2]} {student[3]} {student[4]}')
connect.commit()
cursor.close()
connect.close()
# 添加学生
def add_student():
name = input('新学生姓名：')
sex = input('新学生性别：')
age = input('新学生年龄：')
phone = input('新学生电话')
connect = sqlite3.connect("test_sqlite.db")
cursor = connect.cursor()
# sql=("""
# INSERT INTO students(name, sex, age, phone) VALUES ("%s", "%s",%s, "%s")
# """%(name, sex, age , phone))
sql = f"""
INSERT INTO students (name, sex, age, phone) VALUES ("{name}","{sex}",{age},"{phone}")
"""
print(sql)
cursor.execute(sql)
connect.commit()
connect.close()
# 修改
def update_student():
show_students()
connect = sqlite3.connect("test_sqlite.db")
cursor = connect.cursor()
new_id = input("请输入你想要修改的编号:")
new_name=input('请输入修改后的姓名:')
new_sex=input('请输入修改后的性别：')
new_age = input('请输入新的年龄：')
new_phone = input('请输入新电话')
# 先查询输入的学生id是否存在，存在的话更新，不存在的给出用户提示
sql = f"""
SELECT 1 FROM students WHERE id="{new_id}";
"""
cursor.execute(sql)
student = cursor.fetchall()
if student:
sql2 = f"""
UPDATE students SET name = "{new_name}",sex="{new_sex}",age="{new_age}",phone="{new_phone}" WHERE id="{new_id}";
"""
cursor.execute(sql2)
connect.commit()
else:
print('学生不存在，请重新操作。')
connect.close()
print('学生修改成功')
# 下面的方法也行 看你用哪种
# cursor.execute("""UPDATE students SET name=?, sex= ?,age=?,phone=? WHERE id="""+new_id,(new_name, new_sex, new_age, new_phone))
# connect.commit()
# connect.close()
# 删除
def delete_student():
connect = sqlite3.connect("test_sqlite.db")
cursor = connect.cursor()
print(""" 删除> 请输入子操作编号：
1）按学生编号删除
2）删除全部学生（clear）
""")
sub_select = int(input('请选择子操作：'))
if sub_select == 1:
show_students()
stu_name = input('要删除的学生姓名：')
delete_name = f""" delete from students where name='{stu_name}'"""
cursor.execute(delete_name)
connect.commit()
connect.close()
print('删除成功')
elif sub_select == 2:
connect = sqlite3.connect("test_sqlite.db")
cursor = connect.cursor()
confirm = input('要删除全部学生？( Y/y):')
if confirm == 'y':
clear_as = f"""DELETE FROM main.students;"""
cursor.execute(clear_as)
connect.commit()
connect.close()
print('删除全部成功')
def main():
# 主函数，程序入口
while True:
print("""
欢迎使用学生管理系统
1-查看学员姓名
2-添加学员姓名
3-修改学员姓名
4-删除学员姓名
0-退出程序
""")
num = int(input('请输入操作编号：'))
if num == 1:
show_students()
elif num == 2:
add_student()
elif num == 3:
update_student()
elif num == 4:
delete_student()
elif num == 0:
break
if __name__ == '__main__':
main()
# 可能出现的错误：
# 插入功能
# sql = """
# INSERT INTO students (name, sex, age, phone) VALUES (%s, %s, %d, %s);
# """ % (name, sex, int(age), phone)
# print(sql)
# 报错 sqlite3.OperationalError: no such column: aaa
# 原因 sql INSERT INTO students (name, sex, age, phone) VALUES (aaa, nan, 13, 13000); 值并不是sql解释器理解的字符串。
# 解决 %s两侧加引号。 INSERT INTO students (name, sex, age, phone) VALUES ("%s", %s, %d, %s) % (name, sex, int(age), phone);
# sql补充
# SELECT 1 FROM students WHERE name="{stu_name}"; -- 只关心一行数据是否存在。效率比select * 高。