网络抓取可以获取数据，但存储和分析这些数据同样重要。SQL 数据库为您提供了一种结构化的方式来存储大量抓取的数据，以便您可以查询、过滤和分析以获取见解。无论您是跟踪产品价格、监控竞争对手还是收集研究数据，将抓取的数据存储在 SQL 中都有助于保持井然有序和数据可访问。

在本文中，我们将探讨为什么 SQL 是存储抓取数据的理想选择、如何设置数据库以及如何使用 SQL 查询插入和分析数据。我们开始吧！

目录

  1. 为什么要将抓取的数据存储在 SQL 中？
  2. 为抓取的数据设置 SQL 数据库
  • 选择正确的 SQL 数据库
  • 安装 SQL 数据库
  • 创建数据库和表
  • 将 Python 连接到 SQL 数据库
  1. 将抓取的数据插入 SQL 数据库
  • 将数据插入 SQLite
  • 将数据插入MySQL
  • 处理大量数据插入
  1. 使用 SQL 查询分析数据
  • 过滤和排序数据
  • 汇总数据以获取见解
  • 连接表格以进行更深入的分析
  1. 使用 SQL 存储和分析抓取数据的最佳实践
  2. 总结
  3. 常见问题 (FAQ)

为什么要将抓取的数据存储在 SQL 中？

在网页抓取数据时，正确存储数据是轻松访问、组织和分析的关键。SQL 数据库为您提供了一种结构化的方式来高效管理大型数据集，并保持数据的一致性和可扩展性。

为什么要使用 SQL 来抓取数据

  • 结构化存储 – SQL 使用表，以便您可以轻松组织和关联数据。
  • 高效查询 – 使用 SQL 查询，您可以快速过滤、排序和分析数据，无需任何手动操作。
    数据的完整性 – SQL 通过主键和外键等约束使您的数据保持一致。
  • 可扩展性 – 无论是数千条还是数百万条记录，SQL 数据库都能有效扩展。
  • 安全性 – SQL 数据库具有身份验证和加密功能，可保证您的数据安全。

为抓取的数据设置 SQL 数据库

在存储网页抓取数据之前，您需要设置一个 SQL 数据库。无论使用 MySQL、PostgreSQL 还是 SQLite，流程都是创建数据库、定义表并将其连接到 Python 脚本。

1.选择正确的SQL数据库

  • SQLite – 最适合小型项目和本地存储。
  • MySQL – 非常适合 Web 应用程序和大规模数据存储。
  • PostgreSQL的 – 非常适合处理复杂的查询和大型数据集。

2.安装 SQL 数据库

对于 MySQL 或 PostgreSQL，安装数据库并设置用户：

1
2
3
4
5
6
7
8
# 安装 MySQL（Ubuntu）
须藤 apt更新
须藤 apt安装mysql-server
须藤 mysql_secure_installation

# 安装 PostgreSQL（Ubuntu）
须藤 apt更新
须藤 apt 安装 postgresql postgresql-contrib

对于 SQLite，不需要安装，因为它附带在 Python 中。

3.创建数据库和表

安装后，创建一个数据库并定义一个用于抓取数据的表：

对于 MySQL/PostgreSQL：

1
2
3
4
5
6
7
8
9
10
CREATE 数据库抓取的数据；
使用scraped_data；

创建表 产品（
    id INT 主键 自动增量，
    姓名 变量(255),
    车资 DECIMAL(10,2),
    网址文本，
    时间戳 TIMESTAMP DEFAULT CURRENT_TIMESTAMP 时间戳
);

对于 SQLite（使用 Python）：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
进口 sqlite3

conn = sqlite3.connect（“scraped_data.db” 复制代码)
光标 = conn.cursor()

游标.执行（'''
    如果不存在则创建表产品 (
        id 整数主键自动增量，
        名称文本，
        价格真实，
        网址文本，
        时间戳 DATETIME DEFAULT CURRENT_TIMESTAMP
    )
''')

conn.commit()
conn.close()

4.将 Python 连接到 SQL 数据库

要插入抓取的数据，请使用 Python 中的 MySQL Connector、Psycopg2（PostgreSQL）或 SQLite 建立连接。

MySQL 示例：

1
2
3
4
5
6
7
8
9
进口 mysql.连接器

conn = mysql.连接器.connect（
    主机=“本地主机”,
    用户=“你的用户名”,
    密码=“你的密码”,
    数据库=“scraped_data”
)
光标 = conn.cursor()

现在数据库已经准备好了，下一步是将抓取的数据插入 SQL 表中。

将抓取的数据插入 SQL 数据库

设置好 SQL 数据库后，下一步就是插入抓取的数据。使用 Python 和 SQL，您可以存储结构化数据以供进一步分析。
蟒蛇

1.将数据插入SQLite

对于小型项目，SQLite 是一个简单而轻量级的选择。下面是一个将抓取的数据插入 SQLite 数据库的 Python 脚本。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
进口 sqlite3

# 连接到 SQLite 数据库
conn = sqlite3.connect（“scraped_data.db” 复制代码)
光标 = conn.cursor()

# 样本爬取数据
数据 = [
    (“笔记本电脑 A”, 799.99, “https://example.com/laptop-a”),
    (“笔记本电脑 B”, 999.99, “https://example.com/laptop-b”),
]

# 向表中插入数据
光标.executemany('''
    插入产品（名称、价格、网址）值（？，？，？）
'''， 数据）

conn.commit()
conn.close()

打印(“数据插入成功！”)

2.将数据插入MySQL

对于较大的数据集，MySQL 可提供更好的性能。以下是将抓取的数据插入 MySQL 表的方法：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
进口 mysql.连接器

# 连接到 MySQL 数据库
conn = mysql.连接器.connect（
    主机=“本地主机”,
    用户=“你的用户名”,
    密码=“你的密码”,
    数据库=“scraped_data”
)
光标 = conn.cursor()

# 样本爬取数据
数据 = [
    (“笔记本电脑 A”, 799.99, “https://example.com/laptop-a”),
    (“笔记本电脑 B”, 999.99, “https://example.com/laptop-b”),
]

# 将数据插入MySQL
查询 = “插入产品（名称，价格，网址）值（％s，％s，％s）”
cursor.executemany（查询，数据）

conn.commit()
conn.close()

打印(“数据插入成功！”)

3.处理大量数据插入

如果您正在处理大型数据集，批量插入可以通过减少数据库事务的数量来提高性能。

  • 使用 VHDL 语言编写 executemany() 一次插入多行。
  • 使用索引 在经常查询的列上以获得更好的性能。
  • 批量提交数据 而不是每次插入后都插入，以提高效率。

一旦数据存储在 SQL 数据库中，您就可以继续查询和分析它。

使用 SQL 查询分析数据

将抓取的数据存储在 SQL 数据库中后，您可以使用 SQL 查询对其进行分析。SQL 允许您筛选、排序、聚合和连接表，以从数据集中获取见解。

1. 过滤和排序数据

过滤和排序有助于根据特定条件提取相关信息。

  • 使用以下方式过滤数据 WHERE 仅获取所需的记录。
  • 使用以下方式对结果进行排序 ORDER BY 按价格、日期或任何其他列排列它们。

示例：获取价格低于 500 美元的所有产品，按价格升序排序

1
2
3
4
选择 名称、价格、网址
从 产品
在 车资 < 500
订单 BY 车资 ASC;

2. 汇总数据以获取见解

聚合函数如下 COUNT(), SUM(), AVG(), MIN()和 MAX() 帮助总结大型数据集。

  • 查找列出的产品总数 运用 COUNT()
  • 计算平均价格 运用 AVG()
  • 查找最高价和最低价 运用 MAX() 和 MIN()

示例：获取产品总数和平均价格

1
2
选择 COUNT个(*) AS 总产品数， AVG（价格） AS 平均价格
从 产品;

3. 连接表格进行更深入的分析

如果您的数据存储在多个表中，JOIN 语句有助于合并相关数据。

  • 使用 VHDL 语言编写 INNER JOIN 从两个表中获取匹配的记录。
  • 使用 VHDL 语言编写 LEFT JOIN 从一个表中获取所有记录并从另一个表中匹配记录。

示例：从两个表中获取产品名称及其类别名称 (products 和 categories)

1
2
3
选择 产品.名称, 类别.类别名称
从 产品
内蒙古 注册 类别 ON 产品.category_id = 类别.id；

使用 SQL 存储和分析抓取数据的最佳实践

为了确保您的网页抓取和 SQL 数据分析过程高效，遵循最佳实践至关重要。正确的数据库管理有助于维护数据完整性、提高性能并简化未来的查询。

1. 使用结构良好的数据库模式

设计一个清晰的数据库模式，以有效地组织数据。

  • 在单独的表中存储不同的数据类型（例如产品、评论、价格）。
  • 使用主键和外键来维护表之间的关系。

示例：存储产品数据的表结构

1
2
3
4
5
6
7
创建表 产品（
    序列号 主键,
    名称 文字 不是NULL,
    车资 DECIMAL(10,2),
    类别编号 INT,
    scraped_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 时间戳
);

2.优化数据存储和索引

大型数据集可能会降低查询速度。要提高性能，请执行以下操作：

  • 使用索引 在经常搜索的列上。
  • 规范化数据 消除冗余信息。

示例：在价格列上创建索引以便更快地进行搜索

1
CREATE 指数 idx_price ON 产品（价格）；

3. 保持数据清洁且最新

抓取的数据可能有错误或重复。通过以下方式确保准确性：

  • 使用删除重复条目 DISTINCT or DELETE.
  • 定期更新记录以反映最新信息。
    蟒蛇
    示例：根据产品名称删除重复记录
1
2
3
4
删除 从 产品
在 id 不是 IN (
    选择 闵（ID） 从 产品 公司 BY 姓名
);

4. 自动化数据插入和维护

无需手动插入数据，而是使用脚本或存储过程自动执行该过程。

  • 将 Python 与 SQLAlchemy 结合使用 动态插入数据。
  • 安排自动更新 使用 cron 作业或任务调度程序。

示例：使用 Python 和 SQLAlchemy 插入抓取的数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
， 炼金术 进口 create_engine、表、列、整数、字符串、元数据

引擎=创建引擎（“postgresql://用户:密码@localhost/mydatabase”)
元数据 = MetaData()

产品 = 表（
    “产品”、元数据、
    柱子（“ID”，整数，主键=真),
    柱子（“名称”， 细绳），
    柱子（“价钱”，整数）
)

- 引擎.连接() as 连接：
    conn.执行（产品.插入（），{“名称”: “笔记本电脑”, “价钱”: 1200})

5. 保护您的数据库

通过以下方式保护敏感数据并防止未经授权的访问：

  • 限制用户权限 基于角色的访问.
  • 加密数据库连接和存储的数据。

示例：向用户授予只读访问权限

1
授 选择 ON 产品 TO 只读用户；

总结

在 SQL 数据库中存储和分析抓取的数据是将原始数据转化为黄金的关键。通过组织数据、优化查询和遵循最佳实践，您可以快速存储和分析数据。SQL 可以轻松过滤、聚合和连接数据，以便您做出数据驱动的决策。

无论您处理的是小型数据集还是大型网页抓取项目，使用 SQL 都可以确保您的数据结构清晰、干净且可供分析。通过正确的方法，您可以充分发挥抓取数据的潜力并获得有意义的见解。

常见问题 (FAQ)

问：为什么我应该将抓取的数据存储在 SQL 数据库中，而不是 CSV 或 JSON 文件中？

与 CSV 或 JSON 文件相比，SQL 数据库具有更好的组织性、更快的查询速度和可扩展性。它们允许结构化存储、高效的数据检索和使用 SQL 查询的高级分析，因此非常适合大量抓取的数据。

问：如何优化我的 SQL 数据库以进行大规模网页抓取？

要优化 SQL 数据库，请使用索引来加快搜索速度，规范化数据以避免冗余，并使用适当的数据类型。此外，批量插入、定期维护和查询优化将提高性能和存储效率。

问：分析抓取的数据的最佳 SQL 查询是什么？

常见的分析 SQL 查询包括 SELECT 用于检索数据， WHERE 用于过滤， ORDER BY 用于排序， GROUP BY 用于聚合，以及 JOIN 用于组合多个表。这些查询将帮助您快速从抓取的数据中提取有意义的见解。