python基础之实现sql增删改查

# encoding:utf-8
# Author:"richie"
# Date:2017/8/2
import re 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' def read_file(filename):
"""
读文件
:return:
"""
l = []
with open(filename, encoding='utf-8') as f_r:
for line in f_r.readlines():
line = line.strip().split(',')
l.append(line)
return l def write_file(f_name, data_l):
"""
写文件
:return:
"""
with open(f_name, 'w', encoding='utf-8') as f_w:
for item in data_l:
# 逗号分割写入文件
l = ','.join(item)
f_w.writelines(l+'\n')
return 'write ok' def condition_raw(col, select_l):
"""
查询行
:param col:
:param select_l:
:return:
"""
r_l = [] # 返回最终结果序列
new_l = [] # 每一行的索引 for item in col:
for i in select_l:
index = key_l.index(i)
new_l.append(item[index])
r_l.append(new_l)
new_l = []
return r_l def condition_col(term_l, message_l):
"""
查询列
:param term_l:
:param message_l:
:return:
"""
_l = []
if len(term_l) == 1 and '>' in term_l[0]:
l = term_l[0].split('>')
index = key_l.index(l[0])
for item in message_l:
if int(item[index]) > int(l[1]):
_l.append(item)
elif len(term_l) == 1 and '<' in term_l[0]:
l = term_l[0].split('<')
index = key_l.index(l[0])
for item in message_l:
if int(item[index]) < int(l[1]):
_l.append(item)
elif len(term_l) == 1 and '=' in term_l[0]:
l = term_l[0].split('=')
index = key_l.index(l[0])
for item in message_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 in message_l:
if term_l[2] in item[index]:
_l.append(item)
return _l def select_command(cmd, message_l):
"""
查询命令
:param cmd:
:param message_l:
:return:
"""
result_l = []
if 'where' not in cmd and '*' in cmd:
result_l = message_l
elif 'where' not in cmd:
# 选择行
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)
elif set(select_l).issubset(set(key_l)):
# 选择列
col = condition_col(term_l, message_l)
# 选择行
result_l = condition_raw(col, select_l) # 打印输出结果
for item in result_l:
if isinstance(item, list):
for i in item:
print(i, end=' ')
else:
print(item, end=' ')
print()
return 'select ok' def delete_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 in re_l:
message_l.remove(item)
# 写入文件
return write_file(f_name, message_l) def insert_command(f_name, cmd, message_l):
"""
插入数据
:param f_name: 文件名
:param cmd: 输入的命令
:param message_l: 员工信息表
:return: 写入文件是否成功
"""
index = 0
for item in cmd:
# 移除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)
return write_file(f_name, message_l) def update_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 in re_l:
i = message_l.index(item)
message_l.remove(item)
item[key_index] = before_l[1]
message_l.insert(i, re_l[0])
return write_file(f_name, message_l) def user_login():
"""
用户登录
:return:
"""
name = input('plz input account>>:').strip()
pass_wd = input('plz input password>>:').strip()
if name == 'richie' and pass_wd == '':
print('login ok')
return 1
else:
print('login failed')
return 0 def wrapper(f):
def inner(*args, **kwargs):
"""
装饰器,用户登录
:param args:
:param kwargs:
:return:
"""
if user_login():
print('\n' * 150) # 伪清屏
return f(*args, **kwargs)
return inner @wrapper # 装饰登陆功能
def func(f_name, message_l):
"""
用户查询功能函数
:param message_l:
:return:
"""
print('welcome to the User management system') while True:
print("命令说明:\n\033[1;35m" + tip_str + "\033[0m")
cmd = input('please input your commend>>:')
if len(cmd) == 0:
continue
elif cmd == 'exit':
break
result = ''
cmd = [x for x in re.split(' |\'|, ', cmd) if x]
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)

文件数据

1,Alex,22,13651054608,IT
2,Egon,23,13304320533,Tearcher
3,nezha,25,1333235322,IT
4,Gates,25,17749792796,Teacher
5,Gates,25,17749792796,Teacher
# encoding:utf-8
# Author:"richie"
# Date:2017/8/2
import re 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' def read_file(filename):
"""
读文件
:return:
"""
l = []
with open(filename, encoding='utf-8') as f_r:
for line in f_r.readlines():
line = line.strip().split(',')
l.append(line)
return l def write_file(f_name, data_l):
"""
写文件
:return:
"""
with open(f_name, 'w', encoding='utf-8') as f_w:
for item in data_l:
# 逗号分割写入文件
l = ','.join(item)
f_w.writelines(l+'\n')
return 'write ok' def condition_raw(col, select_l):
"""
查询行
:param col:
:param select_l:
:return:
"""
r_l = [] # 返回最终结果序列
new_l = [] # 每一行的索引 for item in col:
for i in select_l:
index = key_l.index(i)
new_l.append(item[index])
r_l.append(new_l)
new_l = []
return r_l def condition_col(term_l, message_l):
"""
查询列
:param term_l:
:param message_l:
:return:
"""
_l = []
if len(term_l) == 1 and '>' in term_l[]:
l = term_l[].split('>')
index = key_l.index(l[])
for item in message_l:
if int(item[index]) > int(l[]):
_l.append(item)
elif len(term_l) == 1 and '<' in term_l[]:
l = term_l[].split('<')
index = key_l.index(l[])
for item in message_l:
if int(item[index]) < int(l[]):
_l.append(item)
elif len(term_l) == 1 and '=' in term_l[]:
l = term_l[].split('=')
index = key_l.index(l[])
for item in message_l:
if item[index].lower() == l[].lower():
_l.append(item)
elif term_l[] in key_l: # 判断输入的索引名是否在信息列表中
index = key_l.index(term_l[])
for item in message_l:
if term_l[] in item[index]:
_l.append(item)
return _l def select_command(cmd, message_l):
"""
查询命令
:param cmd:
:param message_l:
:return:
"""
result_l = []
if 'where' not in cmd and '*' in cmd:
result_l = message_l
elif 'where' not in cmd:
# 选择行
select_l = cmd[:]
result_l = condition_raw(message_l, select_l)
else:
where_index = cmd.index('where')
select_l = cmd[:where_index]
term_l = cmd[where_index + :]
if len(select_l) == 1 and select_l[] == '*':
# 选择列
result_l = condition_col(term_l, message_l)
elif set(select_l).issubset(set(key_l)):
# 选择列
col = condition_col(term_l, message_l)
# 选择行
result_l = condition_raw(col, select_l) # 打印输出结果
for item in result_l:
if isinstance(item, list):
for i in item:
print(i, end=' ')
else:
print(item, end=' ')
print()
return 'select ok' def delete_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 + :]
# 查询要删除的列
re_l = condition_col(term_l, message_l)
# 删除要删除的列
for item in re_l:
message_l.remove(item)
# 写入文件
return write_file(f_name, message_l) def insert_command(f_name, cmd, message_l):
"""
插入数据
:param f_name: 文件名
:param cmd: 输入的命令
:param message_l: 员工信息表
:return: 写入文件是否成功
"""
index = 0
for item in cmd:
# 移除cmd中的括号
if '(' == item or ')' == item:
cmd.remove(item)
index = cmd.index('values')
# 切片获取命令
insert_data = cmd[index+:]
# 插入数据
insert_data.insert(, '%d' % (len(message_l) + ))
message_l.append(insert_data)
return write_file(f_name, message_l) def update_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-].split('=')
key_index = key_l.index(before_l[])
term_l = cmd[where_index + :]
re_l = condition_col(term_l, message_l)
for item in re_l:
i = message_l.index(item)
message_l.remove(item)
item[key_index] = before_l[]
message_l.insert(i, re_l[])
return write_file(f_name, message_l) def user_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')
return 0 def wrapper(f):
def inner(*args, **kwargs):
"""
装饰器,用户登录
:param args:
:param kwargs:
:return:
"""
if user_login():
print('\n' * ) # 伪清屏
return f(*args, **kwargs)
return inner @wrapper # 装饰登陆功能
def func(f_name, message_l):
"""
用户查询功能函数
:param message_l:
:return:
"""
print('welcome to the User management system') while True:
print("命令说明:\n\033[1;35m" + tip_str + "\033[0m")
cmd = input('please input your commend>>:')
if len(cmd) == :
continue
elif cmd == 'exit':
break
result = ''
cmd = [x for x in re.split(' |\'|, ', cmd) if x]
if cmd[] == 'select':
result = select_command(cmd, message_l)
elif cmd[] == 'insert':
result = insert_command(f_name, cmd, message_l)
elif cmd[] == 'update':
result = update_command(f_name, cmd, message_l)
elif cmd[] == 'delete':
result = delete_command(f_name, cmd, message_l)
print(result)
# 函数调用执行
msg_l = read_file(file_name)
func(file_name, msg_l)
文件数据

1,Alex,22,13651054608,IT
2,Egon,23,13304320533,Tearcher
3,nezha,25,1333235322,IT
4,Gates,25,17749792796,Teacher
5,Gates,25,17749792796,Teacher

上一篇:baseadapter.getItemId的使用方法:实现listview筛选、动态删除


下一篇:java web 学习七(HttpServletResponse对象1)