8.创建数据库以及用户表

首先需要新建数据库：直接在命令行直接即可

react_v5_admin_db

create database react_v5_admin_db charset utf8;

创建用户表：user_info

create table user_info(id int primary key auto_increment, username varchar(20), password varchar(12), phone varchar(11), email varchar(20), role_id varchar(50));

配合登陆接口即可实现登陆/注册/登出操作

后端Model层与MySQL的交互如下：

import pymysql


# 获取MySQL数据库连接，使用了来雪花算法生成唯一ID
from epidemic_situation_pro.utils.snowflake_utils import get_unique_db_id


def get_mysql_conn():
    mysql_conn = pymysql.connect(host='localhost', port=3306, user='root',
                                 password="xxx", database="react_v5_admin_db", charset="utf8")
    if mysql_conn == None:
        print("MySQL数据库连接失败!")
    else:
        print("MySQL数据库连接成功!")

    # 拿到游标对象
    cursor = mysql_conn.cursor()
    return mysql_conn, cursor


# 释放资源
def release_mysql_conn(conn, cursor):
    cursor.close()
    conn.close()


# 查询数据
def query(sql, *args):
    """
    :param sql: 查询的sql语句
    :param args: 需要的可变参数
    :return: 查询结果
    """
    conn, cursor = get_mysql_conn()
    cursor.execute(sql, args)
    res = cursor.fetchall()
    return res


# 插入数据
def insert(sql, params):
    """
    :param sql: 插入的sql语句
    :param params: 需要的可变参数
    :return: 查询结果
    """
    conn, cursor = get_mysql_conn()
    aff_res = cursor.execute(sql, params)
    conn.commit()
    release_mysql_conn(conn, cursor)
    return aff_res


# 更新数据
def update(sql, params):
    """
    :param sql: 更新的sql语句
    :param params: 需要的可变参数
    :return: 查询结果
    """
    conn, cursor = get_mysql_conn()
    print("sql update category params =", params)
    aff_res = cursor.execute(sql, params)
    conn.commit()
    release_mysql_conn(conn, cursor)
    return aff_res

# 查询用户是否存在，不存在返回False并插入一条数据
def get_user_by_u_p(u, p):
    #  查询用户表
    # SQL模块中自带的填充方式
    sql_str = 'select username, password from user_info where username = %s and password= %s'
    res = query(sql_str, u, p)  # 返回多个元组，只取第一个元素
    # print("center1 res ==", res[0], type(res[0]))
    print("get user  res ==", res)
    if len(res) == 0:
        print("need insert new row data ")
        # 插入一条数据，测试使用一般都需要注册接口调用
        # add_user_by_u_p(u, p)
        return False
    return res[0]


# 添加用户
def add_user_by_u_p(u, p):
    sql_str = "insert into user_info (username, password, phone, email, role_id) value (%s, %s, %s, %s, %s)"
    if u != 'admin':
        params = (u, p, None, None, None)
    else:
        params = (u, p, '1111111111', '[email protected]', 'admin')
    res = insert(sql_str, params)  # 返回 1
    return res


if __name__ == '__main__':
    # 测试代码
    get_user_by_u_p("admin", "123456")

控制层即controller，即业务逻辑处理如下：

from epidemic_situation_pro.react_data_util import get_user_by_u_p


def get_user_by_username_pwd(username, password):
    if (username == "") | (password == ""):
        return False
    res_user = get_user_by_u_p(username, password)
    user_dict = {}
    if res_user:
        user_dict["username"] = res_user[0]
    return user_dict