python3连接mysql数据库，简单实现注册、登录判断

# 先创建数据库，并插入一条数据,用sha1加密处理后密码固定是40位，所以用char
create table userinfos(id int primary key auto_increment not null,
name varchar(20),passwd char(40));
# 插入数据，用户名abc，密码123
insert into userinfos(name,passwd)values('abc','40bd001563085fc35165329ea1ff5c5ecbdbbeef');

#!/usr/bin/env python
# coding=utf-8
# 实现注册
import pymysql
from MysqlHelper import MysqlHelper
from hashlib import sha1

# 提示注册信息
print("=============欢迎注册=============")
user_name = input("请输入注册名：")
passwd = input("请输入密码：")

# 对密码进行加密
s1 = sha1()
s1.update(passwd.encode())
pwd = s1.hexdigest()

# 连接数据库
conn = pymysql.connect(host="localhost",port=3306,user="root",passwd="123456",db="python3")
cursor = conn.cursor()
# 创建插入sql语句
sql = "insert into userinfos(name,passwd)VALUES(%s,%s)"
cursor.execute(sql,[user_name,pwd])
conn.commit()
cursor.close()
conn.close()
print("注册成功！")

# 连接数据库方法二
# conn = MysqlHelper("localhost",3306,"python3","root","123456")
# sql = "insert into userinfos(name,passwd)VALUES(%s,%s)"
# conn.cud(sql,[user_name,pwd])

 

#!/usr/bin/env python
# coding=utf-8
# 实现登录判断
from hashlib import sha1
from MysqlHelper import MysqlHelper
import pymysql

# 提示用户输入账号信息
print("=============欢迎登录=============")
user_name = input("请输入用户名：")
passwd = input("请输入密码：")

# 对密码进行加密
s1 = sha1()
s1.update(passwd.encode())
pwd2 = s1.hexdigest()

# 连接数据库方法一
conn = pymysql.connect(host="localhost",port=3306,user="root",passwd="123456",db="python3")
cursor = conn.cursor()
sql="select passwd from userinfos WHERE name=%s"
cursor.execute(sql,[user_name])
result = cursor.fetchall()

# 连接数据操作方法二，利用封装
# conn = MysqlHelper("localhost",3306,"python3","root","123456")
# sql = "select passwd from userinfos WHERE name=%s"
# result = conn.all(sql,[user_name])
# print(result)
# 判断处理
if len(result) == 0:
    print("用户名错误！")
elif result[0][0] == pwd2:
    print("登录成功！")
else:
    print("密码错误！")

连接数据库操作封装代码：

#!/usr/bin/env python
# coding=utf-8
# mysql封装使用
import pymysql

class MysqlHelper():
    def __init__(self,host,port,db,user,passwd,charset='utf8'):
        self.host = host
        self.port = port
        self.db = db
        self.user = user
        self.password = passwd
        self.charset = charset
    def open(self):
        # 连接数据库
        self.conn = pymysql.connect(host=self.host,port=self.port,db=self.db,                                            
                    user=self.user,passwd=self.password,charset=self.charset)
        # 创建游标对象
        self.cursor = self.conn.cursor()
    # 关闭
    def close(self):
        self.cursor.close()
        self.conn.close()

    # 增加、修改、删除命令语句
    def cud(self,sql,params):
        try:
            self.open()
            # 处理逻辑数据，传入sql语句以及参数化
            self.cursor.execute(sql,params)
            # 执行事务
            self.conn.commit()
            self.close()
            print("操作成功")
        except Exception as e:
            self.conn.rollback()
            print("错误",e)
    # 查询所有数据,多个值
    def all(self,sql,params=()):
        try:
            self.open()
            self.cursor.execute(sql,params)
            data = self.cursor.fetchall()
            self.close()
            return data
        except Exception as e:
            print("错误", e)