设计博客系统表结构并实现相应功能——基于Python、SQL

根据业务需求设计相关的表结构，内部需涵盖如下功能： 1 登录、注册 2 发布博客 3 查看博客列表
显示博客标题、创建时间、阅读数量、评论数量、赞数量 等。（支持分页查看） 4 博客详细，显示博文详细、评论等（*）。
发表评论
赞 or 踩
阅读数量 + 1

全部结构

一、

import crm.utils.登录或注册 as LR
import crm.utils.发布博客 as re
import crm.utils.查看博客列表 as ch
import crm.utils.博客详情 as de


def run():
    text = "\t1.发布博客\t2.查看博客列表\t3.博文详细\n"
    choice = {'1': re.release, '2': ch.check, '3': de.detail}
    print('\t\t\t欢迎使用博客系统')
    while True:
        que = input('是否进入系统（Y/N）:')
        if que.upper() != 'Y' and que.upper() != 'N':
            print('请输入Y/N:')
            continue
        elif que.upper() == 'N':
            break
        else:
            user_id = LR.Log_or_Reg()
        while user_id:
            ans = input('\n请输入需求,Q或q退出:\n' + text)
            if ans.upper() == 'Q':
                break
            elif not (ans.isdecimal() and 1 <= int(ans) <= 3):
                print('输入错误')
                continue
            else:
                choice[ans](user_id)

二、

import pymysql
from crm import config
conn = pymysql.connect(host=config.HOST, port=config.PORT, user=config.USER, passwd=config.PASSWD)
cursor = conn.cursor()

cursor.execute('drop database if exists 博客系统')
cursor.execute('create database 博客系统')
cursor.execute('use 博客系统')

user_text = '''
create table user(
    id int not null primary key auto_increment,
    username varchar(32) not null,
    nickname varchar(32) not null,
    mobile varchar(64) not null,
    password varchar(64) not null,
    email varchar(64),
    ctime datetime default current_timestamp,
    unique (username,mobile)
    ) default charset=utf8'''

article_text = '''
create table article(
    id int not null primary key auto_increment,
    title varchar(64) not null,
    text varchar(225) not null,
    read_count int default 0,
    comment_count int default 0,
    up_count int default 0,
    down_count int default 0,
    user_id int not null,
    ctime datetime default current_timestamp,
    constraint fk_article_user foreign key (user_id) references user(id)
    ) default charset=utf8'''

comment_text = '''
create table comment(
    id int not null primary key auto_increment,
    content varchar(225) not null,
    user_id int not null,
    article_id int not null,
    ctime datetime default current_timestamp,
    constraint fk_comment_user foreign key (user_id) references user(id),
    constraint fk_comment_article foreign key (article_id) references article(id)
    ) default charset=utf8'''

updown_text = '''
create table up_down(
    id int not null primary key auto_increment,
    choice int not null check (choice in (0,1)),
    user_id int not null,
    article_id int not null,
    ctime datetime default current_timestamp,
    constraint fk_up_down_user foreign key (user_id) references user(id),
    constraint fk_up_down_article foreign key (article_id) references article(id),
    unique (user_id,article_id)
    ) default charset=utf8'''

cursor.execute(user_text)
cursor.execute(article_text)
cursor.execute(comment_text)
cursor.execute(updown_text)

user_set_text = '''
insert into
    user(username,nickname,mobile,password,email)
    values
    ('1','姓名1','手机号1','密码1','邮箱1'),
    ('2','姓名2','手机号2','密码2','邮箱2'),
    ('3','姓名3','手机号3','密码3','邮箱3')
'''

article_set_text = '''
insert into
    article(title,text,read_count,comment_count,up_count,down_count,user_id)
    values
    ('解密','已经很快就换个方向',101,10,1,1,1),
    ('底层','cxdfyuilnbvfrrty',1000,2,10,0,1)
'''

comment_set_text = '''
insert into
    comment(content,user_id,article_id)
    values
    ('good',1,1),
    ('okokok',2,1),
    ('厉害',3,2)
'''

updown_set_text = '''
insert into
    up_down(choice,user_id,article_id)
    values
    (1,1,1),
    (0,2,1),
    (1,3,2)
'''

cursor.execute(user_set_text)
cursor.execute(article_set_text)
cursor.execute(comment_set_text)
cursor.execute(updown_set_text)

conn.commit()
cursor.close()
conn.close()

三、

import pymysql
from crm import config


def detail(user_id):
    conn = pymysql.connect(host=config.HOST, port=config.PORT, user=config.USER, passwd=config.PASSWD, db=config.DB)
    cursor = conn.cursor()
    while True:
        cursor.execute('select max(id) from article')
        maxnum = list(list(cursor.fetchall())[0])[0]
        ans = input('请输入所查看博客序号,Q或q退出:')
        if ans.upper() == 'Q':
            break
        elif not (ans.isdecimal() and 0 <= int(ans) <= maxnum):
            print('输入有误!')
            continue
        else:
            cursor.execute('select text from article where id={}'.format(int(ans)))
            text_result = cursor.fetchall()
            print('博文内容：\n\t', end='')
            print(text_result)
            cursor.execute('update article set article.read_count = article.read_count+1 where id={}'.format(int(ans)))
            conn.commit()
            cursor.execute('select content from comment where article_id = {}'.format(int(ans)))
            content_result = cursor.fetchall()
            print('评论：\n\t', end='')
            print(content_result)
            cont = input('请发表评论:')
            if cont:
                cursor.execute("insert into comment(content, user_id, article_id) values ('%s', %d, %d)" % (
                cont, user_id, int(ans)))
                cursor.execute('update article set comment_count = comment_count+1 where id={}'.format(int(ans)))
                conn.commit()
            cursor.execute('select user_id, article_id from up_down')
            updown_result = cursor.fetchall()
            if (user_id, int(ans)) not in updown_result:
                num = input('是否对文章赞或踩（1：赞  0：踩）:')
                if num.isdecimal() and 0 <= int(num) <= 1:
                    cursor.execute('insert into up_down(choice, user_id, article_id) values (%d, %d, %d)' % (
                    int(num), user_id, int(ans)))
                    if int(num) == 1:
                        cursor.execute('update article set up_count=up_count+1 where id={}'.format(int(ans)))
                    else:
                        cursor.execute('update article set down_count = down_count+1 where id={}'.format(int(ans)))
                    conn.commit()
    cursor.close()
    conn.close()

四、

import pymysql
from crm import config


def release(user_id):
    conn = pymysql.connect(host=config.HOST, port=config.PORT, user=config.USER, passwd=config.PASSWD, db=config.DB)
    cursor = conn.cursor()
    title = input('请输入标题:')
    text = input('请输入正文:')
    data = (title, text, user_id)
    print(data)
    cursor.execute('insert into article(title,text,user_id) values {}'.format(data))
    conn.commit()
    print('发布成功!')
    cursor.close()
    conn.close()

五、

import pymysql
from crm import config


def check(user_id):
    conn = pymysql.connect(host=config.HOST, port=config.PORT, user=config.USER, passwd=config.PASSWD, db=config.DB)
    cursor = conn.cursor()
    cursor.execute('select max(id) from article')
    maxnum = list(list(cursor.fetchall())[0])[0]
    for i in range(0, maxnum, 10):
        print('第{}页'.format((i + 10) / 10))
        print("\n\t 标题\t\t\t\t创建时间\t\t\t\t\t\t阅读数 评论数 赞数")
        cursor.execute(
            'select id,title, ctime, read_count, comment_count, up_count from article limit 10 offset {}'.format(i))
        result = cursor.fetchall()
        for data in result:
            print(data)
    cursor.close()
    conn.close()

六、

import pymysql
from crm import config

conn = pymysql.connect(host=config.HOST, port=config.PORT, user=config.USER, passwd=config.PASSWD, db=config.DB)
cursor = conn.cursor()


def Log_or_Reg():
    text_1 = '\n 1.登录\t2.注册\n'
    choice_1 = {'1': Log, '2': Reg}
    while True:
        ans_1 = input('请输入需求，Q或q退出:' + text_1)
        if ans_1.upper() == 'Q':
            break
        elif not (ans_1.isdecimal() and 1 <= int(ans_1) <= 2):
            print('输入错误!')
            continue
        else:
            result = choice_1[ans_1]()
            return result
    cursor.close()
    conn.close()


def Log():
    user = input('请输入用户名:')
    passwd = input('请输入密码:')
    cursor.execute('select username, password from user')
    res = cursor.fetchall()
    if (user, passwd) not in res:
        print('用户名或密码错误!')
        return 0
    else:
        print('登录成功!')
        cursor.execute('select id from user where username="{}" and password="{}"'.format(user, passwd))
        user_id = list(list(cursor.fetchall())[0])[0]
        return user_id


def Reg():
    username = input('请输入用户名(必填):')
    nickname = input('请输入昵称(必填):')
    mobile = input('请输入手机号(必填):')
    password = input('请输入密码(必填):')
    email = input('请输入邮箱')
    data = (username, nickname, mobile, password, email)
    cursor.execute('select username, mobile from user')
    res = cursor.fetchall()
    if (username, mobile) in res:
        print('该用户已注册!')
        return 0
    else:
        text = 'insert into user(username, nickname, mobile, password, email) values {}'.format(data)
        cursor.execute(text)
        conn.commit()
        print('注册成功!')
        cursor.execute('select id from user where username="{}" and password="{}"'.format(username, password))
        user_id = list(list(cursor.fetchall())[0])[0]
        return user_id

七、

from crm.src.handler import run

if __name__ == '__main__':
    run()

八、

HOST='127.0.0.1'
PORT=3306
USER='root'
PASSWD=''
DB='博客系统'