Допустим, вашей компании необходимо получить данные HTML-таблицы с разных веб-страниц для поддержания аналитических процессов и соблюдения основных требований к отслеживанию данных. Значит вам нужно программное решение для загрузки содержимого HTML-таблицы в таблицы SQL Server, реализуемое быстро, легко и недорого.

Веб-скрейпинг - это мощный инструмент для сбора данных из Интернета для таблиц SQL Server. HTML-таблицы широко используются на веб-сайтах из-за их гибкости для хранения и организации данных. Теги HTML-таблицы содержат набор тегов строк, каждый из которых содержит набор тегов ячеек. Каждая ячейка в наборе строк может содержать текст, ссылки, изображения и другое помеченное содержимое. Вы должны учитывать это разнообразие при разработке стратегий извлечения данных для таблиц HTML. Кроме того, на одной веб-странице может легко быть несколько таблиц HTML. Поэтому перед началом извлечения и загрузки данных из таблицы HTML в веб-странице на веб-сайте может потребоваться выбрать там определенную таблицу.

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

На веб-сайте NASDAQ также доступны биржевые котировки для других крупных бирж, таких как биржи NYSE и AMEX. Эти дополнительные ресурсы позволяют вам легко расширить подход, продемонстрированный здесь для акций, торгуемых на других биржах, отличных от NASDAQ. Кроме того, тот же общий подход к загрузке данных тикера из таблиц HTML в Интернете широко применим к веб-сайтам, содержащим данные для разных типов доменов, например, от членов конгресса до заключенных в тюрьмах. Перед разделом «Следующие шаги» в этом совете перечислены некоторые общие рекомендации по загрузке данных с любой веб-страницы на SQL Server.

Начало работы с веб-скрейпингом с помощью Python для SQL Server

Существует три подготовительных этапа для скрейпинга веб-страниц через Python с таблицами HTML для таблиц SQL Server.

• Установите  Python.
• Настройте SQL Server для безопасного запуска сценариев Python и импорта данных в SQL Server.
• Установите и загрузите внешнюю библиотеку BeautifulSoup для Python; эта библиотека значительно улучшает вашу способность легко извлекать данные с веб-страниц.

Программное обеспечение Python и связанные с ним внешние библиотеки доступны для загрузки из Интернета  БЕСПЛАТНО. Это значительно удешевит весь процесс.

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

Кроме того, Python является исключительно надежным и простым языком программирования. Это одна из причин того, что популярность Python быстро растет среди разработчиков, а также того, что Microsoft предпочла разрешить версии SQL Server 2016 и 2017 для запуска программ Python. Этот совет показывает, как запускать программы Python из любой версии SQL Server, которая может запускать расширенную хранимую процедуру xp_cmdshell , которая есть еще в SQL Server 2000.

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

Визуальный осмотр источников HTML-таблицы для веб-скрейпинга

Как указано в разделе «Решение», этот совет показывает, как загрузить выбранный контент из таблицы HTML на веб-сайте NASDAQ. Заголовок и первые несколько строк таблицы HTML отображаются в следующем скриншоте.

• Обратите внимание, что браузер ссылается на страницу companies-by-industry.aspx с веб-сайта www.nasdaq.com; поле адреса в браузере также включает значение параметра NASDAQ, которое передается через параметр обмена на веб-сайт. Значение параметра указывает запрос данных только для компаний, перечисленных на бирже NASDAQ.
• В таблице в окне браузера отображается строка заголовка столбца, за которой следуют пары строк для каждого значения тикеров
• Прямо над строкой заголовка находится краткое сообщение (отображается 1-50 из 3271 результатов), означающее, что

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

• Данные о трех компаниях отображаются в окне браузера в верхней части таблицы HTML.

• значения тикера: PIH, TURN и FLWS; они отображаются в столбце Symbol таблицы HTML.
• соответствующие названия компаний для трех значений тикера: 1347 Property Insurance Holdings, Inc., 180 Degree Capital Corp. и 1-800 FLOWERS.COM, Inc .; имена компаний отображаются в первом столбце таблицы HTML.
• другие значения столбцов, отображаемые в таблице HTML, включают в себя: Market Cap, Country, IPO year, и Subsector, который иногда называют финансовой отчетностью в отрасли.

• Ниже каждой строки с биржевыми сводками и названием компании находится вторая строка со ссылками для получения дополнительной информации о котировках акций и рейтингах сообщества, а также ссылка на отчет об акциях для компании.
• ссылки на эти три темы обозначаются как короткий описательный текст, такой как PIH Stock Quote; изображение появляется непосредственно перед каждой ссылкой.
• при нажатии на короткий описательный текст открывается связанная страница. Пользователи могут щелкнуть значок назад браузера, чтобы вернуться с новой связанной страницы в таблицу HTML с тикерами и соответствующими данными по отдельности.

  Следующий скриншот отображает нижнюю часть той же страницы

  • Размещение строк  в нижней части страницы совпадает с верхней частью страницы.
  • Текст ниже пары строк для пятидесятого тикера указывает, что n / a используется для IPO Year, когда значение года меньше 1970.
  • Ниже текстового сообщения находится раскрывающийся список.
  • Пользователь, просматривающий страницу, может изменить количество акций  на странице с 50 на другое число, например 100, 150 или 200 акций на страницу.
  • Создание выбора может изменить адресную строку браузера

- с  http://www.nasdaq.com/screening/companies-by-industry.aspx?exchange=NASDAQ

- на http://www.nasdaq.com/screening/companies-by-industry.aspx?exchange=NASDAQ&pagesize=100

• Вы также можете вручную изменить параметр pagesize  в поле адреса браузера на очень большое число. Например, число 4000 в настоящее время достаточно велико, чтобы браузер отображал все значения котировок NASDAQ в одном окне.  Если вы хотите показать все символы тикера, независимо от того, какое их количество активно, не будет никаких проблем. даже если это число больше, чем максимальное количество биржевых тикеров.  Отображение данных компании для всех значений тикера в одном окне браузера упрощает извлечение программных данных, поскольку вы можете загружать данные только с одной страницы, а не с нескольких.

Программный контроль таблицы HTML для веб-скрейпинга

На следующем скриншоте показан простой сценарий Python для получения всех значений строк из HTML, рассмотренных в предыдущем разделе.

• Скрипт начинается со ссылки на внешнюю библиотеку BeautifulSoup. Хотя BeautifulSoup не является обязательным для этого простого скрипта, достаточно полезно иметь его в качестве инструмента для обработки  HTML-страниц. Ссылка на внешнюю библиотеку BeautifulSoup потребуется в следующем скрипте.
• Затем скрипт ссылается на внутреннюю библиотеку запросов Python.
• Библиотека Requests работает по методу GET, который позволяет вам указать URL-адрес и вернуть HTML-содержимое URL-адреса в виде текста, который может выводиться командой печати Python.

Обратите внимание, что аргумент метода GET включает параметр pagesize со значением 4000. Это значение более чем достаточно для извлечения всех значений кода NASDAQ и связанных с ним данных компании с веб-сайта обмена NASDAQ.

HTML-страница, возвращаемая методом GET, имеет несколько разных тегов таблицы. Следующий скриншот блокнота   Notepad ++ показывает выборку з верхней части тега таблицы для таблицы, рассмотренной в браузере в предыдущем разделе. Выборка была скопирована из выходного приложения IDLE, которое автоматически настраивается с установками Python.  Результаты были скопированы в Notepad ++, потому что здесь их было легче изучить, чем в IDLE.

• Тег таблицы имеет параметр, указывающий значение id для таблицы, а именно id = «CompanylistResults». Веб-страница содержит несколько пар открывающих и закрывающих табличных тегов, но только одна из этих пар имеет значение id CompanylistResults.
• Исходный HTML блок в тэге таблицы  CompanylistResults - пара тэга  <thead>

- Между открывающими и закрывающими тегами <thead> расположен один ряд с открывающими и закрывающими тэгами <tr>

- Внутри блока  <tr> находятся семь пар пар для каждого из семи столбцов в таблице HTML.

- Вот  последовательные имена столбцов:

• Name - для названия компании
• Symbol - для значения тикера
• Market Cap - для общей рыночной капитализации компании
• ADR TSO - для общей суммы акций ADR; это значение применяется только к выбранным не-американским акциям, - например, из Китая
• Country - страна, для которой указаны акции неамериканские компании могут иметь свои акции ADR, торгуемые на биржах США, а также допустимые к обмену в их родной стране
• IPO Year обозначает год, когда акции компании первоначально предлагались для продажи на бирже
• Subsector - категория, обозначающая отрасль, в которой ведет основую деятельность компания, предлагающая акции.

- За исключением значений столбца ADO TSO, все столбцы таблицы HTML предназначены для отображения в браузере. Хотя столбец TSO ADR не предназначен для отображения в браузере, его значение возвращается методом GET t библиотеки Python Requests.

• Этот исходный отрывок из таблицы CompanylistResults завершает эту прокомментированную строку текста: <!-- begin data rows -->

Следующая выдержка из блока таблицы  CompanylistResults показывает первые два ряда размеченных данных для первого тикера - один со значением PIH.

• Этот скриншот начинается с того места, где заканчивается предыдущий  первая пронумерованная строка текста: <!-- begin data rows -->
• В этом блоке есть две пары тэгов <tr>

- Одна пара tr-тегов предназначена для первых семи столбцов данных, представленных в таблице HTML; эти первые семь столбцов соответствуют парам тегов <th> в разделе thead таблицы HTML

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

• Последние данные котировок акций
• Рейтинги сообщества от посетителей сайта (если они есть) для акций
• Выбранные факты об акциях, включая краткое описание компании, фондовые  и финансовые показатели

Каждый фонд в таблице HTML, как и фонд для тикера PIH, имеет две строки в таблице HTML.

Программный список строк таблицы для HTML таблицы

Следующий сценарий - это код Python для перечисления строк и ячеек таблицы данных CompanylistResults в нашем исходном URL-адресе. Код взят из файла с именами всех nasdaq компаний с beautifulsoup.py. Два параметра в конце url для аргумента функции GET определяют определенный тип тикеров (NASDAQ) и их максимальное количество (4000), обрабатывающее скрипт. Например, если бы было выделено только 3272 титров NASDAQ, когда значения тикера были извлечены, информация о запасах была бы извлечена только для 3272 акций, хотя для параметра pagesize установлено значение 4000.

Строки комментариев начинаются с знака #. Этот пример кода Python запускается аналогично примеру проверки страницы. То есть, он извлекает все текстовые значения со страницы и сохраняет их в переменной Python (данные). Однако вместо немедленной печати содержимого страницы после этого код передает текстовые значения в библиотеку BeautifulSoup для разбора с его синтаксическим анализатором html; библиотека BeautifulSoup также имеет XML-парсер.

Следующая строка кода извлекает и сохраняет HTML для таблицы с идентификационным значением CompanylistResults. Эта таблица содержит все тикеры NASDAQ вместе со связанной информацией, например, название компании и рыночную капитализацию.

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

Две функции очищают вывод из кода Python.

• Функция get_text извлекает только текстовое значение из HTML внутри ячейки.
• Затем функция strip удаляет ведущие и конечные пробелы из текстового значения в каждой ячейке. Если в ячейке нет текста (как это часто бывает для ячеек столбца TSO ADR), тогда функция возвращает строковое значение нулевой длины.

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

• Каждый фонд имеет свои значения, представленные выходом в четырнадцать строк.
• Вторая строка каждого набора из четырнадцати строк для фонда обозначает значение тикера.
• Если нет выпущенных акций ADR, четвертая строка представляет собой пустое строковое значение.
• Первые три строки с пятую по седьмую строки выделены для:

• 1 - company name
• 2 - ticker
• 3 - market cap
• 5 - country
• 6- ipo year
• 7 - industry

• Восьмая строка - для текста первой из трех ссылок во второй строке для акций. Текст для восьмой строки имеет свои ведущие и конечные пробелы, удаленные функцией  strip. Однако функция  strip применима только к первой из трех ссылок во второй строке для фонда.
• Во вторых двух ссылках на второй строке для фонда не удалены их ведущие и конечные пробелы. Как следствие, текст для каждой из вторых двух ссылок во второй строке происходит по трем строкам каждый - две пустые строки, за которыми следует третья строка с ведущими пробелами перед тикером.
• Поэтому каждый фонд в таблице HTML имеет свои значения, представленные четырьмя строками вывода скрипта Python, который состоит из:

• 8 строк с удаленными ведущими и завершающими пробелами
• 3 строки для первой из двух финальных ссылок
• 3 строки для второй из двух финальных ссылок

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

Чтение результатов синтаксического анализа Python для заполнения таблицы SQL Server

Теперь, когда все фондовые индексы NASDAQ и связанные с ними данные компании извлекаются с помощью программы Python, мы можем сфокусировать наше внимание на переносе результатов в таблицу SQL Server. Этот перенос предназначен для всех биржевых котировок NASDAQ с сайта NASDAQ с момента запуска переноса. Кроме того, процесс передачи вывода программы Python в таблицу SQL Server активно применяется, когда вам нужно скопировать все или часть содержимого из таблицы HTML в таблицу SQL Server.

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

Чтобы внести ясность в эти специфичные переносы  результатов из HTML-таблицы, разобьем обработку на три отдельные шага:

1. На первом этапе осуществляется необработанный вывод из командного файла Windows, который запускает программу Python, показывающую значения ячеек таблицы, и добавляет порядковый номер для облегчения последующих шагов. Каждая строка получает число от 1 для первой строки до количества строк для последней строки, возвращаемой программой Python.
2. Как показано в предыдущем разделе, каждый тикер возвращается как часть набора из 14 строк вывода для запаса из программы Python. Некоторые строки для запаса пустые, а другие результаты не представляют интереса, поскольку они относятся к небольшому образцу тикеров (ADR TSO). Когда вы загружаете данные из таблицы HTML на веб-сайт, вам могут не понадобиться все исходные столбцы. Наш второй этап обработки выполняет три функции.

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

3. Третий шаг предполагает дополнительную обработку, включая вращение результатов таким образом, что окончательное отображение вывода Python в таблице SQL Server показывает каждый биржевой тикер и связанные с ним идентификаторы фонда в одной строке, а не список строк с одной строкой на каждое значение идентификатора запаса. Вращаемый формат является гораздо более простым способом для изучения и проверки результатов, так как таблица SQL Server отображает значения в том же формате, что и HTML-таблица.

Пакетный файл Windows для запуска программы Python имеет имя всех компаний nasdaq с beautifulsoup.bat. В файле есть две команды.

• Первая команда изменяет каталог на путь C: \ Program Files \ Python36 \. Здесь устанавливается интерпретатор Python.
• Вторая команда вызывает интерпретатор Python (python.exe) для запуска скрипта Python, который считывает таблицу HTML CompanylistResults с веб-сайта NASDAQ. Эта команда возвращает все выходные данные программы Python в командное окно Windows.

Далее появится сегмент сценария T-SQL для вызова всех nasdaq-компаний с файлом beautifulsoup.bat. Этот сегмент кода доступен либо из первой, либо второй, либо третьей обработки веб-скрейпингом файлов  results.sql для этого совета.

• Сегмент сценария начинается с настройки SQL Server для запуска расширенной хранимой процедуры xp_cmdshell. Именно эта расширенная хранимая процедура позволяет SQL Server запускать пакетные файлы Windows из SSMS.
• Затем создается временная таблица с именем #Result для хранения строк вывода из всех nasdaq-компаний с файлом beautifulsoup.bat.
• Затем оператор insert с вложенным оператором exec для расширенной хранимой процедуры xp_cmdshell заполняет таблицу #Result выходом из командного файла Windows.
• Следующий шаг в сценарии перенастраивает SQL Server для отключения расширенной хранимой процедуры xp_cmdshell. Этот шаг - защита кибербезопасности от угроз потенциальных злоумышленников.
• Последняя строка в следующем сегменте скрипта должна отображать необработанный вывод. Этот вывод не является абсолютно необходимым, но его обзор поможет вам понять процесс заполнения таблицы SQL Server из таблицы HTML в контексте нашего примера биржевого тикера.

Первые тридцать две строки из таблицы #Results в предыдущем отрывке отображаются на скриншоте, расположенном ниже.

• Первые четыре строки не выводятся из программы Python. Вместо этого строки 2 и 4 предназначены для команд во всех nasdaq-компаниях с файлом beautifulsoup.bat.

• Вторая строка изменяет каталог на c: \ Program Files \ Python36 \ - это адрес расположения приложения Python.
• Четвертая строка вызывает интерпретатор Python для программы Python (все компании nasdaq с beautifulsoup.py).
• Первая и третья строки - это строки Windows, которые возвращаются как значения NULL через расширенную хранимую процедуру xp_cmdshell.

• Следующие 14 строк предназначены для акций с тикером PIH из таблицы HTML списка CompanylistResults. Эти строки соответствуют значениям из первых четырнадцати строк на скриншоте в предыдущем  Программном листинге, в котором перечислены строки таблицы для раздела таблицы HTML.
• Последний набор из 14 строк предназначен для акций с тикером TURN из таблицы CompanylistResults. Эти строки соответствуют второму набору из четырнадцати строк на снимке экрана в предыдущем Программном листинге строк таблицы для раздела таблицы HTML.

Последняя строка в таблице #Result, как и первая и третья строки, содержит значения, отображающие значения NULL в SQL Server. Они предназначены для строк, которые не нужно преобразовывать из значений таблицы HTML в значения таблиц SQL Server.

Первым этапом обработки вывода всех компаний nasdaq с файлом beautifulsoup.bat является назначение порядковых номеров для строк. Оно необходимо, чтобы сохранить исходный порядок строк в виде вывода командного файла Windows и программы Python, вызванной командным файлом Windows. Для этого есть несколько причин.

• Во-первых, вам нужно отдельно обрабатывать первые четыре строки и последнюю строку из вывода в таблице #Result, потому что эти строки не содержат строк таблицы HTML для сопоставления экземпляру SQL Server таблицы.
• Во-вторых, вы должны иметь возможность хранить строки вывода в том порядке, в котором они изначально генерируются из программы Python. Напомним, что каждый запас имеет блок из 14 строк в выводе программы Python. Кроме того, существует установленный порядок появления типов значений, таких как тикер и название компании, в блоке из 14 строк. Присвоение порядковых номеров из объекта последовательности обеспечивает основу для отслеживания 14-строчных блоков для отдельных запасов и порядка разных типов значений в блоке из 14 строк.

Следующий фрагмент T-SQL демонстрирует, как этот шаг выполняется с объектом последовательности и новой временной таблицей с именем #Result_with_to_delete_row_number.

• Исходный блок кода создает новый экземпляр объекта последовательности с именем CountBy1_to_delete_row_number.
• Следующий сегмент кода создает новую копию таблицы #Result_with_to_delete_row_number. В этой таблице содержатся исходные строки #Result с новым набором порядковых номеров в столбце с именем source_row_number. Следующее значение для функции заполняет порядковые номера в столбце source_row_number.
• Сегмент кода завершается оператором select, который отображает строки таблицы #Result_with_to_delete_row_number, упорядоченной по source_row_number.

Следующий скриншот показывает первые 32 строки из таблицы #Result_with_to_delete_row_number. Он нужен лишь для того, чтобы вы убедились, что порядковые номера сохраняют исходный порядок строк из таблицы #Result.

Второй этап обработки использует значения source_row_number, созданные на первом этапе обработки, для выполнения целей второго этапа обработки. Он реализуется как сценарий T-SQL, который впоследствии повторно используется в качестве подзапроса на третьем этапе обработки.  Код на втором этапе обработки выбирает подмножество столбцов в исходной таблице HTML, а также группирует значения ячеек суррогатным значением (TICKER_NUMBER) для значения тикера. Кроме того, второй этап обработки удаляет первые четыре строки и последнюю строку из таблицы #Result_with_to_delete_row_number.

Вот сценарий для второго этапа обработки. Этот сегмент кода доступен из первой, второй или третьей обработки веб-скрейпинга файлов results.sql

• Оператор declare заполняет локальную переменную @max_source_row_number с максимальным значением source_row_number из таблицы #Result_with_to_delete_row_number. Эта локальная переменная указывает на последнюю строку в таблице.
• Второй этап обработки состоит из подзапроса внутри внешнего запроса с условием order by; формулировка подзапроса для легкого включения в третий этап обработки.
• Условие where внутри подзапроса имеет три критериальных выражения

- Первые два выражения критериев должны исключать строки, которые не соответствуют строкам таблицы HTML; выражения достигают этой цели, исключая

• строку с source_row_number, равным @max_source_row_number
• строки с значениями source_row_number от 1 до 4

- Третье выражение критериев выбирает шесть идентификаторов столбцов для каждого фонда

• столбцы обозначаются индексом для позиции, начинающейся со значения 0 для первого столбца и заканчивающейся значением 6 для последнего включенного столбца
• значение индекса позиции (row_number_within_ticker) вычисляется на основе модульной функции (%) с делителем 14; эта функция возвращает значения от 0 до 6 для первых семи значений столбцов для первой строки таблицы HTML запаса; содержимое из второй строки таблицы HTML фонда намеренно исключается, не программируя какой-либо код для извлечения содержимого второй строки
• четвертый столбец из исходной таблицы HTML с индексом позиции 3, не включен в столбцы таблицы SQL Server; этот столбец предназначен для значения TSO ADR из исходной таблицы HTML, которая относится к небольшому подмножеству строк в общей таблице HTML

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

• Первые шесть строк относятся к запасу с тикером PIH, и все эти строки имеют значение TICKER_NUMBER, равное 0.
• Вторые шесть строк для запаса с тикером TURN, и все эти строки имеют значение TICKER_NUMBER 1.
• Значение TICKER_NUMBER является результатом деления значения целочисленного типа данных на второе целочисленное значение типа данных

- Первое целочисленное значение, которое нужно разделить, имеет это выражение (source_row_number-5) в предыдущем скрипте

- Второе целочисленное значение - это число 14

• Типы данных SQL Server возвращают целочисленное значение типа данных, когда одно целое делится на другое целое число; возвращаемое значение - это  коэффициент, усеченный до целого целого числа 

Третий этап обработки включает две операции в двух отдельных запросах.

• Первый запрос использует подзапрос на втором этапе, а также заменяет значения индекса позиции, обозначая столбцы фактическими именами столбцов.
• Второй запрос выполняет вывод из первого запроса, так что набор результатов отображается как ненормированная таблица с отдельной строкой для каждого значения тикера. В каждой строке указывается тикер и его идентификаторы соответствия, такие как название компании и рыночная капитализация.
• Сегменты кода для третьего этапа обработки доступны из третьей обработки веб-скрейпинга файла results.sql для этого совета. Этот файл также содержит код для первых двух этапов обработки.

Следующий сегмент кода T-SQL предназначен для переопределения значений индекса позиции (row_number_within_ticker) для значений имени столбца (ticker_ids).

• После объявления и заполнения локальной переменной @max_source_row_number сегмент начинается с отбрасывания таблицы #pivotsource, если она уже существует. Эта таблица заполняется ключевым словом в следующем запросе.
• Запрос заполняет три столбца в своем результирующем наборе.

- Первое значение столбца - это число, обозначающее запас.

- Второе значение столбца  - строка, представляющая имя для идентификаторов столбцов; эти имена идентификаторов включают тикер и другие имена столбцов, связанные с фондом, такие как название компании и рыночная капитализация. Имена идентификаторов столбцов назначаются на основе значения row_number_within_ticker из оператора case ... end, определяющего столбец ticker_ids в списке выбора запроса.

- Третье значение столбца - это значение идентификатора, связанное с именем столбца во втором столбце.

• Оператор select в конце сегмента кода отображает набор результатов #pivotsource, упорядоченный по тикеру_number и ticker_ids (для идентификаторов тикера).

Фрагмент из набора результатов #pivotsource для первых двух акций отображен в следующем скриншоте. Есть несколько изменений в этих 12 строках с выхода по сравнению с предыдущим скриншотом, показывающего результаты обработки второго шага.

• Наиболее очевидным изменением является замена значений row_number_within_ticker с именами столбцов в столбце ticker_ids.
• Кроме того, порядок значений идентификатора в этом результирующем наборе отличается от предыдущего набора результатов. Это происходит из-за предложения order by в последнем предложении select в предыдущем сегменте кода. После того, как значения row_number_within_ticker были заменены именами столбцов, обозначающими идентификатор, больше не нужно было сохранять порядок строк, возвращаемых скриптом Python.

Далее появится код для вращения значений в наборе результатов #pivotsource.

• Ключевое слово PIVOT позволяет SQL Server переставлять значения в результирующем наборе, чтобы значения отображались в формате перекрестных таблиц, а не в виде списка.
• В списке выбора указывается порядок значений столбцов для кросс-таблицы.

- Столбец TICKER_NUMBER из списка переименован в TICKER INDEX для отображения данных в перекрестной таблице.

- Другие столбцы имеют одинаковое имя в списке и при отображении данных  в перекрестной таблице.

• Условие PIVOT запроса select используется функция MAX для значений TICKER_VALUE, за которой следует условие FOR.

- Функция MAX позволяет поворачивать строки без агрегации. Для этой операции поворота нецелесообразно агрегировать значения ticker_ids, которые являются отдельными значениями столбцов для каждой строки в поворотном наборе результатов.

- Условие FOR, включающее ключевое слово IN, определяет порядок значений столбцов при отображении данных в перекрестной таблице.

На следующем скриншоте показаны первые три строки из поворотного результирующего набора. Обратите внимание, что значения тикера соответствуют первым трем строкам на первом скриншоте в этом совете с веб-сайта NASDAQ. Другими словами, таблица SQL Server имеет аналогичную форму и те же значения, что и исходная HTML-таблица!

Общие рекомендации по загрузке содержимого с веб-страниц на SQL Server

• Визуально проверяйте с помощью браузера веб-страницу, с которой вы хотите загрузить контент.
• Программно проверьте содержимое HTML-страницы.

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

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

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

• Напишите сценарий Python для извлечения данных с исходной веб-страницы. Затем убедитесь, что извлеченные данные соответствуют как содержимому исходной страницы, так и вашим требованиям к сбору данных. Если вы собираетесь использовать BeautifulSoup для облегчения извлечения контента, встроенного в веб-страницу, убедитесь, что он установлен вместе с Python на вашем компьютере SQL Server.
• После того, как вы подтвердите, что скрипт Python извлекает на основе исходных данных необходимые вам значения, вы можете запустить скрипт Python из SSMS. Один из способов сделать это - вызывать командный файл Windows, запускающий скрипт Python. Хранимая процедура cmdshell xp_extended раскрывает эту возможность.
• Выполните захват вывода из сценария Python в промежуточную таблицу в SQL Server.
• Выполните любую окончательную обработку значений в промежуточной таблице и сохраните преобразованные, зафиксированные значения в таблице SQL Server.

Следующие шаги

1. Установите Python на свой компьютер, если он еще не установлен.
2. Затем установите любые специальные библиотеки, такие как BeautifulSoup, на которые вам нужно ссылаться вместе со встроенными библиотеками Python.
3. Затем загрузите файлы для этого совета  в каталог c: \ python_programs на вашем компьютере для запуска SQL Server.
4. Затем запустите скрипт Python для получения информации о тикерах NASDAQ с веб-сайта NASDAQ. Убедитесь, что ваш вывод из Python соответствует тому, что из вашего браузера при использовании той же исходной веб-страницы.
5. Запустите сценарий T-SQL для загрузки выбранных столбцов из таблицы NASDAQ HTML по отраслям в таблицу SQL Server. Убедитесь, что ваш результирующий набор из сценария T-SQL соответствует соответствующим значениям с HTML-страницы NASDAQ.
6. Попробуйте изменить код T-SQL, чтобы не включать столбец для его набора результатов. Вы можете добиться этого так же просто, как исключение столбца из тех, которые повернуты. Кроме того, вы можете ранее внести в скрипт изменения. Однако, когда вы делаете изменение на более раннем этапе, вам необходимо изменить все последующие шаги соответствующим образом.

No Comments