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', '17700002796', '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 == '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' * 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,17700002796,Teacher
5,Gates,25,17700002796,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', '17700002796', '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 == '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' * 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,17700002796,Teacher
5,Gates,25,17700002796,Teacher