# -*- coding: utf-8 -*-
import pymysql

class article_post:
    def __init__(self,dbconfig):
        super(article_post, self).__init__()
        self.dbconfig = dbconfig

    def select_article(self):
        # 创建链接字符串
        conn = pymysql.connect(**self.dbconfig)
        # 创建游标，操作mysql必须得有这个
        cursor = conn.cursor()

        # 2.返回多条数据,0-10000条
        sql = 'select id,keywords,title,content,category from article where is_post=0 order by rand() limit 2'
        cursor.execute(sql)
        result = cursor.fetchall()
        # print(result)
        # for itmes in result:
        #     id = itmes.get('id')
        #     keywords = itmes.get('keywords')
        #     title = itmes.get('title')
        #     content = itmes.get('content')
        #     print(id,keywords,title,content)

        # 操作完毕必须关闭连接
        conn.close()
        print('获取结果完毕!')
        return result

    def insert_article(self,keywords,title,content,category,is_post):
        is_insert = 0
        try:
            conn = pymysql.Connect(**self.dbconfig)
            try:
                sql = f"insert ignore into article(keywords,title,content,category,is_post) values('{keywords}','{title}','{content}','{category}','{is_post}')"

                with conn.cursor() as cursor:
                    is_insert = cursor.execute(sql)
            except pymysql.err.Error as err:
                print(f'数据入库出错，标题：{title}，异常：{err}')
            else:
                conn.commit()
                conn.close()
        except pymysql.err.MySQLError:
            pass
        return is_insert

    def update_article(self,id):
        result = 0
        try:
            conn = pymysql.Connect(**self.dbconfig)
            try:
                sql = f'update article set is_post=1 where id={id}'

                with conn.cursor() as cursor:
                    result = cursor.execute(sql)
            except pymysql.err.Error as err:
                print(f'修改发布状态失败！ id号：{id}，异常：{err}')
            else:
                conn.commit()
                conn.close()
        except pymysql.err.MySQLError:
            pass
        return result

if __name__ == '__main__':
    # 数据库配置
    dbconfig = dict(
        host='localhost',
        user='articledb',
        passwd='123456',
        db='articledb',
        port=3306,
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor
    )

    post = article_post(dbconfig)
    # post.select_article()
    #
    # post.update_article(13)
    post.select_article()