9.3月7日 阶段复习

ubuntu环境

注意虚拟机克隆到D盘

真机设置网卡
虚拟机设置网卡
安装ssh sudo apt install ssh
真机通过xshell连接虚拟机

后续操作通过xshell 进行

mysql数据库

请创建数据库“my_db”

并创建表

班级	姓名	期中语文	期中数学	期中英语	期末语文	期末数学	期末英语
字符	字符	int	int	int	int	int	int

表结构设计

CREATE TABLE student_scores (
    id INT AUTO_INCREMENT PRIMARY KEY,
    class VARCHAR(50),
    name VARCHAR(50),
    mid_term_chinese INT,
    mid_term_math INT,
    mid_term_english INT,
    final_term_chinese INT,
    final_term_math INT,
    final_term_english INT
);

数据爬取

环境准备看课程软件和环境

修改cookie看这！：

'''
Author: xiandaidl 694060435@qq.com
Date: 2024-03-03 20:32:18
LastEditors: xiandaidl 694060435@qq.com
LastEditTime: 2024-03-03 20:58:04
FilePath: \测试\测试.py
Description: 这是网页爬虫请求的典型代码，在实际使用中，请将url,headers,cookies进行替换。
'''
import requests

'''
description: 用于向url请求数据
return {返回json,如果请求失败返回None}
'''
def fetch_data(url,headers,cookies,data):
    try:
        response = requests.post(url, headers=headers, data=data, cookies=cookies)
        response.raise_for_status()
        return response.json()
    except requests.HTTPError as http_err:
        print(f"网页请求出错，错误代码: {http_err}")
    except Exception as err:
        print(f"出现错误: {err}")
    

url = "https://xxxxxx"
headers = {
    "User-Agent": "xxxxxxx",
    }
cookies = {
    "ASP.NET_SessionId": "xxxxxxxxxxxxxxxxxxxx",
    }
data = {
    "key": "value",
    }  

res_json=fetch_data(url,headers,cookies,data)
if res_json!=None:
    print(res_json)

数据处理

取学科的code

    for  i in range(0,len(res_json[0]['examSubData'])):
        if res_json[0]['examSubData'][i]['subName']=="语文":
            yuwen_code=res_json[0]['examSubData'][i]['code']
        if res_json[0]['examSubData'][i]['subName']=="数学":
            shuxue_code=res_json[0]['examSubData'][i]['code']
        if res_json[0]['examSubData'][i]['subName']=="英语":
            yingyu_code=res_json[0]['examSubData'][i]['code']
    code_list=[yuwen_code,shuxue_code,yingyu_code]
    print(code_list)

取语数英成绩

stu_data_list=[]
    for stu in res_json[0]['listData']:
        name=stu['XM']
        yuwen=stu[yuwen_code]
        shuxue=stu[shuxue_code]
        yingyu=stu[yingyu_code]
        stu_data=[class_name,name,yuwen,shuxue,yingyu]
        stu_data_list.append(stu_data)
    print(stu_data_list)

对多个班级进行循环

class_list=["23401","23402","23403"]
for class_name in class_list:
    data = {  
        "active": "initStuScoreInfo",  
        "sAuth": "@05@O@09@O@82@",  
        "EXAMTYPE": "qm",  
        "XNXQBM": "2023101",  
        "BJBH": class_name  
    }

对期中期末进行循环

class_list=['22301','22302','22303','22304','22305','22306']
qzqm_list=['qz','qm']

for class_name in class_list:
  for qzqm in qzqm_list:
    data = {
      "active": "initStuScoreInfo",
      "sAuth": "@05@O@09@O@82@",
      "EXAMTYPE": qzqm,
      "XNXQBM": "2023101",
      "BJBH": class_name
      }

数据存入mysql

import pymysql
'''
description: 连接数据库
return {*} 返回connection和cursor
'''
def get_database_connection():
    connection = pymysql.connect(host='localhost',
                                 user='username',
                                 password='password',
                                 db='dbname',
                                 charset='utf8mb4',
                                 cursorclass=pymysql.cursors.DictCursor)
    return connection, connection.cursor()
'''
description: 执行sql的插入语句
return {*}无返回
'''
def exe_sql(sql, data):
    try:
        cursor.execute(sql, data)
        connection.commit()
        print("执行了:", data)
    except pymysql.MySQLError as e:
        print(f"出错: {e}")

try:
    connection, cursor = get_database_connection()
except pymysql.MySQLError as e:
    print(f"连接数据库时出错: {e}")

# 在此处定义您的 SQL 查询和数据
sql = "INSERT INTO 表名 (列1, 列2) VALUES (%s, %s)"
test_item = ["数值1", "数值2"]

# 执行 SQL 查询
exe_sql(sql, test_item)

connection.close()

阶段代码


import requests
import pymysql

def get_database_connection():
    connection = pymysql.connect(host='192.168.120.128',
                                 user='xd',
                                 password='123',
                                 db='my_db',
                                 charset='utf8mb4',
                                 cursorclass=pymysql.cursors.DictCursor)
    return connection, connection.cursor()
def fetch_data(url,headers,cookies,data):
    try:
        response = requests.post(url, headers=headers, data=data, cookies=cookies)
        response.raise_for_status()
        return response.json()
    except requests.HTTPError as http_err:
        print(f"网页请求出错，错误代码: {http_err}")
    except Exception as err:
        print(f"出现错误: {err}") 
def Processing_data(class_name,qzqm,res_json):
    for  i in range(0,len(res_json[0]['examSubData'])):
        if res_json[0]['examSubData'][i]['subName']=="语文":
            yuwen_code=res_json[0]['examSubData'][i]['code']
        if res_json[0]['examSubData'][i]['subName']=="数学":
            shuxue_code=res_json[0]['examSubData'][i]['code']
        if res_json[0]['examSubData'][i]['subName']=="英语":
            yingyu_code=res_json[0]['examSubData'][i]['code']
    stu_data_list=[]
    for stu in res_json[0]['listData']:
        name=stu['XM']
        yuwen=stu[yuwen_code]
        shuxue=stu[shuxue_code]
        yingyu=stu[yingyu_code]
        if qzqm =="qz":
            stu_data=[class_name,name,'期中',yuwen,shuxue,yingyu]
        else:
            stu_data=[class_name,name,'期末',yuwen,shuxue,yingyu]
        stu_data_list.append(stu_data)
    return stu_data_list
def exe_sql(sql, data):
    try:
        cursor.execute(sql, data)
        connection.commit()
        print("执行了:", data)
    except pymysql.MySQLError as e:
        print(f"出错: {e}")

try:
    connection, cursor = get_database_connection()
except pymysql.MySQLError as e:
    print(f"连接数据库时出错: {e}")
url = "https://xdzx.chneic.sh.cn/XDEAM/Svl_Zbcjcx"
headers ={
        "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:123.0) Gecko/20100101 Firefox/123.0",
        "Accept": "application/json, text/javascript, */*; q=0.01",
        "Accept-Language": "zh-CN,zh;q=0.8,zh-TW;q=0.7,zh-HK;q=0.5,en-US;q=0.3,en;q=0.2",
        "Content-Type": "application/x-www-form-urlencoded; charset=UTF-8",
        "X-Requested-With": "XMLHttpRequest",
        "Sec-Fetch-Dest": "empty",
        "Sec-Fetch-Mode": "cors",
        "Sec-Fetch-Site": "same-origin"
    }

cookies = {
    "ASP.NET_SessionId": "4qp5vzd4ze1t13yhmwnatdwl",
    "JSESSIONID": "DD5BAA6D7A6D2259BA57EBE6133BBDDA"
    }
class_list=["23401","23402"]
qzqm_list=['qz','qm']
for class_name in class_list:
    for qzqm in qzqm_list:
        data = {  
            "active": "initStuScoreInfo",  
            "sAuth": "@05@O@09@O@82@",  
            "EXAMTYPE": qzqm,  
            "XNXQBM": "2023101",  
            "BJBH": class_name  
        }  

        res_json=fetch_data(url,headers,cookies,data)
        if res_json!=None:
            p_data_list=Processing_data(class_name,qzqm,res_json)
            for stu_data in p_data_list:
                print(stu_data)
                sql = "INSERT INTO student_scores (class, name,mid_term_chinese,mid_term_math,mid_term_english ) VALUES (%s,%s,%s,%s,%s)"
                test_item=[stu_data[0],stu_data[1],stu_data[3],stu_data[4],stu_data[5]]
                exe_sql(sql,test_item)



connection.close()

导出数据库

mysqldump -u xd -p my_db student_scores > cj.sql