#!/usr/bin/env python
# -*- coding: utf-8 -*-
import random
from sqlalchemy import Column, Integer, String, TIMESTAMP, Index
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql import func
from sqlalchemy.dialects.mysql import MEDIUMTEXT
from spider163 import settings
from spider163.utils import pylog
Base = declarative_base()
class Playlist163(Base):
__tablename__ = "playlist163"
id = Column(Integer(), primary_key=True, autoincrement=True) # 歌曲ID
title = Column(String(5000), server_default="System Title") # 歌单名字
link = Column(String(255), server_default="No Link") # 歌曲链接
cnt = Column(Integer(), server_default="-1") # 歌曲数量
playCount = Column(Integer(), server_default="-1") # 播放次数
shareCount = Column(Integer(), server_default="-1") # 分享次数
commentCount = Column(Integer(), server_default="-1") # 评论数量
description = Column(MEDIUMTEXT)
tags = Column(String(255), server_default="")
dsc = Column(String(255), server_default="No Description")
create_time = Column(TIMESTAMP, server_default=func.now())
update_time = Column(TIMESTAMP, server_default=func.now())
done = Column(String(255), server_default="N")
done_link = Index("done_link", done, link)
class Music163(Base):
__tablename__ = "music163"
id = Column(Integer(), primary_key=True, autoincrement=True)
song_id = Column(Integer())
song_name = Column(String(5000), server_default="No Name")
author = Column(String(5000), server_default="No Author")
playTime = Column(Integer(), server_default="-1") # 歌曲播放次数
done = Column(String(255), server_default="N")
has_lyric = Column(String(255), server_default="N")
create_time = Column(TIMESTAMP, server_default=func.now())
update_time = Column(TIMESTAMP, server_default=func.now())
comment = Column(Integer(), server_default="-1")
done_id = Index("done_id", done,id)
song_id_comment = Index("song_id_comment", song_id, comment)
class Toplist163(Base):
__tablename__ = "top163"
id = Column(Integer(), primary_key=True, autoincrement=True)
song_id = Column(Integer())
song_name = Column(String(5000), server_default="No Name")
author = Column(String(5000), server_default="No Author")
playTime = Column(Integer(), server_default="-1") # 歌曲播放次数
done = Column(String(255), server_default="N")
mailed = Column(String(255), server_default="N")
has_lyric = Column(String(255), server_default="N")
create_time = Column(TIMESTAMP, server_default=func.now())
update_time = Column(TIMESTAMP, server_default=func.now())
comment = Column(Integer(), server_default="-1")
lastRank = Column(Integer(), server_default="100000000") # 上次排名字段
playlist_id = Column(Integer(), server_default="-1") # 排行榜歌单ID
position = Column(Integer(), server_default="0")
done_id = Index("done_id", done, id)
song_id_comment = Index("song_id_comment", song_id, comment)
class Comment163(Base):
__tablename__ = "comment163"
id = Column(Integer(), primary_key=True, autoincrement=True)
song_id = Column(Integer())
txt = Column(MEDIUMTEXT)
author = Column(String(5000), server_default="No Author")
liked = Column(Integer(), server_default="0")
create_time = Column(TIMESTAMP, server_default=func.now())
Index("liked_song_id", liked, song_id)
Index("song_id_liked", song_id, liked)
class Lyric163(Base):
__tablename__ = "lyric163"
id = Column(Integer(), primary_key=True, autoincrement=True)
song_id = Column(Integer())
txt = Column(MEDIUMTEXT)
create_time = Column(TIMESTAMP, server_default=func.now())
key_song_id = Index("song_id", song_id)
def single(table, k, v):
cnt = settings.engine.execute('select count(*) from ' + table + ' where ' + k + '=\'' + str(v) + '\'').fetchone()
if cnt[0] == 0:
return True
else:
return False
def stat_playlist():
data = {}
data["gdType"] = settings.Session.query(func.substring(Playlist163.dsc, 4, 2).label('type'), func.count('*').label('count')).group_by("type").all()
data["gdOver"] = settings.Session.query(Playlist163.done.label('over'), func.count('*').label('count')).group_by("over").all()
return data
def stat_music():
data = {"author-comment-count": []}
cd = settings.Session.query(Music163.author.label('author'), func.sum(Music163.comment).label('count')).group_by("author").order_by(func.sum(Music163.comment).label('count').label('count').desc()).limit(30).all()
for m in cd:
data["author-comment-count"].append([m[0], int(m[1])])
data["music-comment-count"] = settings.Session.query(Music163.song_name, Music163.comment.label("count")).order_by(Music163.comment.label("count").desc()).limit(30).all()
return data
def stat_data():
data = {}
data["countPlaylist"] = int(settings.engine.execute("select(select count(*) from playlist163 where done = 'Y')*100 / count(*) from playlist163").fetchone()[0]);
data["countComment"] = int(settings.engine.execute("select(select count(*) from music163 where done = 'Y')*100 / count(*) from music163").fetchone()[0]);
data["countLyric"] = int(settings.engine.execute("select(select count(*) from music163 where has_lyric = 'Y')*100 / count(*) from music163").fetchone()[0]);
return data
def random_data():
rng = settings.Session.query(func.min(Comment163.id), func.max(Comment163.id)).all()[0]
data = []
for i in range(12):
v = random.uniform(rng[0], rng[1])
d = settings.engine.execute("select txt,liked,a.author,song_name,a.song_id,b.author from comment163 a inner join music163 b on a.song_id= b.song_id where a.id>" +str(v) + " limit 1").fetchone()
data.append({"txt": d[0],"like": d[1] ,"author": d[2], "song" :{"name":d[3], "author": d[5], "id": d[4]}})
return data
def initdb():
try:
Base.metadata.create_all(settings.engine)
except Exception as e:
pylog.print_warn("自动生成数据库表出现问题: {}".format(e))
def dropdb():
try:
Base.metadata.drop_all(settings.engine)
except Exception as e:
pylog.print_warn("自动删除数据库表出现问题: {}".format(e))
