Python学习day4作业-员工信息表
@(学习)[python]
day4作业：员工信息表
作业需求
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.可创建新员工纪录，以phone做唯一键，staff_id需自增；
3.可删除指定员工信息纪录，输入员工id，即可删除；
4.可修改员工信息，语法如下:
update staff_table set dept = "Market" where dept = "IT";
5.充分使用函数，请尽你的最大限度来减少重复代码；
- [x] 博客
- [x] 模糊查询
- [x] 创建员工纪录
- [x] 删除员工纪录
- [x] 修改员工纪录
1.程序说明
实现功能如下
- [x] 博客
- [x] 模糊查询
- [x] 创建员工纪录
- [x] 删除员工纪录
- [x] 修改员工纪录
2.基本流程图
3.程序代码
#!/usr/bin/env python
# _*_coding:utf-8_*_
'''
* Created on 2016/12/5 22:31.
* @author: Chinge_Yang.
'''
import os
import sys
def file_to_data(table):
"""
# 读文件成数据
:param
:return:
"""
n = 0
data_list = []
with open(table, "r", encoding="utf-8") as f:
for line in f:
line = line.strip() # 去除换行符
if n == 0:
# ["staff_id","name","age","phone","dept","enroll_date"]
struct_list = line.split(",") # 第一行
else:
line_list = line.split(",")
data_list.append(line_list) # 其它行
n += 1
return struct_list, data_list
def data_to_file(struct_list, data_list, table):
"""
# 把数据写入文件
:return:
"""
with open(table, "w", encoding="utf-8") as f:
f.write(','.join(struct_list) + "\n")
for sub_list in data_list:
f.write(",".join(sub_list) + "\n")
print("Done!")
def print_help(action):
print("The grammar of the {} support likes:\n".format(action))
if action == "select":
print("\tselect * from staff_table;")
print("\tselect name,age from staff_table where age > 22;")
print("\tselect * from staff_table where dept = \"IT\";")
print("\tselect * from staff_table where enroll_date like \"2013\";")
elif action == "add":
print("\tadd [Alex Li,22,13651054608,IT,2013-04-01];")
elif action == "update":
print("\tupdate staff_table set dept = \"Market\" where dept = \"IT\";")
elif action == "delete":
print("\tdelete 5;")
def get_column_number(column, struct_list):
"""
# 获取列位置
:param column: 列名，此处只实现支持一个
:return:
"""
column_number = struct_list.index(column) # 结果为数字
return column_number
def input_sql():
# 获取输入SQL
exit_flag = False
while exit_flag is not True:
print("-".center(60, "-"))
print("Tip: Input 【help [select/update/add/delete]】 to get help.")
print("-".center(60, "-"))
sql = input("Please input SQL:").strip().strip(";")
if sql.startswith('help'):
action = sql.split(" ")[1]
print_help(action)
continue
if sql == "q" or sql == "quit":
exit(" Bye Bye ".center(60, "#"))
exit_flag = True
return sql
def sql_to_list(sql):
tmp_sql = sql.split(' ')
sql_list = []
tmp = ''
flag = 1 # 列表添加元素标识
for l in tmp_sql:
if l.startswith('"') and l.endswith('"'):
flag = 1
elif l.startswith('"') and (not l.endswith('"')):
flag = 0
tmp = l + ' '
elif (not l.startswith('"')) and (not l.endswith('"')):
if flag == 0:
l += ' '
tmp += l
else:
flag = 1
elif (not l.startswith('"')) and l.endswith('"'):
if flag == 0:
tmp += l
flag = 1
sql_list.append(tmp)
continue
if flag == 1:
sql_list.append(l)
return sql_list
def check_table(table, c_table):
"""
# 判断表是否存在
:param table: 表名
:return:
"""
if table != c_table:
print("Your input table \033[31m{}\033[0m is not exists,"
"please check!".format(c_table))
print("#".center(60, "#"))
return True # 标记给continue_flag
def check_quotes(str):
"""
:param str: 需要处理的字符串
:return: 返回无符号的字符串
"""
if '"' in str:
str = str.strip('"')
return str
def auto_increment_id(data_list):
file = "auto_increment_id"
max_staff_id = int(data_list[-1][0]) # 表中最大的staff_id
id = 0 # 初始化
if os.path.exists(file): # 自增id文件存在时
with open(file, "r+") as f:
for line in f:
id = int(line)
if max_staff_id <= id:
new_staff_id = id + 1
else:
new_staff_id = max_staff_id + 1
with open(file, "w+") as f:
f.write(str(new_staff_id))
return new_staff_id
def analyze(sql):
input_info = sql_to_list(sql)
# return input_info
action = input_info[0] # 查：select；增：add；改：update；删：delete
return action
def select(sql, struct_list, data_list, table): # select sql语法分析
input_info = sql_to_list(sql)
select_column = input_info[1].split(",") # 可能有“,”号
try:
table_name = input_info[3]
except Exception as e:
print("Your input is error!")
return True
continue_flag = check_table(table, table_name)
if continue_flag is True:
return True
all_column = False
all_line = False
# 查询列，* 或者指定列
if "*" in select_column: # 打印所有列
all_column = True
else:
column_numbers = [] # 输出列的数字列表
for s_column in select_column:
s_number = get_column_number(s_column, struct_list)
column_numbers.append(s_number)
if "where" in sql:
# 由于双引号问题，此处加上双引号
where_flag = input_info[4] # where
condition_column = input_info[5] # 条件字段
condition_str = input_info[6] # 限制条件关键字，支持“=”，“>=”，“like”等
condition_value = input_info[7] # 条件参数
condition_value = check_quotes(condition_value) # 去除双引号
column_number = get_column_number(condition_column, struct_list) # 列位置
match_data_list = [] # 匹配出来的结果，列表格式，
# 查询行,有like、>=、= 等
if where_flag == "where": # 有where
if condition_str == "like":
# like
for line in data_list: # line也是列表
if condition_value in line[column_number]: # 匹配like
match_data_list.append(line)
elif condition_str == "=":
for line in data_list:
if line[column_number] == condition_value:
match_data_list.append(line)
elif condition_str == ">":
for line in data_list:
if line[column_number] > condition_value:
match_data_list.append(line)
elif condition_str == ">=":
for line in data_list:
if line[column_number] >= condition_value:
match_data_list.append(line)
elif condition_str == "<":
for line in data_list:
if line[column_number] < condition_value:
match_data_list.append(line)
elif condition_str == "<=":
for line in data_list:
if line[column_number] <= condition_value:
match_data_list.append(line)
else: # 无where，取所有行
all_line = True
match_data_list = data_list
# 打印结果
print("The select result:")
print("#".center(60, "#"))
print("\033[32m{}\033[0m rows in set".format(len(match_data_list)))
if all_column is True:
print("{:>8} {:>8} {:>8} {:>8} {:>8} {:>8}".format(*struct_list))
for line in match_data_list:
print("{:>8} {:>8} {:>8} {:>8} {:>8} {:>8}".format(*line))
else:
len_num = len(select_column)
format_str = '{:>8} ' * len_num
print(format_str.format(*select_column))
for line in match_data_list:
line_list = []
for s in column_numbers:
line_list.append(line[s])
print(format_str.format(*line_list))
print("#".center(60, "#"))
def add(sql, struct_list, data_list, table):
# sql: Alex Li,22,13651054608,IT,2013-04-01
input_info = sql.strip().strip("add [").strip("]")
add_list = input_info.split(",")
phone = add_list[2]
phone_exist = False
for d_list in data_list:
if phone == d_list[3]:
phone_exist = True
if phone_exist is True:
print("Thone phone is exist,can't add.")
return True
else:
new_staff_id = auto_increment_id(data_list)
add_list.insert(0, str(new_staff_id))
data_list.append(add_list)
data_to_file(struct_list, data_list, table)
def delete(sql, struct_list, data_list, table):
# delete 5;
delete_flag = False
input_info = sql_to_list(sql)
staff_id = input_info[1]
for d_list in data_list:
if staff_id == d_list[0]:
delete_flag = True
data_list.remove(d_list)
if delete_flag is not True:
print("The staff_id is not exist,can't delete.")
else:
data_to_file(struct_list, data_list, table)
def update(sql, struct_list, data_list, table):
# update staff_table set dept = "Market" where dept = "IT"; 只允许修改age,phone,dept,enroll_date
input_info = sql_to_list(sql)
table_name = input_info[1]
set_flag = input_info[2]
modify_column = input_info[3] # 修改的字段
equal_flag = input_info[4] # 等于符号
modify_value = input_info[5] # 修改后的值
modify_value = check_quotes(modify_value) # 去除双引号
where_flag = input_info[6]
condition_column = input_info[7] # 条件字段
condition_str = input_info[8] # 限制条件关键字，只支持“=”
condition_value = input_info[9] # 条件参数
condition_value = check_quotes(condition_value) # 去除双引号
modify_column_number = get_column_number(modify_column, struct_list) # 列位置
condition_column_number = get_column_number(condition_column, struct_list) # 列位置
modify_flag = False
continue_flag = check_table(table, table_name)
if continue_flag is True:
return True
if set_flag == "set" and equal_flag == "=" and where_flag == "where" and condition_str == "=":
phone_exist = False
phone = modify_value
for d_list in data_list:
if phone == d_list[3]:
phone_exist = True
if phone_exist is True:
print("Thone phone is exist,can't update.")
return True
for d_list in data_list:
# 由于双引号问题，此处加上双引号
if d_list[condition_column_number] == condition_value:
d_list[modify_column_number] = modify_value
modify_flag = True
if modify_flag is not True:
print("Not match any record!")
else:
data_to_file(struct_list, data_list, table)
else:
print("Your input is error!")
def main():
exit_flag = False
table = "staff_table"
while exit_flag is not True:
sql = input_sql()
action = analyze(sql)
struct_list, data_list = file_to_data(table)
if action == "select":
continue_flag = select(sql, struct_list, data_list, table)
if continue_flag is True:
continue # 重新循环
elif action == "add":
continue_flag = add(sql, struct_list, data_list, table)
if continue_flag is True:
continue # 重新循环
elif action == "update":
continue_flag = update(sql, struct_list, data_list, table)
if continue_flag is True:
continue
elif action == "delete":
delete(sql, struct_list, data_list, table)
else:
print("Your input error!")
if __name__ == '__main__':
main()4.程序测试
cat staff_table
staff_id,name,age,phone,dept,enroll_date
1,Alex Li,22,13651054608,IT,2013-04-01
2,Jim,25,13651058808,IT,2011-09-01
3,Tom,29,13761054698,IT,2010-03-01
4,Suzen,40,13957057707,Manager,2003-01-01
5,Mark,32,13351959999,CTO,2014-08-08python staff_table_manager.py
------------------------------------------------------------
Tip: Input 【help [select/update/add/delete]】 to get help.
------------------------------------------------------------
Please input SQL:help select
The grammar of the select support likes:
select * from staff_table;
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";
------------------------------------------------------------
Tip: Input 【help [select/update/add/delete]】 to get help.
------------------------------------------------------------
Please input SQL:select * from staff_table;
The select result:
############################################################
5 rows in set
staff_id name age phone dept enroll_date
1 Alex Li 22 13651054608 IT 2013-04-01
2 Jim 25 13651058808 IT 2011-09-01
3 Tom 29 13761054698 IT 2010-03-01
4 Suzen 40 13957057707 Manager 2003-01-01
5 Mark 32 13351959999 CTO 2014-08-08
############################################################
------------------------------------------------------------
Tip: Input 【help [select/update/add/delete]】 to get help.
------------------------------------------------------------
Please input SQL:select name,age from staff_table where age > 22;
The select result:
############################################################
4 rows in set
name age
Jim 25
Tom 29
Suzen 40
Mark 32
############################################################
------------------------------------------------------------
Tip: Input 【help [select/update/add/delete]】 to get help.
------------------------------------------------------------
Please input SQL:select * from staff_table where dept = "IT";
The select result:
############################################################
3 rows in set
staff_id name age phone dept enroll_date
1 Alex Li 22 13651054608 IT 2013-04-01
2 Jim 25 13651058808 IT 2011-09-01
3 Tom 29 13761054698 IT 2010-03-01
############################################################
------------------------------------------------------------
Tip: Input 【help [select/update/add/delete]】 to get help.
------------------------------------------------------------
Please input SQL:select * from staff_table where enroll_date like "2013";
The select result:
############################################################
1 rows in set
staff_id name age phone dept enroll_date
1 Alex Li 22 13651054608 IT 2013-04-01
############################################################
------------------------------------------------------------
Tip: Input 【help [select/update/add/delete]】 to get help.
------------------------------------------------------------
Please input SQL:help update
The grammar of the update support likes:
update staff_table set dept = "Market" where dept = "IT";
------------------------------------------------------------
Tip: Input 【help [select/update/add/delete]】 to get help.
------------------------------------------------------------
Please input SQL:update staff_table set dept = "Market" where dept = "IT";
Done!
------------------------------------------------------------
Tip: Input 【help [select/update/add/delete]】 to get help.
------------------------------------------------------------
Please input SQL:select * from staff_table;
The select result:
############################################################
5 rows in set
staff_id name age phone dept enroll_date
1 Alex Li 22 13651054608 Market 2013-04-01
2 Jim 25 13651058808 Market 2011-09-01
3 Tom 29 13761054698 Market 2010-03-01
4 Suzen 40 13957057707 Manager 2003-01-01
5 Mark 32 13351959999 CTO 2014-08-08
############################################################
------------------------------------------------------------
Tip: Input 【help [select/update/add/delete]】 to get help.
------------------------------------------------------------
Please input SQL:update staff_table set phone = 13651054608 where name = "Mark";
Thone phone is exist,can't update.
------------------------------------------------------------
Tip: Input 【help [select/update/add/delete]】 to get help.
------------------------------------------------------------
Please input SQL:help add
The grammar of the add support likes:
add [Alex Li,22,13651054608,IT,2013-04-01];
------------------------------------------------------------
Tip: Input 【help [select/update/add/delete]】 to get help.
------------------------------------------------------------
Please input SQL:add [Alex Li,22,13651054608,IT,2013-04-01];
Thone phone is exist,can't add.
------------------------------------------------------------
Tip: Input 【help [select/update/add/delete]】 to get help.
------------------------------------------------------------
Please input SQL:add [Alex Li,22,13651054666,IT,2013-04-01];
Done!
------------------------------------------------------------
Tip: Input 【help [select/update/add/delete]】 to get help.
------------------------------------------------------------
Please input SQL:select * from staff_table where name = "Alex Li";
The select result:
############################################################
2 rows in set
staff_id name age phone dept enroll_date
1 Alex Li 22 13651054608 Market 2013-04-01
6 Alex Li 22 13651054666 IT 2013-04-01
############################################################
------------------------------------------------------------
Tip: Input 【help [select/update/add/delete]】 to get help.
------------------------------------------------------------
Please input SQL:help delete
The grammar of the delete support likes:
delete 5;
------------------------------------------------------------
Tip: Input 【help [select/update/add/delete]】 to get help.
------------------------------------------------------------
Please input SQL:delete 6
Done!
------------------------------------------------------------
Tip: Input 【help [select/update/add/delete]】 to get help.
------------------------------------------------------------
Please input SQL:delete 5
Done!
------------------------------------------------------------
Tip: Input 【help [select/update/add/delete]】 to get help.
------------------------------------------------------------
Please input SQL:select * from staff_table;
The select result:
############################################################
4 rows in set
staff_id name age phone dept enroll_date
1 Alex Li 22 13651054608 Market 2013-04-01
2 Jim 25 13651058808 Market 2011-09-01
3 Tom 29 13761054698 Market 2010-03-01
4 Suzen 40 13957057707 Manager 2003-01-01
############################################################
------------------------------------------------------------
Tip: Input 【help [select/update/add/delete]】 to get help.
------------------------------------------------------------
Please input SQL:add [Alex Li,22,13651054666,IT,2013-04-01];
Done!
------------------------------------------------------------
Tip: Input 【help [select/update/add/delete]】 to get help.
------------------------------------------------------------
Please input SQL:select * from staff_table;
The select result:
############################################################
5 rows in set
staff_id name age phone dept enroll_date
1 Alex Li 22 13651054608 Market 2013-04-01
2 Jim 25 13651058808 Market 2011-09-01
3 Tom 29 13761054698 Market 2010-03-01
4 Suzen 40 13957057707 Manager 2003-01-01
7 Alex Li 22 13651054666 IT 2013-04-01
############################################################
------------------------------------------------------------
Tip: Input 【help [select/update/add/delete]】 to get help.
------------------------------------------------------------
Please input SQL:quit
######################### Bye Bye ##########################
Process finished with exit code 1
扫一扫
2698
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
