Веб-скрапинг дает вам данные, но хранение и анализ этих данных так же важны. Базы данных 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 – Идеально подходит для веб-приложений и крупномасштабного хранения данных.
  • PostgreSQL – Отлично подходит для обработки сложных запросов и больших наборов данных.

2. Установка базы данных SQL

Для MySQL или PostgreSQL установите базу данных и настройте пользователя:

1
2
3
4
5
6
7
8
# Установка MySQL (Ubuntu)
Sudo склонное обновление
Sudo установить MySQL-сервер
Sudo mysql_secure_installation

# Установка PostgreSQL (Ubuntu)
Sudo склонное обновление
Sudo apt install postgresql postgresql-contrib

Для SQLite установка не требуется, так как он поставляется вместе с Python.

3. Создание базы данных и таблицы

После установки создайте базу данных и определите таблицу для извлеченных данных:

Для MySQL/PostgreSQL:

1
2
3
4
5
6
7
8
9
10
СОЗДАТЬ БАЗА ДАННЫХ scraped_data;
ИСПОЛЬЗОВАТЬ scraped_data;

СОЗДАТЬ ТАБЛИЦУ товары (
    id INT ПЕРВИЧНЫЙ КЛЮЧ АВТО_ИНКРЕТ,
    имя ВАРЧАР(255),
    цена ДЕСЯТИЧНЫЙ(10,2),
    URL-адрес ТЕКСТА,
    отметка времени ВРЕМЯ 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.подключить("scraped_data.db")
курсор = conn.cursor ()

курсор.выполнить('' 'Окно выдачи
    СОЗДАТЬ ТАБЛИЦУ, ЕСЛИ НЕ СУЩЕСТВУЕТ продукты (
        id INTEGER ПЕРВИЧНЫЙ КЛЮЧ АВТОИНКРЕТ,
        имя ТЕКСТ,
        цена РЕАЛЬНАЯ,
        URL-адрес ТЕКСТА,
        временная метка DATETIME DEFAULT CURRENT_TIMESTAMP
    )
'' 'Окно выдачи)

конн.коммит()
conn.close ()

4. Подключение Python к базе данных SQL

Чтобы вставить извлеченные данные, установите соединение с помощью MySQL Connector, Psycopg2 (PostgreSQL) или SQLite в Python.

Пример для MySQL:

1
2
3
4
5
6
7
8
9
Импортировать mysql.коннектор

conn = mysql.connector.connect(
    host =«Локальный»,
    пользователь ="ваше_имя_пользователя",
    пароль ="ваш_пароль",
    база данных="scraped_data"
)
курсор = conn.cursor ()

Теперь, когда база данных готова, следующим шагом станет вставка извлеченных данных в таблицы SQL.

Вставка извлеченных данных в базу данных SQL

После настройки базы данных SQL следующим шагом будет вставка извлеченных данных. Используя Python и SQL, вы можете хранить структурированные данные для дальнейшего анализа.
питон

1. Вставка данных в SQLite

Для небольших проектов SQLite — простой и легкий вариант. Ниже представлен скрипт Python, который вставляет извлеченные данные в базу данных SQLite.

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.подключить("scraped_data.db")
курсор = conn.cursor ()

# Образец извлеченных данных
данные = [
    ("Ноутбук А", 799.99, "https://example.com/laptop-a"),
    ("Ноутбук Б", 999.99, "https://example.com/laptop-b"),
]

# Вставить данные в таблицу
курсор.executemany('' 'Окно выдачи
    ВСТАВИТЬ В продукты (название, цена, URL) ЗНАЧЕНИЯ (?, ?, ?)
'' 'Окно выдачи, данные)

конн.коммит()
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.connector.connect(
    host =«Локальный»,
    пользователь ="ваше_имя_пользователя",
    пароль ="ваш_пароль",
    база данных="scraped_data"
)
курсор = conn.cursor ()

# Образец извлеченных данных
данные = [
    ("Ноутбук А", 799.99, "https://example.com/laptop-a"),
    ("Ноутбук Б", 999.99, "https://example.com/laptop-b"),
]

# Вставить данные в MySQL
запрос = "ВСТАВИТЬ В продукты (название, цена, URL) ЗНАЧЕНИЯ (%s, %s, %s)"
курсор.executemany(запрос, данные)

конн.коммит()
conn.close ()

Распечатать(«Данные успешно вставлены!»)

3. Обработка больших объемов данных

Если вы имеете дело с большим набором данных, пакетная вставка может повысить производительность за счет сокращения количества транзакций базы данных.

  • Используйте executemany() для вставки нескольких строк одновременно.
  • Использовать индексы по столбцам, которые часто запрашиваются для повышения производительности.
  • Передача данных партиями а не после каждой вставки для повышения эффективности.

После того, как данные сохранены в базе данных SQL, можно приступать к их запросу и анализу.

Анализ данных с помощью SQL-запросов

После того, как ваши извлеченные данные будут сохранены в базе данных SQL, вы сможете проанализировать их с помощью запросов SQL. SQL позволяет вам фильтровать, сортировать, агрегировать и объединять таблицы для получения информации из вашего набора данных.

1. Фильтрация и сортировка данных

Фильтрация и сортировка помогают извлекать релевантную информацию на основе определенных условий.

  • Фильтровать данные с помощью WHERE чтобы получить только необходимые записи.
  • Сортировать результаты с помощью ORDER BY упорядочить их по цене, дате или любому другому столбцу.

Пример: получить все продукты стоимостью ниже 500 долларов США, отсортированные по цене в порядке возрастания.

1
2
3
4
ВЫБОР имя, цена, URL
от продукты
ГДЕ цена < 500
ЗАКАЗАТЬ BY цена ASC;

2. Агрегирование данных для получения аналитических данных

Функции агрегации, такие как COUNT(), SUM(), AVG(), MIN() и MAX() помогают обобщать большие наборы данных.

  • Найдите общее количество перечисленных продуктов через COUNT()
  • Рассчитайте среднюю цену через AVG()
  • Найдите самые высокие и самые низкие цены через MAX() и MIN()

Пример: получить общее количество товаров и среднюю цену

1
2
ВЫБОР СЧИТАТЬ(*) AS общее_количество_продуктов, AVG(цена) AS средняя_цена
от товары;

3. Объединение таблиц для более глубокого анализа

Если ваши данные хранятся в нескольких таблицах, операторы JOIN помогают объединить связанные данные.

  • Используйте INNER JOIN для получения совпадающих записей из обеих таблиц.
  • Используйте LEFT JOIN чтобы получить все записи из одной таблицы и сопоставить записи из другой.

Пример: получить названия продуктов и их категории из двух таблиц (products и categories)

1
2
3
ВЫБОР название_продуктов, название_категорий, название_категории
от продукты
ВНУТРЕННИЙ РЕГИСТРАЦИЯ категории ON products.category_id = категории.id;

Лучшие практики хранения и анализа извлеченных данных в SQL

Чтобы обеспечить эффективность процессов веб-скрейпинга и анализа данных SQL, важно следовать лучшим практикам. Правильное управление базами данных помогает поддерживать целостность данных, повышать производительность и упрощать будущие запросы.

1. Используйте хорошо структурированную схему базы данных

Разработайте понятную схему базы данных, которая эффективно организует данные.

  • Храните различные типы данных (например, продукты, отзывы, цены) в отдельных таблицах.
  • Используйте первичные и внешние ключи для поддержания связей между таблицами.

Пример: Структура таблицы для хранения данных о продуктах

1
2
3
4
5
6
7
СОЗДАТЬ ТАБЛИЦУ товары (
    идентификатор СЕРИЙНЫЙ ПЕРВИЧНЫЙ КЛЮЧ,
    имя ТЕКСТ НЕНУЛЕВОЙ,
    цена ДЕСЯТИЧНЫЙ(10,2),
    ид_категории INT,
    царапанный_в ВРЕМЯ DEFAULT CURRENT_TIMESTAMP
);

2. Оптимизируйте хранение и индексацию данных

Большие наборы данных могут замедлить запросы. Для повышения производительности:

  • Использовать индексы в часто просматриваемых столбцах.
  • Нормализовать данные для устранения избыточной информации.

Пример: создание индекса по столбцу цен для более быстрого поиска

1
СОЗДАТЬ ИНДЕКС idx_price ON продукция(цена);

3. Поддерживайте чистоту и актуальность своих данных

Скопированные данные могут иметь ошибки или дубликаты. Обеспечьте точность:

  • Удаление дубликатов записей с помощью DISTINCT or DELETE.
  • Регулярное обновление записей для отражения последней информации.
    питон
    Пример: удаление дубликатов записей на основе названия продукта
1
2
3
4
УДАЛИТЬ от продукты
ГДЕ id НЕ IN (
    ВЫБОР MIN(Я бы) от продукты GROUP 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")
метаданные = Метаданные()

продукты = Таблица(
    "продукты", метаданные,
    Столбец("мне бы", Целое число, primary_key=Правда),
    Столбец("название", Нить),
    Столбец("цена", Целое число)
)

  engine.connect() as соединение:
    conn.execute(products.insert(), {"название": "Ноутбук", "цена": 1200})

5. Защитите свою базу данных

Защитите конфиденциальные данные и предотвратите несанкционированный доступ с помощью:

  • Ограничение прав пользователя с помощью доступ на основе ролей.
  • Шифрование подключений к базам данных и хранимых данных.

Пример: предоставить пользователю доступ только для чтения

1
ГРАНТ ВЫБОР ON продукты К только_читающий_пользователь;

Заключение

Хранение и анализ извлеченных данных в базах данных SQL — ключ к превращению необработанных данных в золото. Организуя данные, оптимизируя запросы и следуя лучшим практикам, вы можете быстро сохранять и анализировать данные. SQL упрощает фильтрацию, агрегацию и объединение данных, чтобы вы могли принимать решения на основе данных.

Независимо от того, имеете ли вы дело с небольшими наборами данных или масштабными проектами по веб-скрапингу, использование SQL гарантирует, что ваши данные будут структурированы, очищены и готовы к анализу. При правильном подходе вы сможете раскрыть весь потенциал ваших скрапинговых данных и получить значимые идеи.

FAQ

В. Почему мне следует хранить извлеченные данные в базе данных SQL, а не в файле CSV или JSON?

Базы данных SQL предлагают лучшую организацию, более быстрые запросы и масштабируемость, чем файлы CSV или JSON. Они обеспечивают структурированное хранение, эффективный поиск данных и расширенный анализ с использованием запросов SQL, поэтому они идеально подходят для больших объемов извлеченных данных.

В. Как оптимизировать базу данных SQL для крупномасштабного веб-скрапинга?

Чтобы оптимизировать базу данных SQL, используйте индексирование для более быстрого поиска, нормализуйте данные, чтобы избежать избыточности, и используйте правильные типы данных. Кроме того, пакетные вставки, регулярное обслуживание и оптимизация запросов улучшат производительность и эффективность хранения.

В. Какие SQL-запросы лучше всего подходят для анализа извлеченных данных?

Обычные SQL-запросы для анализа: SELECT для извлечения данных, WHERE для фильтрации, ORDER BY для сортировки, GROUP BY для агрегации и JOIN для объединения нескольких таблиц. Эти запросы помогут вам быстро извлечь значимую информацию из ваших извлеченных данных.