[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

自己写的代码，替换和删除后续补上：

 #查询函数

 def select(column,term=[],symbol=[],option=[]):

     f=open("staff_table","r",encoding="utf-8")

     object = f.readline().strip().split(",")

     #查询column为*，不带条件，比如:select * from staff_table

     if column == "*" and not term and not symbol and not option:

         for line in f:

             object = line.strip().split(",")

             print("%s %s %s %s %s %s" % (object[0], object[1], object[2], object[3], object[4], object[5]))

         exit()

     #查询column为*，带条件，比如:select * from staff_table where age > 25

     if column == "*" and term:

         show_list=[]

         position = object.index(term)

         for line in f:

             object = line.strip().split(",")

             if object[0].isdigit:

                 if symbol == "=":

                     if object[position] == option.strip('\"'):

                         show_list.append(object)

                 elif symbol == ">":

                     if object[position] > option.strip('\"'):

                         show_list.append(object)

                 elif symbol == "<":

                     if object[position] < option.strip('\"'):

                         show_list.append(object)

                 elif symbol == ">=":

                     if object[position] >= option.strip('\"'):

                         show_list.append(object)

                 elif symbol == "<=":

                     if object[position] <= option.strip('\"'):

                         show_list.append(object)

                 elif symbol == "like":

                     if  option.strip('\"') in object[position]:

                         show_list.append(object)

         i = 0

         for i, ele in enumerate(show_list):

             print("%s %s %s %s %s %s" % (ele[0], ele[1], ele[2], ele[3], ele[4], ele[5]))

         print("共%s位成员%s %s %s" % (i + 1, term, symbol, option))

         exit()

     # 查询column为自选，不带条件，比如:select name,age,dept from staff_table

     if column != "*" and not term and not symbol and not option:

         column = column.split(",")

         position_list = []

         show_list1 = []

         show_list2 = []

         for i in range(0,len(column)):

             position = object.index(column[i])

             position_list.append(position)

         for line in f:

             object = line.strip().split(",")

             for i in range(0,len(position_list)):

                 show_list1.append(object[position_list[i]])

             show_list2.append(show_list1)

             show_list1 = []

         for i in show_list2:

             print(i)

         exit()

     # 查询column为自选，带条件，比如:select name,age,dept from staff_table where dept = "运维"

     if column != "*" and term:

         show_list=[]

         position = object.index(term)

         for line in f:

             object_data = line.strip().split(",")

             if object_data[0].isdigit:

                 if symbol == "=":

                     if object_data[position] == option.strip('\"'):

                         show_list.append(object_data)

                 elif symbol == ">":

                     if object_data[position] > option.strip('\"'):

                         show_list.append(object_data)

                 elif symbol == "<":

                     if object_data[position] < option.strip('\"'):

                         show_list.append(object_data)

                 elif symbol == ">=":

                     if object_data[position] >= option.strip('\"'):

                         show_list.append(object_data)

                 elif symbol == "<=":

                     if object_data[position] <= option.strip('\"'):

                         show_list.append(object_data)

                 elif symbol == "like":

                     if  option.strip('\"') in object_data[position]:

                         show_list.append(object_data)

         column = column.split(",")

         position_list = []

         show_list1 = []

         show_list2 = []

         for i in range(0,len(column)):

             position = object.index(column[i])

             position_list.append(position)

         for j in range(0,len(show_list)):

             for i in range(0,len(position_list)):

                 show_list1.append(show_list[j][position_list[i]])

             show_list2.append(show_list1)

             show_list1 = []

         for i in show_list2:

             print(i)

         exit()

 #插入函数：

 def insert(name,age,phone,dept,enroll_date):

     f = open("staff_table","r",encoding="utf-8")

     num = 0

     for line in f:

         object = line.strip().split(",")

         if phone == object[3]:

             print("手机号重复，无法插入")

             exit()

         num = object[0]

     f.close()

     num = int(num)

     num = num + 1

     num = str(num)

     name = name.strip('\"')

     age = str(age)

     phone = str(phone)

     dept = dept.strip('\"')

     enroll_date = enroll_date.strip('\"')

     new_line = "\n"+num+","+name+","+age+","+phone+","+dept+","+enroll_date

     f = open("staff_table","a",encoding="utf8")

     f.write(new_line)

     f.close()

 def update():

     pass

 def delete():

     pass

 msg = """

 请选择：

 1.查询

 2.插入

 3.更新

 4.删除

 5.退出

 """

 main_dic = {

     "":select,

     "":insert,

     "":update,

     "":delete,

     "":exit

 }

 while True:

     f = open("staff_table","r",encoding="utf-8")

     print("成员表如下".center(40, "*"))

     for line in f:

         object = line.strip().split(",")

         print("%s %s %s %s %s %s" % (object[0],object[1],object[2],object[3],object[4],object[5]))

     print(msg)

     column=[]

     term=[]

     symbol=[]

     option=[]

     choice = input("what do you want :")

     if len(choice) == 0 or choice not in main_dic:continue

     if choice == "":break

     if choice == "":

         select_sql = input("查询语句 ：")

         select_sql_list=select_sql.split(" ")

         column = select_sql_list[1]

         if len(select_sql_list) > 7:

             term = select_sql_list[5]

             symbol = select_sql_list[6]

             option = select_sql_list[7]

         main_dic[""](column,term,symbol,option)

     if choice == "":

         insert_sql = input("插入语句 ：")

         insert_sql_list = insert_sql.split(" ")

         # insert into staff_table values ("terry",22,13333333,"it","2013-1-1")

         column_str = insert_sql_list[4]

         column_list = column_str.lstrip("(").rstrip(")").split(",")

         name = column_list[0]

         age = column_list[1]

         phone = column_list[2]

         dept = column_list[3]

         enroll_date = column_list[4]

         main_dic[""](name,age,phone,dept,enroll_date)

巴特西

[terry笔记]对人员列表文件进行数据库操作

最新文章

热门文章