Python爬虫与MySQL完美结合：从环境搭建到实战优化

🔸 Windows和Linux下MySQL环境搭建

Windows下安装MySQL

首先，在Windows系统下安装MySQL。下载MySQL安装包并按照以下步骤进行安装：

1. 双击安装包，选择"Custom"安装类型。
2. 选择MySQL Server，Workbench等组件，点击Next。
3. 设置Root密码并创建用户。
4. 完成安装。

Linux下安装MySQL

在Linux系统下，通过包管理器安装MySQL：

sudo apt-get update
sudo apt-get install mysql-server
sudo systemctl start mysql
sudo systemctl enable mysql

🔹 安装完成后，使用以下命令进行安全配置：

sudo mysql_secure_installation

🔸 MySQL表结构设计

设计一个适合爬虫存储数据的MySQL表结构，例如存储文章数据的表：

CREATE TABLE articles (id INT AUTO_INCREMENT PRIMARY KEY,title VARCHAR(255) NOT NULL,author VARCHAR(100),content TEXT,url VARCHAR(255) UNIQUE,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

🔹 这个表结构包含文章的ID、标题、作者、内容、URL和创建时间，能够满足大多数爬虫存储文章数据的需求。

🔸 MySQL动态传参

在Python中使用动态参数传递插入数据到MySQL表中：

import pymysql# 连接到数据库
connection = pymysql.connect(host='localhost',user='yourusername',password='yourpassword',database='yourdatabase'
)try:with connection.cursor() as cursor:# 动态传参插入数据sql = "INSERT INTO articles (title, author, content, url) VALUES (%s, %s, %s, %s)"data = ("文章标题", "作者名", "文章内容", "http://example.com/article")cursor.execute(sql, data)connection.commit()
finally:connection.close()

🔹 通过这种方式，可以灵活地将爬取到的数据插入到MySQL表中。

🔸 MySQL写入规范

为了确保数据一致性和安全性，建议在插入数据时使用事务处理和异常捕获：

import pymysql# 连接到数据库
connection = pymysql.connect(host='localhost',user='yourusername',password='yourpassword',database='yourdatabase'
)try:with connection.cursor() as cursor:# 开始事务connection.begin()# 插入数据sql = "INSERT INTO articles (title, author, content, url) VALUES (%s, %s, %s, %s)"data = ("文章标题", "作者名", "文章内容", "http://example.com/article")cursor.execute(sql, data)# 提交事务connection.commit()
except Exception as e:# 回滚事务connection.rollback()print(f"Error: {e}")
finally:connection.close()

🔹 通过事务处理和异常捕获，可以确保数据的一致性和安全性。

🔸 MySQL对接爬虫实战

将以上知识结合起来，构建一个简单的爬虫，并将爬取到的数据存储到MySQL中：

import requests
from bs4 import BeautifulSoup
import pymysql# 爬取网页数据
url = 'http://example.com/articles'
response = requests.get(url)
html_content = response.content
soup = BeautifulSoup(html_content, 'html.parser')# 连接到数据库
connection = pymysql.connect(host='localhost',user='yourusername',password='yourpassword',database='yourdatabase'
)try:with connection.cursor() as cursor:# 开始事务connection.begin()# 解析并插入数据articles = soup.find_all('div', class_='article')for article in articles:title = article.find('h1').textauthor = article.find('span', class_='author').textcontent = article.find('p', class_='content').texturl = article.find('a')['href']sql = "INSERT INTO articles (title, author, content, url) VALUES (%s, %s, %s, %s)"data = (title, author, content, url)cursor.execute(sql, data)# 提交事务connection.commit()
except Exception as e:# 回滚事务connection.rollback()print(f"Error: {e}")
finally:connection.close()

🔹 在这个示例中，我们爬取网页中的文章数据，并将其插入到MySQL表中，实现了爬虫和数据库的完美对接。

🔸 MySQL优化

为了提升MySQL的性能，可以考虑以下优化措施：

1. 索引优化：为常用的查询字段添加索引，例如文章表中的title和url字段。

   CREATE INDEX idx_title ON articles(title);
   CREATE UNIQUE INDEX idx_url ON articles(url);
   

2. 批量插入：一次性插入多条记录，减少事务提交次数。

   sql = "INSERT INTO articles (title, author, content, url) VALUES (%s, %s, %s, %s)"
   data = [("标题1", "作者1", "内容1", "http://example.com/1"),("标题2", "作者2", "内容2", "http://example.com/2"),# 更多数据...
   ]
   cursor.executemany(sql, data)
   connection.commit()
   

3. 查询优化：使用适当的查询语句，避免全表扫描。

   SELECT * FROM articles WHERE title LIKE 'Python%' LIMIT 10;
   

🔹 通过这些优化措施，可以显著提升MySQL的性能和查询效率。

🔸 总结

🔹 通过本次学习，我们掌握了在Windows和Linux系统下安装MySQL，设计适合爬虫存储数据的表结构，使用动态参数传递数据，并通过事务处理和异常捕获确保数据的一致性和安全性。此外，还结合实际爬虫示例展示了如何将爬取到的数据存储到MySQL中，并进行了MySQL性能优化。