def check_table_playlist_status(self,status=75):##用于更新song '''检查抓取率小于75%的数据''' sql='select ids,song_ids from playlist where status <%s order by insert_time asc limit 100 '%(status) logging.info(u'正在查询数据表playlist中status为0的数据') return self.execute(sql)
def create_table_discover(self,): sql='''create table if not exists discover( ids bigint primary key, status int default "0", insert_time timestamp default current_timestamp) default charset utf8''' self.execute(sql) logging.info(u'create table discover successfully')
def insert_table_playlist_ids(self,ids): sql='insert ignore into playlist (ids) values(%s)'%(ids) try: self.execute(sql) logging.info(u'successfully insert_table_playlist_ds') except Exception,e: logging.error(u'failed insert_table_playlist id cause by %s'%str(e))
def update_table_playlist_comment(self,comment_count,comments,ids): '''可以与song合并成一个函数''' comments=self.check_str(comments) sql='''update playlist set comment_count="%s",comments="%s" where ids="%s "'''%(comment_count,comments,ids) try: self.execute(sql) logging.info(u'successfully update_table_playlist_comment info') except Exception,e: logging.error(u'failed update_table_playlist_comment cause by %s'%str(e))
def update_table_song(self,comment_count,comments,ids): comments=self.check_str(comments) sql='''update song set comment_count="%s",comments="%s" where ids="%s"'''%(comment_count,comments,ids) try: self.execute(sql) logging.info(u'successfully update_table_song comments') except Exception,e: logging.error(u'failed update_table_song comments cause by %s'%str(e))
def insert_table_playlist(self,title=0,ids=0,author=0,tags=0,total=0,fav=0,comment=0,share=0,description=0,song_ids=0): title=self.check_str(title) description=self.check_str(description) sql='''insert ignore into playlist (title,ids,author,tags,total,fav,comment,share,description,song_ids) values("%s","%s","%s","%s","%s","%s","%s","%s","%s","%s")'''%(title,ids,author,tags,total,fav,comment,share,description,song_ids) try: self.execute(sql) logging.info(u'successfully insert_table_playlist info') except Exception,e: logging.error(u'failed insert_table_playlist info cause by %s'%str(e))
def insert_table_song(self,song,singer,ids,url,refer): sql='''insert ignore into song(song,singer,ids,url,refer) values("%s","%s","%s","%s","%s")'''%(song,singer,ids,url,refer) try: self.execute(sql) logging.info(u'successfully insert_table_song') except Exception,e: import traceback logging.error(u'traceback is:%s'%traceback.format_exc()) print u'error cause by:',e logging.error(u'failed insert_table_song cause by %s'%str(e))
def update_table_playlist_info(self,title,ids,author,tags,total,fav,comment,share,description,song_ids): title=self.check_str(title) description=self.check_str(description) sql='''update playlist set title="%s",author="%s",tags="%s",total="%s", fav="%s",comment="%s",share="%s",description="%s",song_ids="%s" where ids=%s '''%(title,author,tags,total,fav,comment,share,description,song_ids,ids) try: self.execute(sql) logging.info(u'successfully update_table_playlist info') except Exception,e: logging.error(u'failed update_table_playlist_info cause by %s'%str(e))
def create_table_song(self,): sql='''create table if not exists song( song varchar(50), singer varchar(50), ids bigint primary key not null, url varchar(200), refer bigint, comment_count int default '-1', comments text, status int default '0', insert_time timestamp default current_timestamp )default charset utf8 ''' logging.info(u'创建数据表song') self.execute(sql)
def auto_update_playlist_status(self,): sql='select count(*),refer from song group by refer' data=self.execute(sql) print data for i in data[1]: count=i['count(*)'] refer=i['refer'] sql='select total from playlist where ids=%s and status<75'%(refer) result=self.execute(sql) print result if result[0]:## total=result[1][0]['total'] print refer,count,total status=int(count*1.0/total*100) assert status<101 ,'status max=100' ##抓取率最大为100 self.update_table_playlist_status(ids=refer,status=status) logging.info(u'update playlist id=%s status=%s'%(refer,status))
def create_table_playlist(self,): sql='''create table if not exists playlist( title varchar(200) not null default 'title', ids bigint primary key not null , author varchar(50), tags varchar(50), total int default '-1', fav int, comment int, share int, description varchar(5000), song_ids varchar(3000), comment_count int default '-1', comments text, status int default '0', insert_time timestamp default current_timestamp )default charset utf8 ''' self.execute(sql) logging.info(u'successfully create table playlist')
def mysql_execute(self,sql,info): logging.info(info) return self.execute(sql)
def check_table_playlist(self,):#用于更新playlist comments sql='select ids from playlist where comment_count=-1 order by insert_time asc limit 1000' logging.info(u'正在查询数据表playlist中comment_count为-1的数据') return self.execute(sql)
def update_table_playlist_status(self,ids,status): '''更改status为抓取百分率''' sql='update playlist set status=%s where ids=%s'%(status,ids) logging.info(u'更新playlist中status的值为%s'%(status)) return self.execute(sql)
def check_table_playlist_total(self,):##用于更新playlist info sql='select ids from playlist where total=-1 order by insert_time asc limit 1000' logging.info(u'正在查询数据表playlist中total为-1的数据') return self.execute(sql)
def truncate_table(self,tb='song'): sql='drop table %s'%tb logging.info(u'truncate数据表%s'%(tb)) self.execute(sql)
def drop_table(self,tb='playlist'): sql='drop table %s'%tb logging.info(u'移除数据表%s'%(tb)) self.execute(sql)
def check_table_song(self,limit=1000):#用于更新song comments sql='select ids from song where comment_count<0 order by insert_time asc limit %s'%limit ##避免查询过多 logging.info(u'检查数据表song中comment_count为-1的数据') return self.execute(sql)