zhiyu_content_spider/content_spider/mysqlHelper.py

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

import pymysql.cursors


class MysqlHelper(object):
    def __init__(self, host, user, password, db, source,source_id):
        self.__conn = pymysql.connect(host=host, user=user, password=password, db=db, charset='utf8mb4',
                                      cursorclass=pymysql.cursors.DictCursor)
        self.source = source
        self.source_id = source_id

    def get_book_info_by_source(self, source_bid):
        sql = 'select id from zy_books where cp_bid="%s" and  cp_id = %s'
        with self.__conn.cursor() as cursor:
            cursor.execute(sql, (int(source_bid), self.source_id))
            result = cursor.fetchone()
        self.__conn.commit()
        return result

    def get_book_info_by_id(self,bid):
        sql = 'select id,cp_bid from zy_books where id= %s and  cp_id = %s'
        with self.__conn.cursor() as cursor:
            cursor.execute(sql, (int(bid), self.source_id))
            result = cursor.fetchone()
        self.__conn.commit()
        return result

    def get_need_update_book_list(self):
        sql = 'select id,cp_bid from zy_books where cp_id=%s and `status` = 0'
        with self.__conn.cursor() as cursor:
            cursor.execute(sql, (self.source_id,))
            result = cursor.fetchall()
        self.__conn.commit()
        return result


    def get_last_cid_by_bid(self, bid):
        sql_format = "select id,bid,`name`,sequence,source_chapter_id from zy_book_chapters where bid = {} order by sequence desc limit 1"

        sql = sql_format.format(bid)
        with self.__conn.cursor() as cursor:
            cursor.execute(sql)
            result = cursor.fetchone()
        self.__conn.commit()
        return result


    def get_cid_by_bid_sequence(self, bid, sequence):
        sql = "select id,chapter_content_id  from zy_book_chapters where  bid = %s and  sequence=%s"
        with self.__conn.cursor() as cursor:
            cursor.execute(sql, (int(bid), int(sequence)))
            result = cursor.fetchone()
        self.__conn.commit()
        return result


    def insert_book(self, item):
        now = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
        sql = '''
        insert into zy_books(cp_id,cp_name,cp_bid,`name`,author, intro, cover ,keyword , category_id,status
        ,`size`,category_name,channel,updated_at,created_at)
        values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
        '''
        with self.__conn.cursor() as cursor:
            cursor.execute(sql, (self.source_id,self.source,item.get('cp_bid'),
                                 item.get('name'),
                                 item.get('author'),
                                 item.get('intro'),
                                 item.get('cover'),
                                 item.get('keyword'),
                                 item.get('category_id'),
                                 item.get('status'),
                                 item.get('size'),
                                 item.get('category_name'),
                                 item.get('channel'),
                                 now,now
                                 ))
            bid = int(cursor.lastrowid)
        self.__conn.commit()
        return bid

    def insert_chapter(self, item):
        chapter_content_id = self.insert_content(item)
        sql = "INSERT INTO `zy_book_chapters` (`bid`, `name`,`sequence`,`size`,`is_vip`,`prev_cid`,`next_cid`," \
              "`recent_update_at`,`created_at`,`updated_at`,`chapter_content_id`,source_chapter_id) " \
              "VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
        with self.__conn.cursor() as cursor:
            cursor.execute(sql, (
                item['bid'], item['name'], item['sequence'], item['size'], item['is_vip'], item['prev_cid'],
                item['next_cid'], item['recent_update_at'], item['created_at'], item['updated_at'],
                chapter_content_id,
                item['source_chapter_id']))
            cid = int(cursor.lastrowid)
        self.__conn.commit()
        return cid

    def insert_content(self, item):
        sql = "insert into zy_book_chapter_contents (bid,chapter_name,content,created_at,updated_at) values (%s,%s,%s,%s,%s)"
        with self.__conn.cursor() as cursor:
            cursor.execute(sql, (
                item['bid'],item['name'], item['content'], item['created_at'], item['updated_at']))
            content_id = int(cursor.lastrowid)
        self.__conn.commit()
        return content_id



    def update_content(self, content_id, chapter_name, content):
        now = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
        sql = 'update zy_book_chapter_contents set chapter_name=%s,content=%s,updated_at=%s  where id=%s'
        with self.__conn.cursor() as cursor:
            cursor.execute(sql, (
                chapter_name, content, now, int(content_id)))
        self.__conn.commit()

    def update_chapter(self, item):
        now = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
        sql = 'update zy_book_chapters set `name`=%s,`sequence`=%s,`size`=%s,`is_vip`=%s,' \
              'updated_at=%s,`source_chapter_id`=%s where id = %s'
        with self.__conn.cursor() as cursor:
            cid = int(item['cid'])
            cursor.execute(sql, (
                item['name'], item['sequence'], item['size'], item['is_vip'],  now,
                item['source_chapter_id'],  cid))
        self.__conn.commit()

    def update_book_info(self, book_info):
        sql = 'update zy_books set `name`=%s,author=%s,intro=%s,cover=%s,channel=%s,category_name=%s,category_id=%s where id =%s'
        with self.__conn.cursor() as cursor:
            cursor.execute(sql, (
                book_info['name'], book_info['author'], book_info['intro'], book_info['cover'],
                book_info['channel'], book_info['category'],book_info['category_id'], int(book_info['bid'])))
        self.__conn.commit()

    def update_book_status(self, bid,status):
        sql = 'update zy_books set status={} where id ={}'.format(status,bid)
        with self.__conn.cursor() as cursor:
            cursor.execute(sql)
        self.__conn.commit()


    def re_sequence(self, bid):
        sql = '''
        update zy_book_chapters a join ( 
SELECT id,(@a:=@a+1) as sequence FROM zy_book_chapters,(SELECT @a:=0) as a WHERE bid = {}  ORDER BY sequence
 ) b on a.id  = b.id set a.sequence = b.sequence where a.bid = {}
        '''.format(bid, bid)
        with self.__conn.cursor() as cursor:
            cursor.execute(sql)
        self.__conn.commit()

    
    def after_spider(self,bid,start=1):
        chapter_list = self.get_simple_chapter_list(bid,start)
        if chapter_list is None:
            return None
        point = 0
        for chapter_item in chapter_list:
            if point == 0:
                point = chapter_item['id']
                continue
            sql1 = 'update zy_book_chapters set next_cid={} where id={}'.format(chapter_item['id'],point)
            sql2 = 'update zy_book_chapters set prev_cid={},next_cid=0 where id={}'.format(point,chapter_item['id'])
            self.simple_update(sql1)
            self.simple_update(sql2)
            point = chapter_item['id']

        
        book_info = self.get_base_info(bid)
        book_sql = '''
            update zy_books set size={},chapter_count={},first_cid={},last_cid={},last_chapter='{}'  where  id = {}
        '''.format(book_info['size'],book_info['chapter_count'],book_info['first_cid'],book_info['last_cid'],book_info['last_chapter'],bid)

        self.simple_update(book_sql)

    def after_fix_delete_unnecessary(self,bid,start):
        sql = 'update zy_book_chapters set bid=-bid where bid={} and sequence > {}'.format(bid,start)
        self.simple_update(sql)


    def get_base_info(self,bid):
        sql = '''
        SELECT id as last_cid,`name` as  last_chapter,( SELECT id FROM zy_book_chapters WHERE bid = {} ORDER BY sequence limit 1 ) as first_cid ,
( SELECT count(*) FROM zy_book_chapters WHERE bid = {} ) as chapter_count,
( SELECT sum(size) FROM zy_book_chapters WHERE bid = {} ) as size
FROM zy_book_chapters where   bid = {} ORDER BY sequence desc  LIMIT 1
        '''.format(bid,bid,bid,bid) 
        with self.__conn.cursor() as cursor:
            cursor.execute(sql)
            result = cursor.fetchone()
        self.__conn.commit()
        return result
    
    def get_simple_chapter_list(self,bid,start):
        sql = 'select id from zy_book_chapters  where bid = {} and sequence >={} order by sequence'.format(bid,start)
        with self.__conn.cursor() as cursor:
            cursor.execute(sql)
            result = cursor.fetchall()
        self.__conn.commit()
        return result

    def simple_update(self,sql):
        with self.__conn.cursor() as cursor:
            cursor.execute(sql)
        self.__conn.commit()