實現功能大概

?

?

將python和MySQL數據庫交互進行 封裝 ---》》utils.py

文件程序 ----》blog.py

?

# -*- coding: utf-8 -*-
# @Time    : 2019/08/30 15:33
# @Author  : Liu
# @File    : utils.pyimport pymysql
import hashlibclass dbHelper:def __init__(self, host, user, password, port, database):self.host = hostself.user = userself.password = passwordself.port = portself.database = databasedef connect(self):self.conn = pymysql.connect(host=self.host, user=self.user, password=self.password, port=self.port,database=self.database)self.cursor = self.conn.cursor(pymysql.cursors.DictCursor)# 關閉def close(self):self.cursor.close()self.conn.close()# 封裝增刪改def __oper(self, sql, params):row = 0try:self.connect()row = self.cursor.execute(sql, params)self.conn.commit()self.close()except Exception as e:print(e)return rowdef insert(self, sql, params=[]):return self.__oper(sql, params)def delete(self, sql, params=[]):return self.__oper(sql, params)def update(self, sql, params=[]):return self.__oper(sql, params)def find_one(self, sql, params=[]):data = Nonetry:self.connect()self.cursor.execute(sql, params)data = self.cursor.fetchone()self.close()except Exception as e:print(e)return datadef find_all(self, sql, params=[]):# try:self.connect()self.cursor.execute(sql, params)datas = self.cursor.fetchall()self.close()# except Exception as e:#     print(e)return datasdef secret_sha256(self,password):sha256 = hashlib.sha256()sha256.update(password.encode('utf-8'))password = sha256.hexdigest()return password

blog.py

# -*- coding: utf-8 -*-
# @Time    : 2019/08/30 17:18
# @Author  : Liu
# @File    : blog.py
from utils import dbHelper
from datetime import date
from datetime import datetime# 創建數據庫工具類對象
db = dbHelper(host='10.0.102.54', user='root', password='root', port=3306, database='blog')#登陸while True:choice = input('請選擇:1.登陸 2.注冊 3.發表博客 4.退出登陸 5.瀏覽文章 6. 查看全部用戶文章 7.忘記密碼 ')if choice == '1':username = input('用戶名:')password = input('密碼:')if username and password:# 數據庫查詢sql = 'select * from user where username=%s'data = db.find_one(sql, (username,))  # {'id':1,'username':'admin','password':'....',...}if data:# 此人注冊過db_password = data.get('password')password = db.secret_sha256(password)if password == db_password:sql = 'update user set islogin=1 where id=%s'db.update(sql, (data.get('id'),))print('用戶登錄成功！')else:print('用戶名或者密碼有誤！')else:print('輸入的用戶名有誤！')else:print('輸入不能為空')#注冊elif choice == '2':# 注冊username = input('用戶名:')password = input('密碼:')repassword = input('確認密碼:')phone = input('手機號碼:')email = input('郵箱:')# 查詢if username:sql = 'select * from user where username=%s or phone=%s or email=%s'data = db.find_one(sql, params=(username, phone, email))# print('1')if data:print('此用戶名或者手機號碼或者郵箱已被占用！')else:# 注冊if password and repassword:if password == repassword:# print('2')password = db.secret_sha256(password)sql = 'insert into user(username,password,phone,email,rdate) values(%s,%s,%s,%s,%s)'row = db.insert(sql, (username, password, phone, email, date.today()))if row:print('注冊成功！')else:print('注冊失敗！')else:print('兩次密碼不一致！')else:print('密碼不能為空～')else:print('用戶名不能為空！')#發表博客elif choice == '3':# 發表文章username = input('用戶名:')if username:# 數據庫查詢sql = 'select * from user where username=%s'data = db.find_one(sql, (username,))  # {'id':1,'username':'admin','password':'....',...}if data:islogin = data.get('islogin')if islogin:# 發表文章title = input('請輸入文章標題:')content = input('輸入內容:')# 構建sqlsql = 'insert into blog(title,content,pdate,uid) values(%s,%s,%s,%s)'row = db.insert(sql, (title, content, datetime.now(), data.get('id')))if row:print(username + '發表文章成功')else:print('文章發表失敗！')else:print('此用戶還沒有登陸，請登陸....')else:print('用戶名有誤！')#退出登陸elif choice == '4':# 退出登陸username = input('用戶名:')if username:# 數據庫查詢sql = 'select * from user where username=%s'data = db.find_one(sql, (username,))  # {'id':1,'username':'admin','password':'....',...}if data:sql = 'update user set islogin=0 where id=%s'row = db.update(sql, (data.get('id'),))if row:print(username + '成功退出！')breakelse:print('退出失敗！')else:print('輸入的用戶名有誤！')else:print('用戶名不能為空！')#瀏覽自己的文章elif choice == '5':username = input('用戶名:')if username:# 數據庫查詢sql = 'select * from user where username=%s'data = db.find_one(sql, (username,))  # {'id':1,'username':'admin','password':'....',...}if data:islogin = data.get('islogin')if islogin:# 瀏覽文章# 構建sqluid = data.get('id')sql = 'select title,content from blog where uid=%s'# print(sql)content = db.find_one(sql,(uid,))print('標題為：'+ content.get('title'))print('內容為：'+ content.get('content'))# print(content)else:print('此用戶還沒有登陸，請登陸....')else:print('用戶名有誤！')#查看所有用戶文章elif choice == '6':username = input('用戶名:')if username:# 數據庫查詢sql = 'select * from user 'data = db.find_all(sql,)  # {'id':1,'username':'admin','password':'....',...}print(data)if data:sql = 'select title,content from blog'# print(sql)content = db.find_all(sql,)print(content)i = len(content)print(i-1)for i in  range(i):print('標題為：'+ content[i].get('title'))print('內容為：'+ content[i].get('content'))print('--------------------------------------')i+=1# print(content)else:print(' 查詢失敗')#忘記密碼elif choice == '7':# 忘記用戶名username = input('輸入忘記密碼的用戶名:')if username:# 數據庫查詢sql = 'select * from user where username=%s'data = db.find_one(sql, (username,))  # {'id':1,'username':'admin','password':'....',...}if data:islogin = data.get('islogin')if islogin:# 登陸可直接修改密碼newpassword = input('請輸入新的用戶密碼')newpassword =db.secret_sha256(newpassword)# 構建sql#  update 表名 set 字段1=值1,字段2=值2... where 條件  #不加where修改的是所有的記錄sql = 'update  user set password =%s where username= %s 'row = db.insert(sql, (newpassword,username,))if row:print(username + '密碼修改成功')else:print('修改失敗！')else:print('此用戶還沒有登陸，請登陸修改密碼....')else:print('無此用戶！')else:print('輸入錯誤！')