python某公司为员工发放奖品_python练习_module02-1-员工信息表

python 操作 员工信息表

要求：

可进行模糊查询，语法至少支持下面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 where dept = "IT"

注意：以上需求，要充分使用函数，请尽你的最大限度来减少重复代码

达成要求：

查询命令的各单词之间可以有任意数量的空格

查询时不设定条件(where语句)也可以执行

错误信息高亮显示

代码：

#! /usr/bin/env python3

# Author:Jailly

import pickle,re

# 查找

def search(search_condition):

res = [] # 储存查找结果

# where条件存在

if search_condition:

# 三种条件格式

m1 = re.search(r‘age\s+(>|

m2 = re.search(r‘‘‘

(?P(staff_id)|(name)|(age)|(phone)|(dept)|(enroll_date))

\s+=\s+

(?P.*)

‘‘‘, search_condition, re.X)

m3 = re.search(r‘‘‘

(?P(staff_id)|(name)|(age)|(phone)|(dept)|(enroll_date))

\s+like\s+

(?P[1-9]\d*)

‘‘‘, search_condition, re.X)

# age \d 的情况

if m1:

# 筛选条目

for i in staff_table:

info_dict = dict(zip([‘staff_id‘, ‘name‘, ‘age‘, ‘phone‘, ‘dept‘, ‘enroll_date‘], i))

if eval(m1.group().replace(‘age‘, str(i[2]))):

res.append(info_dict)

# category = \w+ 的情况

elif m2:

# 筛选条目

for i in staff_table:

# 对应信息表中每个条目的字典

info_dict = dict(zip([‘staff_id‘, ‘name‘, ‘age‘, ‘phone‘, ‘dept‘, ‘enroll_date‘], i))

if str(info_dict[m2.group(‘category‘)]) == m2.group(‘value‘).strip():

res.append(info_dict)

# category like \w+ 的情况

elif m3:

for i in staff_table:

info_dict = dict(

zip([‘staff_id‘, ‘name‘, ‘age‘, ‘phone‘, ‘dept‘, ‘enroll_date‘], i)) # 对应每个条目的字典

if info_dict[m3.group(‘category‘)].count(m3.group(‘value‘)) or str(info_dict[m3.group(‘category‘)]).count(str(m3.group(‘value‘))):

res.append(info_dict)

# 条件格式不正确的情况

else:

print(‘\033[1;31mwhere语句格式错误！\033[0m‘)

# 无where条件

else:

for i in staff_table:

info_dict = dict(zip([‘staff_id‘, ‘name‘, ‘age‘, ‘phone‘, ‘dept‘, ‘enroll_date‘], i))

res.append(info_dict)

return res

# 显示查找结果

def show(res, search_categories):

if res:

for i in res:

# 查询所有条目 *

if search_categories.strip() == ‘*‘:

print(‘,‘.join([str(i[‘staff_id‘]), i[‘name‘], str(i[‘age‘]), i[‘phone‘], i[‘dept‘], i[‘enroll_date‘]]))

# 查询指定条目 name,age,...

else:

category_list = search_categories.split(‘,‘)

for j in category_list:

print(i[j.strip()], end=‘,‘)

print()

print(‘\n共查找到%d条记录\n‘ % len(res))

# 保存文件

def save():

with open(‘staff_table.pkl‘, ‘wb‘) as f:

pickle.dump(staff_table, f)

if __name__ == ‘__main__‘:

while 1:

with open(‘staff_table.pkl‘, ‘rb‘) as f:

staff_table = pickle.load(f)

cmd = input(‘command -> ‘)

# 查询指令

search_cmd = re.search(r‘‘‘

^\s*select\s+

(?P\*|

(

((staff_id)|(name)|(age)|(phone)|(dept)|(enroll_date))

(\s*,\s*((staff_id)|(name)|(age)|(phone)|(dept)|(enroll_date)))?

(\s*,\s*((staff_id)|(name)|(age)|(phone)|(dept)|(enroll_date)))?

(\s*,\s*((staff_id)|(name)|(age)|(phone)|(dept)|(enroll_date)))?

(\s*,\s*((staff_id)|(name)|(age)|(phone)|(dept)|(enroll_date)))?)

)

\s+from\s+staff_table

(\s+where\s+

(?P.*))?$‘‘‘,cmd,re.X)

# 添加指令

insert_cmd = re.search(r‘‘‘

^\s*[\w ]+, # 姓名

[1-9]\d*, # 年龄

\d{11}, # 手机号码

[\w ]+, # 部门

((20)|(19))\d{2}-((10)|(11)|(12)|(0[1-9]))-(([0-2][1-9])|(3[01]))$ # 入职日期

‘‘‘,cmd,re.X)

# 删除指令

delete_cmd = re.search(r‘^\d+$‘,cmd)

# 更改指令

update_cmd = re.search(r‘‘‘

^update\s+staff_table\s+set\s+

(?P(name)|(age)|(phone)|(dept)|(enroll_date))\s+

=\s+

(‘|")(?P.*)(‘|")

(\s+where\s+

(?P.*))?$

‘‘‘,cmd,re.X)

# 查询

if search_cmd:

search_categories = search_cmd.group(‘categories‘)

search_condition = search_cmd.group(‘condition‘)

res = search(search_condition)

show(res,search_categories)

# 添加

elif insert_cmd:

insert_list = insert_cmd.group().split(‘,‘)

repeat_flag = 0

for i in staff_table:

if insert_list[2] == i[3]:

print(‘\033[1;31m手机号码已存在!\033[0m‘)

repeat_flag = 1

break

else:

insert_list.insert(0,( staff_table[-1][0] + 1 ) )

staff_table.append(insert_list)

save()

print(‘添加成功！‘)

# 删除

elif delete_cmd:

for i in staff_table:

if delete_cmd.group() == str(i[0]):

staff_table.remove(i)

save()

print(‘删除成功！‘)

break

else:

print(‘\033[1;31m该staff_id不存在\033[0m‘)

# 修改

elif update_cmd:

search_condition = update_cmd.group(‘condition‘)

res = search(search_condition)

for i in range(len(staff_table)):

for j in res:

if staff_table[i][0] == j[‘staff_id‘]:

category_update = update_cmd.group(‘category_update‘)

new_value = update_cmd.group(‘new_value‘).strip()

j[category_update] = int(new_value) if category_update == ‘age‘ else new_value

# 保证顺序，不能用list(j.values())

staff_table[i] = list([j[‘staff_id‘],j[‘name‘],j[‘age‘],j[‘phone‘],j[‘dept‘],j[‘enroll_date‘]])

save()

print(‘修改成功！‘)

# 退出

elif cmd == ‘q‘:

break

# 输入格式不正确的情况

else:

print(‘\033[1;31m输入格式错误\033[0m‘)

原文：http://www.cnblogs.com/jailly/p/6837945.html