python与mysql交互

• TeacherMysql.py

   #encoding=utf8
   import pymysql
   
   class MysqlHelper():
       def __init__(self,host,port,db,user,passwd,charset='utf8'):
           # 数据库ip
           self.host=host
           # 数据库端口
           self.port=port
           # 数据库名称
           self.db=db
           # 登入mysql的用户名
           self.user=user
           # 登入mysql的密码
           self.passwd=passwd
           # 数据库的格式
           self.charset=charset
   
       def connect(self):
           # 链接数据库， 创建数据库对象
           self.conn=pymysql.connect(host=self.host,port=self.port,db=self.db,user=self.user,passwd=self.passwd,charset=self.charset)
           # 创建表的对象
           self.cursor=self.conn.cursor()
   
       def close(self):
           # 关闭表的对象链接
           self.cursor.close()
           # 关闭数据库的对象链接
           self.conn.close()
   
       def get_one(self,sql,params=()):
           result=None
           try:
               # 链接数据库
               self.connect()
               # 执行你写入的sql语句
               self.cursor.execute(sql, params)
               result = self.cursor.fetchone() # fetchone()执行查询语句时，获取查询结果集的第一个行数据，返回一个元组
               # 数据库对象的链接
               self.close()
           except Exception as e:
               print(e)
           return result
   
       def get_all(self,sql,params=()):
           list=()
           try:
               self.connect()
               self.cursor.execute(sql,params)
               # fetchall()执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回
               list=self.cursor.fetchall()
               self.close()
           except Exception as e:
               print(e)
           return list
   
       def insert(self,sql,params=()):
           """插入数据"""
           return self.__edit(sql,params)
   
       def update(self, sql, params=()):
           """修改数据"""
           return self.__edit(sql, params)
   
       def delete(self, sql, params=()):
           """删除数据"""
           return self.__edit(sql, params)
   
       def __edit(self,sql,params):
           count=0
           try:
               # 链接数据库
               self.connect()
               # 执行sql语句
               count=self.cursor.execute(sql,params)
               # 提交事务
               self.conn.commit()
               # 关闭数据库对象
               self.close()
           except Exception as e:
               print(e)
           return count
  

• tRegister.py

   from TeacherMysql import MysqlHelper
   from hashlib import sha1
   
   name = input("请输入注册的用户名:")
   passwd = input("请输入注册的密码:")
   
   s1 = sha1()
   s1.update(passwd.encode("utf-8"))
   pwd2 = s1.hexdigest()
   
   
   Helper = MysqlHelper("localhost", 3306, "python3", "root", "123456")
   
   # sql = "insert into users(name,passwd) values(%s,%s)"
   sql = "select name from users where name=%s"
   result = Helper.get_all(sql,[name])
   if len(result) == 0:
       print("注册成功.....")
       sql = "insert into users(name,passwd) values(%s,%s)"
       Helper.insert(sql,[name,pwd2])
   else:
       print("注册失败,客用户名已被使用...")
  
  

• tLogin.py

   from TeacherMysql import MysqlHelper
   from hashlib import sha1
   
   # 接收用户输入
   name = input("请输入用户名:")
   pwd = input("请输入用户密码:")
   
   
   
   # 对密码加密
   s1 = sha1()
   s1.update(pwd.encode("utf-8"))
   pwd2 = s1.hexdigest()
   
   sql = "select passwd from users where name=%s"
   
   Helper = MysqlHelper("localhost", 3306, "python3", "root", "123456")
   
   result = Helper.get_all(sql,[name])
   
   if len(result) == 0:
       print("没有这个用户名....")
   
   elif result[0][0] == pwd2:
       print("登入成功.....")
   
   else:
       print("密码错误....")