1,Alex Li,22,13651054608,IT,2013‐04‐01
2,Jack Wang,28,13451024608,HR,2015‐01‐07
3,Rain Wang,21,13451054608,IT,2017‐04‐01
4,MacQiao,44,15653354208,Sales,2016‐02‐01
5,Rachel Chen,23,13351024606,IT,2013‐03‐16
6,Eric Liu,19,18531054602,Marketing,2012‐12‐01
7,Chao Zhang,21,13235324334,Administration,2011‐08‐08
8,Kevin Chen,22,13151054603,Sales,2013‐04‐01
9,Shit Wen,20,13351024602,IT,2017‐07‐03
10,Shanshan Du,26,13698424612,Operation,2017‐07‐02
需求:
1.可进行模糊查询,语法至少支持下面3种:
1.select name,age from staff_table where age > 22
2.select * from staff_table where dept = IT
3.查到的信息,打印后,最后面还要显示查到的条数
2.可创建新员工纪录,以phone做唯一键,staff_id需自增
3.可删除指定员工信息纪录,输入员工id,即可删除
4.可修改员工信息,语法如下:
1.UPDATE staff_table SET dept= 无 where dept = IT
import os
def table_info():
# 读取文件,并转化为列表
date_file = open('data.txt','r',encoding='utf-8')
lines = date_file.readlines()
data = []
for line in lines:
data.append(line.split(','))
return data
date_file.close()
table_info()
def qurey1(x):
count = 0
for line in table_info():
if line[2] > '22':
d1[count] = [line[1], line[2]]
count += 1
print('name', ' ', 'age')
for i in d1.keys():
print(d1[i][0], ',', d1[i][1])
print('满足本次查询的个数为:', count)
def qurey2(x):
count = 0
str = x[1:3]
for line in table_info():
if line[4] == str:
d1[count] = line
count += 1
for i in d1.values():
for m in i:
print(m, end=' ')
print('满足本次查询的个数为:', count)
def qurey3(x):
count = 0
str = x[1:5]
for line in table_info():
if line[-1][:4] == str:
d1[count] = line
count += 1
for i in d1.values():
for m in i:
print(m, end=' ')
print('满足本次查询的个数为:', count)
def delete():
while True:
count = 0
print("退出请输入Q")
input1 = input('请输入新建命令的SQL语句:').strip()
input_del = input1.split(' ')
if input1 == "Q" or input1 == 'q':
break
else:
del_id = input_del[-1].split('=')[-1]
with open('data.txt','r',encoding='utf-8') as f_read:
lines = f_read.readlines()
with open('data.txt','w',encoding='utf-8') as f_wirte:
for line in lines:
if del_id == line.split(',')[0]:
count +=1
continue
else:
f_wirte.write(line)
print('本次删除的数目为:',count)
def new():
while True:
print("退出请输入Q")
input_a = input('请输入新建命令的SQL语句:').strip()
input_add = input_a.split(' ')
if input_a == 'Q' or input_a == 'q':
print('返回到第一层!')
break
else:
name = input_add[2]
msg = input_add[3]
phone = msg.split(',')[2]
count = 0
num = 0
for line in table_info():
count += 1
if int(line[3]) == int(phone):
print('您输入的用户已存在')
newstr = str(count + 1) + ',' + name + ','
f = open('data.txt', 'a', encoding='utf-8')
f.writelines('\n ')
f.writelines(newstr)
f.writelines(msg)
f.close()
num += 1
print('\033[1;35m 用户已添加 \033[0m'.center(10, '.'))
print('本次操作的命令行数为',num)
def modify():
while True:
print("""支持以下语句修改员工信息,包括部门名称与年龄:')
UPDATE staff_table SET dept="Market" WHERE dept = "IT"')
UPDATE staff_table SET age=25 WHERE name = "Alex Li"')
退出请按Q""")
print('请输入您要操作的语句:')
user_mod = input('<<<').strip()
user_msg = user_mod.split(' ')[3].split('=')
# 提取年龄
if user_mod == "q" or user_mod == "Q":
print('退出到上一层!')
break
elif user_msg[0] == 'age':
aft = user_msg[1]
name = user_mod.split('"')[-2]
count = 0
with open('data.txt', 'r+', encoding='utf-8') as f1:
lines = f1.readlines()
with open('data.txt', 'w', encoding='utf-8') as f2:
for line in lines:
if name in line:
bef = line.split(',')[2]
line = line.replace(bef, aft)
print(1)
count += 1
f2.write(line)
print('信息已更新!')
print('本次更新信息数目为:', count)
else:
input_update = user_mod.split('"')
before = input_update[3]
after = input_update[1]
count = 0
with open('data.txt', 'r+', encoding='utf-8') as f1:
lines = f1.readlines()
with open('data.txt', 'w', encoding='utf-8') as f2:
for line in lines:
if before in line:
line = line.replace(before, after)
count += 1
f2.write(line)
print('信息已更新!')
print('本次更新信息数目为:', count)
print('欢迎来到员工查询系统'.center(40,'*'))
while True:
print('''请根据以下提示输入对应指令序号:')
1.查询员工信息')
2.创建员工信息')
3.删除员工信息')
4.修改员工信息''')
input_num = int(input('>>>>>').strip())
if input_num == 1:
while True:
print('退出请按Q')
print('我们支持以下查询语句'.center(40,'-'))
print('''
find name,age from staff_table where age > 22'
find * from staff_table where dept = "IT"'
find * from staff_table where enroll_date like "2013"'
请输入您的查询语句:''')
input_sent = input('<<<<').strip()
input_sent1 = input_sent.split(' ')
d1 = {}
print("本次查询的结果如下:".center(20, "-"))
if input_sent == 'find name,age from staff_table where age > %s' % (input_sent1[-1]):
qurey1(input_sent1[-1])
elif input_sent == 'find * from staff_table where dept = %s' % (input_sent1[-1]):
qurey2(input_sent1[-1])
elif input_sent == 'find * from staff_table where enroll_date like %s' % (input_sent1[-1]):
qurey3(input_sent1[-1])
elif input_sent == "Q" or input_sent == 'q':
break
else:
print('您的输入格式有误,请重新输入!')
elif input_num == 2:
new()
elif input_num == 3:
delete()
elif input_num == 4:
modify()
else:
print('您输入有误,请重新输入')
问题:table_info 函数其实可以改成modify等函数的调用方式