需求
员工信息表一张
| staff_id | name | age | phone | dept | enroll_date |
|---|---|---|---|---|---|
| 1 | Alex Li | 22 | 13612684608 | IT | 2013-04-01 |
| 2 | Jack Wang | 30 | 18613305112 | HR | 2015-05-03 |
| 3 | Nick Cave | 25 | 18962295184 | Sales | 2015-04-06 |
现在需要对这个员工信息文件，实现增删改查工作
- 可以进行模糊查询，语法至少支持下面三种：
- 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"
需求分析
员工信息表按列表形式存放
一条员工信息是这样的
[1,'Alex Li','22','1803601111','IT','2013-04-01']
多条员工信息这样写：
[[1,'Alex Li','22','1803601111','IT','2013-04-01'],[2,'Jack Wang','30','18613305112','HR','2015-05-03'],[3,'Nick Cave','25','18962295184','Sales','2015-04-06']]
支持语法按照作业实际需求可以就写那么几种，避免程序逻辑复杂。
- select查询，语法支持{name age phone dept enroll_date 和 *} 查询，也是我们的判断要根据输入字符串长度来做条件判断，后面的语句我们可以固定为from tablename where ... 这样（6个词组）
可以这样写逻辑
def select_func(sql):
'''judge sql is correct length'''
sql=sql.split(' ')
if len(sql) <8 or len(sql) > 11:
print('error')
else:
result=sel_inner_func(sql)
return result
将输入的语句按空格分成列表，如果列表项超过11个或者少于8个——那也就是代表肯定不是select .. from tablename where ...合格的长度——我们就会返回一个错误消息，如果符合规定，我们会继续对它做处理
接下去的处理：
def sel_inner_func(right_sql):
'''if sql is 8 characters length and return demo_list or part of demo_list'''
if len(right_sql) == 8:
if right_sql[1] == '*':
return demo_list
elif right_sql[1] in key_list:
key=right_sql[1]
key_index=key_list.index(key)+1
return demo_list[key_index]
else:
pass
'''if sql is more than 8 characters length and return raw_sel_list'''
elif len(right_sql) >= 9:
for args in right_sql:
if args == 'from':
break
args_list.append(args)
print(args_list)
key_set=set(args_list[1:])
key_list_set=set(key_list)
if key_set.issubset(key_list_set):
for i in key_set:
key_index=key_list.index(i)+1
raw_sel_list.append(demo_list[key_index])
return raw_sel_list
else:
pass
对列表长度做一个判断，一种是长度为8，那么select后面就一定是某个键值或者*，那么针对这两种情况进行判断，可以返回对应的信息。
如果长度超过8，我们对列表再做一次筛选，用for循环，如果循环到'from'，那我们就停止循环，因为'from'接下去的信息不是我们想要的信息。选出来的组成列表args_list，然后我们再用集合set，判断是否是key_list集合的子集（如果是就代表所有的键值都在合法的键值里面），然后用for循环将键值位置对应的数据列表位置取出来，返回结果。
如果sql语句几个key值之间的是逗号，就更方便了
def sel_inner_func(right_sql):
'''if sql is 8 characters length and return demo_list or part of demo_list'''
args=right_sql[1]
args_list=args.split(',')
if args == '*':
return demo_list
else:
for i in args_list:
if i in key_list:
key_index=key_list.index(i)+1
raw_sel_list.append(demo_list[key_index])
else:
print('Sorry,your enter is invaild!')
return raw_sel_list
接下去要做的是对from ... 和where ... 信息的判断。
在长度是固定的情况下，from后面一位就是表格名称，在sql语句中应该是第四位
def from_table(right_sql):
'''read table'''
table_name=right_sql[3]
if os.path.exists(table_name):
f=open(table_name,'rb')
demo_list=pickle.load(f)
f.close()
return demo_list
else:
print('Sorry,your enter table is not exist!')
return False
这里要回过头对之前的sel_inner_func()函数做修改：
import pickle,os
key_list=['name','age','phone','dept','enroll_date']
demo_dict={}
def select_func(sql):
'''judge sql is correct length'''
sql=sql.split(' ')
if len(sql) == 8:
demo_list=from_table(sql)
if demo_list:
result=sel_inner_func(sql,demo_list)
else:
result='ERROR'
else:
result='ERROR'
return result
def sel_inner_func(right_sql,demo_list):
'''if sql is 8 characters length and return demo_list or part of demo_list'''
args=right_sql[1]
args_list=args.split(',')
if args == '*':
return demo_list[:]
else:
for i in args_list:
raw_sel_list=[]
if i in key_list:
demo_dict[i]=raw_sel_list
key_index=key_list.index(i)+1
for list in demo_list:
raw_sel_list.append(list[key_index])
else:
print('Sorry,your enter is invaild!')
return demo_dict
def from_table(right_sql):
'''read table'''
table_name=right_sql[3]
if os.path.exists(table_name):
f=open(table_name,'rb')
demo_list=pickle.load(f)
f.close()
return demo_list
else:
print('Sorry,your enter table is not exist!')
return False
可以看到from_table()函数必须放在sel_inner_func()函数的前面，先把文件中员工信息列表pickle出来，再传给sel_inner_func函数。
这里里面函数返回的是一个字典，sql语句中的Key对应查询列表中的值（查询列表用来for list in demo_list语句循环，为的是把demo_list里的子列表都过一遍）
对where语句的判断
同理，对where语句的判断也应该放在sel_inner_func()函数的前面，经过无数次完善（中间省略一万字），我们暂时得到了一个初始版本的判断函数：
def where(right_sql,demo_list):
'''where ...'''
args=right_sql[5]
judge=right_sql[6]
values=right_sql[7]
def select_after_where_func():
candidate_list=[]
filter_list=[]
for list in demo_list:
candidate_list.append(list[key_index])
if judge == '=':
if values == candidate_list[-1]:
filter_list.append(list)
elif judge == '>':
if values.isdigit():
int_values=int(values)
determine_values=int(candidate_list[-1])
if determine_values > int_values:
filter_list.append(list)
else:
print('Your enter {} is not a digit!'.format())
elif judge == '<':
if values.isdigit():
int_values=int(values)
determine_values=int(candidate_list[-1])
if determine_values < int_values:
filter_list.append(list)
else:
print('Your enter {} is not a digit!'.format(values))
else:
print('select after where function is worry!')
return False
return filter_list
if args in key_list:
if judge in judge_list:
key_index=key_list.index(args)+1
demo_list=select_after_where_func()
return demo_list
else:
print('Sorry,"{}" is not a legal args'.format(judge))
return False
else:
print('Sorry,"{}" is not a legal args'.format(args))
return False
大函数where()里面嵌套了一个子函数select_after_where_func()函数，用途是对'=','>','<'这些语句进行判断，返回一个大列表（里面包含数个子列表）
还需要统计出查询到的条数
我们在最后打印的时候，写打印项目数的语句，sel_inner_func函数返回的值是一个元组，里面包含两个元素。同时要做一个判断，对返回结果是否有两个元素（查询结果字典，项目数）做判断，有的打印出来了，实现的代码如下：
raw_sel_result=select_func(enter)
if len(raw_sel_result) != 2:
print('Error!Something worry...')
else:
sel_result=raw_sel_result[0]
sel_len=raw_sel_result[1]
print(sel_result)
print('Find {} items'.format(sel_len))
完整的查询模块代码如下
#select function module
import pickle,os,file_module
dirname=os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
database_dir=dirname+os.sep+'database'
key_list=['name','age','phone','dept','enroll_date']
judge_list=['>','<','=','like']
def select_func(sql):
'''judge sql is correct length'''
sql=sql.split(' ')
if len(sql) == 8:
demo_list=from_table(sql)
if demo_list:
demo_list=where(sql,demo_list)
if demo_list:
result=sel_inner_func(sql,demo_list)
if len(result) != 2:
print('Error!Something worry...')
else:
sel_result=result[0]
sel_len=result[1]
print(sel_result)
print('Find {} items'.format(sel_len))
else:
result='Sorry,do not find!'
else:
result='ERROR'
else:
print('Sorry,you enter lenght is not correct!')
result='ERROR'
return result
def sel_inner_func(right_sql,demo_list):
'''if sql is 8 characters length and return demo_list or part of demo_list'''
demo_dict={}
len_list=0
args=right_sql[1]
args_list=args.split(',')
if args == '*':
len_list=len(demo_list)
return demo_list[:],len_list
else:
for i in args_list:
raw_sel_list=[]
if i in key_list:
demo_dict[i]=raw_sel_list
key_index=key_list.index(i)+1
for list in demo_list:
raw_sel_list.append(list[key_index])
len_list=len_list+len(raw_sel_list)
else:
print('Sorry,your enter is invaild!')
break
return demo_dict,len_list
def from_table(right_sql):
'''read table'''
table_name=right_sql[3]
table_file=database_dir+os.sep+table_name
if os.path.exists(table_file):
f=open(table_file,'rb')
demo_list=pickle.load(f)
f.close()
return demo_list
else:
print('Sorry,your enter table is not exist!')
return False
def where(right_sql,demo_list):
'''where ...'''
args=right_sql[5]
judge=right_sql[6]
values=right_sql[7]
def select_after_where_func():
candidate_list=[]
filter_list=[]
for list in demo_list:
candidate_list.append(list[key_index])
if judge == '=':
if values == candidate_list[-1]:
filter_list.append(list)
elif judge == '>':
if values.isdigit() and candidate_list[-1]:
int_values=int(values)
determine_values=int(candidate_list[-1])
if determine_values > int_values:
filter_list.append(list)
else:
print('Your enter {} is not a digit!'.format())
elif judge == '<':
if values.isdigit() and candidate_list[-1]:
int_values=int(values)
determine_values=int(candidate_list[-1])
if determine_values < int_values:
filter_list.append(list)
else:
print('Your enter {} is not a digit!'.format(values))
elif judge == 'like':
if candidate_list[-1].find(values) != -1:
filter_list.append(list)
else:
pass
else:
print('select after where function is worry!')
return False
return filter_list
if args in key_list:
if judge in judge_list:
key_index=key_list.index(args)+1
demo_list=select_after_where_func()
return demo_list
else:
print('Sorry,"{}" is not a legal args'.format(judge))
return False
else:
print('Sorry,"{}" is not a legal args'.format(args))
return False
向表格插入数据的语法是INSERT INTO staff_table name,phone VALUES houyizhong,18011112222
表格名称在第三位index[2]，键值在第四位index[3]，插入数据在最后一位index[-1]，语句总长度在6
需要判断是第三位是否存在表格，第四位是否是键值，最后位value数是否对得上键值数
实现代码如下：
insert employee information
version:demo
import os,pickle
key_list=['name','age','phone','dept','enroll_date']
def main_func(sql):
'''judge sql is correct length'''
sql=sql.split(' ')
if len(sql) == 6:
demo_list=from_table(sql)
if demo_list:
demo_dict=insert_func(sql,demo_list)
if demo_dict:
result=insert_table_func(demo_dict,demo_list)
if len(result) == 6:
demo_list.append(result)
write_to_file(demo_list)
print('You insert staff table:\n {}'.format(result))
else:
result='ERROR'
else:
result='ERROR'
else:
print('Sorry,you enter lenght is not correct!')
result='ERROR'
return result
def from_table(right_sql):
'''read table'''
table_name=right_sql[2]
if os.path.exists(table_name):
f=open(table_name,'rb')
demo_list=pickle.load(f)
f.close()
return demo_list
else:
print('Sorry,your enter table is not exist!')
return False
def insert_func(right_sql,demo_list):
'''insert employee info into table'''
demo_dict={}
args=right_sql[3]
values=right_sql[-1]
args_list=args.split(',')
values_list=values.split(',')
def judge_phone():
for list in demo_list:
if phone in list:
print(phone)
print(list)
return False
else:
pass
return True
if len(args_list) == len(values_list):
if 'phone' in args_list:
phone_index=args_list.index('phone')
phone=values_list[phone_index]
if judge_phone():
for arg in args_list:
if arg in key_list:
value_index=args_list.index(arg)
value=values_list[value_index]
demo_dict[arg]=value
else:
print('Sorry,your enter args is invaild!')
demo_dict={}
break
else:
print('Sorry,you enter phone is exist!')
else:
print('Sorry,the phone is UNIQUE')
else:
print('Sorry,you enter values is invaild!')
return demo_dict
def insert_table_func(insert_dict,demo_list):
'''insert null and staff_id'''
insert_list=[]
number=len(demo_list)+1
insert_list.insert(0,number)
for i in key_list:
info_values=insert_dict.get(i,'NULL')
insert_list.append(info_values)
return insert_list
def write_to_file(demo_list):
'''write to file'''
f=open('staff_table','wb')
pickle.dump(demo_list,f)
f.close()
enter=input('>>>')
insert_result=main_func(enter)
删除数据
删除数据的语句定义为delete from staff_table id * 语句长度固定为5
实现代码为：
import os,pickle
def main_func(sql):
'''judge sql is correct length'''
sql=sql.split(' ')
if len(sql) == 5:
demo_list=from_table(sql)
if demo_list:
result=delete_func(sql,demo_list)
if result:
write_to_file(result)
print('Delete done!')
else:
result='ERROR'
else:
result='ERROR'
else:
print('Sorry,you enter lenght is not correct!')
result='ERROR'
return result
def from_table(right_sql):
'''read table'''
table_name=right_sql[2]
if os.path.exists(table_name):
f=open(table_name,'rb')
demo_list=pickle.load(f)
f.close()
return demo_list
else:
print('Sorry,your enter table is not exist!')
return False
def write_to_file(demo_list):
'''write to file'''
f=open('staff_table','wb')
pickle.dump(demo_list,f)
f.close()
def delete_func(right_sql,demo_list):
delete_id=right_sql[-1]
delete_id=delete_id.split(',')
for id in delete_id:
if id.isdigit():
id=int(id)
if 0 < id <= len(demo_list):
id=id - 1
demo_list.pop(id)
return demo_list
else:
print('Sorry,you enter id is not exist')
else:
print('Sorry,you enter id invaild')
enter=input('>>>')
delete_result=main_func(enter)
update功能
语句UPDATE staff_table SET dept=Market where dept = "IT"，长度为8，[3]为选项，定义列表key和value对应关系
import pickle,os
key_list=['name','age','phone','dept','enroll_date']
judge_list=['>','<','=','like']
def main_func(sql):
'''judge sql is correct length'''
sql=sql.split(' ')
if len(sql) == 8:
demo_list=from_table(sql)
if demo_list:
demo_list2=where(sql,demo_list)
if demo_list2:
print(demo_list2)
result=set_func(sql,demo_list2)
if result:
print(demo_list)
write_to_file(demo_list)
else:
result='Sorry,do not find!'
else:
result='ERROR'
else:
print('Sorry,you enter lenght is not correct!')
result='ERROR'
return result
def set_func(right_sql,demo_list):
'''if sql is 8 characters length and return demo_list or part of demo_list'''
args=right_sql[3]
args_list=args.split('=')
key_args=args_list[0]
values_args=args_list[1]
if key_args in key_list:
key_index=key_list.index(key_args)+1
for list in demo_list:
list.pop(key_index)
list.insert(key_index,values_args)
return demo_list
else:
print('Sorry,your enter key invaild.')
def from_table(right_sql):
'''read table'''
table_name=right_sql[1]
if os.path.exists(table_name):
f=open(table_name,'rb')
demo_list=pickle.load(f)
f.close()
return demo_list
else:
print('Sorry,your enter table is not exist!')
return False
def where(right_sql,demo_list):
'''where ...'''
args=right_sql[5]
judge=right_sql[6]
values=right_sql[7]
def update_after_where_func():
candidate_list=[]
filter_list=[]
for list in demo_list:
candidate_list.append(list[key_index])
if judge == '=':
if values == candidate_list[-1]:
filter_list.append(list)
elif judge == '>':
if values.isdigit() and candidate_list[-1].isdigit():
int_values=int(values)
determine_values=int(candidate_list[-1])
if determine_values > int_values:
filter_list.append(list)
else:
print('Your enter {} is not a digit!'.format(args))
elif judge == '<':
if values.isdigit() and candidate_list[-1].isdigit():
int_values=int(values)
determine_values=int(candidate_list[-1])
if determine_values < int_values:
filter_list.append(list)
else:
print('Your enter {} is not a digit!'.format(args))
else:
print('select after where function is worry!')
return False
return filter_list
if args in key_list:
if judge in judge_list:
key_index=key_list.index(args)+1
demo_list=update_after_where_func()
return demo_list
else:
print('Sorry,"{}" is not a legal args'.format(judge))
return False
else:
print('Sorry,"{}" is not a legal args'.format(args))
return False
def write_to_file(demo_list):
'''write to file'''
f=open('staff_table','wb')
pickle.dump(demo_list,f)
f.close()
enter=input('>>>')
update_result=main_func(enter)
最后是操作手册
##操作手册
查询语句，支持以下三种方式，用空格分开，查询条件','分开
select * from staff_table where age > 23
select name,age from staff_table where dept = IT
select name,phone from staff_table where enroll_date like 2015
插入语句（可小写） INTO后面是key，按','分开，一一对应VALUES后面的值，其中phone键是唯一键，id自增无需输入
INSERT INTO staff_table name,age,phone,dept,enroll_date VALUES root,23,18000000,IT,2017-01-01
更新语句，其中dept=market是更新项，前一个是key后一个是values，中间是=，dept = IT 是筛选，用空格分开
update staff_table set dept=market where dept = IT
删除语句，输入id即可删除
delete from staff_table id 4
初始化数据库
chushihua
退出
q
