作业:
现要求你写一个简单的员工信息增删该查程序,需求如下:
当然此表在文件存储时可以这样表示
1,Alex Li,22,13651054608,IT,2013-04-01
2,Jack Wang,28,13451024608,HR,2015-01-07
3,Rain Wang,21,13451054608,IT,2017-04-01
4,Mack Qiao,44,15653354208,Sales,2016-02-01
5,Rachel Chen,23,13351024606,IT,2013-03-16
6,Eric Liu,19,18531054602,Marketing,2012-12-01
7,Chao Zhang,21,13235324334,Administration,2011-08-08
8,Kevin Chen,22,13151054603,Sales,2013-04-01
9,Shit Wen,20,13351024602,IT,2017-07-03
10,Shanshan Du,26,13698424612,Operation,2017-07-02
1.可进行模糊查询,语法至少支持下面3种查询语法:
find name,age from staff_table where age > 22
find * from staff_table where dept = "IT"
find * from staff_table where enroll_date like "2013"
2.可创建新员工纪录,以phone做唯一键(即不允许表里有手机号重复的情况),staff_id需自增
语法: add staff_table Alex Li,25,134435344,IT,2015-10-29
3.可删除指定员工信息纪录,输入员工id,即可删除
语法: del from staff_table where id=3
4.可修改员工信息,语法如下:
UPDATE staff_table SET dept="Market" WHERE dept = "IT" 把所有dept=IT的纪录的dept改成Market
UPDATE staff_table SET age=25 WHERE name = "Alex Li" 把name=Alex Li的纪录的年龄改成25
5.以上每条语名执行完毕后,要显示这条语句影响了多少条纪录。 比如查询语句 就显示 查询出了多少条、修改语句就显示修改了多少
import os import re def create_staff_table(): ''' 1.如果表存在就加载表,不存在就创建信息表 2.正常打印表 :return: ''' msg = [ '1,Alex Li,22,13651054608,IT,2013-04-01', '2,Jack Wang,28,13451024608,HR,2015-01-07', '3,Rain Wang,21,13451054608,IT,2017-04-01', '4,Mack Qiao,44,15653354208,Sales,2016-02-01', '5,Rachel Chen,23,13351024606,IT,2013-03-16', '6,Eric Liu,19,18531054602,Marketing,2012-12-01', '7,Chao Zhang,21,13235324334,Administration,2011-08-08', '8,Kevin Chen,22,13151054603,Sales,2013-04-01', '9,Shit Wen,20,13351024602,IT,2017-07-03', '10,Shanshan Du,26,13698424612,Operation,2017-07-02', ] if not os.path.isfile('staff_table'): with open('staff_table', 'w+', encoding='utf-8') as f: for i in msg: f.write(i+'\n') print('初始化文件已完成') else: with open('staff_table', 'r', encoding='utf-8') as f:print('+-----------staff_table------------+\n'+f.read()+'+-----------end------------+') def add_info(): ''' 增加用户信息 1.phone必须是唯一值 2.id自增 3.语法 add staff_table Alex Li,25,134435344,IT,2015-10-29 :return: ''' add_in = input('增加语法如下\nadd staff_table Alex Li,25,134435344,IT,2015-10-29\n请输入增加命令>>').strip().split(',') if add_in[0].split(' ')[0] == 'add' and add_in[0].split(' ')[1] == 'staff_table': add_out = ' '.join(add_in[0].split(' ')[2:]) + ',' + ','.join(add_in[1:]) with open('staff_table', 'a+', encoding='utf-8') as f: phone_list = [] f.seek(0) for i in f: phone_list.append(i.strip().split(',')[3]) if add_in[2] not in phone_list: staff_id = int(i.strip().split(',')[0]) + 1 f.write(str(staff_id) + ',' + add_out + '\n') print('1条记录已新增!') else: print('该手机号已经注册了!') else: print('错误的命令') def del_info(): ''' 删除用户信息 1.只能根据用户的id删除 2.语法 del from staff where id=x (满足条件:1.语法验证正确;) 【bug】:删除不存在id也不提示错误信息 :return: ''' del_in = input('删除语法如下\ndel from staff where id=x\n请输入删除命令>>').strip().split(' ') if del_in[0] + del_in[1] + del_in[2] + del_in[3] + del_in[4] == 'delfromstaffwhereid=' + re.split('=', del_in[4])[1]:#验证语法是否正确 old_file = open('staff_table','r+',encoding='utf-8') new_file = open('staff_table1','w',encoding='utf-8') for i in old_file: line = i.strip().split(',') if re.split('=', del_in[4])[1] != line[0]: new_file.write(i) new_file.close() old_file.close() os.remove('staff_table') os.rename('staff_table1','staff_table') else: print('错误的语法') def change_info(): ''' 修改语法: UPDATE staff_table SET dept="Market" WHERE dept = "IT" 把所有dept=IT的纪录的dept改成Market UPDATE staff_table SET age=25 WHERE name = "Alex Li" 把name=Alex Li的纪录的年龄改成25 1.验证语法 2.新建文件并修改msg 3.重写文件并删除原文件 ----------------------- 0.找到所有输入中查找的值 1.拿到输入的值 2.替换原值 3.存储变更的数据(按索引) 4.排序后覆盖原文件 :return: ''' change_in = input('更新语法如下\nUPDATE staff_table SET dept="Market" WHERE dept = "IT"\n' 'UPDATE staff_table SET age=25 WHERE name = "Alex Li"\n请输入更新命令>>').strip().split(' ') dept_list = [] # 查询到的数据放这个列表 dept_list_left = [] # 没查到的数据放这个列表 dept_list_temp = [] # 我都忘这个干啥用的.. if change_in[0] == 'UPDATE'.upper() and change_in[1] == 'staff_table' \ and change_in[2] == 'SET'.upper() and change_in[4] == 'WHERE'.upper() and change_in[6] == 'dept': change_in_dept_set = re.split('=', change_in[3])[1].replace('"', '') # 1.拿到输入的Market change_in_dept_file = change_in[8].replace('"', '') # 1.拿到输入的IT f = open('staff_table', 'r+', encoding='utf-8') n_f = open('staff_table_new', 'w', encoding='utf-8') for line in f: dept_list_temp.append(line.strip()) for l in dept_list_temp: if change_in_dept_file == l.strip().split(',')[4]: l1 = l.replace(l.strip().split(',')[4], change_in_dept_set) dept_list.append(l1) else: dept_list_left.append(l) res = dept_list + dept_list_left for lines in res: n_f.write(lines + '\n') f.close() n_f.close() os.remove('staff_table') os.rename('staff_table_new', 'staff_table') print('\033[31;1m%s\033[0m条数据受到影响!' % len(set(dept_list))) elif change_in[0] == 'UPDATE'.upper() and change_in[1] == 'staff_table' \ and change_in[2] == 'SET'.upper() and change_in[4] == 'WHERE'.upper() and change_in[6] == 'name': f = open('staff_table', 'r+', encoding='utf-8') n_f = open('staff_table_new', 'w', encoding='utf-8') change_in_dept_set = re.split('=', change_in[3])[1] # 1.拿到输入的age change_in_dept_file = ' '.join(change_in[8:]).replace('"', '') # 2.拿到输入的name for line in f: dept_list_temp.append(line.strip()) for l in dept_list_temp: if change_in_dept_file == l.strip().split(',')[1]: l1 = l.replace(l.strip().split(',')[2], change_in_dept_set) dept_list.append(l1) else: dept_list_left.append(l) res = dept_list + dept_list_left for lines in res: n_f.write(lines + '\n') f.close() n_f.close() os.remove('staff_table') os.rename('staff_table_new', 'staff_table') print('\033[31;1m%s\033[0m条数据受到影响!' % len(set(dept_list))) else: print('错误的语法!') def find_info(): ''' 1.可进行模糊查询,语法至少支持下面3种查询语法: find name,age from staff_table where age > 22 find * from staff_table where dept = "IT" find * from staff_table where enroll_date like "2013" :return: ''' find_in = input('查询语法如下\n' 'find name,age from staff_table where age > 22\n' 'find * from staff_table where dept = "IT"\n' 'find * from staff_table where enroll_date like "2013"\n' '请输入查询命令>>').strip().split(' ') find_list = [] if find_in[1] == 'name,age' and find_in[3] =='staff_table' and find_in[5] == 'age': with open('staff_table','r',encoding='utf-8') as f: for line in f: if int(find_in[7]) < int(line.strip().split(',')[2]): find_list.append(line) print('+--staff_table--+\n'+'+name------'+'--age+') for i in find_list: print('|'+i.strip().split(',')[1], i.strip().split(',')[2]+'\t|') print('+---------------+\n'+'查询到\033[31;1m%s\033[0m条数据!'% len(set(find_list))) elif find_in[1] == '*' and find_in[3] =='staff_table' and find_in[5] =='dept': with open('staff_table','r',encoding='utf-8') as f: for line in f: if find_in[7].replace('"','') == line.strip().split(',')[4]: find_list.append(line.strip()) print('+----------staff_table------+\n') for i in find_list: print(i) print('+-----------------------------+\n'+'查询到\033[31;1m%s\033[0m条数据!' % len(set(find_list))) elif find_in[1] == '*' and find_in[3] =='staff_table' and find_in[5] == 'enroll_date': with open('staff_table', 'r', encoding='utf-8') as f: for line in f: if find_in[7].replace('"', '') == re.split('-', line.strip().split(',')[5])[0]: find_list.append(line.strip()) print('+----------staff_table------+\n') for i in find_list: print(i) print('+-----------------------------+\n' + '查询到\033[31;1m%s\033[0m条数据!' % len(set(find_list))) else: print('错误的命令!') def main(): while True: print('+--------操作界面--------+\n0.打印当前信息\n1.查询员工信息\n2.新增员工信息\n3.删除员工信息\n4.修改员工信息\n5.退出\n+--------end--------+') choice = input('请输入您的选项>>>').strip() if choice == '1': find_info() elif choice == '2': add_info() elif choice == '3': del_info() elif choice == '4': change_info() elif choice == '5': exit() elif choice == '0': create_staff_table() else: print('错误的命令,请重试!') if __name__ == '__main__': create_staff_table() main()
7月9日安排
上午 完成 函数所有作业及视频学习和笔记,并完成员工信息表的增加和删除
下午 完成员工信息表的修改和查询
晚上 完成模块10个视频,记录笔记