from flask import Flask, render_template, request
import requests
from lxml import etree
import pymysql

app = Flask(__name__)

def getDB():
    db = pymysql.connect(host='localhost',user='root',password='123456',database='douban')
    return db



@app.route('/movies')
def movies():
    db=getDB()
    cursor=db.cursor()
    sql="select film_name,director,actor,language,ranks,rating_num,summary,links from movies"
    cursor.execute(sql)
    data=cursor.fetchall()
    datalist=[]
    for item in data:
        datalist.append(item)
    cursor.close()
    db.close()
    return render_template('movies.html',movies=datalist)

@app.route('/index')
def index():
    return render_template('index.html')


@app.route('/index1')
def index1():
    uname=request.args.get('u')
    psw=request.args.get('p')
    if uname=="肖旺" and psw=="123":
        return render_template('index.html')
    else:
        return render_template('regist.html')

@app.route('/login')
def login():
    return render_template('login.html')

@app.route('/regist')
def regist():
    return render_template('login.html')

@app.route('/get_nbadata')
def get_nbadata():
    db=getDB()
    cursor=db.cursor()
    sql="select no,name,score,team,info from nba"
    cursor.execute(sql)
    data=cursor.fetchall()
    datalist=[]
    for item in data:
        datalist.append(item)
    cursor.close()
    db.close()
    return render_template('nba.html',nbadata=datalist)


'''@app.route('/get_nbadata')
def get_nbadata():
    url='https://nba.hupu.com/stats/players'
    header={'User-Agent':'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/122.0.0.0 Safari/537.36 Edg/122.0.0.0'}
    res=requests.get(url,headers=header)
    e=etree.HTML(res.text)
    names=e.xpath('//table[@class="players_table"]//tr/td[2]/a/text()')
    teams=e.xpath('//table[@class="players_table"]//tr/td[3]/a/text()')
    nos=e.xpath('//table[@class="players_table"]//tr/td[1]/text()')
    nos=nos[1::]
    scores=e.xpath('//table[@class="players_table"]//tr/td[4]/text()')
    scores=scores[1::]
    
    infos=e.xpath('//table[@class="players_table"]//tr/td[2]/a/@href')
    total_ls = []
    infos_ls=[]
    for no, name, team, score in zip(nos, names, teams, scores):
        st1 = f"排名：{no}  姓名：{name}  队伍：{team}  得分：{score}"
        total_ls.append(st1)
    for info in infos:
        infos_ls.append(info)
    return render_template('nba.html',total_ls=total_ls,infos_ls=infos_ls)


@app.route('/music')
def get_music():

    headers = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/123.0.0.0 Safari/537.36 Edg/123.0.0.0'
    }
    url = 'https://www.kugou.com/yy/html/rank.html'
    res = requests.get(url=url, headers=headers)
    e = etree.HTML(res.text)
    music_names = e.xpath('//div[@id="rankWrap"]//ul/li/a/text()')
    music_urls = e.xpath('//div[@id="rankWrap"]//ul/li/a/@href')
    return render_template('music.html', music_urls=music_urls, music_names=music_names)
'''

@app.route('/music')
def music():
    db=getDB()
    cursor=db.cursor()
    sql="select name,info from music"
    cursor.execute(sql)
    data=cursor.fetchall()
    datalist=[]
    for item in data:
        datalist.append(item)
    cursor.close()
    db.close()
    return render_template('music.html',datalist=datalist)


@app.route('/get_text')
def get_text():
    headers= {
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/123.0.0.0 Safari/537.36'
    }
    url = 'https://www.biqg.cc/top/'
    res = requests.get(url=url,headers=headers)
    e = etree.HTML(res.text)
    text_names=e.xpath('//div[@class="wrap rank"]//li/a/text()')
    text_urls=e.xpath('//div[@class="wrap rank"]//li/a/@href')
    text_authors=e.xpath('//div[@class="wrap rank"]//li/text()')
    text_authors2 = []
    for i in range(len(text_authors)):
        x = str(text_authors[i]).strip('/').strip('\n').rstrip('\n ')
        if x=='':
            text_authors2.append('匿名作者')
        else:
            text_authors2.append(x)
    print(text_authors2)
    return render_template('text.html',text_names=text_names,text_authors2=text_authors2,text_urls=text_urls)



@app.route('/manh')
def get_url_info():
    db = getDB()
    cursor = db.cursor()
    sql = "select names,ranks,renqi,shoucang,authors,urls from manh"
    cursor.execute(sql)
    data = cursor.fetchall()
    datalist = []
    for item in data:
        datalist.append(item)
    cursor.close()
    db.close()
    return render_template('manh.html', datalist=datalist)

@app.route('/qm')
def get_qm_info():
    db = getDB()
    cursor = db.cursor()
    sql = "select names,ranks,authors,summarys,urls from xiaoshuo"
    cursor.execute(sql)
    data = cursor.fetchall()
    datalist = []
    for item in data:
        datalist.append(item)
    cursor.close()
    db.close()
    return render_template('qm.html', datalist=datalist)



@app.route('/about')
def about():
    return render_template('about.html')

@app.route('/talk')
def talk():
    return render_template('talk.html')

@app.route('/zhanghao')
def zhanghao():
    return render_template('login.html')


if __name__=='__main__':
    app.run()