员工信息表 实现增删改查操作

【项目要求】

可进行模糊查询，语法至少支持下面3种:

　　select name,age from staff_table where age > 22
　　select * from staff_table where dept = "IT"
　　select * from staff_table where enroll_date like "2013%%"

可修改员工信息，语法如下:
　　update staff_table set dept="Market"  where dept = "IT"

1. 支持至少三种方法的select查询，并在最后显示查询到的条数。
2. 创建新员工记录，以phone为唯一键，staff_id自增
3. 输入员工id可删除指定员工信息记录
4. 可以使用update命令修改指定员工信息。

 

方法一：利用数据库实现！

具体代码如下：

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,CHAR,VARCHAR,DATE
from sqlalchemy.orm import sessionmaker

#建立链接
engine = create_engine("mysql+pymysql://root:761379545@127.0.0.1:3306/test_db?charset=utf8", max_overflow=5)
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()
class Emp(Base):
    __tablename__ = 'staff_table'
    id = Column(Integer,primary_key=True,autoincrement=True)
    name = Column(VARCHAR(20),nullable=False)
    age = Column(Integer,nullable=True)
    phone = Column(CHAR(11),unique=True)
    dept = Column(VARCHAR(255))
    enroll_date = Column(CHAR(20))
# #创建表格
# Base.metadata.create_all(engine)

def search_user():
    msg ='''
可进行模糊查询，语法至少支持下面3种:
　　select name,age from staff_table where age > 22
　　select * from staff_table where dept = "IT"
　　select * from staff_table where enroll_date like('2013%%')
    '''
    while 1:
        print(msg)
        cmd = input('请输入要进行操作的语句【返回B】：
>>>').strip()
        if not cmd:continue
        if cmd.lower() == 'b':
            break
        try:
            data = engine.execute(cmd)
            res= data.fetchall()
            if not res:
                print('没有满足条件的员工！')
            else:
                for row in res:
                    print(row)
        except Exception:
            print('请输入正确的查询语句！')
            continue

def add_user():
    '''
    新增员工信息
    '''
    while 1:
        user_name = input('请输入员工名【返回B】：').strip()
        if user_name.lower() == 'b':
            break
        user_age = input('请输入年龄：').strip()
        user_age_int = int(user_age)
        user_phone = input('请写入手机号：').strip()
        user_dept = input('所属部门').strip()
        user_enroll_date = input('入职年月日【年-月-日】：').strip()
        try:
            session.add(Emp(name=user_name,age=user_age_int,phone=user_phone,dept=user_dept,enroll_date=user_enroll_date))
            session.commit()
            print('员工添加成功！')
        except Exception:
            print('员工添加失败！')
            continue

def modify_user():
    msg = '''
可修改员工信息，语法如下:
　　update staff_table set dept="Market"  where dept = "IT"
    '''
    while 1:
        print(msg)
        cmd = input('请输入要进行操作的语句【返回B】：
>>>').strip()
        if not cmd: continue
        if cmd.lower() == 'b':
            break
        try:
            engine.execute(cmd)
            print('修改成功！')
        except Exception:
            print('请输入正确的查询语句！')
            continue

def delete_user():
    '''
    删除员工信息
    '''
    while 1:
        del_id = input('请输入您要删除的员工ID【返回B】:').strip()
        if not del_id:continue
        if del_id.lower() == 'b':
            break
        if del_id.isdigit():
            del_id_int = int(del_id)
            try:
                session.query(Emp).filter(Emp.id==del_id_int).delete()
                session.commit()
                print('删除成功！')
            except Exception:
                print('删除失败！')
                continue

#主程序
if __name__ == '__main__':
    print('欢迎进入员工信息操作界面！'.center(23,'*'))
    while 1:
        choice = input('请输入想要进行的操作代码：'
                       '
	【1】查询员工信息'
                       '
	【2】新增员工信息'
                       '
	【3】修改员工信息'
                       '
	【4】删除员工信息'
                       '
	【Q】退出操作'
                       '
>>>').strip()
        if choice == '1':
            search_user()
        elif choice == '2':
            add_user()
        elif choice == '3':
            modify_user()
        elif choice == '4':
            delete_user()
        elif choice.lower() == 'q':
            print('退出成功，感谢您的使用！'.center(22,'*'))
            break
        else:
            print('您的操作有误，请重新选择！')

方法二：函数加文件读写实现（有待完善）！

import os
def inquiry_system():
    welcome ='''
    -----------员工信息查询系统------------
    操作说明：
            增加ADD:1
            删除DELETE:2
            修改CHANGE:3
            查询SEARCH:4
            退出QUIT:5
    ---------------------------------------
    '''
    print(welcome)

def add():
    # 添加语法： name,age,phone,dept,enroll-date
    add_staff =input('请输入要添加的员工信息：(添加格式： name,age,phone,dept(大写）,enroll-date)
')
    list_data =add_staff.split(',')
    list_all =[]
    f = open('staff_table.txt','r+',encoding='utf8')
    for line in f:
        list_all.append(line.strip().split(',')[3])
    if list_data[2] in list_all:
        print('用户已存在！')
        f.close()
    else:
        for line in f:
            f.write(line)
        staff_id = str(len(list_all) + 1)
        list_data.insert(0, str(staff_id))
        f.write('
')
        f.write(','.join(list_data))
        f.close()
        print('添加成功')

def delete():
    del_staff_id =input('请输入要删除的员工ID：')
    f = open('staff_table.txt','r',encoding='utf8')
    f1 = open('new_staff_table.txt','w',encoding='utf8')
    for line in f:
        each_line = line.split(',')
        if int(each_line[0]) != int(del_staff_id):
            f1.write(line)
        #若想实现删除后员工ID顺序排列使用下三行代码！
        # elif int(each_line[0]) > int(del_staff_id):
        #     each_line[0] =str(int(each_line[0])-1)
        #     f1.write(','.join(each_line))
        else:
            continue
    f.close()
    f1.close()
    os.remove('staff_table.txt') #删除指定目录
    os.rename('new_staff_table.txt', 'staff_table.txt') #重命名

def change():
    # 修改请输入（注意空格和没有引号）：UPDATE staff_table SET dept = IT where dept = MARKET
    data = input("请输入您要修改的信息：(修改格式：UPDATE staff_table SET dept = IT where dept = MARKET)
")
    old = data.split(' ')[5]
    new = data.split(' ')[9]
    f = open('staff_table.txt', 'r', encoding='utf-8')
    f1 = open('new_staff_table.txt', 'w', encoding="utf-8")
    for line in f:
        if old in line:
            line = line.replace(old, new)
        f1.write(line)
    f.close()
    f1.close()
    os.remove('staff_table.txt')
    os.rename('new_staff_table.txt', 'staff_table.txt')
    print('修改成功')

def search():
    formation='''
    查询方式1：（按年龄查询）select * from staff_table where age >= 22
    查询方式2：（按职业查询）select * from staff_table where dept = "IT"
    查询方式3：（按年份查询）select * from staff_table where enroll_date like "2013"
    '''
    print(formation)

    while True:
        search_way = input('请输入您的查询方式(退出Q)：')
        res = []
        count = 0
        if search_way.lower() =='q':
            exit()
        if search_way =='1':
            age_search = input('请输入要查询的最小年龄：')
            with open('staff_table.txt','r',encoding='utf8') as f:
                for line in f:
                    each_line =line.split(',')
                    if int(each_line[2]) >=int(age_search):
                        res.append(line)
                        count += 1
            print("查询的结果为：")
            for per in res:
                print(per)
            print('共有%s个员工满足查询信息条件!'%count)
            break
        if search_way =='2':
            dept_search = input('请输入要查询的职业：')
            with open('staff_table.txt', 'r', encoding='utf8') as f:
                for line in f:
                    each_line = line.split(',')
                    res.append(each_line)
                result =filter(lambda l:l[4]==dept_search.upper(),res)
                for i in result:
                    count+=1
                    print(i)
                print('共有%s个员工满足查询信息条件!'%count)
                break
        if search_way =='3':
            enroll_date = input('请输入要查询的年份：')
            with open('staff_table.txt', 'r', encoding='utf8') as f:
                for line in f:
                    each_line = line.split(',')
                    res.append(each_line)
                result = filter(lambda l:enroll_date in l[5],res)
                for i in result:
                    count += 1
                    print(i)
                print('共有%s个员工满足查询信息条件!' %count)
                break
        else:
            print('请重新输入！')

my_choice = {
    '1':add,
    '2':delete,
    '3':change,
    '4':search,
    '5':'quit'
}
while True:
    inquiry_system()
    choice = input('请输入操作序号：')
    if choice not in my_choice:
        print("输入错误，请重新输入！")
        continue
    if int(choice) == 5:
        exit()
    else:
        my_choice[choice]()