MySQL实现用户登陆注册

"""MySQL做数据的管理-实现用户登陆注册"""

1 数据准备

　新建txt文件.复制内容,改名为user.sql

    然后将数据导入Navicat

    或自己创建数据也可以

/*
 Navicat Premium Data Transfer

 Source Server         : localhost_3306
 Source Server Type    : MySQL
 Source Server Version : 50640
 Source Host           : localhost:3306
 Source Schema         : db6

 Target Server Type    : MySQL
 Target Server Version : 50640
 File Encoding         : 65001

 Date: 24/02/2022 15:00:31
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 26 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, 'aaa', '111');
INSERT INTO `user` VALUES (2, 'tom', '222');

SET FOREIGN_KEY_CHECKS = 1;

图(一种办法):

2 函数版代码

import pymysql

#  连接数据库
con = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='1',
    charset='utf8',
    autocommit=True,
    database='db6'

)

# 产生光标对象
cursor = con.cursor(cursor=pymysql.cursors.DictCursor)


# 判断用户输入正确性(登陆用)
def sql_auth_userinfo(username, password):
    match_user_input = "select * from user where name=%s and password=%s"
    judge_user_input = cursor.execute(match_user_input, (username, password))
    print(judge_user_input)
    if judge_user_input:
        return True


# 判断用户名是否已被使用(注册用)
def sql_auth_username(username):
    match_name = "select * from user where name=%s"
    is_exist_name = cursor.execute(match_name, (username,))
    if not is_exist_name:
        return True


# # 将用户信息存入数据库(注册用)
def sql_insert_userinfo(username, password):
    insert_userinfo = "insert into user(name, password) values(%s, %s)"
    insert_userinfo_res = cursor.execute(insert_userinfo, (username, password))
    return insert_userinfo_res


# 登陆
def login():
    while True:
        # 获取用户输入
        username = input('请输入用户名>>>:').strip()
        password = input('请输入密码>>>:').strip()

        if not username or not password:
            print('输入不能为空')
            continue

        if sql_auth_userinfo(username, password):
            print('登陆成功')
            break
        else:
            print('登陆失败')


# 注册
def register():
    while True:
        username = input('请输入用户名>>>:').strip()
        password = input('请输入密码>>>:').strip()
        re_password = input('请再次输入密码>>>:').strip()

        if not username or not password:
            print('输入不能为空')
            continue
        if not sql_auth_username(username):
            print('用户名已存在')
            continue
        if password != re_password:
            print('密码输入不一致')
            continue
        insert_userinfo_res = sql_insert_userinfo(username, password)
        if insert_userinfo_res == 1:
            print('用户%s注册成功' % username)
            break
        else:
            print('注册异常')


# 主函数
def run():
    while True:
        print('功能选择'.center(30, '-'))
        func_dict = {"1": login, "2": register}
        func_choice = input("请选择功能编号>>>:\n"
                            "1: 登陆\n"
                            "2: 注册:").strip()
        if not func_choice:
            continue
        if func_choice not in func_dict:
            print('您输入的功能不存在')
            continue
        func = func_dict.get(func_choice)
        func()


if __name__ == '__main__':
    run()