Python爬取微博数据并存入mysql，excel中

寒假期间做微博数据统计，由于是部门要求，也没办法，自己又是一个懒人，直接用python实现吧。写的很粗糙，也很差，请大家别介意。

总的来说分成两个模块：一个爬取微博数据存入数据库，一个从数据库调取数据放入excel表格中。还有一个config文件，由于每周值班人员几乎都在变动以及日期也都是从周一到周日，由于某些原因不能做实时数据统计，所以需要config文件设置代码中的部分情况。注意我在mysql中是提前建立表的，而我放假回来后，莫名其妙mysql某个配置文件丢失，导致我花了很久才重装了，数据也都丢失了。

平心而论，代码很简单，但值得注意的是要关注爬取数据中的不同点：

微博数据客户端与手机端获取数据有差，有部分数据丢失。

 

import requests
import time
import pymysql
import re
from pyquery import PyQuery as py
db = pymysql.connect('localhost', 'root', '123456', 'hfut_weibo',use_unicode=True, charset="utf8")
cursor = db.cursor()
cursor.execute('SET NAMES utf8;')
cursor.execute('SET CHARACTER SET utf8;')
cursor.execute('SET character_set_connection=utf8;')
headers = {'user-agent':'Mozilla/5.0 (Linux; Android 6.0; Nexus 5 Build/MRA58N) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/63.0.3239.132 Mobile Safari/537.36'}

def mysql(date,attitudes_count,comments_count, reposts_count, index,title, data):
    data = transferContent(data)
    title = transferContent(title)
    comtent = (
        '#冰糖说英语#', '#冰糖点歌台#', '#冰糖小百科#', '#冰糖的树洞#', '#冰糖么么答#', '#冰糖赠书#', '#冰糖书影音#', '#老照片#', '#直播#',
        '#送福利#')
    for com in comtent:
        parttern = re.compile(com)
        match = re.search(parttern,title)
        if match:
            title = match.group()
            print(data)
            sql = '''
                    INSERT INTO inital_weibo_data_special VALUES ("%s",'%d','%d','%d','%f',"%s","%s")
                ''' % (date, attitudes_count, comments_count, reposts_count, index, title, data)
            cursor.execute(sql)
            db.commit()
            return
    sql = '''
                       INSERT INTO inital_weibo_data VALUES ('%s',"%d","%d",'%d','%f',"%s","%s")
                   ''' % (date, attitudes_count, comments_count, reposts_count, index, title, data)
    cursor.execute(sql)
    db.commit()
    return

def transferContent( content):
        string = ""
        for c in content:
            if c == '"':
                string += '\\\"'
            elif c == "'":
                string += "\\\'"

            else:
                 string += c
        return string
def error_com(data):
    # comtent = ('#冰糖说英语#','#冰糖点歌台#','#冰糖小百科#','#冰糖的树洞#','#冰糖么么答#','#冰糖赠书#','#冰糖书影音#','#老照片#','#直播#','#送福利#')
    # # for com in comtent:
    # for com in comtent:
    #     parttern = re.compile(com)
    #     martch = re.search(parttern,data)
    #
    #     if  martch:
    #         print(martch.string)
    #         return martch.string
    doc = py(data)
    return doc.find('a').text()
def data_store():
    Q = 0
    for i in range(1,30):
        time.sleep(0.5)
        url  = "https://m.weibo.cn/api/container/getIndex?uid=2348668214&luicode=10000011&lfid=100103type%3D1%26q%3D%E5%90%88%E8%82%A5%E5%B7%A5%E4%B8%9A%E5%A4%A7%E5%AD%A6&featurecode=20000320&type=uid&value=2348668214&containerid=1076032348668214&page={num}".format(num = i)
        r_1 = requests.get(
            url,
            headers=headers)
        try:

            if i == 1:
                for j in range(2, 11):
                    time.sleep(0.05)

                    page_1 = r_1.json()['data']['cards'][j]['mblog']
                    date = page_1['created_at']
                    attitudes_count = page_1['attitudes_count']
                    comments_count = page_1['comments_count']
                    reposts_count = page_1['reposts_count']
                    data = page_1['text']
                    index = attitudes_count + comments_count*0.5 + reposts_count*2
                    doc = py(data)
                    title =  doc.find('a').text()


                    print(page_1['text'])
                    print(page_1['attitudes_count'])
                    print(page_1['comments_count'])
                    print(page_1['reposts_count'])
                    print(index)
                    print(page_1['created_at'])
                    print(title)

                    try:


                        mysql(date, attitudes_count, comments_count, reposts_count, index, title, data)
                    except:
                        # title = page_1['page_info']['page_title']
                        # mysql(date, attitudes_count, comments_count, reposts_count, index, title)
                        # print('本次插入错误，继续下一次')
                        data = title
                        mysql(date, attitudes_count, comments_count, reposts_count, index, title, data)

                        continue

            else:
                print(r_1.json()['data']['cards'])
                for j in range(0,10):

                    print(url)
                    time.sleep(0.05)

                    page_1 = r_1.json()['data']['cards'][j]['mblog']
                    date = page_1['created_at']
                    attitudes_count = page_1['attitudes_count']
                    comments_count = page_1['comments_count']
                    reposts_count = page_1['reposts_count']
                    data = page_1['text']
                    index = attitudes_count + comments_count * 0.5 + reposts_count * 2
                    doc = py(data)
                    title = doc.find('a').text()

                    print(j)
                    print(page_1['text'])
                    print(page_1['attitudes_count'])
                    print(page_1['comments_count'])
                    print(page_1['reposts_count'])
                    print(index)
                    print(page_1['created_at'])

                    try:
                        mysql(date, attitudes_count, comments_count, reposts_count, index, title, data)
                    except:
                        print(11111111111111111111111111111111111111111111111111111111111111111111111111111111)
                        # title = page_1['page_info']['page_title']
                        # mysql(date, attitudes_count, comments_count, reposts_count, index, title)
                        data = title
                        mysql(date, attitudes_count, comments_count, reposts_count, index, title, data)
                        continue
        except:
            Q = Q +1
            continue
    print(Q)

def main():
    data_store()

if __name__ == '__main__':
    main()
    db.close()

　

import xlsxwriter
import pymysql


##################################################################
from config_155 import *
##################################################################



db = pymysql.connect('localhost', 'root', '123456', 'hfut_weibo',use_unicode=True, charset="utf8")
cursor = db.cursor()
cursor.execute('SET NAMES utf8;')
cursor.execute('SET CHARACTER SET utf8;')
cursor.execute('SET character_set_connection=utf8;')
workbook = xlsxwriter.Workbook("E:\合肥工业大学微博值班数据\%s.xlsx" % document_name)
worksheet1 = workbook.add_worksheet("日常值班")
worksheet1.write(0, 0,'日期')
worksheet1.write(0, 1, '值班人')
worksheet1.write(0, 2, '转发数')
worksheet1.write(0, 3, '评论数')
worksheet1.write(0, 4, '点赞数')
worksheet1.write(0, 5, '指标数')

worksheet2 = workbook.add_worksheet("特色栏目")
worksheet2.write(0, 0,'栏目')
worksheet2.write(0, 1, '负责人')
worksheet2.write(0, 2, '转发数')
worksheet2.write(0, 3, '评论数')
worksheet2.write(0, 4, '点赞数')
worksheet2.write(0, 5, '指标数')



def mysql_read(time,name,i,sig):
    if sig == 1:
        sql='''
        SELECT * FROM inital_weibo_data WHERE date='%s'
        ''' % time
    else:
        sql = '''
               SELECT * FROM inital_weibo_data_special WHERE title='%s' AND date BETWEEN '%s' AND '%s'
               ''' % (time,timt_start,time_conclude)
    return sql
def excel_work(sql,i,time,name,worksheet):
    try:
        # 执行SQL语句
        cursor.execute(sql)
        # 获取所有记录列表
        results = cursor.fetchall()
        print(results)
        worksheet.write(i, 0, time)
        worksheet.write(i, 1, name)
        index_all = 0
        attitudes_count_all = 0
        comments_count_all = 0
        reposts_count_all = 0
        for row in results:
            date = row[0]
            attitudes_count = row[1]
            comments_count = row[2]
            reposts_count = row[3]
            index = row[4]
            index_all += index
            attitudes_count_all += attitudes_count
            comments_count_all += comments_count
            reposts_count_all += reposts_count

            worksheet.write(i, 2, reposts_count)
            worksheet.write(i, 3, comments_count)
            worksheet.write(i, 4, attitudes_count)
            worksheet.write(i, 5, index)
            i = i + 1

            # 打印结果
            print(date, attitudes_count, comments_count, reposts_count, index)
        worksheet.write(i, 2, reposts_count_all)
        worksheet.write(i, 3, comments_count_all)
        worksheet.write(i, 4, attitudes_count_all)
        worksheet.write(i, 5, index_all)
        return i + 2
    except:
        print("Error: unable to fecth data")
def main():
    i = 1
    for (k, v) in work_1.items():
        time = k
        name = v
        sql = mysql_read(time, name, i, 1)
        i = excel_work(sql,i,time,name,worksheet1)
    i=1
    for (k, v) in work_0.items():
        time = k
        name = v
        sql = mysql_read(time, name, i, 0)
        i = excel_work(sql, i, time, name, worksheet2)

if __name__ == '__main__':
    main()
    db.close()
    workbook.close()

　　

work = {'星期一':'张壮','星期二':'王修齐','星期三':'徐露露','星期四':'张雪辰','星期五':'孙艳丽','星期六':'马玉龙','星期日':'陈之羽',}
work_0 = \
    {'#冰糖说英语#':'彭二丹','#冰糖点歌台#':'董令','#冰糖小百科#':'周成旭','#冰糖的树洞#':'张雪辰','#冰糖么么答#':'',\
     '#冰糖赠书#':'蒋媛媛','#冰糖书影音#':'云曈','#老照片#':'彭二丹','#直播#':'蒋媛媛','#送福利#':'彭二丹','#冰糖说英语#':'彭二丹',}
work_1 = {'01-08':'张壮','01-09':'王修齐','01-10':'徐露露','01-11':'张雪辰','01-12':'孙艳丽','01-13':'马玉龙','01-14':'陈之羽',}
document_name = '150周（第十九教学周）数据统计（2018.1.08-2018.1.14）'
timt_start = '01-08'
time_conclude = '01-14'

　　

 

　

 

转载于:https://www.cnblogs.com/wanmudong/p/8762620.html