2023数据采集与融合技术实践作业2

from bs4 import BeautifulSoup
import urllib.request
import sqlite3

line = "{:^10}\t{:^10}\t{:^12}\t{:^15}\t{:^12}"   # 定义一个格式化字符串，用于输出表格数据。
print(line.format("序号","地区", "日期", "天气信息", "温度"))

class WeatherForecast:  # 定义一个WeatherForecast类
    def __init__(self):  # 初始化方法，设置请求头和城市代码
        self.headers = {
            'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/117.0.0.0 Safari/537.36'}
        self.cityCode={"北京":"101010100","上海":"101020100","广州":"101280101","深圳":"101280601"}

    def forecastCity(self,city):  # 定义forecastCity方法，输入参数为城市名称
        if city not in self.cityCode.keys():  # 判断城市是否在城市代码中
            print(city+" code cannot be found")  # 输出城市代码不存在的提示信息
            return

        url="http://www.weather.com.cn/weather/"+self.cityCode[city]+".shtml"  # 构造请求URL
        try:  # 尝试执行以下代码
            req=urllib.request.Request(url,headers=self.headers)  # 发送带有请求头的HTTP请求
            data=urllib.request.urlopen(req)  # 获取响应数据
            data=data.read()  # 读取响应数据
            soup=BeautifulSoup(data,"lxml")  # 使用BeautifulSoup解析响应数据
            lis=soup.select("ul[class='t clearfix'] li")  # 提取包含天气信息的li标签
            num = 0
            for li in lis:  # 遍历li标签
                try:
                    num += 1
                    date=li.select('h1')[0].text  # 提取日期信息
                    weather=li.select('p[class="wea"]')[0].text  # 提取天气信息
                    temp=li.select('p[class="tem"] span')[0].text+"/"+li.select('p[class="tem"] i')[0].text  # 提取温度信息
                    print(line.format(num,city,date,weather,temp)) # 输出城市、日期、天气和温度信息
                    self.saveToDB(city, date, weather, temp)  # 调用saveToDB方法将信息保存到数据库
                except Exception as err:  # 捕获异常并输出错误信息
                    print(err)
        except Exception as err:  # 捕获异常并输出错误信息
            print(err)

    def saveToDB(self, city, date, weather, temp):  # 定义saveToDB方法，输入参数为城市名称、日期、天气和温度信息
        conn = sqlite3.connect('weathers.db')  # 连接SQLite数据库
        c = conn.cursor()  # 创建游标对象
        c.execute('''CREATE TABLE IF NOT EXISTS forecast
                     (city TEXT, date TEXT, weather TEXT, temp TEXT)''')  # 创建名为forecast的表，如果不存在则创建
        c.execute("INSERT INTO forecast VALUES (?,?,?,?)", (city, date, weather, temp))  # 向表中插入数据
        conn.commit()  # 提交事务
        conn.close()  # 关闭数据库连接

    def process(self,cities):  # 定义process方法，输入参数为城市列表
        for city in cities:  # 遍历城市列表
            self.forecastCity(city)  # 调用forecastCity方法获取并保存天气信息
        print("completed")  # 输出完成信息

ws=WeatherForecast()
# 创建WeatherForecast类的实例
ws.process(["北京","上海","广州","深圳"])  # 调用process方法处理四个城市的天气信息

import requests
import json
import sqlite3


def getdata():
    url = 'http://47.push2.eastmoney.com/api/qt/clist/get?cb=jQuery11240080126732179717_1697270562788&pn=1&pz=20&po=1&np=1&ut=bd1d9ddb04089700cf9c27f6f7426281&fltt=2&invt=2&wbp2u=|0|0|0|web&fid=f3&fs=m:0+t:6,m:0+t:80,m:1+t:2,m:1+t:23,m:0+t:81+s:2048&fields=f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,f12,f13,f14,f15,f16,f17,f18,f20,f21,f23,f24,f25,f22,f11,f62,f128,f136,f115,f152&_=1697270562789'
    headers = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/117.0.0.0 Safari/537.36'
    }
    response = requests.get(url=url, headers=headers)
    content = response.text
    content = content.split('(')[1].split(')')[0]
    json_data = json.loads(content)
    data = json_data['data']['diff']
    return data


def savedb(data):
    conn = sqlite3.connect('沪深京A股.db')
    cursor = conn.cursor()
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS stocks (
        id INTEGER PRIMARY KEY,
        代码 TEXT,
        名称 TEXT,
        最新价 REAL,
        涨跌幅 REAL,
        涨跌额 REAL,
        成交量 REAL,
        成交额 REAL,
        振幅 REAL,
        最高 REAL,
        最低 REAL,
        今收 REAL,
        昨收 REAL
    )
''')
    for obj in data:
        cursor.execute('''
            insert into stocks(
        代码 ,名称 ,最新价 ,涨跌幅 ,涨跌额 ,成交量 ,成交额 ,振幅 ,最高 ,最低 ,今收 ,昨收 
        )
        VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ''', (
            obj['f12'],
            obj['f14'],
            obj['f2'],
            obj['f3'],
            obj['f4'],
            obj['f5'],
            obj['f6'],
            obj['f7'],
            obj['f15'],
            obj['f16'],
            obj['f17'],
            obj['f18']
        )
                       )
    conn.commit()
    cursor.close()
    conn.close()


if __name__ == "__main__":
    data = getdata()
    savedb(data)

import requests
import re
import sqlite3

url = 'https://www.shanghairanking.cn/_nuxt/static/1697106492/rankings/bcur/2021/payload.js'
header = {
    "User-Agent": "Mozilla/5.0 (Windows; U; Windows NT 6.0 x64; en-US; rv:1.9pre) Gecko/2008072421 Minefield/3.0.2pre"
}
response = requests.get(url=url, headers=header)
response.encoding = 'utf-8'
content = response.text
province_list = {
    'k': '江苏', 'n': '山东', 'o': '河南', 'p': '河北', 'q': '北京', 'r': '辽宁', 's': '陕西', 't': '四川', 'u': '广东',
    'v': '湖北', 'w': '湖南', 'x': '浙江', 'y': '安徽', 'z': '江西', 'A': '黑龙江', 'B': '吉林', 'C': '上海',
    'D': '福建', 'E': '山西',
    'F': '云南', 'G': '广西', 'I': '贵州', 'J': '甘肃', 'K': '内蒙古', 'L': '重庆', 'M': '天津', 'N': '新疆', 'Y': '海南'
}

type_list = {
    'f': '综合',
    'e': '理工',
    'h': '师范',
    'm': '农业',
    'T': '林业',
}
names = re.findall('univNameCn:"(.*?)"', content)
scores = re.findall('score:(.*?),', content)
provinces = re.findall('province:(.*?),', content)
untypes = re.findall('univCategory:(.*?),', content)

conn = sqlite3.connect('大学排名.db')
cursor = conn.cursor()
cursor.execute('''
CREATE TABLE IF NOT EXISTS lists(
    name text,
    province text,
    untybe text,
    score real)
    ''')
for name, score, province, untybe in zip(names, scores, provinces, untypes):
    type_name = type_list.get(untybe,'未知类型')
    province_name = province_list.get(province, '未知省份')
    cursor.execute("INSERT INTO lists (name, province, untybe, score) VALUES (?, ?, ?, ?)",
                   (name, province_name, type_name, score))
conn.commit()
cursor.close()
conn.close()