### 实现豆瓣Top250网页抓取并保存至SQL数据库 #### 编程环境准备 为了完成此项目，需安装一些必要的Python包。可以通过`pip install requests beautifulsoup4 pymysql pandas openpyxl`命令来安装所需的库。 #### 数据获取流程 通过发送HTTP请求访问目标页面，并解析HTML文档提取所需信息。对于豆瓣Top250榜单而言，每页展示25部影片的信息，共十页[^1]。 ```python import requests from bs4 import BeautifulSoup def fetch_movie_data(url): headers = { "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64)" } response = requests.get(url, headers=headers) soup = BeautifulSoup(response.text, 'html.parser') movies = [] for item in soup.select('.item'): movie_info = {} title = item.find('span', class_='title').get_text() rating_num = float(item.find('span', class_='rating_num').get_text()) movie_info['title'] = title movie_info['rating'] = rating_num movies.append(movie_info) return movies ``` #### 存储结构设计 创建名为`movies_top_250`的数据表用于存储电影名称以及评分等字段。这里采用MySQL作为关系型数据库管理系统[^2]。 ```sql CREATE TABLE IF NOT EXISTS `movies_top_250` ( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255), rating FLOAT ); ``` #### Python连接MySQL数据库 利用PyMySQL模块建立与本地或远程服务器上的MySQL实例之间的连接，执行SQL语句向指定表格内插入新纪录。 ```python import pymysql class MysqlHelper(object): def __init__(self, host='localhost', port=3306, user='', passwd='', db=''): self.conn = pymysql.connect( host=host, port=int(port), user=user, password=passwd, database=db, charset="utf8mb4" ) self.cursor = self.conn.cursor() def insert(self, table_name, data_dict): keys = ', '.join(data_dict.keys()) values = ', '.join(['%s'] * len(data_dict)) sql_query = f'INSERT INTO {table_name} ({keys}) VALUES ({values})' try: self.cursor.execute(sql_query, tuple(data_dict.values())) self.conn.commit() except Exception as e: print(f"Insert failed: {e}") self.conn.rollback() helper = MysqlHelper(host='localhost', port=3306, user='root', passwd='password', db='testdb') for movie in fetched_movies: helper.insert('movies_top_250', movie) ```