python-作业:员工信息表
程序可实现以下功能:
1、查询,输入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",查询到符合要求的信息。
2、创建,输入insert Mickle,22,13651054608,IT,2013-04-01,加信息加入的员工信息表中。
3、删除,输入delect id,将指定的id号员工信息删除。
4、修改,输入update staff_table set dept = "Market" where dept = "IT",将要修改的内容修改。
程序还提供help功能,为用户输入正确的sql语句格式提供帮助。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 | import os #导入os模块,方便文件的删除和改名 def info_display(): #打印程序启动信息 print ( "-" .center( 60 , "-" )) print ( "欢迎来到员工管理系统" .center( 50 , " " )) print ( "【输入help显示帮助信息】" .center( 50 , " " )) print ( "-" .center( 60 , "-" )) sql = input ( "请输入\033[1;31msql\033[0m信息>>>>" ).strip("") if sql = = "q" or sql = = "quit" : #用户如果输入q,程序退出 exit( " Bye Bye " .center( 60 , "-" )) elif sql = = "help" : #用户输入help,打印help信息,help信息为打印的格式 print ( "\033[1;35m 查询 输入格式:\n\tselect name,age from staff_table where age > 22\033[0m" ) print ( "\033[1;35m 创建 输入格式:\n\tinsert Mickle,22,13651054608,IT,2013-04-01\033[0m" ) print ( "\033[1;35m 修改 输入格式:\n\tupdate staff_table set dept = \"Market\" where dept = \"IT\"\033[0m" ) print ( "\033[1;35m 删除 输入格式:\n\tdelete 5\033[0m" ) else : sql_parse(sql) #用户输入正确的sql语法,调用sql解析函数 def sql_parse(sql): #对用户输入的sql语句进行解析,根据sql语句调用不同的函数 func_choice = { "insert" : insert, "delete" : delete, "update" : update, "select" : select} sql_list = sql.replace( "," , " " ).split() # 将sql解析成列表形式 if sql_list[ 0 ] = = "select" : #如果用户输入的sql语句是查询 sql_dict = { "from" : [], "where" : [], "limit" : []} tag = False for item in sql_list: #根据sql解析出sql_dict if tag and item in sql_dict: tag = False if not tag and item in sql_dict: tag = True key = item continue if tag: sql_dict[key].append(item.strip( '\"' )) func_choice.get(sql_list[ 0 ])(sql_dict) #解析出sql_dict后,传入查询函数,并调用查询函数 elif sql_list[ 0 ] = = "insert" : #如果用户输入的sql语句是创建 sql_dict = {} sql_list.remove( "insert" ) sql_dict.setdefault( "values" ,sql_list) #解析出sql_dict,传入创建函数 insert(sql_dict) #调用创建函数 elif sql_list[ 0 ] = = "delete" : #解析出sql_dict后,传入删除函数,并调用查询函数 delete_id = int (sql_list[ 1 ]) #获取用户要删除的id delete(delete_id) #调用删除函数 elif sql_list[ 0 ] = = "update" : #如果用户输入的sql语句是修改 sql_dict = { "set" :[], "where" :[], "update" :[]} tag = False for items in sql_list: #根据用户的sql,解析出sql_dict字典 if tag and items in sql_dict: tag = False if not tag and items in sql_dict: tag = True key = items continue if tag: sql_dict[key].append(items.strip( '\"' )) update(sql_dict) #传入修改函数,并调用修改函数 return sql_list def insert(sql_dict): #创建信息函数 with open ( "staff_table" , "ab+" ) as f: offs = - 100 while True : f.seek(offs, 2 ) lines = f.readlines() if len (lines) > 1 : last = lines[ - 1 ] break offs * = 2 last = last.decode(encoding = "utf-8" ) last_id = int (last.split( "," )[ 0 ]) new_id = last_id + 1 record = sql_dict.get( "values" ) record.insert( 0 , str (new_id)) record_str = "," .join(record) + "\n" f.write(bytes(record_str,encoding = "utf-8" )) f.flush() print ( "\033[1;31m创建成功 \033[0m" ) def delete(delete_id): #删除函数 with open ( "staff_table" , "r" ,encoding = "utf-8" ) as f1, open ( "staff_table_bak" , "w" ,encoding = 'utf-8' ) as f2: del_count = 1 for line in f1: if del_count ! = delete_id: f2.write(line) elif del_count = = delete_id: pass del_count + = 1 print ( "\033[1;31m删除成功 \033[0m" ) os.remove( "staff_table" ) #删除原员工信息文件 os.rename( "staff_table_bak" , "staff_table" ) #修改新员工信息文件名 def update(sql_dict): #修改函数 set_list = sql_dict.get( "set" ) #获取要修改的内容 set_key = set_list[ 0 ] where_list = sql_dict.get( "where" ) #获取要修改的文件内容 with open ( "staff_table" , "r" , encoding = "utf-8" ) as f1, open ( "staff_table_bak" , "w" , encoding = 'utf-8' ) as f2: for line in f1: title = "id,name,age,phone,dept,enroll_date" dic = dict ( zip (title.split( "," ),line.split( "," ))) #将文件每一行打包成字典的形式 if logic_action(dic,where_list): # 逻辑判断,调用逻辑判断函数 line = line.replace(dic[set_key],set_list[ 2 ]) #修改需要修改的那一行内容 f2.write(line) #将原文件写进新文件 print ( "\033[1;31m修改成功 \033[0m" ) os.remove( "staff_table" ) os.rename( "staff_table_bak" , "staff_table" ) def select(sql_dict): #查询函数 title = "id,name,age,phone,dept,enroll_date" f = open ( "staff_table" , "r" , encoding = "utf-8" ) # 1、找到数据库 res = [] for line in f: dict1 = dict ( zip (title.split( "," ), line.strip().split( "," ))) where_list = sql_dict.get( "where" ) if len (where_list) ! = 0 : if logic_action(dict1, where_list): # 逻辑判断,调用逻辑判断函数 res.append(line.strip()) else : res = f.readlines() print ( "查询到的信息有 \033[1;35m %s \033[0m 条" % len (res)) for i in res: print ( "分别是\033[1;35m [%s]\033[0m " % i) return res def logic_action(dict1, where_list): #逻辑判断函数,如果文件的信息符合要求,就返回True tag = False if where_list[ 1 ] = = "<" : if dict1[where_list[ 0 ]] < where_list[ 2 ]: tag = True elif where_list[ 1 ] = = "=" : if dict1[where_list[ 0 ]] = = where_list[ 2 ]: tag = True else : pass elif where_list[ 1 ] = = ">" : if dict1[where_list[ 0 ]] > where_list[ 2 ]: tag = True else : pass elif where_list[ 1 ] = = "like" : if where_list[ 2 ] in dict1[where_list[ 0 ]]: tag = True return tag while True : try : info_display() except IndexError as e: print ( "【%s】,请输入正确的格式" % e) #如果用户输入的格式不正确,就打印提示 |
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步