首页 > 代码库 > [terry笔记]对人员列表文件进行数据库操作
[terry笔记]对人员列表文件进行数据库操作
原文件（数据已经脱敏）：
staff_id,name,age,phone,dept,enroll_date
1,姬建明,25,152015410,运维,2013-11-01
2,刘海龙,26,186184177,运维,2012-12-25
3,闫培飞,25,157016802,IT,2015-10-14
4,孙海涛,33,158206312,IT,2005-09-27
5,张亚磊,24,137338110,IT,2014-06-23
6,张松,26,136837094,IT,2013-10-08
7,刘文剑,25,155550213,SA,2014-05-21
8,魏曼,18,189108670,运维,2014-05-21
9,宋晓楠,18,110168082,测试,2014-05-21
10,吴东杭,21,177108908,运维,1995-08-29
11,李静,18,186127011,测试,2015-08-20
12,文鑫,23,181805410,无,2016-7-31
13,于洪林,31,133232639,运维,2013-6-24
14,王新凯,22,169965127,运维,2015-10-31
15,盖爽,18,185003763,运维,2015-10-28
16,张振兴,27,135214320,运维,2015-10-28
17,侯梓煜,21,186819511,运维,2016-10-31
18,张玉新,27,101241184,运维,2015-10-28
19,纪冠宇,24,150113743,运维,2015-11-12
20,李乾龙,24,185105376,运维,2015-7-13
21,杨洋,21,188131922,运维,2015-1-1
22,张泽伟,27,101029861,无,2011-2-1
如下是要求：
# 有以下员工信息表staff_table
# 1, AlexLi, 22, 13651054608, IT, 2013-04-01
# 现需要对这个员工信息文件，实现增删改查操作
# 可进行模糊查询，语法至少支持下面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"
# 查到的信息，打印后，最后面还要显示查到的条数
# 可创建新员工纪录，以phone做唯一键，staff_id需自增
# 可删除指定员工信息纪录，输入员工id，即可删除
# 可修改员工信息，语法如下:
# UPDATE staff_table SET dept = "Market" where dept = "IT"
# 注意：以上需求，要充分使用函数，请尽你的最大限度来减少重复代码！
# select * from staff_table where age < 25
自己写的代码，替换和删除后续补上：
1 #查询函数 2 def select(column,term=[],symbol=[],option=[]): 3 f=open("staff_table","r",encoding="utf-8") 4 object = f.readline().strip().split(",") 5 6 #查询column为*，不带条件，比如:select * from staff_table 7 if column == "*" and not term and not symbol and not option: 8 for line in f: 9 object = line.strip().split(",") 10 print("%s %s %s %s %s %s" % (object[0], object[1], object[2], object[3], object[4], object[5])) 11 exit() 12 13 #查询column为*，带条件，比如:select * from staff_table where age > 25 14 if column == "*" and term: 15 show_list=[] 16 position = object.index(term) 17 for line in f: 18 object = line.strip().split(",") 19 if object[0].isdigit: 20 if symbol == "=": 21 if object[position] == option.strip(‘\"‘): 22 show_list.append(object) 23 elif symbol == ">": 24 if object[position] > option.strip(‘\"‘): 25 show_list.append(object) 26 elif symbol == "<": 27 if object[position] < option.strip(‘\"‘): 28 show_list.append(object) 29 elif symbol == ">=": 30 if object[position] >= option.strip(‘\"‘): 31 show_list.append(object) 32 elif symbol == "<=": 33 if object[position] <= option.strip(‘\"‘): 34 show_list.append(object) 35 elif symbol == "like": 36 if option.strip(‘\"‘) in object[position]: 37 show_list.append(object) 38 i = 0 39 for i, ele in enumerate(show_list): 40 print("%s %s %s %s %s %s" % (ele[0], ele[1], ele[2], ele[3], ele[4], ele[5])) 41 print("共%s位成员%s %s %s" % (i + 1, term, symbol, option)) 42 exit() 43 44 # 查询column为自选，不带条件，比如:select name,age,dept from staff_table 45 if column != "*" and not term and not symbol and not option: 46 column = column.split(",") 47 position_list = [] 48 show_list1 = [] 49 show_list2 = [] 50 for i in range(0,len(column)): 51 position = object.index(column[i]) 52 position_list.append(position) 53 for line in f: 54 object = line.strip().split(",") 55 for i in range(0,len(position_list)): 56 show_list1.append(object[position_list[i]]) 57 show_list2.append(show_list1) 58 show_list1 = [] 59 for i in show_list2: 60 print(i) 61 exit() 62 63 # 查询column为自选，带条件，比如:select name,age,dept from staff_table where dept = "运维" 64 if column != "*" and term: 65 show_list=[] 66 position = object.index(term) 67 for line in f: 68 object_data = http://www.mamicode.com/line.strip().split(",") 69 if object_data[0].isdigit: 70 if symbol == "=": 71 if object_data[position] == option.strip(‘\"‘): 72 show_list.append(object_data) 73 elif symbol == ">": 74 if object_data[position] > option.strip(‘\"‘): 75 show_list.append(object_data) 76 elif symbol == "<": 77 if object_data[position] < option.strip(‘\"‘): 78 show_list.append(object_data) 79 elif symbol == ">=": 80 if object_data[position] >= option.strip(‘\"‘): 81 show_list.append(object_data) 82 elif symbol == "<=": 83 if object_data[position] <= option.strip(‘\"‘): 84 show_list.append(object_data) 85 elif symbol == "like": 86 if option.strip(‘\"‘) in object_data[position]: 87 show_list.append(object_data) 88 column = column.split(",") 89 position_list = [] 90 show_list1 = [] 91 show_list2 = [] 92 for i in range(0,len(column)): 93 position = object.index(column[i]) 94 position_list.append(position) 95 for j in range(0,len(show_list)): 96 for i in range(0,len(position_list)): 97 show_list1.append(show_list[j][position_list[i]]) 98 show_list2.append(show_list1) 99 show_list1 = [] 100 for i in show_list2: 101 print(i) 102 exit() 103 104 #插入函数： 105 def insert(name,age,phone,dept,enroll_date): 106 f = open("staff_table","r",encoding="utf-8") 107 num = 0 108 for line in f: 109 object = line.strip().split(",") 110 if phone == object[3]: 111 print("手机号重复，无法插入") 112 exit() 113 num = object[0] 114 f.close() 115 num = int(num) 116 num = num + 1 117 num = str(num) 118 name = name.strip(‘\"‘) 119 age = str(age) 120 phone = str(phone) 121 dept = dept.strip(‘\"‘) 122 enroll_date = enroll_date.strip(‘\"‘) 123 new_line = "\n"+num+","+name+","+age+","+phone+","+dept+","+enroll_date 124 f = open("staff_table","a",encoding="utf8") 125 f.write(new_line) 126 f.close() 127 128 129 def update(): 130 pass 131 132 def delete(): 133 pass 134 135 msg = """ 136 请选择： 137 1.查询 138 2.插入 139 3.更新 140 4.删除 141 5.退出 142 """ 143 main_dic = { 144 "1":select, 145 "2":insert, 146 "3":update, 147 "4":delete, 148 "5":exit 149 } 150 151 152 while True: 153 f = open("staff_table","r",encoding="utf-8") 154 print("成员表如下".center(40, "*")) 155 for line in f: 156 object = line.strip().split(",") 157 print("%s %s %s %s %s %s" % (object[0],object[1],object[2],object[3],object[4],object[5])) 158 print(msg) 159 column=[] 160 term=[] 161 symbol=[] 162 option=[] 163 choice = input("what do you want :") 164 if len(choice) == 0 or choice not in main_dic:continue 165 if choice == "5":break 166 if choice == "1": 167 select_sql = input("查询语句 ：") 168 select_sql_list=select_sql.split(" ") 169 column = select_sql_list[1] 170 if len(select_sql_list) > 7: 171 term = select_sql_list[5] 172 symbol = select_sql_list[6] 173 option = select_sql_list[7] 174 main_dic["1"](column,term,symbol,option) 175 if choice == "2": 176 insert_sql = input("插入语句 ：") 177 insert_sql_list = insert_sql.split(" ") 178 # insert into staff_table values ("terry",22,13333333,"it","2013-1-1") 179 column_str = insert_sql_list[4] 180 column_list = column_str.lstrip("(").rstrip(")").split(",") 181 name = column_list[0] 182 age = column_list[1] 183 phone = column_list[2] 184 dept = column_list[3] 185 enroll_date = column_list[4] 186 main_dic["2"](name,age,phone,dept,enroll_date)
[terry笔记]对人员列表文件进行数据库操作
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。