Цель

Мы хотим сохранить данные о стоимости жизни с веб-сайта Numbeo.com для различных городов по всему миру в базе данных MySQL. Мы достигнем этого, разработав базу данных MySQL для хранения наших данных, а затем, используя Python, будем парсить и загружать данные в базу данных с помощью созданных в MySQL хранимых процедур.

Данные с сайта Numbeo.com являются результатом коллективного размещения и могут часто изменяться в зависимости от количества недавних отправок от пользователей. По этой причине мы автоматизируем наш скрипт на Python для запуска ежедневно с помощью планировщика задач Windows и обновления базы данных с самой свежей информацией с веб-сайта. Предыдущие записи данных не будут перезаписываться. Это позволяет нам отслеживать увеличение и уменьшение различных затрат со временем.

Шаг 1: Построение концептуальной модели базы данных

Наш первый шаг в проектировании базы данных - построение концептуальной модели, которая будет представлять сущности и их взаимосвязи внутри базы данных. Мы будем использовать онлайн-инструмент для моделирования под названием draw.io, чтобы построить диаграмму сущность-связь (ER-диаграмму), которая визуально представит нашу концептуальную модель.

Просмотр данных о стоимости жизни для любого города на сайте Numbeo.com даст нам представление о том, как должна выглядеть наша ER-диаграмма. "Набор стоимости" в этом контексте представляет собой просто снимок всех доступных цен в любое заданное время, для любого заданного города, в любой из выбранных категорий. Категории включают рестораны, рынки, транспорт, одежду, аренду, коммунальные услуги и досуг.

У каждого города может быть много наборов стоимости для каждой категории (поскольку мы не перезаписываем ранее полученные данные во время обновлений). Каждый набор стоимости может быть связан только с одним городом. Это отношение один-ко-многим, как показано на диаграмме.

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

Шаг 2: Построение логической модели базы данных

Логическая модель похожа на концептуальную модель, но содержит дополнительные детали, такие как типы данных и дополнительные сущности, которые могут представлять отношения. Логическая модель, подобно концептуальной модели, не зависит от выбранной СУБД. По этой причине мы используем общие типы данных, такие как float, string и integer, а не специфичные для СУБД типы данных, такие как DECIMAL, VARCHAR и INT.

В нашей логической модели нам нужно добавить способ записи времени, когда данные были спарсены и обновлены. Мы можем сделать это, добавив новую сущность под названием Update с атрибутом даты.

Один город в конечном итоге будет иметь много обновлений, но одно обновление будет влиять только на один город. Это отношение один-ко-многим между City и Update.

Каждому набору стоимостей будет соответствовать только одно обновление, и каждое обновление будет влиять только на один набор стоимостей для каждого города и каждой категории. Все они будут отношениями один-к-одному.

Обновления происходят на уровне набора стоимостей для каждой комбинации города и категории. Например, если мы парсим данные для 100 разных городов и запускаем скрипт на Python один раз в день, мы сгенерируем 700 обновлений в день, потому что есть 7 различных категорий набора стоимостей, которые обновляются для каждого города.

Шаг 3: Создание физической модели базы данных

Наконец, нам нужно создать физическую модель базы данных в MySQL. На этом шаге мы создаем таблицы из сущностей нашей ER-диаграммы, с колонками в качестве атрибутов. Мы также устанавливаем типы данных для каждой колонки. Эти типы данных зависят от конкретной СУБД. Также на этом шаге мы устанавливаем первичные и внешние ключи.

Все таблицы теперь имеют колонки с типами данных, специфичными для СУБД, в данном случае MySQL. Многие таблицы также имеют внешние и первичные ключи, некоторые из которых являются автоинкрементирующимися целыми числами. Названия городов, их страны и регионы теперь имеют тип данных VARCHAR.

Цены установлены как тип данных DECIMAL, с точностью, зависящей от оценки стоимости самого предмета. Масштаб всегда установлен на 2, так как у нас никогда не будет больше 2 чисел после десятичной точки.

Таблица cities имеет первичный ключ с названием city_id, который установлен как автоинкрементирующийся тип данных SMALLINT. city_id также присутствует в таблице updates в качестве внешнего ключа.

Таблица updates также имеет автоинкрементирующийся первичный ключ с названием update_id. update_id служит в качестве первичного внешнего ключа для каждой из таблиц с наборами стоимости. Использование внешнего ключа также в качестве первичного ключа рекомендуется только в случае, если у нас есть однозначное соответствие между таблицами. Это верно для всех отношений между таблицей updates и каждой из таблиц с наборами стоимости.

Ограничения внешнего ключа

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

• **fk_updates_cities- **Это внешний ключ city_id, находящийся в таблице updates- При обновлении установлено значение CASCADE- При удалении установлено значение RESTRICT

Если возникает ситуация, когда пользователь обновляет city_id в таблице cities, то настройка CASCADE также обновит внешний ключ в таблице updates для соответствия. Если пользователь пытается удалить city_id, MySQL не позволит это сделать с помощью настройки RESTRICT.

• fk_{{category}}_cost_sets_updates **Это внешние первичные ключи update_id, находящиеся в таблицах cost_sets- При обновлении установлено значение CASCADE- При удалении установлено значение RESTRICT

Снова, если пользователь пытается изменить update_id в таблице updates, изменение будет отражено во всех первичных внешних ключах в каждой из таблиц cost_sets. Пользователю также будет запрещено удаление update_id в таблице updates. Эти настройки должны быть применены для каждого внешнего первичного ключа в каждой таблице cost_sets.

Нормализация

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

• 1-я нормальная форма - столбцы не могут повторяться, и каждая ячейка может содержать только одно значение
• 2-я нормальная форма - все таблицы должны описывать только одну сущность, и все столбцы в этой таблице должны описывать эту сущность
• 3-я нормальная форма - столбец в таблице не должен быть производным от других столбцов в этой таблице

Наша физическая модель до сих пор соответствует всем требованиям нормализации. Вот файл для физической модели этой базы данных. Вы можете открыть его в MySQL Workbench. Теперь мы можем создать саму базу данных.

Шаг 4: Прямое проектирование базы данных

Теперь самая легкая часть. Когда наша физическая модель готова, мы готовы прямо проектировать базу данных из модели. Откройте модель и перейдите на вкладку "База данных", затем выберите "Прямое проектирование". Следуйте инструкциям, пока база данных не будет создана.

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

Шаг 5: Заполнение таблицы городов

Далее нам нужно выбрать несколько городов с сайта Numbeo.com и добавить их вместе с соответствующими странами в таблицу cities. Мы можем получить список поддерживаемых городов с этой страницы на Numbeo.com. Вот SQL-файл для добавления городов в таблицу городов, с 100 городами со всего мира.

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

Шаг 6: Откройте новый файл Python и импортируйте библиотеки

Теперь, когда наша база данных готова к загрузке данных, мы можем начать писать наш скрипт парсинга веб-страниц на Python.

Мы будем использовать PyCharm для написания нашего скрипта, но любая среда разработки подойдет также. Сначала нам нужно установить библиотеки MySQL Connector и BeautifulSoup. Вы можете сделать это с помощью pip, или если вы используете PyCharm, просто перейдите по следующему пути:

Файл → Настройки → Текущий проект → Интерпретатор Python → Установить (+ иконка)

Затем найдите библиотеку, которую вы хотите установить, выберите ее и нажмите кнопку Установить пакет. Вы увидите сообщение об успешной установке, когда установка будет завершена. Наконец, вам нужно открыть новый файл Python и импортировать необходимые библиотеки, показанные ниже.

import mysql.connector
from bs4 import BeautifulSoup as soup
from urllib.request import urlopen as uReq
import unicodedata
from datetime import datetime

Шаг 7: Подключение к базе данных MySQL в Python

Затем нам нужно указать Python, как подключиться к базе данных MySQL, которую мы создали ранее. Вам потребуется найти несколько кусочков информации из MySQL Workbench.

Некоторую информацию можно найти в окне "Управление подключениями к серверу" в MySQL Workbench. Перейдите на вкладку Server в верхней панели инструментов и нажмите Management Access Settings, чтобы попасть сюда. Затем перейдите на вкладку Connection.

Имя хоста, имя пользователя и пароль должны были быть созданы при настройке локального сервера MySQL.

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

# подключение к базе данных
connection = mysql.connector.connect(
    host='localhost',
    database='numbeo_col',
    user='root',
    password='password'
)

# отображение информации о базе данных
if connection.is_connected():
    db_Info = connection.get_server_info()
    print("Connected to MySQL Server version", db_Info)
    cursor = connection.cursor()
    cursor.execute("select database();")
    record = cursor.fetchone()
    print("You're connected to database:", record, '\n\n')

# создание объекта курсора с помощью метода .cursor()
cursor = connection.cursor()

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

Шаг 8: Получение и обработка списка городов из базы данных

Нам нужно указать Python, как получить доступ к веб-страницам с данными о стоимости жизни для каждого города. Для этого мы будем динамически вставлять названия городов в этот URL для каждого города.

http://www.numbeo.com/cost-of-living/in/{{city}}displayCurrency=USD

Мы можем легко сделать это, выполнив простой запрос select с помощью объекта курсора и затем вызвав функцию .fetchall() для объекта курсора.

Нам нужно использовать функцию .extend() для списка и цикл for, чтобы очистить результат, потому что функция .fetchall() возвращает список городов с набором скобок вокруг каждой строки.

# получение списка городов из базы данных
cursor.execute('SELECT city_name FROM cities')

cities = []
for i in cursor.fetchall():
    cities.extend(i)

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

Например, "Рио-де-Жанейро" должно стать "Рио-Де-Жанейро", что можно легко сделать с помощью генератора списка и функций .title() и .replace() для строк.

# делаем первые буквы заглавными и добавляем дефисы
cities = [city.title().replace(' ', '-') for city in cities]

Кроме того, по какой-то причине URL для Каира, Египет должен включать название страны. Возможно, в базе данных Numbeo есть другой Каир. Чтобы обойти это, нам просто нужно заменить строку в списке для этого города.

# исправляем строку для Каира
cities[0] = 'Каир-Египет'

Шаг 9: Получение HTML-кода с веб-страницы для каждого города

Теперь для каждого города мы скажем Python'у перейти на веб-сайт, проанализировать HTML-код и получить данные, которые нам нужны для каждой из наших таблиц в базе данных. Мы будем использовать библиотеку BeautifulSoup для парсинга HTML-кода и получения данных.

# перебираем веб-страницы для каждого города
for index, city in enumerate(cities):
    req_url = f'https://www.numbeo.com/cost-of-living/in/{city}?displayCurrency=USD'

    # открываем соединение, получаем страницу
    uClient = uReq(req_url)
    page_html = uClient.read()
    uClient.close()

    # парсинг HTML-кода
    page_soup = soup(page_html, 'html.parser')

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

Мы должны использовать функцию enumerate при переборе нашего списка городов. Она возвращает индекс текущего элемента в цикле for, что поможет нам при сохранении значений для столбца city_id в таблице updates позже.

Затем мы вставляем обработанные названия городов в URL-адрес запроса с помощью форматированной строки, получаем HTML-код со веб-страницы и парсим его с помощью BeautifulSoup.

Шаг 10: Найти строки данных в HTML и установить переменные для строк категорий

Теперь нам нужно найти строки данных в разметке. Сначала мы находим все строки с помощью функции .findAll() из библиотеки BeautifulSoup. HTML-элемент <tr> определяет строку ячеек в таблице. Мы вызываем функцию .findAll() для HTML с аргументом tr, чтобы получить список всех элементов строк на веб-странице. Обратите внимание, что все это происходит внутри цикла for для каждого названия города.

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

# получаем строки данных таблицы в виде списка HTML-строк
all_rows_html = page_soup.findAll('tr')

# выбираем строки для каждой категории
restaurant_cost_rows_html = all_rows_html[2:10]
market_cost_rows_html = all_rows_html[11:30]
transportation_cost_rows_html = all_rows_html[31:39]
utilities_cost_rows_html = all_rows_html[40:43]
leisure_cost_rows_html = all_rows_html[44:47]
clothing_cost_rows_html = all_rows_html[51:55]
rent_cost_rows_html = all_rows_html[56:60]

Шаг 11: Напишите функцию, которая извлекает и обрабатывает данные о стоимости

Мы напишем функцию, которая принимает в качестве аргумента HTML-код для каждого элемента строки для каждой категории и возвращает список чисел с плавающей точкой, представляющих стоимость каждого товара в каждой категории.

Каждая строка содержит элемент таблицы данных <td> с атрибутом класса, равным priceValue. Именно так мы будем определять стоимость товаров для каждой строки.

Функция находит текст элемента таблицы данных для каждого HTML-элемента строки в аргументе. Текст элемента данных имеет странную форму, похожую на эту строку: ‘ 535.77\xa0$’, поэтому нам нужно ее очистить перед сохранением. Нам нужны только цифры и десятичная точка.

К сожалению, мы не можем просто вызвать функции .strip() или .replace() для удаления части ‘\xa0’ из строки, потому что она закодирована по-другому. Вместо этого нам нужно привести ее к нормализованной форме NFKC, которая преобразует ее в обычный пробел, а затем удалить его.

Наконец, мы удаляем все запятые, пробелы и знаки валюты, а затем преобразуем данные из строки в число с плавающей точкой. Каждое вновь извлеченное и обработанное значение добавляется в список стоимостей. После того, как цикл for пройдет через все HTML-строки, он возвращает список стоимостей для этой категории.

# извлечение стоимости из каждой строки в каждой категории
def получить_стоимость(html_строки_стоимости):
    """Эта функция извлекает стоимость в виде чисел с плавающей точкой из html-строк для каждой категории"""

    стоимости = []
    for строка in html_строки_стоимости:
        стоимость = строка.find('td', {'class': 'priceValue'}).text
        стоимость = стоимость.replace(u'\xa0', unicodedata.normalize("NFKC", u'\xa0'))
        стоимость = стоимость.replace(',', '')
        стоимость = стоимость.strip(' $')
        стоимость = float(стоимость)
        стоимости.append(стоимость)

    return стоимости

Шаг 12: Получение списков стоимости для каждой категории

Далее мы будем использовать нашу функцию вместе с списками элементов HTML строк, которые мы создали на Шаге 10, чтобы получить данные в виде списков с плавающей точкой для каждой категории. Обратите внимание, что все это происходит в первом цикле for для каждого имени города. Этот процесс выполняется для каждого города.

# получение списков стоимости для каждой категории
restaurant_data = get_costs(restaurant_cost_rows_html)
market_data = get_costs(market_cost_rows_html)
transportation_data = get_costs(transportation_cost_rows_html)
utilities_data = get_costs(utilities_cost_rows_html)
leisure_data = get_costs(leisure_cost_rows_html)
clothing_data = get_costs(clothing_cost_rows_html)
rent_data = get_costs(rent_cost_rows_html)

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

Шаг 13: Хранимые процедуры MySQL добавляют записи в базу данных

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

Важно отметить, что при создании хранимой процедуры для таблицы update поле update_id является автоинкрементным. Это означает, что нам необходимо исключить параметр update_id при определении входных параметров в хранимой процедуре и установить значение DEFAULT в операторе INSERT.

Хранимые процедуры для каждой из таблиц с наборами стоимостей имеют похожий синтаксис. Основное отличие здесь заключается в том, что вместо DEFAULT в качестве первого аргумента нам нужно иметь самый последний update_id. Помните, что первичные ключи для таблиц с наборами стоимостей также являются внешними ключами из столбца update_id в таблице updates. Нам нужно выбрать максимальное значение столбца update_id в качестве первого аргумента.

Синтаксис для остальных хранимых процедур для каждой из таблиц с наборами стоимостей точно такой же, за исключением имен переменных и типов данных. SQL-скрипт, используемый для создания всех хранимых процедур, находится здесь. Просто запустите его после создания таблицы cities.

Шаг 14: Напишите цикл в скрипте на Python для загрузки данных

Вернемся к нашему скрипту на Python и напишем цикл for, который вызывает хранимые процедуры insert_update_data и insert_category_data для списков затрат, которые мы создали на шаге 12.

Сначала нам нужно создать список кортежей, содержащих строки с названиями категорий и списками данных для этих конкретных категорий с шага 12. Затем мы будем перебирать этот список и динамически вставлять имена и списки в аргументы функций .callproc().

# создаем список категорий и их наборов данных для итерации
category_data = [
    ('ресторан', restaurant_data),
    ('рынок', market_data),
    ('транспорт', transportation_data),
    ('коммунальные услуги', utilities_data),
    ('досуг', leisure_data),
    ('одежда', clothing_data),
    ('аренда', rent_data)
]

Помните, что обновления происходят на уровне набора затрат. Нам нужно вызвать хранимую процедуру insert_update_data для каждой таблицы набора затрат для каждого города. Однако нам все еще нужны некоторые данные для этой хранимой процедуры. Она принимает city_id и datetime в качестве аргументов.

Мы можем получить city_id в самом начале первого цикла for, который перебирает наш список городов. Мы использовали функцию enumerate для генерации индекса, который говорит нам, с каким городом мы работаем. Мы просто должны добавить 1 к этой переменной индекса, чтобы получить наш city_id.

# получаем city_id для обновления данных
city_id = index + 1

Теперь, когда у нас есть нужные данные, мы можем, наконец, написать цикл for. Мы устанавливаем переменную datetime каждый раз, когда выполняется обновление, создаем список для наших обновленных данных и вызываем две функции .callproc() на нашем курсоре.

Функция .callproc() из библиотеки MySQL Connector принимает имя хранимой процедуры в виде строки в качестве первого аргумента и список аргументов самой хранимой процедуры в качестве второго аргумента.

Мы можем динамически вставить имя категории из нашего списка кортежей в первый аргумент второго вызова функции .callproc(), чтобы завершить имя функции .callproc() для каждой категории.

# выполняем обновление для каждой категории с использованием хранимых процедур
for category in category_data:
    dt = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    update_data = [city_id, dt]

    cursor.callproc('insert_update_data', update_data)
    cursor.callproc(f'insert_{category[0]}_data', category[1])
connection.commit()

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

Шаг 15: Закрыть соединение с базой данных

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

# закрыть соединение с базой данных
connection.close()

Шаг 16: Автоматизировать скрипт в планировщике задач Windows

Последний шаг - указать планировщику задач Windows запускать скрипт на Python каждый день.

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