python实现增删改查_python基础之实现sql增删改查

#encoding:utf-8#Author:"richie"#Date:2017/8/2

importre

key_l= ['id', 'name', 'age', 'phone', 'job']#提示信息

tip_str = "示例：\n\

select name, age where age>22\n\

select * where job=IT\n\

select * where phone like 133\n\

insert employee values ('Gates', '25', '17749792796', 'Teacher')\n\

delete from employee where name=egon\n\

update person set name=richie where age=22\"flag=True

file_name= 'data.txt'

defread_file(filename):"""读文件

:return:"""l=[]

with open(filename, encoding='utf-8') as f_r:for line inf_r.readlines():

line= line.strip().split(',')

l.append(line)returnldefwrite_file(f_name, data_l):"""写文件

:return:"""with open(f_name,'w', encoding='utf-8') as f_w:for item indata_l:#逗号分割写入文件

l = ','.join(item)

f_w.writelines(l+'\n')return 'write ok'

defcondition_raw(col, select_l):"""查询行

:param col:

:param select_l:

:return:"""r_l= [] #返回最终结果序列

new_l = [] #每一行的索引

for item incol:for i inselect_l:

index=key_l.index(i)

new_l.append(item[index])

r_l.append(new_l)

new_l=[]returnr_ldefcondition_col(term_l, message_l):"""查询列

:param term_l:

:param message_l:

:return:"""_l=[]if len(term_l) == 1 and '>' interm_l[0]:

l= term_l[0].split('>')

index=key_l.index(l[0])for item inmessage_l:if int(item[index]) > int(l[1]):

_l.append(item)elif len(term_l) == 1 and '

l= term_l[0].split('

index=key_l.index(l[0])for item inmessage_l:if int(item[index]) < int(l[1]):

_l.append(item)elif len(term_l) == 1 and '=' interm_l[0]:

l= term_l[0].split('=')

index=key_l.index(l[0])for item inmessage_l:if item[index].lower() == l[1].lower():

_l.append(item)elif term_l[0] in key_l: #判断输入的索引名是否在信息列表中

index =key_l.index(term_l[0])for item inmessage_l:if term_l[2] initem[index]:

_l.append(item)return_ldefselect_command(cmd, message_l):"""查询命令

:param cmd:

:param message_l:

:return:"""result_l=[]if 'where' not in cmd and '*' incmd:

result_l=message_lelif 'where' not incmd:#选择行

select_l = cmd[1:]

result_l=condition_raw(message_l, select_l)else:

where_index= cmd.index('where')

select_l= cmd[1:where_index]

term_l= cmd[where_index + 1:]if len(select_l) == 1 and select_l[0] == '*':#选择列

result_l =condition_col(term_l, message_l)elifset(select_l).issubset(set(key_l)):#选择列

col =condition_col(term_l, message_l)#选择行

result_l =condition_raw(col, select_l)#打印输出结果

for item inresult_l:ifisinstance(item, list):for i initem:print(i, end=' ')else:print(item, end=' ')print()return 'select ok'

defdelete_command(f_name, cmd, message_l):"""删除命令

:param f_name: 文件名

:param cmd: 命令

:param message_l: 员工信息列表

:return: 写入是否成功"""where_index= cmd.index('where')

term_l= cmd[where_index + 1:]#查询要删除的列

re_l =condition_col(term_l, message_l)#删除要删除的列

for item inre_l:

message_l.remove(item)#写入文件

returnwrite_file(f_name, message_l)definsert_command(f_name, cmd, message_l):"""插入数据

:param f_name: 文件名

:param cmd: 输入的命令

:param message_l: 员工信息表

:return: 写入文件是否成功"""index=0for item incmd:#移除cmd中的括号

if '(' == item or ')' ==item:

cmd.remove(item)

index= cmd.index('values')#切片获取命令

insert_data = cmd[index+1:]#插入数据

insert_data.insert(0, '%d' % (len(message_l) + 1))

message_l.append(insert_data)returnwrite_file(f_name, message_l)defupdate_command(f_name, cmd, message_l):"""修改数据

:param f_name: 文件名

:param cmd: 命令

:param message_l: 员工信息

:return: 写入是否成功"""where_index= cmd.index('where')

before_l= cmd[where_index-1].split('=')

key_index=key_l.index(before_l[0])

term_l= cmd[where_index + 1:]

re_l=condition_col(term_l, message_l)for item inre_l:

i=message_l.index(item)

message_l.remove(item)

item[key_index]= before_l[1]

message_l.insert(i, re_l[0])returnwrite_file(f_name, message_l)defuser_login():"""用户登录

:return:"""name= input('plz input account>>:').strip()

pass_wd= input('plz input password>>:').strip()if name == 'richie' and pass_wd == '123':print('login ok')return 1

else:print('login failed')return0defwrapper(f):def inner(*args, **kwargs):"""装饰器，用户登录

:param args:

:param kwargs:

:return:"""

ifuser_login():print('\n' * 150) #伪清屏

return f(*args, **kwargs)returninner

@wrapper#装饰登陆功能

deffunc(f_name, message_l):"""用户查询功能函数

:param message_l:

:return:"""

print('welcome to the User management system')whileTrue:print("命令说明：\n\033[1;35m" + tip_str + "\033[0m")

cmd= input('please input your commend>>:')if len(cmd) ==0:continue

elif cmd == 'exit':breakresult= ''cmd= [x for x in re.split('|\'|,', cmd) ifx]if cmd[0] == 'select':

result=select_command(cmd, message_l)elif cmd[0] == 'insert':

result=insert_command(f_name, cmd, message_l)elif cmd[0] == 'update':

result=update_command(f_name, cmd, message_l)elif cmd[0] == 'delete':

result=delete_command(f_name, cmd, message_l)print(result)#函数调用执行

msg_l =read_file(file_name)

func(file_name, msg_l)