Technology Sharing

Python function production to obtain the corresponding data of all published articles on CSDN

2024-07-12

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

Hello everyone, today I want to share a practicalPython Script, which can help you batch obtain the relevant data of all published articles on the CSDN blog and save the data into an Excel file. In addition, the script will obtain a quality score for each article and record this score in Excel as well. Let's get started!

Script functionality overview

This script is mainly divided into two parts:

  1. Get article information and save to Excel：This part will get your article list from CSDN API and save the key information into an Excel file.
  2. Get article quality score and update Excel: This part will request a quality score for each article and add the score to the corresponding Excel file.

Implementation steps

1. Import necessary libraries

First, we need to import some Python libraries to help us with this task:

  1. import json
  2. import pandas as pd
  3. from openpyxl import Workbook, load_workbook
  4. from openpyxl.utils.dataframe import dataframe_to_rows
  5. import math
  6. import requests

2. Define and save article informationExcelthe type

We defined a classGetInformationToExcelTo handle the acquisition of article information and the saving of Excel files:

  1. class GetInformationToExcel:
  2.     def __init__(self, username, cookies, Referer, page, size, filename):
  3.         self.username = username
  4.         self.cookies = cookies
  5.         self.Referer = Referer
  6.         self.size = size
  7.         self.filename = filename
  8.         self.page = page
  9.  
  10.     # 发送HTTP GET请求到CSDN的API，获取文章列表
  11.     def get_articles(self):
  12.         url = "https://blog.csdn.net/community/home-api/v1/get-business-list"
  13.         params = {
  14.             "page": {self.page},
  15.             "size": {self.size},
  16.             "businessType": "blog",
  17.             "username": {self.username}
  18.         }
  19.         headers = {
  20.             'User-Agent': 'Mozilla/5.0 (Windows NT 11.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.3',
  21.             'Cookie': self.cookies,
  22.             'Referer': self.Referer
  23.         }
  24.  
  25.         try:
  26.             response = requests.get(url, params=params, headers=headers)
  27.             response.raise_for_status()
  28.             data = response.json()
  29.             return data.get('data', {}).get('list', [])
  30.         except requests.exceptions.HTTPError as e:
  31.             print(f"HTTP错误: {e.response.status_code} {e.response.reason}")
  32.         except requests.exceptions.RequestException as e:
  33.             print(f"请求异常: {e}")
  34.         except json.JSONDecodeError:
  35.             print("解析JSON失败")
  36.         return []
  37.  
  38.     # 将文章列表转换为Pandas DataFrame,选择并重命名必要的列。
  39.     def export_to_excel(self):
  40.         df = pd.DataFrame(self.get_articles())
  41.         df = df[['title', 'url', 'postTime', 'viewCount', 'collectCount', 'diggCount', 'commentCount']]
  42.         df.columns = ['文章标题', 'URL', '发布时间', '阅读量', '收藏量', '点赞量', '评论量']
  43.         wb = Workbook()
  44.         sheet = wb.active
  45.         for r in dataframe_to_rows(df, index=False, header=True):
  46.             sheet.append(r)
  47.         for column in sheet.columns:
  48.             max_length = 0
  49.             column = [cell for cell in column]
  50.             for cell in column:
  51.                 try:
  52.                     if len(str(cell.value)) > max_length:
  53.                         max_length = len(cell.value)
  54.                 except:
  55.                     pass
  56.             adjusted_width = (max_length + 5)
  57.             sheet.column_dimensions[column[0].column_letter].width = adjusted_width
  58.         # Save the workbook
  59.         wb.save(self.filename)

In this class, we implemented the following methods:

  • __init__: Initialization method, setting the basic properties of the class.
  • get_articles: Send an HTTP GET request to the CSDN API to get a list of articles.
  • export_to_excel: Convert the article list to a Pandas DataFrame and save it to an Excel file.

3. Define the class for obtaining article quality scores

Next, we define another classGetArticleScoresTo handle the acquisition of article quality scores and the update of Excel files:

  1. class GetArticleScores:
  2.     def __init__(self, filepath):
  3.         self.filepath = filepath
  4.  
  5.     # 发送HTTP POST请求到一个API，获取文章的质量分。
  6.     @staticmethod
  7.     def get_article_score(article_url):
  8.         url = "https://bizapi.csdn.net/trends/api/v1/get-article-score"
  9.         headers = {
  10.             "Accept": "application/json, text/plain, */*",
  11.             "X-Ca-Key": "203930474",
  12.             "X-Ca-Nonce": "b35e1821-05c2-458d-adae-3b720bb15fdf",
  13.             "X-Ca-Signature": "gjeSiKTRCh8aDv0UwThIVRITc/JtGJkgkZoLVeA6sWo=",
  14.             "X-Ca-Signature-Headers": "x-ca-key,x-ca-nonce",
  15.             "X-Ca-Signed-Content-Type": "multipart/form-data",
  16.         }
  17.         data = {"url": article_url}
  18.         try:
  19.             response = requests.post(url, headers=headers, data=data)
  20.             response.raise_for_status()  # This will raise an error for bad responses
  21.             return response.json().get('data', {}).get('score', 'Score not found')
  22.         except requests.RequestException as e:
  23.             print(f"Request failed: {e}")
  24.             return "Error fetching score"
  25.  
  26.     def get_scores_from_excel(self):
  27.         """读取Excel文件，获取文章URL列表。
  28.             对每个URL调用 get_article_score 方法，获取分数列表。
  29.             返回分数列表。"""
  30.         df = pd.read_excel(self.filepath)
  31.         urls = df['URL'].tolist()
  32.         scores = [self.get_article_score(url) for url in urls]
  33.         return scores
  34.  
  35.     def write_scores_to_excel(self):
  36.         """读取Excel文件到DataFrame。
  37.             将获取的分数添加到DataFrame中。
  38.             将更新后的DataFrame保存回Excel文件。"""
  39.         df = pd.read_excel(self.filepath)
  40.         df['质量分'] = self.get_scores_from_excel()
  41.         df.to_excel(self.filepath, index=False)

In this class, we implemented the following methods:

  • __init__: Initialization method, setting the basic properties of the class.
  • get_article_score: Static method that sends an HTTP POST request to an API to obtain the quality score of the article.
  • get_scores_from_excel: Read the Excel file, get the article URL list, and get the score list.
  • write_scores_to_excel: Read the Excel file into a DataFrame, add the obtained scores to the DataFrame, and save it back to the Excel file.

4. Main program

Finally, we set the total number of articles, cookies, Referer, and CSDN user ID in the main program and performed the following steps:

  • Calculate the number of pages that need to be requested.
  • Loop through each page of articles, create an Excel file, and get the quality score and write it into Excel.
  1. if __name__ == '__main__':
  2.     # 请填写:已发文章总数量,cookies,你的首页Referer，你的id：CSDNid
  3.     total = 145
  4.     cookies = 'uuid_tt_dd=10'  # Simplified for brevity
  5.     Referer = 'https://blog.csdn.net/q244645787'
  6.     CSDNid = 'q244645787'
  7.     # 下面是计算和获取
  8.     t_index = math.ceil(total / 100) + 1  # 向上取整，半闭半开区间，开区间+1。
  9.     for index in range(1, t_index):  # 文章总数
  10.         filename = "score" + str(index) + ".xlsx"
  11.         exporter_excel = GetInformationToExcel(CSDNid, cookies, Referer, index, 100, filename)  # Replace with your username
  12.         exporter_excel.export_to_excel()
  13.         article_score = GetArticleScores(filename)
  14.         article_score.write_scores_to_excel()
  15.  
  16.     print("获取完成")

After the execution is completed, you will get an Excel file containing all article data and quality scores.

All code:

  1. import json
  2. import pandas as pd
  3. from openpyxl import Workbook, load_workbook
  4. from openpyxl.utils.dataframe import dataframe_to_rows
  5. import math
  6. import requests
  7.  
  8.  
  9. # 批量获取文章信息并保存到excel
  10. class GetInformationToExcel:
  11.     def __init__(self, username, cookies, Referer, page, size, filename):
  12.         self.username = username
  13.         self.cookies = cookies
  14.         self.Referer = Referer
  15.         self.size = size
  16.         self.filename = filename
  17.         self.page = page
  18.  
  19.     # 发送HTTP GET请求到CSDN的API，获取文章列表
  20.     def get_articles(self):
  21.         url = "https://blog.csdn.net/community/home-api/v1/get-business-list"
  22.         params = {
  23.             "page": {self.page},
  24.             "size": {self.size},
  25.             "businessType": "blog",
  26.             "username": {self.username}
  27.         }
  28.         headers = {
  29.             'User-Agent': 'Mozilla/5.0 (Windows NT 11.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.3',
  30.             'Cookie': self.cookies,
  31.             'Referer': self.Referer
  32.         }
  33.  
  34.         try:
  35.             response = requests.get(url, params=params, headers=headers)
  36.             response.raise_for_status()
  37.             data = response.json()
  38.             return data.get('data', {}).get('list', [])
  39.         except requests.exceptions.HTTPError as e:
  40.             print(f"HTTP错误: {e.response.status_code} {e.response.reason}")
  41.         except requests.exceptions.RequestException as e:
  42.             print(f"请求异常: {e}")
  43.         except json.JSONDecodeError:
  44.             print("解析JSON失败")
  45.         return []
  46.  
  47.     # 将文章列表转换为Pandas DataFrame,选择并重命名必要的列。
  48.     def export_to_excel(self):
  49.         df = pd.DataFrame(self.get_articles())
  50.         df = df[['title', 'url', 'postTime', 'viewCount', 'collectCount', 'diggCount', 'commentCount']]
  51.         df.columns = ['文章标题', 'URL', '发布时间', '阅读量', '收藏量', '点赞量', '评论量']
  52.         wb = Workbook()
  53.         sheet = wb.active
  54.         for r in dataframe_to_rows(df, index=False, header=True):
  55.             sheet.append(r)
  56.         for column in sheet.columns:
  57.             max_length = 0
  58.             column = [cell for cell in column]
  59.             for cell in column:
  60.                 try:
  61.                     if len(str(cell.value)) > max_length:
  62.                         max_length = len(cell.value)
  63.                 except:
  64.                     pass
  65.             adjusted_width = (max_length + 5)
  66.             sheet.column_dimensions[column[0].column_letter].width = adjusted_width
  67.         # Save the workbook
  68.         wb.save(self.filename)
  69.  
  70.  
  71. # 获取每篇文章的质量分，并将分数写入到Excel文件中
  72. class GetArticleScores:
  73.     def __init__(self, filepath):
  74.         self.filepath = filepath
  75.  
  76.     # 发送HTTP POST请求到一个API，获取文章的质量分。
  77.     @staticmethod
  78.     def get_article_score(article_url):
  79.         url = "https://bizapi.csdn.net/trends/api/v1/get-article-score"
  80.         headers = {
  81.             "Accept": "application/json, text/plain, */*",
  82.             "X-Ca-Key": "203930474",
  83.             "X-Ca-Nonce": "b35e1821-05c2-458d-adae-3b720bb15fdf",
  84.             "X-Ca-Signature": "gjeSiKTRCh8aDv0UwThIVRITc/JtGJkgkZoLVeA6sWo=",
  85.             "X-Ca-Signature-Headers": "x-ca-key,x-ca-nonce",
  86.             "X-Ca-Signed-Content-Type": "multipart/form-data",
  87.         }
  88.         data = {"url": article_url}
  89.         try:
  90.             response = requests.post(url, headers=headers, data=data)
  91.             response.raise_for_status()  # This will raise an error for bad responses
  92.             return response.json().get('data', {}).get('score', 'Score not found')
  93.         except requests.RequestException as e:
  94.             print(f"Request failed: {e}")
  95.             return "Error fetching score"
  96.  
  97.     def get_scores_from_excel(self):
  98.         """读取Excel文件，获取文章URL列表。
  99.             对每个URL调用 get_article_score 方法，获取分数列表。
  100.             返回分数列表。"""
  101.         df = pd.read_excel(self.filepath)
  102.         urls = df['URL'].tolist()
  103.         scores = [self.get_article_score(url) for url in urls]
  104.         return scores
  105.  
  106.     def write_scores_to_excel(self):
  107.         """读取Excel文件到DataFrame。
  108.             将获取的分数添加到DataFrame中。
  109.             将更新后的DataFrame保存回Excel文件。"""
  110.         df = pd.read_excel(self.filepath)
  111.         df['质量分'] = self.get_scores_from_excel()
  112.         df.to_excel(self.filepath, index=False)
  113.  
  114.  
  115. if __name__ == '__main__':
  116.     # 请填写:已发文章总数量,cookies,你的首页Referer，你的id：CSDNid
  117.     total = 145
  118.     cookies = 'uuid_tt_dd=10'  # Simplified for brevity
  119.     Referer = 'https://blog.csdn.net/q244645787'
  120.     CSDNid = 'q244645787'
  121.     # 下面是计算和获取
  122.     t_index = math.ceil(total / 100) + 1  # 向上取整，半闭半开区间，开区间+1。
  123.     for index in range(1, t_index):  # 文章总数
  124.         filename = "score" + str(index) + ".xlsx"
  125.         exporter_excel = GetInformationToExcel(CSDNid, cookies, Referer, index, 100, filename)  # Replace with your username
  126.         exporter_excel.export_to_excel()
  127.         article_score = GetArticleScores(filename)
  128.         article_score.write_scores_to_excel()
  129.  
  130.     print("获取完成")

Effect

Personal profile

I have devoted myself to the research of technology for more than 30 years. I am proficient in various languages ​​such as Java, Linux, JavaScript, PHP, CSS, etc. I have made many contributions in the field of open source. I have established a developer documentation site to share some problems in technology development for everyone to read.

my contact information

Mail