python学生管理系统连接数据库版，很详细，这个是用函数版的增删改查，拿去用，不谢。...

最新推荐文章于 2024-08-23 11:33:24 发布

weixin_30566063

最新推荐文章于 2024-08-23 11:33:24 发布

阅读量565

点赞数

文章标签：数据库 python

原文链接：http://www.cnblogs.com/nicholas7464/p/10257492.html

版权

# 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','\t\t', '姓名''\t\t', '性别''\t\t', '年龄''\t\t', '电话')
    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}\t\t\t{student[1]}\t\t\t{student[2]}\t\t\t{student[3]}\t\t\t{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','\t\t', '姓名''\t\t', '性别''\t\t', '年龄''\t\t', '电话')
    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}\t\t\t{student[1]}\t\t\t{student[2]}\t\t\t{student[3]}\t\t\t{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 * 高。

转载于:https://www.cnblogs.com/nicholas7464/p/10257492.html