爬取周杰伦歌曲信息分别保存为excel文件和存入MySQL数据库以及发送到你的邮箱中littlespider889的博客-数据库

大家好，我是天空之城，今天给大家带来，爬取周杰伦歌曲信息分别保存为excel文件和存入MySQL数据库，此处周董可以替换为任意一位歌手，还可以将歌曲信息全部发送到任何一个人的邮箱中

第一部分，爬取周董歌曲信息分别保存为excel文件

import requests, openpyxl  wb = openpyxl.Workbook() sheet = wb.active sheet.title = 'geci'  sheet['A1'] = '歌曲名'  # 加表头，给A1单元格赋值 sheet['B1'] = '所属专辑'  # 加表头，给B1单元格赋值 sheet['C1'] = '播放时长'  # 加表头，给C1单元格赋值 sheet['D1'] = '播放链接'  # 加表头，给D1单元格赋值 url = 'https://c.y.qq.com/soso/fcgi-bin/client_search_cp' for x in range(5):      params = {         'ct': '24',         'qqmusic_ver': '1298',         'new_json': '1',         'remoteplace': 'sizer.yqq.song_next',         'searchid': '64405487069162918',         't': '0',         'aggr': '1',         'cr': '1',         'catZhida': '1',         'lossless': '0',         'flag_qc': '0',         'p': str(x + 1),         'n': '20',         'w': '周杰伦',         'g_tk': '5381',         'loginUin': '0',         'hostUin': '0',         'format': 'json',         'inCharset': 'utf8',         'outCharset': 'utf-8',         'notice': '0',         'platform': 'yqq.json',         'needNewCode': '0'     }      res_music = requests.get(url, params=params)     json_music = res_music.json()     list_music = json_music['data']['song']['list']     for music in list_music:         # print(music['name'])         # print('所属专辑：' + music['album']['name'])         # print('播放时长：' + str(music['interval']) + '秒')         # print('播放链接：https://y.qq.com/n/yqq/song/' + music['file']['media_mid'] + '.htmlnn')          name = music['name']         album = music['album']['name']         time = str(music['interval'])         link = 'https://y.qq.com/n/yqq/song/' + music['file']['media_mid'] + '.htmlnn'          row = [name, album, time, link]         sheet.append(row) wb.save('zhoujielun1.xlsx')  

看下Excel截图

第二部分，爬取周董（任何一个人）歌曲信息发送到任何一个人邮箱

import requests from bs4 import BeautifulSoup import smtplib from email.mime.text import MIMEText from email.header import Header   headers = { 'Referer': 'https://movie.douban.com/top250?start=1&filter=', 'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; rv:46.0) Gecko/20100101 Firefox/46.0'} url = 'https://c.y.qq.com/soso/fcgi-bin/client_search_cp'  list_all=[]  def getdata():     name0 = input('请输入歌手名字：') for x in range(5):         params = { 'ct': '24', 'qqmusic_ver': '1298', 'new_json': '1', 'remoteplace': 'sizer.yqq.song_next', 'searchid': '64405487069162918', 't': '0', 'aggr': '1', 'cr': '1', 'catZhida': '1', 'lossless': '0', 'flag_qc': '0', 'p': str(x + 1), 'n': '20', 'w': name0, 'g_tk': '5381', 'loginUin': '0', 'hostUin': '0', 'format': 'json', 'inCharset': 'utf8', 'outCharset': 'utf-8', 'notice': '0', 'platform': 'yqq.json', 'needNewCode': '0' }          res_music = requests.get(url, params=params,headers=headers)         json_music = res_music.json()         list_music = json_music['data']['song']['list'] for music in list_music:             name = music['name']             album = music['album']['name']             time = str(music['interval'])             link = 'https://y.qq.com/n/yqq/song/' + music['file']['media_mid'] + '.htmlnn'             list_all.append(name + 'n' + album + 'n' + time+'s' + 'n' + link ) return 'nn'.join(list_all)  def send_email(list):     mailhost = 'smtp.qq.com'     qqmail = smtplib.SMTP()     qqmail.connect(mailhost, 25)     qqmail.login(account, password)     content = '亲爱的，今天的推荐歌曲是：' + list     message = MIMEText(content, 'plain', 'utf-8')     subject = '今日推荐'     message['Subject'] = Header(subject, 'utf-8') try:         qqmail.sendmail(account, receiver, message.as_string()) print('邮件发送成功')     except: print('邮件发送失败')     qqmail.quit() if __name__ == '__main__':     account = input('请输入你的邮箱：')     password = input('请输入你的邮箱密码：')     receiver = input('请输入收件人：')     mess = getdata() send_email(mess) 

第三部分，爬取周董（任何一个人）歌曲信息存入MySQL数据库
第一小步，先建表

 import pymysql  conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='你的数据库密码', db='你的数据库名称', charset='utf8')  cursor = conn.cursor()   sql = """create table qqmusic3(         m_id int primary key auto_increment,         m_name varchar (100) not null,         m_album text null,         m_time varchar(10) not null,         m_link varchar(100) not null                  )"""  cursor.execute(sql)  cursor.close() conn.close()  

第二小步，爬取数据存入数据库

 import requests,pymysql  conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='你的数据库密码', db='你的数据库名称', charset='utf8')  cursor = conn.cursor()  name0=input('请输入你要爬取歌手的名字')  headers={'Referer':'https://y.qq.com/',     'User-Agent':'pgv_pvid=1717322000; ts_uid=6297933380; pgv_pvi=3979401216; RK=+ZbNcEN3Qw; ptcz=4278eb1e104c210e009b4fa2e86ef406c85fb5712664ab041b0ec59f39a70f78; ts_refer=www.baidu.com/link; yqq_stat=0; pgv_info=ssid=s7548246693; pgv_si=s6134429696; ts_last=y.qq.com/'  }  url = 'https://c.y.qq.com/soso/fcgi-bin/client_search_cp' for x in range(5):      params = {         'ct': '24',         'qqmusic_ver': '1298',         'new_json': '1',         'remoteplace': 'sizer.yqq.song_next',         'searchid': '64405487069162918',         't': '0',         'aggr': '1',         'cr': '1',         'catZhida': '1',         'lossless': '0',         'flag_qc': '0',         'p': str(x + 1),         'n': '20',         'w': name0,         'g_tk': '5381',         'loginUin': '0',         'hostUin': '0',         'format': 'json',         'inCharset': 'utf8',         'outCharset': 'utf-8',         'notice': '0',         'platform': 'yqq.json',         'needNewCode': '0'     }      res_music = requests.get(url, params=params,headers=headers)     json_music = res_music.json()     list_music = json_music['data']['song']['list']     for music in list_music:         # print(music['name'])         # print('所属专辑：' + music['album']['name'])         # print('播放时长：' + str(music['interval']) + '秒')         # print('播放链接：https://y.qq.com/n/yqq/song/' + music['file']['media_mid'] + '.htmlnn')          name = music['name']         album = music['album']['name']         time = str(music['interval'])         link = 'https://y.qq.com/n/yqq/song/' + music['file']['media_mid'] + '.htmlnn'         sql = 'insert into qqmusic3(m_name,m_album, m_time,m_link) values ("%s","%s","%s","%s")' % (             name,album,time, link)          cursor.execute(sql)    conn.commit() cursor.close() conn.close()   

看一下数据库的数据