Python操作MySQL数据库实现注册和登录

python操作数据库的基本步骤：

 

首先打开local，，创建qqq数据库。

新建查询，输入sql语句创建sh_users表

create table sh_users(
id int unsigned primary key auto_increment not null,
username varchar(20) not null,
password char(40) not null,
is_delete bit default 0
)

刷新表页面，输入sql语句尝试插入

insert into sh_users (username, password) values ('r速22t', 'f74e2')

 

接下来就可以写代码了！

from hashlib import *
import pymysql


# 获取数据库连接对象
def mysql_conn():
    conn = pymysql.connect(host='127.0.0.1', user='root', passwd='111111', db='qqq')
    return conn


def register():
    try:
        # 获取数据库连接对象
        conn = mysql_conn()
        # 获取数据库操作cursor（游标）
        cur = conn.cursor()
        # 编写查询的sql语句
        select_sql = f'select password from sh_users where username = "{u_name}"'
        # 执行sql语句
        cur.execute(select_sql)
        # 获取执行结果 fetch_one()，判断结果
        res = cur.fetchone()
        # 如果res返回None 表示没有找到数据，不存在可注册，存在注册失败
        if res is not None:
            print('用户名已存在，注册失败', res)
        else:
            print('该用户名可以使用')
            # 注册-> 插入数据，手动commit
            insert_sql = 'insert into sh_users (username, password) values (%s,%s)'
            insert_params = [u_name, sha_pwd]
            cur.execute(insert_sql, insert_params)
            conn.commit()
            print('注册成功', u_name)
        # 关闭连接
        cur.close()
        conn.close()
    except Exception as e:
        print(e)


def login():
    try:
        conn = mysql_conn()
        cur = conn.cursor()
        select_sql = f'select password from sh_users where username = "{u_name}"'
        cur.execute(select_sql)
        res = cur.fetchone()
        if res is None:
            # 登录：根据用户名没有获取密码
            print('用户名错误，登录失败')
        else:
            # res有值，用户名正确，判断密码正确与否
            m_pwd = res[0]
            print(m_pwd, '===========================')
            if m_pwd == sha_pwd:
                print('登录成功', u_name)
            else:
                print('密码错误，登录失败')
        # 关闭连接
        cur.close()
        conn.close()
    except Exception as e:
        print(e)


if __name__ == '__main__':
    u_name = input('请输入用户名')
    u_pwd = input('请输入密码')

    # sha1加密
    s1 = sha1()
    s1.update(u_pwd.encode())
    sha_pwd = s1.hexdigest()
    print(sha_pwd)

    # register()
    login()

 

这里用了两种字符串格式化操作，sql语句里“=”号后的引号要注意