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

最新推荐文章于 2024-05-22 11:08:18 发布

网络毒刘

最新推荐文章于 2024-05-22 11:08:18 发布

阅读量390

点赞数 2

分类专栏： Python之路 Python MySQL

本文链接：https://blog.csdn.net/qq_41856814/article/details/100166280

版权

Python 同时被 3 个专栏收录

188 篇文章 11 订阅

订阅专栏

Python之路

52 篇文章 2 订阅

订阅专栏

MySQL

33 篇文章 1 订阅

订阅专栏

实现功能大概

将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('输入错误！')