5.9---python连接数据库实现登录注册

使用python连接数据库，实现账号登录与注册功能

import pymysql

# MySQL语句 插入数据行，即注册行为
register_sql = \'insert into user(name,password) values(%s,%s)\'
# MySQL语句 查看name=username的数据行 判断是否已存在该用户
check_sql = \'select name from user where BINARY name =%s \' # 加入BINARY关键字，可使查询结果区分大小写
# check_sql = \'select name from user where name =%s \'  # 这种查询无法区分大小写

# MySQL语句 查看所有符合该账号与密码的用户  若无则有误，若有则输出登录成功
login_sql = \'select * from user where name=%s and password=%s\'

# 连接数据库函数
def MySQL_conn():
    conn = pymysql.connect(
        host = "127.0.0.1",
        port = 3306,
        user = "root",
        password = "123",
        database = "day48",
        charset = "utf8",
        autocommit = True
    )   # 连接数据库

    # 以字典形式输出数据  不设置则默认为元组类型输出
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    return cursor

# 登录函数
def login(cursor):
    while True:
        username = input("请输入用户名：").strip()
        if username.lower == "q":
            return
        password = input("请输入密码：").strip()

        res = cursor.execute(login_sql,(username,password))    # 返回值为数据行数，或字典键值对个数
        if res:
            print("登陆成功！")
            print(\'数据行数：\',res)          # 2
            print(cursor.fetchall())        # [{\'id\': 1, \'name\': \'Jil\', \'password\': \'1118\'}, {\'id\': 3, \'name\': \'Jil\', \'password\': \'1118\'}]
            cursor.scroll(-1,"relative")    # 相对于光标所在的位置继续往前移动1位
            print(cursor.fetchall())        # 光标位置取到取到最后  [{\'id\': 3, \'name\': \'Jil\', \'password\': \'1118\'}]
            return
        else:
            print("用户名或密码错误！")

# 注册函数
def register(cursor):
    while True:
        username = input("注册用户名：").strip()
        if username.lower == "q":
            return
        password = input("设定密码：").strip()
        re_password = input("确认密码：").strip()

        if password != re_password:
            print("两次密码输入不一致，请重新输入！")
            continue
        res = cursor.execute(check_sql,(username))
        # print(res)
        # print(cursor.fetchall())
        if res:
            print("用户名【%s】已存在，请重新输入！"%username)
            continue
        else:
            cursor.execute(register_sql,(username,password))
            print("注册成功！")
            return


if __name__ == \'__main__\':
    cursor = MySQL_conn()
    while True:
        print("登录或注册".center(50,"="))
        print(\'\'\'1、登录
2、注册\'\'\')
        cmd = input("请输入您需要的操作数：").strip()
        if cmd not in ["1","2",\'q\']:
            print("请输入正确指令！")
        if cmd == \'1\':
            login(cursor)
        elif cmd == \'2\':
            register(cursor)
        else:
            break