intern_system/mysql_internes.py

from tkinter import *
import pymysql
# 创建连接数据库interne的对象conn
conn = pymysql.connect(
    host='127.0.0.1',  # 数据库主机名
    port=3310,  # 数据库端口号
    user='root',  # 数据库用户名
    password='123456',  # 数据库密码
    autocommit=True  # 设置修改数据无需确认
)
# 获取游标对象
sort_interne = int(0)
sort_data = int(0)

cursor = conn.cursor()

# 创建数据库，若有则不创建
cursor.execute("create database if not exists interne;")
conn.select_db("interne") # 建立与数据库的连接

# 创建实习生信息表， 若有则不创建
cursor.execute("""CREATE TABLE IF NOT EXISTS internes(
    id int,
    name varchar(10),
    department varchar(10),
    sex varchar(10),
    wages int,
    hiredata varchar(20)
);""")


# 创建账号密码表，若有则不创建
cursor.execute("""CREATE TABLE IF NOT EXISTS admin_name_pwd(
    name varchar(10),
    pwd varchar(10)
);""")


# 判断登录的账号密码是否都正确
def check_login(uname, pwd):
    cursor.execute("select * from admin_name_pwd")
    results = cursor.fetchall()
    # print(results)
    for na, pd in results:
        if na == uname and pd == pwd:
            return True, '登录成功'
    return False, '登录失败,账号或密码错误'

# 添加正确注册的账号以及密码
def add_admin_name_pwd(uname, pwd):
    cursor.execute("insert into admin_name_pwd values('{0}', '{1}');".format(uname, pwd))
# add_admin_name_pwd("123", "123")

# 检验注册的账号名称是否已经存在
def check_usname(uname):
    cursor.execute("select count(*) from admin_name_pwd anp where name = '{0}';".format(uname))
    res = cursor.fetchall()
    if res[0][0]:
        return True
    return False


# 获取数据库中实习生所有信息，按给定的信息给出
# 通过全局变量sort_data以及sort_interne
# sort_interne 为0代表升序，为一代表降序
def all():
    if sort_interne == 1:
        if sort_data == 0:
            cursor.execute("select * from internes order by id;")
        elif sort_data == 1:
            cursor.execute("select * from internes order by sex;")
        elif sort_data == 2:
            cursor.execute("select * from internes order by wages;")
        elif sort_data == 3:
            cursor.execute("select * from internes order by hiredata;")
    else:
        if sort_data == 0:
            cursor.execute("select * from internes order by id desc;")
        elif sort_data == 1:
            cursor.execute("select * from internes order by sex desc;")
        elif sort_data == 2:
            cursor.execute("select * from internes order by wages desc;")
        elif sort_data == 3:
            cursor.execute("select * from internes order by hiredata desc;")
    data = cursor.fetchall()
    key = ('id', 'name', 'department', 'sex', 'wages', 'hiredata')
    jsonList = []
    # 通过数据得到的数据是元组类型，需要压缩成字典类型便于输出
    for i in data:
        jsonList.append(dict(zip(key, i)))
    return jsonList

# 查询录入的工号是否存在

def check_interne_id(id):
    cursor.execute("select count(*) from internes where id = '{0}';".format(id))
    res = cursor.fetchall()
    if res[0][0]:
        return False, "该工号已存在请重新输入"
    return True, '录入成功'


# 单独查询某个班级的成绩
def search_department(department_value):
    cursor.execute("select * from internes where department = '{0}';".format(department_value))
    data = cursor.fetchall()
    key = ('id', 'name', 'department', 'sex', 'wages', 'hiredata')
    jsonList = []
    # 通过数据得到的数据是元组类型，需要压缩成字典类型便于输出
    for i in data:
        jsonList.append(dict(zip(key, i)))
    return jsonList
# 插入一条实习生信息
def insert(ins):
    cursor.execute("insert into internes values('{0}', '{1}', '{2}','{3}', '{4}', '{5}');".
                   format(ins[0], ins[1], ins[2], ins[3], ins[4], ins[5]))

# 通过id来删除实习生信息
def delete_id(user_id):
    cursor.execute("select count(*) from internes where id = '{0}';".format(user_id))
    res = cursor.fetchall()
    if res[0][0]:
        cursor.execute("delete from internes where id = '{0}';".format(user_id))
        return True, '删除成功'
    else: return False, '工号为' + str(user_id) + '的实习生不存在'

# 通过名字来删除实习生信息
def delete_name(user_name):
    cursor.execute("select count(*) from internes where name = '{0}';".format(user_name))
    res = cursor.fetchall()
    # print(res)
    if res[0][0]:
        cursor.execute("delete from internes where name = '{0}';".format(user_name))
        return True, '删除成功'
    else: return False, '姓名为' + str(user_name) + '的实习生不存在'


# 通过id来查询实习生的信息
def search_id(user_id):
    cursor.execute("select count(*) from internes where id = '{0}';".format(user_id))
    res = cursor.fetchall()
    if res[0][0]:
        cursor.execute("select * from internes where id = '{0}';".format(user_id))
        ins = cursor.fetchall()
        return True, ins
    else:
        return False, '工号为' + str(user_id) + '的实习生不存在'

# 通过实习生姓名来查询剩余的信息
def search_name(user_name):
    cursor.execute("select count(*) from internes where name = '{0}';".format(user_name))
    res = cursor.fetchall()
    if res[0][0]:
        cursor.execute("select * from internes where name = '{0}';".format(user_name))
        ins = cursor.fetchall()
        return True, ins
    else:
        return False, '名字为' + str(user_name) + '的实习生不存在'

# 下面内容是初始化数据库，不过需要手动解开注释
tuple = (
    (1, '张三', '开发部', '男', 5000, '2023-01-01'),
    (2, '李四', '市场部', '女', 4500, '2022-12-15'),
    (3, '王五', '人事部', '男', 5500, '2022-11-05'),
    (4, '赵六', '财务部', '女', 5200, '2022-10-20'),
    (5, '孙七', '开发部', '男', 5300, '2022-09-10'),
    (6, '周八', '市场部', '女', 4800, '2022-08-15'),
    (7, '吴九', '人事部', '男', 5100, '2022-07-01'),
    (8, '郑十', '财务部', '女', 6000, '2022-06-20'),
    (9, '陈十一', '开发部', '男', 4000, '2022-05-15'),
    (10, '刘十二', '市场部', '女', 4200, '2022-04-01'),
    (11, '高十三', '人事部', '男', 5000, '2022-03-10'),
    (12, '林十四', '财务部', '女', 4800, '2022-02-20'),
    (13, '郭十五', '开发部', '男', 6500, '2022-01-15'),
    (14, '何十六', '市场部', '女', 6200, '2021-12-01'),
    (15, '罗十七', '人事部', '男', 4500, '2021-11-15'),
    (16, '宋十八', '财务部', '女', 4300, '2021-10-20'),
    (17, '蔡十九', '开发部', '男', 5500, '2021-09-10'),
    (18, '魏二十', '市场部', '女', 5200, '2021-08-15'),
    (19, '韩廿一', '人事部', '男', 6000, '2021-07-01'),
    (20, '薛廿二', '财务部', '女', 8000, '2021-06-20'))


# 往interne中加入信息，若有则不加入
for ins in tuple:
    if check_interne_id(ins[0])[0] == True:
        insert(ins)

# 加入初始账号，若有则不加入
if check_usname("root") == False:
    add_admin_name_pwd('root', 'root')