HTML to MySQL. Или Веб-скрапинг на практике
Разработка ~7 min.

HTML to MySQL. Или Веб-скрапинг на практике

Один мой хороший знакомый и коллега обратился ко мне за помощью. Он делал для одной компании новый сайт взамен старому. Данные со старого, естественно, должны были переместиться на новый. В этом и заключалась моя задача. Было только одно простое условие – доступа к БД старого сайта ни у меня, ни у него не было.

В этой статье я хочу на практике показать, что можно сделать в этой ситуации, несмотря на отсутствие адекватной разметки в коде страницы.

// Не ждите высокотехнологичных мемуаров. Статья предназначена скорее для новичков среднего уровня, поэтому излишне подробная. Но может быть и разработчики уровня повыше смогут найти для себя интересные подходы к решению описанной проблемы :blush:

Что имеется #

Есть сайт с панелью администратора, доступной по логину-паролю, которые имеются, и около 1500 записей по 20 на страницу.

Также есть доступ к БД через панель PhpMyAdmin. (На крайний случай есть SSH доступ, но это для очень больших объемов данных)

Ну и на этом все.

Работа единоразовая, поэтому писать целую инфраструктуру не требуется.

Инструменты #

Для таких задач мне не раз приходил на выручку NodeJs.

  • Во-первых, я его знаю и знаю достаточно хорошо.
  • Во-вторых, под него есть огромное количество библиотек, в том числе и для работы с HTML разметкой.
  • В-третьих, в случае большого количества данных запросы можно распараллелить и это даст значительный прирост к скорости выполнения скрипта. PHP так же может.
  • Ну и последнее, я уже не первый раз выполнял подобную работу и на бо́льших объемах и могу сказать, что лучше JS ничто не выполнит эту задачу лучше.

Так же в некоторых, в том числе и в этом, случаях приходится писать некоторые скрипты прямо в браузер. А там JS по умолчанию.

Часто используемые библиотеки это:

  • request + request-promise которые в удобном виде умеют делать HTTP запросы и делают это хорошо. Можно было использовать встроенный http модуль, надстройкой которому является эта библиотека, но в request уже из коробки учтены такие мелочи, как использование http или https, обработка редиректов, сбор информации из буферов и стримов и прочее, чем обычно заниматься не хочется совсем.
    В нашем случае она не понадобится, но обычно она незаменимый инструмент.
  • cheerio как удобная замена jQuery для NodeJs с идентичным интерфейсом. То, что доктор прописал. Возможно, кто-то со мной не согласится и скажет, что есть что-то удобнее. И я скорее всего соглашусь. Но я пользовался jQuery еще до того, как NodeJS, NPM и прочие появились. Поэтому я использую его, потому что хорошо знаю и люблю.

Шаг первый. Сбор сырой информации #

Был у меня опыт вытягивания данных из новостной ленты одного сайта. Это были публичные данные, поэтому собрать их не с труда любым способом. И я использовал request.

Тут ситуация немного другая – доступ был запаролен, скорее всего с помощью PHP сессий. Быстрый эксперимент с подстановкой Cookie из браузера не дал положительного результата. Возможно, да и скорее всего, я просто сделал что-то не так. Как бы то ни было я решил пойти другим путем.

Оказалось проще вытянуть данные прямо в пределах браузера. На сайте уже использовался jQuery, так что пол дела уже сделано.

Первая проверка на работоспособность прошла успешно:

await $.get('http://example.ru/user/admin?page=1');
// Да-да. jQuery умеет в Promise

И в консоль было выплюнуто корректное содержание запрошенной страницы. Это ведь то, что нам нужно! Достаточно единожды пройтись по страничкам и собрать всю необходимую информацию:

var results = [];
var pages = 73;
for (let i = 1; i <= pages; i++) {
    results.push(await $.get('http://example.ru/user/admin?page=' + i));
}

И Вуаля! В массиве results лежат исходники всех 73 страниц. Тут можно пойти двумя путями: работать с данными дальше в браузере или сохранить их на комп и обрабатывать средствами NodeJs. Я выбрал второй. Так проще и быстрее.

Поэтому следующая задача – сохранить данные в файл. Сделать это можно несколькими способами. Я выбрал следующий: сформировать данные как строку и загрузить как файл.

Гугление привело на эту страничку в StackOverflow, где был приведен вот этот сниппет:

// Function to download data to a file
function download(data, filename, type) {
    var file = new Blob([data], {type: type});
    if (window.navigator.msSaveOrOpenBlob) // IE10+
        window.navigator.msSaveOrOpenBlob(file, filename);
    else { // Others
        var a = document.createElement("a"),
                url = URL.createObjectURL(file);
        a.href = url;
        a.download = filename;
        document.body.appendChild(a);
        a.click();
        setTimeout(function() {
            document.body.removeChild(a);
            window.URL.revokeObjectURL(url);  
        }, 0); 
    }
}

Не вдаваясь в подробности того, как это работает, скопировал этот код в консоль, и эта строчка:

download(JSON.stringify(results), 'data.json', 'application/json');

предложит скачать все нами загруженные данные в виде обычного json файла.

Сбор сырых данных завершен. Можно приступать к анализу.

Шаг второй. Анализ #

Каждая запись на странице внешне выглядит так:

Пример данных

Данные были намеренно искажены. Джонатан Хамстер – мой хомяк.

А HTML код вот такой:

<div class="item" data-key="1494">
    <hr>
    <div class="mb4 brown">
        <p class="mt0">
            <span class="grey">Имя:</span> Джонатан Хамстер
            <a href="/user/update-client?id=1494" class="ml2 size08 hover-nodecor hover-green">
                <img src="/images/pencil.png" class="mr1 middle">изменить
            </a>
        </p>

        <p class="pull-right size08 grey">Заходил(а): 16.02.2019 17:38</p>

        <p class="mt1"><span class="grey">Тип:</span> Обычный покупатель</p>

        <p class="mt1"><span class="grey">До получения/продления скидки осталось:</span> 9000 руб.</p>

        <p class="mt1"><span class="grey">Email:</span> jonathan4@gmail.com</p>
        
        <p class="mt1"><span class="grey">Телефон:</span> 79507757757</p>
        
        <p class="mt1"><span class="grey">Адрес:</span> 111111 г. Москва, ул. Ленина, 28-123</p>

        <p class="mt1 size08"><span class="grey">Зарегистрирован(а):</span> 16.02.2019 17:12</p>
    </div>
</div>

Выглядит ужасно даже после форматирования, но с этим можно работать.

Если кратко прорезюмировать:

  • Все элементы списка располагаются внутри #centercolumn .list-view. На самом деле все .list-view появляется на странице единожды и нет никакой надобности уточнять его расположение.
  • Каждый элемент списка имеет класс .item. Это важно, потому что внутри .list-view еще есть информация о количестве записей (“Показаны записи 1-20 из 1462”) и пагинация.
  • Каждый кусочек данных располагается внутри элемента p.
  • Каждый тип данных никак не отделяется классом. То есть внутри одинаковых элементов с одинаковыми классами лежат разные данные.
  • Внутри почти каждого параграфа есть элемент с классом .grey, который и определяет тип.
  • Количество данных не консистентно – не во всех записях есть Email и Телефон.
  • Сами данные тоже не имеют какого-то стандарта. Адрес записан в произвольном формате (есть случаи, где в качестве адреса указаны “F”, “SPb” или “………”). Имя тоже не имеет единого формата (ФИО, ИФО, ИФ, ФИ, И, Магазин у “Моста”).
  • И самое главное: почти вся полезная информация лежит в тексте, который расположен именно внутри параграфа. То есть если убрать всех потомков и вытащить текст, мы получим то, что нужно.

Шаг третий. Реализация #

Получение типов данных #

Первое, что нужно выяснить, какие типы данных у нас в принципе имеются во всех записях. Для этого можно вытянуть все p .grey и найти уникальные значения. В современном JS есть замечательный класс Set, который хранит в себе набор уникальных значений. Если добавить то, что уже есть, операция будет проигнорирована.

const cheerio = require('cheerio');
const data = require('./data.json');

const main = async () => {
    let types = new Set();
    
    for (let datum of data) {
        const $ = cheerio.load(datum);

        $('.list-view .item').each((i, item) => {
            $(item).find('p').each((i, item) => {
                let type = $(item).find('.grey').text().trim();

                types.add(type)
            });
        });
    }

    console.log(types);
};

main();

И на выходе:

Set {
  'Имя:',
  '',
  'Тип:',
  'До получения/продления скидки осталось:',
  'Телефон:',
  'Зарегистрирован(а):',
  'Email:',
  'Адрес:' }

То есть у нас есть 7 явных значений и одно “пустое”. Краткий анализ содержания этой строки показал, что кроме Заходил(а) там ничего нет. Пустое значение объясняется тем, что в этом поле нет дочернего элемента .grey. Но нам это никак не мешает.

Получение чистых данных #

Во время анализа мы выяснили, что нужные нам данные лежат в параграфе (p) без потомков.

Так и гуглим: jquery get text without child elements

И получаем вот этот кусочек кода, который делает именно то, что нужно:

$(item)
    .clone()    //clone the element
    .children() //select all the children
    .remove()   //remove all the children
    .end()      //again go back to selected element
    .text();

Помимо этого нужно убрать все лишние переносы и повторяющиеся пробелы, поэтому после .text() добавляем еще 2 вызова:

    .trim()
    .replace(/[\s]+/g, ' ');

И в результате получаем красивую строку без лишнего мусора.

Обработка #

У нас есть 8 типов данных, которые нам нужно по-отдельности проанализировать и привести к значению, которое можно сохранить в БД.

Конечно нужно было это сделать раньше, но на этом этапе особенно важно знать, какие значения будут сохраняться в БД.

После сбора информации выяснилось следующее:

  • Фамилия, Имя и Отчество хранятся в отдельных колонках.
  • Поле “Тип” обычно имеет 2 значения: “Обычный покупатель” и “Постоянный покупатель”, что в БД хранится как флаг (0 или 1 соответственно).
  • Поле “До получения/продления скидки осталось” сохраняется как число без указания валюты. То есть в рублях.
  • Телефон и Email сохраняются как есть.
  • Время регистрации и последнего посещения игнорируются.
  • Адрес в БД разбит на все составляющие, типа страны, города, индекса, улицы и п.р.

Для этого сделаем объект, в котором в качестве ключей будет тип данных, а значениями будут функции, которые обрабатывают пришедшие сырые данных:

const strategies = {
    'Имя:': (content) => {
    },
    '': (content) => {
    },
    'Тип:': (content) => {
    },
    'До получения/продления скидки осталось:': (content) => {
    },
    'Телефон:': (content) => {
    },
    'Зарегистрирован(а):': (content) => {
    },
    'Email:': (content) => {
    },
    'Адрес:': (content) => {
    },
};

Так как каждый тип данных может возвращать несколько значений для БД, то каждая функция должна возвращать объект, который будет объединен с остальными данными. Если функция не вернет ничего, то ничего не будем делать.

let strategy = strategies[type];

// Несмотря на то, что мы описали все стратегии обработки типов, 
// cлучаи "а вдруг" все равно стоит предусматривать
if (!strategy) {
    console.log(text);

    return;
}

let result = strategy(text) || {};
// Про переменную person я пока ничего не говорил, 
// но в ней будут храниться все данные конкретного пользователя
person = {
    ...person,
    ...result,
};

В итоге остается только описать стратегии обработки всех данных поочередно проверяя их формат. Несмотря на то, что мы немного почистили данные, им все равно нельзя доверять.

Вот что я выяснил на этом этапе:

  • В графе с именем всего 3 части, разделенные пробелом. Всего частей может быть от 1 до 3. В одном случае ФИО было разделено 2мя пробелами между частями. Поэтому очистка повторяющихся пробелов была очень кстати.
  • Поле “Тип” в большинстве своем имеет одно их 2-х значений: “Обычный покупатель” и “Постоянный покупатель”. Но нашлись 3 записи формата “Представитель из Название_города”. Их решено пометить как “Постоянных покупателей”.
  • Поле “До получения/продления скидки осталось” всегда имеет формат [цифры][пробел]руб., что упростило задачу.
  • В поле с адресом буквально располагался всякий мусор. С этим уже ничего нельзя было сделать, поэтому решено все затолкать в одну колонку без какой-либо дополнительной обработки.

В итоге у нас получается вот такая коллекция со стратегиями:

Карта со стратегиями
const strategies = {
    'Имя:': (content) => {
        let parts = content.split(' ');

        let name = '', surname = '', middlename = '';
        switch (parts.length) {
            case 1:
                name = parts[0];
                break;
            case 2:
                [name, surname] = parts;
                break;
            case 3:
                [name, middlename, surname] = parts;
                break;

            default:
                console.log(parts);
        }

        return {name, middlename, surname};
    },
    'Тип:': (content) => {
        let regular_customer = 1;
        if (content === 'Обычный покупатель') {
            regular_customer = 0;
        }

        return {regular_customer};
    },
    'До получения/продления скидки осталось:': (content) => {
        let parts = content.split(' ');

        if (parts.length !== 2 && parts[1] !== 'руб.') {
            console.log(parts);
        }

        return {
            tmp_sum: parseInt(parts[0]),
        }
    },
    'Телефон:': (phone) => ({phone}),
    'Email:': (email) => ({email}),
    'Адрес:': (address) => ({address}),
    'Зарегистрирован(а):': (content) => {
    },
    '': (content) => {
    },
};

А сам код теперь выглядит так:

Логика
const main = async () => {
    let people = [];

    for (let datum of data) {
        const $ = cheerio.load(datum);

        $('.list-view .item').each((i, item) => {
            let person = {};

            $(item).find('p').each((i, item) => {
                let type = $(item).find('.grey').text().trim();

                let text = $(item)
                    .clone()
                    .children()
                    .remove()
                    .end()
                    .text()
                    .trim()
                    .replace(/[\s]+/g, ' ');

                let strategy = strategies[type];
                if (!strategy) {
                    console.log(text);

                    return;
                }

                let result = strategy(text) || {};
                person = {
                    ...person,
                    ...result,
                };
            });

            people.push(person);
        });
    }

    console.log(people);
};

main();

Тут как раз можно увидеть появление массива people наполненный объектами person.

Приведение к единому формату #

Теперь у нас есть почти все, чтобы идти дальше. Данные получены и обработаны.

Но наша цель – положить все это в БД.

Каждая стратегия описывалась таким образом, чтобы возвращенный объект был в виде, где ключ – колонка в таблице, а значение – ее содержание.

На выходе мы получили массив с информацией о каждом пользователе. Но эта информация не однородна: у кого-то может не быть адреса, у кого-то – телефона или email-a.

Поэтому нужно сделать некоторые атрибуты со значениями по умолчанию. Берем все обязательные поля из таблицы и проставляем их в объект person с указанными стандартными значениям:

let person = {
    email: '',
    password: 'trash123',
    name: '',
    middlename: '',
    surname: '',
    phone: '',
    country: 'Россия',
    zip: '',
    region: '',
    city: '',
    address: '',
    tmp_sum: 0,
    regular_customer: 0,
    status: 1,
};

Так как пароля мы узнать не можем, а в базе хранится только его хеш, поэтому вставляем туда любой мусор, который не является валидным хешем.

Таким образом на выходе у нас будет массив объектов, каждый из которых имеет одинаковую структуру, порядок атрибутов и заполненные обязательные поля.

Формирование файла для импорта в БД #

Тут решений тоже несколько.

Ранее я пользовался Query Builder’ом, который на выходе давал мне сформированные SQL запросы в файлах, которые достаточно было просто импортировать через PMA, либо запустить прямо на сервере. Были сложности с экранированием, но в большинстве своем он меня устраивал.

В этот раз я пошел другим путем. PMA вполне успешно умеет работать с различными форматами, включая CSV.

Этот формат по сути подразумевает, что горизонтально твои данные будут разделены запятыми, а вертикально – переносами:

"1","Виталий","Заславский"
"2","Василий","Пупкин"
"3","Петр","Васечкин"

Все достаточно просто и соответствовало тому, что мы делаем.

Создание контента для CVS укладывается в 4 строчки:

let result = people.map((person) => Object.values(person) // Получаем только значения из всех объектов
    .map(_ => _.replace ? _.replace(/"/g, '""') : _) // Экранируем двойные кавычки " => "" (таков CSV, не я это придумал)
    .map(_ => `"${_}"`) // Обрамление содержимого двойными кавычками
    .join(',') // Объединение значений
).join('\n'); // Объединение всех полученных значений переносами

console.log(result);

Результатом проделанных действий будет выплюнутое в консоль содержание CSV файла. С сохранением этого через fs модуль я не стал. Вот эта команда сделала все за меня:

node index.js > result.csv

Соберем все воедино #

Вот так будет выглядеть скрипт, если собрать все его части:

Большой кусок кода
const cheerio = require('cheerio');
const data = require('./data.json');

const strategies = {
    'Имя:': (content) => {
        let parts = content.split(' ');

        let name = '', surname = '', middlename = '';
        switch (parts.length) {
            case 1:
                name = parts[0];
                break;
            case 2:
                [name, surname] = parts;
                break;
            case 3:
                [name, middlename, surname] = parts;
                break;

            default:
                console.log(parts);
        }

        return {name, middlename, surname};
    },
    'Тип:': (content) => {
        let regular_customer = 1;
        if (content === 'Обычный покупатель') {
            regular_customer = 0;
        }

        return {regular_customer};
    },
    'До получения/продления скидки осталось:': (content) => {
        let parts = content.split(' ');

        if (parts.length !== 2 && parts[1] !== 'руб.') {
            console.log(parts);
        }

        return {
            tmp_sum: parseInt(parts[0]),
        }
    },
    'Телефон:': (phone) => ({phone}),
    'Email:': (email) => ({email}),
    'Адрес:': (address) => ({address}),
    'Зарегистрирован(а):': (content) => {
    },
    '': (content) => {
    },
};

const main = async () => {
    let people = [];

    for (let datum of data) {
        const $ = cheerio.load(datum);

        $('.list-view .item').each((i, item) => {
            let person = {
                email: '',
                password: 'trash123',
                name: '',
                middlename: '',
                surname: '',
                phone: '',
                country: 'Россия',
                zip: '',
                region: '',
                city: '',
                address: '',
                tmp_sum: 0,
                regular_customer: 0,
                status: 1,
            };

            $(item).find('p').each((i, item) => {
                let type = $(item).find('.grey').text().trim();

                let text = $(item)
                    .clone()
                    .children()
                    .remove()
                    .end()
                    .text()
                    .trim()
                    .replace(/[\s]+/g, ' ');

                let strategy = strategies[type];
                if (!strategy) {
                    console.log(text);

                    return;
                }

                let result = strategy(text) || {};
                person = {
                    ...person,
                    ...result,
                };
            });

            people.push(person);
        });
    }


    let result = people.map((person) => Object.values(person)
        .map(_ => _.replace ? _.replace(/"/g, '""') : _)
        .map(_ => `"${_}"`)
        .join(',')
    ).join('\n');

    console.log(result);
};

main();

Не думаю, что эта простыня нуждается в каком-либо дополнительном пояснении.

Шаг четвертый. Наполнение таблицы #

Это самый простой шаг. Все действия будут происходить через PMA панель.

  • Нужно перейти в нужную таблицу.
  • Ткнуть в “Импорт”.
  • Выбрать нужный файл.
  • Формат указать “CSV” (“CSV using LOAD DATA” не пробовал).
  • В “Названия столбцов” указать нужные столбцы через запятую.

По поводу последнего шага. Несмотря на то, что мы почти скопировали поля из таблицы в указанном порядке, есть случаи, когда данные указываются автоматически самой БД. В моем случае это было поле id. Поэтому при импорте достаточно указать в этом поле все атрибуты из объекта person через запятую:

email,password,name,middlename,surname,phone,country,zip,region,city,address,tmp_sum,regular_customer,status

Жмем “Вперёд” и Вуоля! Работа завершена!

Что можно было сделать еще #

Моей задачей было сделать то, что хотел заказчик. Его уcтраивало то, что в поле с адресом лежит мусор, а на месте фамилии может стоять отчество.

И подручными средствами решить эту проблему было не столько сложно, сколько невозможно в короткий срок.

Но если с этой проблемой столкнулся я, значит были и другие. А значит есть инструменты, которые решают подобного рода задачи.

Адрес можно было привести к единому виду с помощью API Яндекс.Карт. Единственное, что он не умеет – определять номер квартиры из адреса.

Либо можно было воспользоваться сервисом Dadata, который и квартиру в адресе определит, и ФИО из любого формата к красивому приведет.

То есть решения были, только заказчику этого было не нужно :grin:

В качестве заключения #

Статья получилась невероятно большой для сравнительно небольшого объема информации. Но содержательной.

То, что я сейчас описал, называется “Веб-скрапинг”. В моем случае это просто перенос данных с одного сайта на другой путем вывода информации прямо со страниц.

В других случаях веб-скрапинг используется для сбора информации из нескольких сайтов для агрегирования информации в одном месте. Например, афиши кинотеатров. Еще этот подход используется для того, чтобы сделать API там, где его официально нет, и т.д.

Да, я знаю и слышал, что есть инструменты более мощные и серьезные. Но в моем случае гораздо быстрее воспользоваться тем, что я уже хорошо знаю.

А мораль сей статьи такова: автоматизируйте все, что автоматизируется. Используйте все подручные средства, чтобы компьютер сделал за вас бо́льшую часть работы. Не делайте руками то, что можно сделать скриптом на 150 строк. В определенный момент вы поймете – это окупается :wink:

Веб-разработка, Базы данных
« Previous
Next »
Подписаться
Telegram
VK
LinkedIn
GitHub
RSS