# -*- 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()