python 实战之在线字典

 需求分析： C/S

技术分析：

        并发：多进程并发

        网络：tcp

        持久化存储：mysql数据库

模块划分：类 mvc

        登录  注册 查询单词 历史记录

数据库建设：

        dict 数据库

create database dict;

        user表 

create table user (
id int primary key auto_increment,
name varchar(32) unique,
password char(64)
);

        words表

create table words (
id int primary key auto_increment,
word varchar(32),
mean varchar(1024)
);

        check_history表

create table check_history (
id int primary key auto_increment,
user_id int,
words_id int,
time DATETIME DEFAULT now(),
foreign key(user_id) references user(id) 
on delete cascade on update cascade,
foreign key(words_id) references words(id)
);

通信协议与响应

                        客户端                  服务端

注册                    I                           成功 T   失败 F

登录                    U

查询                     C

历史                      H

搭建客户端

"""
dict 客户端： 与用户交互
"""
from socket import socket

ADDR = ("localhost", 8888)  # 连接服务器的地址以及使用的端口


class Handle:
    def __init__(self):
        self.sock = self.__create_sock()

    def __create_sock(self):
        """
        创建套接字
        :return:套接字
        """
        sock = socket()
        sock.connect(ADDR)
        return sock

    def sing_in(self, msg):
        """
        向服务器发送注册请求
        :param msg:  I user password
        :return: 注册成功 True 注册失败 False
        """
        self.sock.send(msg.encode())
        judge = self.sock.recv(128).decode()
        if judge == "T":
            return True
        else:
            return False

    def sing_up(self, msg):
        """
        像服务器发送登录请求
        :param msg: U user password
        :return: 服务器响应
        """
        self.sock.send(msg.encode())
        judge = self.sock.recv(1024).decode()
        return judge

    def quit(self):
        self.sock.send(b"E")

    def check_word(self, word, user):
        """
        发起查询单词的请求
        :param user:  用户名
        :param word: 查询的单词
        :return: 单词解释
        """
        msg = "C\t%s\t%s" % (user, word)
        self.sock.send(msg.encode())
        data = self.sock.recv(124).decode()
        if data == "T":
            return self.sock.recv(1024).decode()  # word mean
        else:
            return "F"

    def display_history(self, user):
        """
        向服务端发起查询记录请求
        :return: 有记录 T 无记录 F
        """
        msg = "H\t%s" % user
        self.sock.send(msg.encode())
        judge = self.sock.recv(124).decode()
        if judge == "T":
            hist_list = []
            while True:
                record = self.sock.recv(1024).decode()
                if record == "##":
                    break
                hist_list.append(record)
            print(hist_list)
            return hist_list
        else:
            return judge


class Menu:
    def __init__(self):
        self.handle = Handle()

    def menu_1(self):
        """
        一级界面菜单
        """
        while True:
            print("""
            +++++++++++++ WELLCOME ++++++++++
                1.登录    2.注册    3.退出
            +++++++++++++++++++++++++++++++++
            """)
            cmd = input("请输入选项：")
            if cmd == "1":
                user = input("请输入用户：")
                password = input("请输入密码：")
                msg = "U\t%s\t%s" % (user, password)
                judge = self.handle.sing_up(msg)
                if judge == "T":
                    print("登录成功！")
                    self.__menu_2(user)
                elif judge == "N" or judge == "F":
                    print("账号错误")
                elif judge == "M":
                    print("密码错误")
                else:
                    print("未知错误！")

            elif cmd == "2":
                user = input("请输入用户：")
                password = input("请输入密码：")
                msg = "I\t%s\t%s" % (user, password)
                if self.handle.sing_in(msg):
                    print("注册成功！")
                    self.__menu_2(user)
                else:
                    print("注册失败！用户可能已存在")

            elif cmd == "3":
                self.handle.quit()
                print("欢迎再次登录")
                break
            else:
                print("请输入正确的选项！")

    def __menu_2(self, user):
        """
        二级界面菜单
        :param user: 用户名
        :return: 没有返回值
        """
        while True:
            print("""
            ++++++++++++++++ WELLCOME ++++++++++++++
                1.查询单词    2.历史记录    3.注销
            ++++++++++++++++++++++++++++++++++++++++
            """)
            cmd = input("请输入选项：")
            if cmd == "1":
                print("输入## 退出查询单词")
                while True:
                    word = input("请输入单词：")
                    if word == "" or word == "##":
                        break
                    msg = self.handle.check_word(word, user)
                    if msg != "F":
                        print(msg)
                    else:
                        print("单词不存在")
            elif cmd == "2":
                msg = self.handle.display_history(user)
                if msg != "F":
                    for i in msg:
                        print(i)
                        print("######################")
                else:
                    print("暂无查询记录！")
            elif cmd == "3":
                break
            else:
                print("请输入正确选项！")

    def start(self):
        """
        客户端启动函数
        :return:
        """
        self.menu_1()


if __name__ == '__main__':
    menu = Menu()
    menu.start()

搭建服务端

"""
就收请求，逻辑处理，返回响应
"""
from socket import socket
from multiprocessing import Process
from dict_db import *
from time import sleep

HOST = "127.0.0.1"
PORT = 8888


class Handle(Process):
    def __init__(self, conn):
        super().__init__()
        self.conn = conn
        self.db = DataHandle()

    def run(self):
        while True:
            request = self.conn.recv(1024)
            data = request.decode().split("\t", 1)
            if data[0] == "E" or not data:
                break
            elif data[0] == "I":
                print("sing in")
                self.sing_in_user(*data[1].split("\t"))
            elif data[0] == "U":
                self.sing_up_user(*data[1].split("\t"))
            elif data[0] == "C":
                print("check word", *data[1].split("\t"))
                self.check_words(*data[1].split("\t"))
            elif data[0] == "H":
                self.display_history_words(data[1])

    def sing_in_user(self, *info):
        print("sing in user ", info)
        if self.db.user_in(*info):
            self.conn.send(b"T")
        else:
            self.conn.send(b"F")

    def sing_up_user(self, user, password):
        judge = self.db.is_user(user, password).encode()
        self.conn.send(judge)

    def check_words(self, user, word):
        mean = self.db.is_word(user, word)
        if mean:
            self.conn.send(b"T")
            sleep(0.001)
            self.conn.send(mean.encode())
        else:
            self.conn.send(b"F")

    def display_history_words(self, user):
        recode = self.db.dis_hos_wor(user)
        if recode != "F":
            print("history \n", recode)
            self.conn.send(b"T")
            sleep(0.005)
            for item in recode:
                msg = "单词：{} \n意思：{} \n时间：{}".format(item[0], item[1], item[2])
                sleep(0.05)
                self.conn.send(msg.encode())
            sleep(0.005)
            self.conn.send(b"##")
        else:
            self.conn.send(b"F")


class DictServer:
    def __init__(self):
        self.ADDR = (HOST, PORT)
        self.sock = self.__create_sock()

    def __create_sock(self):
        sock = socket()
        sock.bind(self.ADDR)
        sock.listen(6)
        return sock

    def main(self):
        print("Listen the port %d" % PORT)
        while True:
            conn, addr = self.sock.accept()
            print("Connect from ", addr)
            # 为客户端建立一个进程
            handel = Handle(conn)
            handel.run()


if __name__ == '__main__':
    dict_server = DictServer()
    dict_server.main()

数据处理

"""
根据逻辑处理，提供需要的数据
"""
import hashlib
import pymysql

# 密码加密
def password_change(password):
    h = hashlib.sha256()
    h.update(password.encode())
    return h.hexdigest()


class DataHandle:
    def __init__(self):
        self.INFO = {
            "host": "localhost",
            "port": 3306,
            "user": "root",
            "password": "ludabo",
            "database": "dict",
            "charset": "utf8"
        }
        self.db = pymysql.connect(**self.INFO)
        self.cur = self.db.cursor()

    def close_db_cur(self):
        self.db.close()
        self.cur.close()

    def user_in(self, user, password):
        """
        在数据库中查询注册的账号是否已存在
        :param password: 用户密码
        :param user:  用户名
        :return: 存在 False 不存在 True
        """
        pwd = password_change(password)
        try:
            sql = "insert into user (name,password) values (%s,%s);"
            self.cur.execute(sql, (user, pwd))
            self.db.commit()
            return True
        except Exception as e:
            print(e)
            self.db.rollback()
            return False

    def is_user(self, user, password):
        """
        判断用户是否存在
        :param user: 用户名
        :param password: 用户密码
        :return: N 账号错误 T 登录成功 M 密码错误
        """
        try:
            sql = "select password from user where name = %s"
            self.cur.execute(sql, (user,))
            judge = self.cur.fetchone()
            # print("is user", judge) ("password",)
            pwd = password_change(password)
            if not judge:
                return "N"
            elif judge[0] == pwd:
                return "T"
            else:
                return "M"
        except Exception as e:
            print(e)
            return "S"

    def is_word(self, user, word):
        """
        在数据库中查询单词写入历史并返回结果
        :param word: 查询的单词
        :param user: 用户名
        :return: 查询到返回mean，查不到 False
        """
        try:
            sql = "select mean from words where word = %s;"
            self.cur.execute(sql, (word,))
            mean = self.cur.fetchone()
            print(mean)
            if mean:
                user_id, word_id = self.__get_word_name_id(word, user)
                self.__word_add_history(user_id, word_id)
                return mean[0]
            return False
        except Exception as e:
            print("is_word Error ", e)
            self.db.rollback()

    def __get_word_name_id(self, word, name):
        """
        查询单词和用户的id
        :param word: 单词
        :param name: 用户名
        :return: 用户id, 单词id
        """
        print("word name", word, name)
        user_id = ""
        word_id = ""
        try:
            sql = "select id from user where name = %s;"
            self.cur.execute(sql, (name,))
            user_id = self.cur.fetchone()[0]
            sql = "select id from words where word = %s;"
            self.cur.execute(sql, (word,))
            word_id = self.cur.fetchone()[0]
            print("id ", user_id, word_id)
        except Exception as e:
            print("get_id Error ", e)
        return user_id, word_id

    def __word_add_history(self, user_id, word_id):
        """
        将查询单词添加到历史记录中
        :param user_id: 用户id
        :param word_id: 单词id
        :return: None
        """
        try:
            sql = "insert into check_history (user_id,words_id) values (%s,%s);"
            self.cur.execute(sql, (user_id, word_id))
            self.db.commit()
        except Exception as e:
            self.db.rollback()
            print("word_add_history Error ", e)

    def dis_hos_wor(self, user):
        """
        查询历史
        :return: 最近查询单词 10条记录
        """
        try:
            sql = "select word,mean,time from words,user,check_history " \
                  "where check_history.words_id = words.id " \
                  "and user.id = check_history.user_id and name=%s " \
                  "order by check_history.time desc limit 10;"
            self.cur.execute(sql, (user,))
            data = self.cur.fetchall()
            if len(data) > 0:
                return data
            else:
                return "F"
        except Exception as e:
            print(e)

值得慢慢理理，写一写，对于理解 tcp Process pymysql 有一定的帮助