118      Глава 5. Работа с базами данных

В результате выполнения скрипта выводятся строки с зарплатой каждого сотрудника вместе записью из таблицы emps:

9002, Jane Boorman, sales, 2800 9003, Tom Heints, sales, 2500

УПРАЖНЕНИЕ № 8: ОБЪЕДИНЕНИЕ «ОДИН-КО-МНОГИМ»

Измените код из предыдущего раздела, так чтобы запрос объединял таблицу emps с таблицей orders. Можете сохранить условие о том, что значение empno должно превышать 9001. Измените вызов функции print() так, чтобы выводить строки, полученные в результате нового объединения.

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

При сохранении данных в MySQL можно воспользоваться встроенными в БД средствами аналитики, такими как аналитический SQL, чтобы значительно сократить объем данных, передаваемых между приложением и БД. Аналитический SQL — это дополнительный набор команд SQL, предназначенный не просто для хранения, извлечения и обновления данных, а для их фактического анализа. В качестве примера представим, что нам нужно импортировать биржевые данные только тех компаний, чьи акции упали в цене не более чем на 1% по сравнению с предыдущим днем заданного периода. Этот предварительный анализ выполняется с помощью аналитического SQL, что избавляет от необходимости загружать из БД в скрипт весь датасет с ценами на акции.

Чтобы посмотреть, как это работает, получим биржевые данные с помощью библиотеки yfinance, представленной в главе 3, и сохраним их в таблице БД. Затем запросим таблицу из Python-скрипта, загрузив только ту часть данных, которая удовлетворяет заданному условию. Начнем с создания таблицы в нашей БД sampledb, которая хранит биржевые данные. В таблице должны быть три колонки: ticker (тикер), date (дата) и price (цена). Введите следующую команду в командной строке mysql>:

mysql> CREATE TABLE stocks( ticker VARCHAR(10), date VARCHAR(10), price DECIMAL(15,2) );

Понимание инструкций SQL      119

Используем этот скрипт для получения биржевых данных с yfinance:

import yfinance as yfdata = []

tickers = ['TSLA', 'FB', 'ORCL', 'AMZN'] for ticker in tickers:

tkr = yf.Ticker(ticker)

hist = tkr.history(period='5d')

.reset_index()

records = hist[['Date','Close']].to_records(index=False) records =

list(records)

records = [(ticker, str(elem[0])[:10], round(elem[1],2)) for elem in

records]

data = data + records

Сначала определяем пустой список data, который будет заполнен биржевыми данными . Как мы уже видели выше в этой главе, метод cursor.execute()в блоке INSERTожидает получить данные в виде списка (list). Затем определяем список тикеров, по которым хотим извлечь данные . После этого в цикле передаем каждый тикер из списка tickers в функцию yfinance Ticker() . Функция возвращает объект Ticker. Его метод history() предоставляет данные, связанные с соответствующим тикером. В этом примере мы получаем данные об акциях для каждого тикера за пять последних рабочих дней (period='5d').

Метод history() возвращает данные о котировках в виде pandas DataFrame со столбцом Date в качестве индекса. Наконец, преобразуем этот датафрейм в список кортежей для вставки в базу данных. Поскольку в датасет нам нужно включить столбец Date, удаляем его из индекса с помощью метода DataFrame reset_index(), тем самым превращая Dateв обычный столбец . Затем из полученного датафрейма выбираем только столбцы Date и Close, где значение поля Close содержит цены акций на конец дня, и преобразуем эти столбцы в массив записей NumPy. Это промежуточный шаг в процессе преобразования входных данных . После этого превращаем данные в список кортежей . Вслед за этим необходимо переформатировать каждый кортеж, чтобы его можно было вставить в таблицу базы данных stocks в виде строки. Например, значения колонки Date содержат много лишней информации (часы, минуты, секунды и т. д.). Взяв только первые 10 символов поля 0 в каждом кортеже, мы извлечем год, месяц и день, чего вполне достаточно для анализа . К примеру, 2022-01- 06T00:00:00.000000000 превратится просто в 2022-01-06. Наконец, все еще внутри цикла, добавляем кортежи, связанные с тикером, в список data .

В результате содержимое списка кортежей data будет выглядеть следующим образом:

120      Глава 5. Работа с базами данных

[

('TSLA', '2022-01-06', 1064.7), ('TSLA', '2022-01-07', 1026.96), ('TSLA', '2022-01-10', 1058.12), ('TSLA', '2022-01-11', 1064.4), ('TSLA', '2022-01-12', 1106.22), ('FB', '2022-01-06', 332.46), ('FB', '2022-01-07', 331.79), ('FB', '2022-01-10', 328.07), ('FB', '2022-01-11', 334.37), ('FB', '2022-01-12', 333.26), ('ORCL', '2022-01-06', 86.34), ('ORCL', '2022-01-07', 87.51), ('ORCL', '2022-01-10', 89.28), ('ORCL', '2022-01-11', 88.48), ('ORCL', '2022-01-12', 88.31), ('AMZN', '2022-01-06', 3265.08), ('AMZN', '2022-01-07', 3251.08), ('AMZN', '2022-01-10', 3229.72), ('AMZN', '2022-01-11', 3307.24), ('AMZN', '2022-01-12', 3304.14)

]

Чтобы вставить этот датасет в таблицу stocks в виде строк, добавьте следующий код к предыдущему скрипту и выполните его заново:

import mysql.connector

from mysql.connector import errorcode try:

cnx = mysql.connector.connect(user='root', password='your_pswd', host='127.0.0.1', database='sampledb')

cursor = cnx.cursor()

# объявление запроса

query_add_stocks = ("""INSERT INTO stocks (ticker, date, price) VALUES (%s, %s, %s)""")

# добавление строк с ценами на акции

cursor.executemany(query_add_stocks, data) cnx.commit()

except mysql.connector.Error as err: print("Error-Code:", err.errno) print("Error-Message: {}".format(err.msg))

finally:

cursor.close()

cnx.close()

Понимание инструкций SQL      121

Код строится по той же схеме, что и запрос для вставки данных в БД, приведенный выше. Однако на этот раз мы используем метод cursor.executemany(), который позволяет эффективно выполнить инструкцию INSERT для каждого кортежа из списка data .

Теперь, когда в БД есть данные, можно составлять к ней различные запросы с помощью аналитического SQL, чтобы отвечать на поставленные вопросы. Например, чтобы отфильтровать акции, цена которых упала более чем на 1% по сравнению с ценой в предыдущие дни, как было предложено в начале этого раздела, нам понадобится запрос, который может анализировать цены на один и тот же тикер в течение нескольких дней. В качестве первого шага создадим датасет, который в одной строке будет хранить как текущую цену акции, так и ее цену за предыдущий день. Введите следующий код в командной строке mysql>:

SELECT date, ticker, price,

LAG(price) OVER(PARTITION BY ticker ORDER BY date) AS prev_price FROM stocks;

Функция LAG() внутри SELECT является аналитической функцией SQL. Она позволяет получить доступ к данным предыдущей строки из текущей строки. Блок PARTITION BY в OVER делит датасет на группы по тикеру. Функция LAG() применяется отдельно в каждой группе; это гарантирует, что данные не будут перетекать из одного тикера в другой. Результат запроса будет выглядеть примерно так:

122      Глава 5. Работа с базами данных

20 rows in set (0.00 sec)

Запрос создал новый столбец prev_price с ценой акций за предыдущий день. Как видите, LAG()совмещает в одной строке данные сразу двух строк, то есть можно управлять данными из обеих строк одним математическим выражением в виде части запроса. Например, можно разделить одну цену на другую, чтобы рассчитать изменение цены ото дня ко дню в процентах. Исходя из этого требования, напишем запрос, отбирающий строки только тех тикеров, чьи цены упали не более чем на 1% по сравнению с предыдущими днями заданного периода:

SELECT s.* FROM stocks AS s LEFT JOIN

(SELECT DISTINCT(ticker) FROM(SELECT

price/LAG(price) OVER(PARTITION BY ticker ORDER BY date) AS dif, ticker

WHERE dif <0.99) AS aON a.ticker = s.tickerWHERE a.ticker IS NULL;

Инструкция SQL объединяет два различных запроса к одной и той же таблице — stocks. Первый join извлекает все строки из таблицы stocks , а второй — только названия тикеров, цены которых упали на 1% или более по сравнению с предыдущим днем хотя бы один раз за период анализа . Этот join имеет сложную структуру: он выбирает данные из подзапроса, а не непосредственно из таблицы stocks. Подзапрос, который начинается с , извлекает из таблицы те строки, значения поля price которых по крайней мере на 1% меньше, чем в предыдущей строке. Мы определяем это, разделив price на LAG(price) и проверив, меньше ли результат, чем 0.99 . Затем, внутри списка SELECT основного запроса к полю ticker, применяется функция DISTINCT()для исключения дублируемых названий тикеров из итогового множества .

Мы объединяем запросы по столбцу ticker . В блоке WHERE сообщаем join, что нужно получить только те строки, в которых не найдено соответствия между

124      Глава 5. Работа с базами данных

try:

cnx = mysql.connector.connect(user='root', password='your_pswd', host='127.0.0.1', database='sampledb')

query = ("""

SELECT s.* FROM stocks AS s LEFT JOIN

(SELECT DISTINCT(ticker) FROM (SELECT

price/LAG(price) OVER(PARTITION BY ticker ORDER BY date) AS dif, ticker

FROM stocks) AS b WHERE dif <0.99) AS a ON a.ticker = s.ticker WHERE a.ticker IS NULL""")

df_stocks = pd.read_sql(query, con=cnx)

df_stocks = df_stocks.set_index(['ticker','date']) except mysql.connector.Error as err:

print("Error-Code:", err.errno) print("Error-Message: {}".format(err.msg))

finally:

cnx.close()

В основном скрипт похож на приведенный выше в этой главе. Ключевое отличие

втом, что данные из БД загружаются непосредственно в pandas DataFrame. Для этого используется метод pandas read_sql(), который принимает SQL-запрос

ввиде строки в качестве первого параметра и объект подключения к базе данных

вкачестве второго . Затем мы устанавливаем столбцы ticker и date в качестве индекса датафрейма .

Исходя из данных, представленных выше, итоговый датафрейм df_stocksбудет выглядеть так: