{ "cells": [ { "cell_type": "code", "execution_count": 1, "id": "c28539c1", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Looking in indexes: https://pypi.tuna.tsinghua.edu.cn/simple\n", "Requirement already satisfied: pandas in c:\\programdata\\anaconda3\\lib\\site-packages (1.4.2)\n", "Requirement already satisfied: pymysql in c:\\programdata\\anaconda3\\lib\\site-packages (1.1.2)\n", "Requirement already satisfied: sqlalchemy in c:\\programdata\\anaconda3\\lib\\site-packages (1.4.32)\n", "Requirement already satisfied: python-dateutil>=2.8.1 in c:\\programdata\\anaconda3\\lib\\site-packages (from pandas) (2.8.2)\n", "Requirement already satisfied: numpy>=1.18.5 in c:\\programdata\\anaconda3\\lib\\site-packages (from pandas) (1.21.5)\n", "Requirement already satisfied: pytz>=2020.1 in c:\\programdata\\anaconda3\\lib\\site-packages (from pandas) (2021.3)\n", "Requirement already satisfied: greenlet!=0.4.17 in c:\\programdata\\anaconda3\\lib\\site-packages (from sqlalchemy) (1.1.1)\n", "Requirement already satisfied: six>=1.5 in c:\\programdata\\anaconda3\\lib\\site-packages (from python-dateutil>=2.8.1->pandas) (1.16.0)\n" ] } ], "source": [ "!pip install pandas pymysql sqlalchemy -i https://pypi.tuna.tsinghua.edu.cn/simple" ] }, { "cell_type": "code", "execution_count": 2, "id": "a84238c5", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "✅ MySQL数据库连接成功！字符集已修复支持中文\n" ] } ], "source": [ "import pandas as pd\n", "from sqlalchemy import create_engine\n", "\n", "# --------------------------\n", "# 你的MySQL配置（不用改，保持你自己的）\n", "# --------------------------\n", "MYSQL_USER = \"root\" \n", "MYSQL_PASSWORD = \"123456\" \n", "MYSQL_HOST = \"localhost\" \n", "MYSQL_PORT = \"3306\" \n", "MYSQL_DB = \"test_db\" \n", "\n", "# --------------------------\n", "# ✅ 修复：强制指定中文字符集 utf8mb4（关键代码）\n", "# --------------------------\n", "engine = create_engine(\n", " f\"mysql+pymysql://{MYSQL_USER}:{MYSQL_PASSWORD}@{MYSQL_HOST}:{MYSQL_PORT}/{MYSQL_DB}?charset=utf8mb4\",\n", " # 追加字符集初始化，彻底解决中文乱码/写入失败\n", " connect_args={\"init_command\": \"SET NAMES utf8mb4\"}\n", ")\n", "\n", "# 测试连接\n", "try:\n", " with engine.connect():\n", " print(\"✅ MySQL数据库连接成功！字符集已修复支持中文\")\n", "except Exception as e:\n", " print(f\"❌ 连接失败：{e}\")" ] }, { "cell_type": "code", "execution_count": 5, "id": "aaacd259", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "转换的效果是 课程编号 课程名称 学分 授课教师 上课时间\n", "0 CS001 Python数据分析 4 张老师 周一/周三 第1-2节\n", "1 CS002 数据库原理 3 李老师 周二/周四 第3-4节\n", "2 MA001 高等数学 5 王老师 周一/周五 第5-6节\n", "3 EN001 大学英语 2 刘老师 周三 第7-8节\n", "✅ 任务3.1完成：手动数据已写入MySQL表 course_info_manual\n" ] } ], "source": [ "# --------------------------\n", "# 1. 手动构造课程数据（示例）\n", "# --------------------------\n", "data = {\n", " \"课程编号\": [\"CS001\", \"CS002\", \"MA001\", \"EN001\"],\n", " \"课程名称\": [\"Python数据分析\", \"数据库原理\", \"高等数学\", \"大学英语\"],\n", " \"学分\": [4, 3, 5, 2],\n", " \"授课教师\": [\"张老师\", \"李老师\", \"王老师\", \"刘老师\"],\n", " \"上课时间\": [\"周一/周三 第1-2节\", \"周二/周四 第3-4节\", \"周一/周五 第5-6节\", \"周三 第7-8节\"]\n", "}\n", "\n", "# 转为DataFrame\n", "df_manual = pd.DataFrame(data)\n", "print(f'转换的效果是{df_manual}')\n", "# --------------------------\n", "# 2. 写入MySQL数据库\n", "# --------------------------\n", "# 参数说明：\n", "# name：要写入的表名（如果不存在会自动创建）\n", "# con：数据库连接引擎\n", "# if_exists：处理表已存在的情况\n", "# - \"replace\"：删除原表，重建新表写入\n", "# - \"append\"：在原表末尾追加数据\n", "# - \"fail\"：如果表存在就报错\n", "# index=False：不把pandas的索引写入数据库\n", "\n", "df_manual.to_sql(\n", " name=\"course_info_manual\", # 自定义表名\n", " con=engine,\n", " if_exists=\"replace\",\n", " index=False\n", ")\n", "\n", "print(\"✅ 任务3.1完成：手动数据已写入MySQL表 course_info_manual\")" ] }, { "cell_type": "code", "execution_count": 6, "id": "880e12e6", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "从MySQL读取的课程表数据：\n", " 课程编号 课程名称 学分 授课教师 上课时间\n", "0 CS001 Python数据分析 4 张老师 周一/周三 第1-2节\n", "1 CS002 数据库原理 3 李老师 周二/周四 第3-4节\n", "2 MA001 高等数学 5 王老师 周一/周五 第5-6节\n", "3 EN001 大学英语 2 刘老师 周三 第7-8节\n" ] } ], "source": [ "df_read = pd.read_sql(\n", " sql=\"SELECT * FROM course_info_manual\", # 读取整张表\n", " con=engine\n", ")\n", "# 打印读取到的数据\n", "print(\"从MySQL读取的课程表数据：\")\n", "print(df_read)" ] }, { "cell_type": "code", "execution_count": 7, "id": "0cda03d8", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "✅ 数据整理完成，预览如下：\n", " 日期/星期 天气状况 最高温度 最低温度 风力\n", "0 今天\\n04/27 今天 23 16 \n", "1 周二\\n04/28 周二 17 15 \n", "2 周三\\n04/29 周三 22 14 \n", "3 周四\\n04/30 周四 26 16 \n", "4 周五\\n05/01 周五 29 17 \n", "✅ 数据已成功写入MySQL表：jiangjin_weather_15d\n", "\n", "🔍 数据库中查询到的数据：\n", "('今天\\n04/27', '今天', 23, 16, '')\n", "('周二\\n04/28', '周二', 17, 15, '')\n", "('周三\\n04/29', '周三', 22, 14, '')\n", "('周四\\n04/30', '周四', 26, 16, '')\n", "('周五\\n05/01', '周五', 29, 17, '')\n", "('周六\\n05/02', '周六', 23, 17, '')\n", "('周日\\n05/03', '周日', 22, 17, '')\n", "('周一\\n05/04', '周一', 20, 17, '')\n", "('周二\\n05/05', '周二', 23, 17, '')\n", "('周三\\n05/06', '周三', 26, 20, '')\n", "('周四\\n05/07', '周四', 21, 18, '')\n", "('周五\\n05/08', '周五', 21, 17, '')\n", "('周六\\n05/09', '周六', 26, 19, '')\n", "('周日\\n05/10', '周日', 22, 19, '')\n", "('周一\\n05/11', '周一', 30, 22, '')\n" ] } ], "source": [ "import requests\n", "from bs4 import BeautifulSoup\n", "import pandas as pd\n", "from sqlalchemy import create_engine\n", "\n", "# -------------------------- 1. 配置信息 --------------------------\n", "# 数据库配置（修改为你自己的数据库信息）\n", "DB_CONFIG = {\n", " \"host\": \"localhost\",\n", " \"user\": \"root\", # 你的数据库用户名\n", " \"password\": \"123456\", # 你的数据库密码\n", " \"database\": \"test_db\", # 你的数据库名（提前创建好）\n", " \"port\": 3306,\n", " \"charset\": \"utf8\"\n", "}\n", "\n", "URL = \"http://sq.src.weather.com.cn/mweather15d/101040500.shtml\"\n", "HEADERS = {\n", " \"User-Agent\": \"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/124.0.0.0 Safari/537.36\"\n", "}\n", "\n", "# -------------------------- 2. 爬取网页数据 --------------------------\n", "def fetch_weather_data():\n", " # 发送请求获取网页\n", " response = requests.get(URL, headers=HEADERS, timeout=10)\n", " response.encoding = \"utf-8\" # 解决中文乱码\n", " soup = BeautifulSoup(response.text, \"html.parser\")\n", "\n", " # 定位天气数据列表\n", " weather_items = soup.find(\"ul\", class_=\"list-ul\").find_all(\"li\", class_=\"h15li\")\n", " data = []\n", "\n", " for item in weather_items:\n", " # 提取日期/星期（如：今天04/27、周二04/28）\n", " date_week = item.find(\"div\", class_=\"h15listdaybox h15k\").text.strip()\n", " \n", " # 提取天气状况（如：小雨、多云转阴）\n", " weather_status = item.find(\"div\", class_=\"h15k\").find(\"p\").text.strip()\n", " \n", " # 提取温度（格式：23/16℃ → 分割为最高/最低温）\n", " temp_text = item.find(\"div\", class_=\"h15listtem h15k\").text.strip()\n", " high_temp, low_temp = temp_text.replace(\"℃\", \"\").split(\"/\")\n", " \n", " # 提取风力（当前页面未找到明确风力字段，先留空，可自行扩展）\n", " wind_power = \"\"\n", " \n", " data.append([date_week, weather_status, int(high_temp), int(low_temp), wind_power])\n", " \n", " return data\n", "\n", "# -------------------------- 3. pandas整理数据 --------------------------\n", "def process_with_pandas(data):\n", " df = pd.DataFrame(\n", " data,\n", " columns=[\"日期/星期\", \"天气状况\", \"最高温度\", \"最低温度\", \"风力\"]\n", " )\n", " print(\"✅ 数据整理完成，预览如下：\")\n", " print(df.head())\n", " return df\n", "\n", "# -------------------------- 4. 写入MySQL数据库 --------------------------\n", "def write_to_mysql(df):\n", " # 创建数据库连接引擎\n", " engine = create_engine(\n", " f\"mysql+pymysql://{DB_CONFIG['user']}:{DB_CONFIG['password']}@{DB_CONFIG['host']}:{DB_CONFIG['port']}/{DB_CONFIG['database']}?charset={DB_CONFIG['charset']}\"\n", " )\n", " \n", " # 写入数据库（if_exists='replace'表示表存在则覆盖，也可改为'append'追加）\n", " df.to_sql(\n", " name=\"jiangjin_weather_15d\",\n", " con=engine,\n", " if_exists=\"replace\",\n", " index=False\n", " )\n", " print(\"✅ 数据已成功写入MySQL表：jiangjin_weather_15d\")\n", "\n", "# -------------------------- 5. 验证数据（可选） --------------------------\n", "def verify_data():\n", " import pymysql\n", " conn = pymysql.connect(**DB_CONFIG)\n", " cursor = conn.cursor()\n", " cursor.execute(\"SELECT * FROM jiangjin_weather_15d;\")\n", " result = cursor.fetchall()\n", " print(\"\\n🔍 数据库中查询到的数据：\")\n", " for row in result:\n", " print(row)\n", " conn.close()\n", "\n", "if __name__ == \"__main__\":\n", " # 执行流程\n", " raw_data = fetch_weather_data()\n", " weather_df = process_with_pandas(raw_data)\n", " write_to_mysql(weather_df)\n", " verify_data()" ] }, { "cell_type": "code", "execution_count": 9, "id": "847fe763", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "✅ 数据整理完成，预览如下：\n", " 日期/星期 天气状况 最高温度 最低温度 风力\n", "28日（明天） 小雨 17 15 <3级\n", "29日（后天） 多云转阴 22 14 <3级\n", "30日（周四） 阴 26 16 <3级\n", " 1日（周五） 小雨 29 17 <3级\n", " 2日（周六） 阴 23 17 <3级\n", " 3日（周日） 小雨 22 17 <3级\n", "\n", "✅ 数据已成功写入MySQL表：jiangjin_weather_7d\n", "\n", "🔍 数据库中查询到的数据：\n", "('28日（明天）', '小雨', 17, 15, '<3级')\n", "('29日（后天）', '多云转阴', 22, 14, '<3级')\n", "('30日（周四）', '阴', 26, 16, '<3级')\n", "('1日（周五）', '小雨', 29, 17, '<3级')\n", "('2日（周六）', '阴', 23, 17, '<3级')\n", "('3日（周日）', '小雨', 22, 17, '<3级')\n" ] } ], "source": [ "import requests\n", "from bs4 import BeautifulSoup\n", "import pandas as pd\n", "from sqlalchemy import create_engine\n", "\n", "# ===================== 1. 配置信息（修改为你自己的） =====================\n", "DB_CONFIG = {\n", " \"host\": \"localhost\",\n", " \"user\": \"root\", \n", " \"password\": \"123456\", \n", " \"database\": \"test_db\", \n", " \"port\": 3306,\n", " \"charset\": \"utf8\"\n", "}\n", "\n", "URL = \"https://weather.com.cn/weather/101040500.shtml\"\n", "HEADERS = {\n", " \"User-Agent\": \"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/124.0.0.0 Safari/537.36\"\n", "}\n", "\n", "# ===================== 2. 爬取并解析网页数据（适配当前结构） =====================\n", "def fetch_weather_data():\n", " response = requests.get(URL, headers=HEADERS, timeout=10)\n", " response.encoding = \"utf-8\"\n", " soup = BeautifulSoup(response.text, \"html.parser\")\n", "\n", " # 定位所有天气数据的
  • 标签\n", " weather_items = soup.find_all(\"li\", class_=\"sky skyid lv3\")\n", " if not weather_items:\n", " raise Exception(\"未找到天气数据，请检查网页是否加载完整或URL是否正确\")\n", " \n", " data = []\n", " for item in weather_items:\n", " # 1. 日期/星期（

    标签）\n", " date_week = item.find(\"h1\").text.strip() if item.find(\"h1\") else \"\"\n", " \n", " # 2. 天气状况（

    标签）\n", " weather_tag = item.find(\"p\", class_=\"wea\")\n", " weather = weather_tag.text.strip() if weather_tag else \"\"\n", " \n", " # 3. 最高温度（标签）\n", " high_temp_tag = item.find(\"span\")\n", " high_temp = int(high_temp_tag.text.strip()) if high_temp_tag else None\n", " \n", " # 4. 最低温度（标签，需要去掉℃）\n", " low_temp_tag = item.find(\"i\")\n", " if low_temp_tag:\n", " low_temp_text = low_temp_tag.text.strip().replace(\"℃\", \"\")\n", " low_temp = int(low_temp_text) if low_temp_text.isdigit() else None\n", " else:\n", " low_temp = None\n", " \n", " # 5. 风力（

    标签）\n", " wind_tag = item.find(\"p\", class_=\"win\")\n", " wind = wind_tag.text.strip() if wind_tag else \"\"\n", "\n", " data.append([date_week, weather, high_temp, low_temp, wind])\n", " \n", " return data\n", "\n", "# ===================== 3. 用Pandas整理数据 =====================\n", "def process_with_pandas(data):\n", " df = pd.DataFrame(\n", " data,\n", " columns=[\"日期/星期\", \"天气状况\", \"最高温度\", \"最低温度\", \"风力\"]\n", " )\n", " print(\"✅ 数据整理完成，预览如下：\")\n", " print(df.to_string(index=False))\n", " return df\n", "\n", "# ===================== 4. 写入MySQL数据库 =====================\n", "def write_to_mysql(df):\n", " engine = create_engine(\n", " f\"mysql+pymysql://{DB_CONFIG['user']}:{DB_CONFIG['password']}@{DB_CONFIG['host']}:{DB_CONFIG['port']}/{DB_CONFIG['database']}?charset={DB_CONFIG['charset']}\"\n", " )\n", " df.to_sql(\n", " name=\"jiangjin_weather_7d\",\n", " con=engine,\n", " if_exists=\"replace\",\n", " index=False\n", " )\n", " print(\"\\n✅ 数据已成功写入MySQL表：jiangjin_weather_7d\")\n", "\n", "# ===================== 5. 验证数据（可选） =====================\n", "def verify_data():\n", " import pymysql\n", " conn = pymysql.connect(**DB_CONFIG)\n", " cursor = conn.cursor()\n", " cursor.execute(\"SELECT * FROM jiangjin_weather_7d;\")\n", " result = cursor.fetchall()\n", " print(\"\\n🔍 数据库中查询到的数据：\")\n", " for row in result:\n", " print(row)\n", " conn.close()\n", "\n", "if __name__ == \"__main__\":\n", " raw_data = fetch_weather_data()\n", " weather_df = process_with_pandas(raw_data)\n", " write_to_mysql(weather_df)\n", " verify_data()" ] }, { "cell_type": "code", "execution_count": null, "id": "5b25df7f", "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": null, "id": "81554b7f", "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": null, "id": "757c0aea", "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": null, "id": "8e162d74", "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": null, "id": "19a58651", "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": null, "id": "ff3b39b2", "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": null, "id": "38e0b103", "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.9.12" } }, "nbformat": 4, "nbformat_minor": 5 }