#coding:utf-8

from bs4 import BeautifulSoup
import requests
#import csv
import pymssql
#from lxml import etree
#import re
#import time
'''proxy = {
    'http':'//192.168.1.102:8888'
}'''
headers = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/87.0.4280.141 Safari/537.36'}
def dic2sql(dic, sql):
    sf = ''
    for key in dic:
        tup = (key, dic[key])
        sf += (str(tup) + ',')
    sf = sf.rstrip(',')

    sql2 = sql % sf
    return sql2
def resouget():
    db = pymssql.connect(server='127.0.0.1', port=1433, user='sa', password='123', database='stuDB',
                             charset='UTF-8')  # 数据读取中文为乱码可用 GBK 来解决
    cursor = db.cursor()
    url='https://www.iesdouyin.com/web/api/v2/hotsearch/billboard/word/'

    resp=requests.get(url,headers=headers).json()
    #print(resp)
    resp_list=[]
    for data1 in resp['word_list']:
        item={}
        keyword=data1['word']
        hot_value=data1['hot_value']
        item[keyword]=hot_value
        resp_list.append(item)
        #print(resp_list[0])
    for key in resp_list:#字典写入数据库，先构造一个dic2sql 的函数拆除元素
        print(key)
        sql='insert into resou(reci,rezhi) values %s'
        ret= dic2sql(key,sql)
        s=tuple(key)
        cursor.execute(ret)
    db.commit()
    db.close()
if __name__=='__main__':
    resouget()