数据库设计如下

image.png
image.png

image.png

代码如下

  1. # -*- coding: utf-8 -*-
  2. import requests  # 导入requests包
  3. import json
  4. import datetime
  5. import time
  6. import pymysql
  8. # 打开数据库链接
  9. db = pymysql.connect(
  10.     host="localhost",
  11.     port=3306,
  12.     user="root",
  13.     password="1234",
  14.     database="database1"
  15. )
  17. # 存放微博用户数据
  18. userdata = []
  21. # 从数据库查询所有微博的账户信息
  22. def get_account_mes():
  23.     # 使用cursor创建一个游标对象
  24.     cursor = db.cursor()
  25.     # 准备sql语句
  26.     select_sql = """select * from users"""
  27.     cursor.execute(select_sql)
  28.     result = cursor.fetchall()
  29.     for row in result:
  30.         if row[1] == "微博":
  31.             userdata.append(row)
  32.     cursor.close()
  35. get_account_mes()
  38. # 提交数据至数据库
  39. def Submit_data_to_database(userdata, fbnr_data) -> str:
  40.     try:
  41.         # 提交该账户至账户数据库
  42.         # 使用 cursor() 方法创建一个游标对象 cursor
  43.         cursor = db.cursor()
  44.         tmp = [userdata]
  45.         # 准备插入用户账户数据的SQL
  46.         user_sql = """INSERT INTO `weibo_account`(
  47.         `Subordinate_units`, `name`, `phonenumber`, `platform`, `Account_name`, `weibohao`, `Number_of_vermicelli`, 
  48.         `Attention_number`, `Total_Weibo_number`, `authentication`, `brief_introduction`, `Statistical_time`) 
  49.         VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"""
  50.         # 使用 execute()  方法执行 SQL 查询
  51.         cursor.executemany(user_sql, tuple(tmp))
  52.         # 准备插入用户发布内容的sql
  53.         fbnr_data_sql = """INSERT INTO `weibo_works`(
  54.                         `Subordinate_units`, `name`, `phonenumber`, `platform`, `Account_name`, `weibohao`,
  55.                         `Weibo_content`, `Weibo_number`, `Forward_number`, `Like_number`, `Number_of_comments`,
  56.                         `original`, `Release_time`, `Statistical_time`)
  57.                         VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"""
  58.         cursor.executemany(fbnr_data_sql, fbnr_data)
  59.         # 关闭游标
  60.         cursor.close()
  61.         db.commit()
  62.         return "提交成功"
  63.         # 提交账户发布内容数据至内容数据库
  64.     except Exception as err:
  65.         return str(err)
  68. # 转换时间函数
  69. def trans_format(time_string, from_format, to_format='%Y.%m.%d %H:%M:%S'):
  70.     """
  71.     @note 时间格式转化
  72.     :param time_string:
  73.     :param from_format:
  74.     :param to_format:
  75.     :return:
  76.     """
  77.     time_struct = time.strptime(time_string, from_format)
  78.     times = time.strftime(to_format, time_struct)
  79.     return times
  82. # 构造请求头
  83. headers = {
  84.     "cookie": "SUB=_2AkMVdLNgf8NxqwFRmP0cxW7jaY5-wwvEieKjKEK7JRMxHRl-yT_nqlIAtRB6PvSdgcJJunaym373dN91W1MIJArq7AzH; "
  85.               "SUBP=0033WrSXqPxfM72-Ws9jqgMF55529P9D9WhXawrTDmUF1NkVijRui0Nq; XSRF-TOKEN=etaNQZcst4rs9As_AFABQCIw; _"
  86.               "s_tentry=weibo.com; Apache=5741302240361.778.1646804064448; SINAGLOBAL=5741302240361.778.1646804064448; "
  87.               "ULV=1646804064507:1:1:1:5741302240361.778.1646804064448:; WBPSESS=lGn6cRy34B6AsqM-wzgd2I3xJQoNdKT6SN7Fn"
  88.               "X0cs85ue7ykV44_MHoXYZ8pflevb5zjicdSLORoi8NsE9e4TLIieWK6K88wqnQQWv1OCjPWh7PkbuL85XSFk_elSupLSXOoaSpYmka7"
  89.               "56SI5n5pJLbwdq81wNl0EaauOZM7zfE=",
  90.     "user-agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) "
  91.                   "Chrome/92.0.4515.107 Safari/537.36 HBPC/12.0.0.300",
  92. }
  93. for usermes in userdata:
  94.     try:
  95.         if usermes is None:
  96.             continue
  97.         if usermes[5] != "肖战工作室":
  98.             continue
  99.         time.sleep(1)
  100.         # 准备一个数组存储当前用户的数据
  101.         thisWbUserData = []
  102.         # 添加序号
  103.         # thisWbUserData.append(len(wbUserData)+1)
  104.         # 拼接url
  105.         url = 'https://weibo.com/ajax/profile/info?custom=' + usermes[6]
  106.         strHtml = requests.get(url=url, headers=headers, stream=True)
  107.         strHtml.encoding = 'utf-8'
  108.         mes = json.loads(strHtml.text)
  110.         # 添加账号名称
  111.         print("正在查询" + mes["data"]["user"]["screen_name"])
  112.         # 添加所属单位
  113.         thisWbUserData.append(usermes[2])
  114.         # 添加姓名
  115.         thisWbUserData.append(usermes[3])
  116.         # 添加联系电话
  117.         thisWbUserData.append(usermes[4])
  118.         # 添加开设平台
  119.         thisWbUserData.append(usermes[1])
  120.         # 添加账号名称
  121.         thisWbUserData.append(mes["data"]["user"]["screen_name"])
  122.         # 添加微博号
  123.         thisWbUserData.append(mes["data"]["user"]["idstr"])
  124.         # 添加粉丝数
  125.         thisWbUserData.append(str(mes["data"]["user"]["followers_count"]))
  126.         # 添加关注数
  127.         thisWbUserData.append(str(mes["data"]["user"]["friends_count"]))
  128.         # 添加微博数
  129.         thisWbUserData.append(str(mes["data"]["user"]["statuses_count"]))
  130.         # 添加认证
  131.         if "verified_reason" in mes["data"]["user"]:
  132.             thisWbUserData.append(mes["data"]["user"]["verified_reason"])
  133.         else:
  134.             thisWbUserData.append("暂无认证")
  135.         # 添加简介
  136.         if mes["data"]["user"]["description"] == "":
  137.             thisWbUserData.append("暂无简介")
  138.         else:
  139.             thisWbUserData.append(mes["data"]["user"]["description"])
  140.         # 添加统计时间
  141.         thisWbUserData.append(str(datetime.date.today()))
  143.         # 统计该用户的所有微博数据
  144.         # 构建数组存放微博文章数据
  145.         # 存储文章
  146.         wbUserAtricData = []
  147.         # 先计算该用户的微博共有几页
  148.         pagenum = 0
  149.         if (int(mes["data"]["user"]["statuses_count"]) - 9) % 10 == 0:
  150.             pagenum = int(int(int(mes["data"]["user"]["statuses_count"]) - 9) / 10) + 1
  151.         elif int(mes["data"]["user"]["statuses_count"]) <= 9:
  152.             pagenum = 1
  153.         else:
  154.             pagenum = int(int(int(mes["data"]["user"]["statuses_count"]) - 9) / 10) + 2
  155.         if pagenum >= 100:
  156.             pagenum = 99
  157.         num = 0
  158.         isbreak = False
  160.         for i in range(1, pagenum + 1):
  161.             time.sleep(3)
  162.             # 拼接url
  163.             pageurl = "https://weibo.com/ajax/statuses/mymblog?uid=" + usermes[6] + "&page=" + str(i) + "&feature=0"
  165.             pageMes = requests.get(url=pageurl, headers=headers, stream=True)
  166.             time.sleep(2)
  167.             pageJson = json.loads(pageMes.text)
  168.             # 如果长度为0说明服务器返回数据有误
  169.             if len(pageJson["data"]["list"]) == 0:
  170.                 isbreak = True
  171.                 break
  172.             for i in pageJson["data"]["list"]:
  173.                 # 如果时间小于限定时间，则直接break
  174.                 if datetime.datetime.strptime(
  175.                         trans_format(i["created_at"], '%a %b %d %H:%M:%S +0800 %Y', '%Y-%m-%d %H:%M:%S'),
  176.                         '%Y-%m-%d %H:%M:%S') < datetime.datetime.strptime('2022-01-01 00:00:00', '%Y-%m-%d %H:%M:%S'):
  177.                     isbreak = True
  178.                     break
  179.                 # 准备一个数组存放这篇文章的数据
  180.                 thisArticData = []
  181.                 print(mes["data"]["user"]["screen_name"] + "第" + str(num) + "篇微博")
  182.                 num = num + 1
  184.                 # 添加所属单位
  185.                 thisArticData.append(usermes[2])
  186.                 # 添加姓名
  187.                 thisArticData.append(usermes[3])
  188.                 # 添加联系电话
  189.                 thisArticData.append(usermes[4])
  190.                 # 添加开设平台
  191.                 thisArticData.append(usermes[1])
  192.                 # 添加账号名
  193.                 thisArticData.append(mes["data"]["user"]["screen_name"])
  194.                 # 添加微博号
  195.                 thisArticData.append(mes["data"]["user"]["idstr"])
  196.                 # 添加微博内容
  197.                 if len(i["text_raw"]) > 100:
  198.                     thisArticData.append((i["text_raw"][:100]).replace("\u200b", "").replace("\n", "") + "...")
  199.                 else:
  200.                     thisArticData.append((i["text_raw"]).replace("\u200b", "").replace("\n", ""))
  201.                 # 添加微博编号
  202.                 thisArticData.append(i["idstr"])
  203.                 # 添加转发数
  204.                 thisArticData.append(str(i['reposts_count']))
  205.                 # 添加点赞数
  206.                 thisArticData.append(str(i['attitudes_count']))
  207.                 # 添加评论数
  208.                 thisArticData.append(str(i['comments_count']))
  209.                 if 'page_info' in i or 'retweeted_status' in i:
  210.                     thisArticData.append("转发")
  211.                 else:
  212.                     thisArticData.append("原创")
  213.                 # 添加发布时间
  215.                 tmp_time = trans_format(i["created_at"], '%a %b %d %H:%M:%S +0800 %Y', '%Y-%m-%d %H:%M:%S')
  216.                 # print(tmp_time)
  217.                 thisArticData.append(tmp_time)
  218.                 # 添加统计时间
  219.                 thisArticData.append(str(datetime.date.today()))
  220.                 # 合并至总数组
  221.                 wbUserAtricData.append(tuple(thisArticData))
  222.             # 如果已经查询到日期早于限定的文章，则直接退出
  223.             if isbreak:
  224.                 print("后续微博日期不符合要求，即将提交数据并查询下一位用户")
  225.                 break
  226.         s = Submit_data_to_database(tuple(thisWbUserData), tuple(wbUserAtricData))
  227.         print(s)
  228.     except Exception as e:
  229.         print(e)
  230. # 关闭数据库连接
  231. db.close()