python连接MySQL数据库搭建简易博客

实现功能大概

 

 

将python和MySQL数据库交互进行 封装 ---》》utils.py

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

 

# -*- coding: utf-8 -*-
# @Time : 2019/08/30 15:33
# @Author : Liu
# @File : utils.py

import pymysql
import hashlib

class dbHelper:
def __init__(self, host, user, password, port, database):
self.host = host
self.user = user
self.password = password
self.port = port
self.database = database

def 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 = 0
try:
self.connect()
row = self.cursor.execute(sql, params)
self.conn.commit()
self.close()
except Exception as e:
print(e)

return row

def 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 = None
try:
self.connect()
self.cursor.execute(sql, params)
data = self.cursor.fetchone()
self.close()
except Exception as e:
print(e)

return data

def 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 datas
def 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('输入内容:')

# 构建sql
sql = '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 + '成功退出！')
break
else:
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:
# 浏览文章
# 构建sql
uid = 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('输入错误！')