Python 基础

作业要求及初步思路

员工信息表程序，实现增删改查操作：

① 可进行模糊查询，语法至少支持下面3种:

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"

• 解决方案： sql语句的python解析问题，即将用户输入的sql语句转为我们代码可以正确执行的动作。针对关键字where, values, limit, set 和 like等，新建字典作为key值。解析sql语句后，将相应信息赋值为value。 针对表字段name 和 age, 用for 循环打印字典获取key为name 和 age的value。

② 查到的信息，打印后，最后面还要显示查到的条数

• 解决方案： 读取json文件到字典，满足条件查询到后加入New_Dict, 运用len()计算key值。

③ 可创建新员工纪录，以phone做唯一键，staff_id需自增

• 解决方案： 以phone number作为唯一键，即在创建主要字典存入json的格式为： {1390000000: {'name': 'may Lol', 'age': 23, 'staff_id': xxxxxx, 'enroll_date': xxxxxxxx}}
• staff_id 自增， 需要建立在有序字典， 导入collection 模块， collections.OrderedDict

④ 可删除指定员工信息纪录，输入员工id，即可删除

• 删除字典的key和value

⑤ 可修改员工信息，语法如下:

UPDATE staff_table SET dept="Market" where dept = "IT"

• 同①

⑥ 以上需求，要充分使用函数，请尽你的最大限度来减少重复代码

大约设计的函数有：

• loaded_from_file: 读取json文件
• write_into_file: 写入json文件
• sql_parse: sql语句解析
• add, delete, update, search: 增撒改查
• main: 入库函数

知识点

1. 项目文件格式规范： 看这里

2. 不同目录文件读取

我的笨办法：

root = os.path.dirname(os.path.dirname(os.path.dirname(__file__)))
file_path = root + r"/docs/" + filename

新潮写法：动态的运用绝对路径 os.path.abspath

Base_dir = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
sys.path.append(Base_dir)  # 添加环境变量
from core import main

3. 有序字典

from collections import OrderedDict

我们都知道，字典的key都是无序的。但在本项目中，staff_id是mutually execusive的。所以需要将key有序化，并每增加一个员工，staff_id自动添加1。

collections.OrderedDict的特点：

• OrderedDict的Key会按照插入的顺序排列，不是Key本身排序
• OrderedDict可以实现一个FIFO（先进先出）的dict，当容量超出限制时，先删除最早添加的Key

用法：

from collections import OrderedDict
Ordered_d = OrderedDict([("a",1),("b",2), ("c", 3)])
print(Ordered_d)
# 返回 OrderedDict([('a', 1), ('b', 2), ('c', 3)])

# OrderedDict的key会按照插入的顺序排列，而不是key本身排列
Ordered_d["z"] = 26
Ordered_d["y"] = 25
Ordered_d["x"] = 24
print(Ordered_d)
# 返回 OrderedDict([('a', 1), ('b', 2), ('c', 3), ('z', 26), ('y', 25), ('x', 24)])

详见 Module - collections

4. 表格打印： prettytable 模块 

from prettytable import PrettyTable

from prettytable import PrettyTable
table = PrettyTable(["animal", "ferocity"])
table.add_row(["wolverine", 100])
table.add_row(["grizzly", 87])
table.add_row(["Rabbit of Caerbannog", 110])
table.add_row(["cat", -1])
table.add_row(["platypus", 23])
table.add_row(["dolphin", 63])
table.add_row(["albatross", 44])
table.sort_key("ferocity")
table.reversesort = True
print(table)

详见 Module - prettytable

4. python解析sql语言的执行过程： 这个是让人很头疼的问题，这篇文章写的简介明了。 代码如下：

#!usr/bin/env python
#-*- coding:utf-8 -*- 

# 一张学生表
name,age,class_number = range(3)  # 即 name = 0, age = 1, class_number = 2
zhangsan = ('zhangsan',18, 'Sfware class 1')
lisi = ("lisi",19,'Sfware class 1')
wangwu = ('wangwu', 19, 'Sfware class 2')

Students = { zhangsan, lisi, wangwu }

# 成绩表
name, course, score = range(3)
grade1 = ('zhangsan','math',60)
grade2 = ('zhangsan','C language', 70)
grade3 = ('lisi', 'math', 61)
grade4 = ('wangwu', 'math', 62)
grade5 = ('wangwu','C language', 63)

grades = {grade1, grade2, grade3,grade4,grade5}

# 前奏：遍历“学生表”找到姓名为“张三”的人, 将打印出来
res = [ s for s in Students if s[name] == "zhangsan"]
print(res)  # [('zhangsan', 18, 'Sfware class 1')]

# 高潮 - sql语句：select name,age,class from Students where name = "zhangsan"
# select 学生.姓名， 成绩.学科， 成绩.分数
# from 学生 left join 成绩 on 学生.姓名 = 成绩.姓名
# where 学生.姓名 = "张三"

# 以下解释如何sql语言的查询
# 第一步：笛卡尔积 Cartesian product
Cartesian_product = {(x,y) for x in Students for y in grades}
# print(Cartesian_product)

# 第二步： 增加过滤条件
student_table, grade_table = range(2)
'''写法一：
for x in Cartesian_product:
    if x[student_table][name] == x[grade_table][name] and x[student_table][name] == "zhangsan":
        print(x)
'''
# 写法二：
final_res = { (x[student_table][name],x[grade_table][course], x[grade_table][score]) for x in Cartesian_product if x[student_table][name] == x[grade_table][name] and x[student_table][name] == "zhangsan"}
print("最后结果", final_res)

My work

还存在问题：

1. 代码太长

2. 在select函数中，字段(e.g name 或 age) 只能选择两个字段，不能动态地根据要求变化搜索的字段。

#!usr/bin/env python
#-*- coding:utf-8 -*-
__author__ = "Jane Yue"

import os, json
from collections import OrderedDict
from prettytable import PrettyTable


def loaded_from_file(filename="staff_table.json"):
    # 切换到docs文件夹下读取文件，默认文件staff_table.json文件
    root = os.path.dirname(os.path.dirname(__file__))
    file_path = root + r"/docs/" + filename
    with open(file_path, "r+", encoding="utf-8") as file_object:
        staff_dict = OrderedDict(json.load(file_object))   # 有序字典
    return staff_dict


def write_into_file(staff_dict, filename="staff_table.json"):
    # 写入文件。默认文件docsstaff_table.json
    root = os.path.dirname(os.path.dirname(__file__))
    file_path = root + r"/docs/" + filename
    with open(file_path, "w+", encoding="utf-8") as file_object:
        json.dump(staff_dict, file_object)


def open_page():
    # 首页,入库函数
    print("".center(71, "-"))
    print("33[1;33mWelcome to WONDERLAND'S staff information platform33[0m".center(71, " "))
    print("".center(71, "-"))
    print("33[1;30mOperation Menu:33[0m")
    operation_table = PrettyTable(["operation", "sql sentence"])
    operation_table.add_row(["SEARCH", "select name,age from staff_table where age > 22"])
    operation_table.add_row(["", "select * from staff_table where dept = "IT""])
    operation_table.add_row(["", "select * from staff_table where enroll_date like "2013""])
    operation_table.add_row(["CREATE", "create phone#,name,age,dept,enroll_date"])
    operation_table.add_row(["DELETE", "delete staff_id"])
    operation_table.add_row(["UPDATE", "update staff_table set dept = "Market" where dept = "IT""])
    print(operation_table)
    operation_dict = {"select": search, "create": create, "delete": delete, "update": update}
    while True:
        sql = input("
33[1;31mPlease write your sql sentence accordingly33[0m 
>>>").strip()
        sql_list = sql.split(" ")
        if sql_list[0] in operation_dict.keys():
            operation_dict[sql_list[0]](sql)   # 调用函数
        else:
            print("Sql language errors. Please try again!")
            continue


def search(sql):
    # 查询
    staff_dict = loaded_from_file()
    search_sql_list = sql.replace(""", "").split()
    selection_field_list = search_sql_list[1].split(",")
    if search_sql_list[4] == "where":
        if search_sql_list[6] == ">":
            if "*" in selection_field_list:
                res_all = [[k["name"], k["age"], k["staff_id"], k["dept"], k["enroll_date"]] for i, k in
                           staff_dict.items() if int(k[search_sql_list[5]]) > int(search_sql_list[7])]
                print(res_all)
                y = PrettyTable(["name", "age", "staff_id", "dept", "enroll_date"])
                for j in res_all:
                    y.add_row(j)
                y.add_row(["", "", "", "33[1;31mSEARCH IN TOTAL33[0m", len(res_all)])
                print(y)
            else:
                res = [[k[selection_field_list[0]], k[selection_field_list[1]]] for i, k in staff_dict.items() if
                       int(k[search_sql_list[5]]) > int(search_sql_list[-1])]
                # 问题：这里只能按照要求答应下标为0和1，如何历遍字段列表search_sql_list答应所有
                x = PrettyTable(selection_field_list)
                for i in res:
                    x.add_row(i)
                x.add_row(["33[1;31mSEARCH IN TOTAL33[0m", len(res)])
                print(x)
        elif search_sql_list[6] == "<":
            if "*" in selection_field_list:
                res_all = [[k["name"], k["age"], k["staff_id"], k["dept"], k["enroll_date"]] for i, k in
                           staff_dict.items() if int(k[search_sql_list[5]]) < int(search_sql_list[7])]
                print(res_all)
                y = PrettyTable(["name", "age", "staff_id", "dept", "enroll_date"])
                for j in res_all:
                    y.add_row(j)
                y.add_row(["", "", "", "33[1;31mSEARCH IN TOTAL33[0m", len(res_all)])
                print(y)
            else:
                res = [[k[selection_field_list[0]], k[selection_field_list[1]]] for i, k in staff_dict.items() if
                       int(k[search_sql_list[5]]) < int(search_sql_list[-1])]
                x = PrettyTable(selection_field_list)
                for i in res:
                    x.add_row(i)
                x.add_row(["33[1;31mSEARCH IN TOTAL33[0m", len(res)])
                print(x)
        elif search_sql_list[6] == "=":
            if "*" in selection_field_list:
                res_all = [[k["name"], k["age"], k["staff_id"], k["dept"], k["enroll_date"]] for i, k in
                           staff_dict.items() if k[search_sql_list[5]] == search_sql_list[7]]
                y = PrettyTable(["name", "age", "staff_id", "dept", "enroll_date"])
                for j in res_all:
                    y.add_row(j)
                y.add_row(["", "", "", "33[1;31mSEARCH IN TOTAL33[0m", len(res_all)])
                print(y)
            else:
                res = [[k[selection_field_list[0]], k[selection_field_list[1]]] for i, k in staff_dict.items() if
                       k[search_sql_list[5]] == search_sql_list[-1]]
                x = PrettyTable(selection_field_list)
                for i in res:
                    x.add_row(i)
                x.add_row(["33[1;31mSEARCH IN TOTAL33[0m", len(res)])
                print(x)
        elif search_sql_list[6] == "like":
            if "*" in selection_field_list:
                res_all = [[k["name"], k["age"], k["staff_id"], k["dept"], k["enroll_date"]] for i, k in
                           staff_dict.items() if k[search_sql_list[5]][:4] == search_sql_list[7]]
                y = PrettyTable(["name", "age", "staff_id", "dept", "enroll_date"])
                for j in res_all:
                    y.add_row(j)
                y.add_row(["", "", "", "33[1;31mSEARCH IN TOTAL33[0m", len(res_all)])
                print(y)
            else:
                res = [[k[selection_field_list[0]], k[selection_field_list[1]]] for i, k in staff_dict.items() if
                       k[search_sql_list[5]][:4] == search_sql_list[7]]
                x = PrettyTable(selection_field_list)
                for i in res:
                    x.add_row(i)
                x.add_row(["33[1;31mSEARCH IN TOTAL33[0m", len(res)])
                print(x)
        else:
            print("Darling, something wrong with sql sentence")
            exit()
    else:
        print("Sql language errors. missing keyword "where"")


def create(sql):
    # 增加新员工
    staff_dict = loaded_from_file()
    create_sql_list = sql.split()[1].split(",")
    phone, name, age, dept, enroll_date = range(5)
    staff_dict.setdefault(create_sql_list[phone])
    staff_dict[create_sql_list[phone]] = {"name": None, "age": None, "staff_id": None, "dept": None,
                                          "enroll_date": None}
    staff_dict[create_sql_list[phone]]["name"] = create_sql_list[name]
    staff_dict[create_sql_list[phone]]["age"] = create_sql_list[age]
    staff_dict[create_sql_list[phone]]["dept"] = create_sql_list[dept]
    staff_dict[create_sql_list[phone]]["enroll_date"] = create_sql_list[enroll_date]
    temp_list = [v["staff_id"] for k, v in staff_dict.items()]
    temp_list.remove(None)
    staff_dict[create_sql_list[phone]]["staff_id"] = max(temp_list) + 1
    write_into_file(staff_dict)
    print("New account is created successfully.")


def delete(sql):
    # 删除；因为staff_id和phone都具有唯一性，所以都可以做key
    staff_dict = loaded_from_file()
    sql_list = sql.split()
    phone_dict = {}   # 创建以staff_id为key, phone#为value的字典
    for k in staff_dict:
        phone_dict[staff_dict[k]["staff_id"]] = k
    del staff_dict[phone_dict[int(sql_list[1])]]
    write_into_file(staff_dict)
    print("Account is removed from system.")


def update(sql):
    staff_dict = loaded_from_file()
    set_list = sql.split("set")[1].split("where")[0].replace(""", " ").split()  # 返回：['dept', '=', 'Market']
    where_list = sql.split("set")[1].split("where")[1].replace(""", " ").split()  # 返回：['dept', '=', 'IT']
    if set_list[0] == where_list[0]:
        for k, v in staff_dict.items():
            if v[where_list[0]] == where_list[-1]:
                v[where_list[0]] = set_list[-1]
        print("Information updated")
    else:
        print("the category you want to change is unmatched.")
    write_into_file(staff_dict)

示例

sample 1

sample 2 

sample 3

 

详细描述参考http://www.cnblogs.com/alex3714/articles/5740985.html