ydyspider/ydyspider/mysqlHelper.py

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

import pymysql.cursors


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

    def get_connection(self):
        return self.__conn

    def get_book_info_by_source(self, source_bid):
        sql = 'select id from books where source_bid=%s and  source = %s'
        with self.__conn.cursor() as cursor:
            cursor.execute(sql, (source_bid, self.source))
            result = cursor.fetchone()
        self.__conn.commit()
        return result

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

    def get_last_cid_by_bid(self, bid):
        sql = "select id,bid,`name`,sequence,source_chapter_id from chapters where bid = %s" \
              " order by sequence desc limit 1"
        with self.__conn.cursor() as cursor:
            cursor.execute(sql, (int(bid), ))
            result = cursor.fetchone()
        self.__conn.commit()
        return result

    def insert_book(self, item):
        sql = '''
        insert into books(source_bid, `name`,author, intro, cover ,keyword , category_id,status,
        chapter_count,first_cid,last_cid,`size`,last_chapter,category_name,source,updated_at,created_at)
        values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
        '''
        with self.__conn.cursor() as cursor:
            cursor.execute(sql, (item.get('source_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('chapter_count'),
                                 item.get('first_cid'),
                                 item.get('last_cid'),
                                 item.get('size'),
                                 item.get('last_chapter'),
                                 item.get('category_name'),
                                 item.get('source'),
                                 item.get('updated_at'),
                                 item.get('created_at')
                                 ))
            bid = int(cursor.lastrowid)
        self.__conn.commit()
        return bid

    def insert_chapter(self, item):
        chapter_content_id = self.insert_content(item)
        sql = "INSERT INTO `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 chapter_contents (chapter_name,content,created_at,updated_at) values (%s,%s,%s,%s)"
        with self.__conn.cursor() as cursor:
            cursor.execute(sql, (
                item['name'], item['content'], item['created_at'], item['updated_at']))
            content_id = int(cursor.lastrowid)
        self.__conn.commit()
        return content_id

    def get_book_list(self):
        sql = "select id,source_bid from books where source=%s"
        with self.__conn.cursor() as cursor:
            cursor.execute(sql, (self.source,))
            result = cursor.fetchall()
        self.__conn.commit()
        return result

    def get_chapter_info_by_source_cid(self, bid, source_chapter_id):
        sql = 'select id from chapters where bid=%s and  source_chapter_id = %s'
        with self.__conn.cursor() as cursor:
            cursor.execute(sql, (bid, source_chapter_id))
            result = cursor.fetchone()
        self.__conn.commit()
        return result

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

    def get_cid_by_bid_sequence(self, bid, sequence):
        sql = "select id,chapter_content_id from 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 update_content(self, content_id, chapter_name, content):
        now = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
        sql = 'update 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 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()