Глава 3. Запросы - SQL Lab в JupyterLab

Abstract¶

Расширение JupySQL подсвечивает синтаксис SQL в блокнотах Jupyter, но не на созданном в Jupyter Book сайте. Как обойти это недоразумение было показано в Главе 2. Здесь и далее подобным увлекаться не будем.

from sqlalchemy import create_engine
from sqlalchemy.engine import URL

# Формируем структуру подключения к БД
connection_url = URL.create(
    drivername="mysql+pymysql",
    host="localhost",
    port=3306,
    database="sakila",
    username="root",
    password="********",
)

engine = create_engine(connection_url)

%load_ext sql

%config SqlMagic.displaylimit = 20
# %config SqlMagic.displaycon = False
# %config SqlMagic.feedback = False

%sql engine

print("SQLAlchemy - подключение создано")
print("JupySQL - успешно подключен через SQLAlchemy Engine!")

SQLAlchemy - подключение создано
JupySQL - успешно подключен через SQLAlchemy Engine!

Механика запросов¶

%%sql
SELECT first_name, last_name
FROM customer
WHERE last_name = 'ZIEGLER'

%%sql
SELECT *
FROM category;

Части запроса

Имя	Назначение
select	Определяет, какие столбцы следует включить в результирующий набор запроса
from	Определяет таблицы, из которых следует выбирать данные, а также таблицы, которые должны быть соединены
where	Отсеивает ненужные данные
group by	Используется для группировки строк по общим значениям столбцов
having	Отсеивает ненужные данные
order by	Сортирует строки окончательного результирующего набора по одному или нескольким столбцам

Предложение select¶

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

%%sql
SELECT *
FROM language;

%%sql
SELECT language_id, name, last_update
FROM language;

%%sql
SELECT name
FROM language;

%%sql
SELECT
  language_id,
  'COMMON' language_usage,
  language_id * 3.1415927 lang_pi_value,
  upper(name) language_name
FROM language;

%%sql
SELECT
  version (),
  user (),
  database ();

Псевдонимы столбцов¶

%%sql
SELECT
  language_id,
  'COMMON' AS language_usage,
  language_id * 3.1415927 AS lang_pi_value,
  upper(name) AS language_name
FROM language;

Удаление дубликатов¶

Для аккуратного визуального вывода

Включим автоматическое преобразование внутреннего результата JupySQL в полноценный Pandas DataFrame. Таким образом получим стандартное усечение строк Pandas – чтобы вывод показывал начало и конец через многоточие вместо вывода всех строк.

Да, у нас появится слева дополнительный столбец с индексом. Просто не будем обращать на него внимания, чтобы не заниматься дополнительными манипуляциями.

Перепробовал несколько способов. Этот вариант оказался наиболее оптимальным.

# Импортируем Pandas
import pandas as pd
print(pd.__version__)

3.0.3

# Включаем автоматическое преобразование в `Pandas DataFrame`
%config SqlMagic.autopandas = True

%%sql
SELECT actor_id
FROM film_actor
ORDER BY actor_id;

%%sql
SELECT DISTINCT actor_id
FROM film_actor
ORDER BY actor_id;

Если хочется убрать столбец с индексом, но при этом оставить стандартное усечение строк Pandas

Придется выполнить дополнительные манипуляции. Например, для преобразования последнего результата _ из памяти (последней выполненной ячейки):

df = _.set_axis([''] * len(_), axis=0)


# Или обернуть в функцию
def no_idx(df):
    return df.set_axis([''] * len(df), axis=0)

no_idx(_)

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

df = _.set_axis([''] * len(_), axis=0)
df

# Отключаем автоматическое преобразование
%config SqlMagic.autopandas = False

Предложение from¶

Определяет таблицы, используемые запросом, наряду со средствами связывания таблиц вместе.

Производные таблицы (генерируемые подзапросами)¶

%%sql
SELECT
  concat(cust.last_name, ', ', cust.first_name) AS full_name
FROM
  (
    SELECT first_name, last_name, email
    FROM customer
    WHERE first_name = 'JESSIE'
  ) AS cust;

Временные таблицы¶

%%sql
CREATE TEMPORARY TABLE actors_j (
    actor_id smallint(5),
    first_name varchar(45),
    last_name varchar(45)
);

%%sql
INSERT INTO actors_j
SELECT actor_id, first_name, last_name
FROM actor
WHERE last_name LIKE 'J%';

%%sql
SELECT * FROM actors_j;

Представления¶

%%sql
CREATE VIEW cust_vw
AS
SELECT customer_id, first_name, last_name, active
FROM customer;

%%sql
SELECT first_name, last_name
FROM cust_vw
WHERE active = 0;

Связи таблиц¶

%%sql
SELECT
    customer.first_name,
    customer.last_name,
    time(rental.rental_date) rental_time
FROM customer
    INNER JOIN rental
    ON customer.customer_id = rental.customer_id
WHERE date(rental.rental_date) = '2005-06-14';

Определение псевдонимов таблиц¶

%%sql
SELECT c.first_name, c.last_name,
  time(r.rental_date) rental_time
FROM customer c
  INNER JOIN rental r
  ON c.customer_id = r.customer_id
WHERE date(r.rental_date) = '2005-06-14';

%%sql
SELECT c.first_name, c.last_name,
  time(r.rental_date) rental_time
FROM customer AS c
  INNER JOIN rental AS r
  ON c.customer_id = r.customer_id
WHERE date(r.rental_date) = '2005-06-14';

Предложение where¶

Это механизм для фильтрации нежелательных строк из результирующего набора.

%%sql
SELECT title
FROM film
WHERE rating = 'G' AND rental_duration >= 7;

Чтобы следующий запрос не выводил все 340 строк

Cнова включим автоматическое преобразование.
Только на этот раз в Polars DataFrame (интересно же).

# Импортируем Polars
import polars as pl
print(pl.__version__)

1.43.1

# Включаем автопреобразование в Polars DataFrame
%config SqlMagic.autopolars = True

%%sql
SELECT title
FROM film
WHERE rating = 'G' OR rental_duration >= 7;

type(_)

polars.dataframe.frame.DataFrame

%%sql
SELECT title, rating, rental_duration
FROM film
WHERE (rating = 'G' AND rental_duration >=7)
    OR (rating = 'PG-13' AND rental_duration < 4);

# Отключаем автопреобразование в Polars DataFrame
%config SqlMagic.autopolars = False

Pandas vs Polars

В поиске оптимального решения для вывода усеченных строк – чтобы вывод показывал начало и конец через многоточие вместо вывода всех строк – опробовали Pandas и Polars.

Pandas: отображает значения как в выводе SQL, но добавляет слева поле индекса. Потому что с точки зрения философии Pandas, датафрейм без индекса существовать не может.
Polars: выводит результат запроса без индекса, но строковые значения отображает в кавычках. Потому что с точки зрения философии Polars, визуальная валидация типов и контроль скрытых символов важнее классической визуальной эстетики текста.

Предложения group by и having¶

count(*) – возвращает количество записей таблицы.

%%sql
SELECT c.first_name, c.last_name, count(*)
FROM customer c
  INNER JOIN rental r
  ON c.customer_id = r.customer_id
GROUP BY c.first_name, c.last_name
HAVING count(*) >= 40;

Предложение order by¶

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

%%sql
SELECT c.first_name, c.last_name,
  time(r.rental_date) rental_time
FROM customer c
  INNER JOIN rental r
  ON c.customer_id = r.customer_id
WHERE date(r.rental_date) = '2005-06-14';

%%sql
SELECT c.first_name, c.last_name,
  time(r.rental_date) rental_time
FROM customer c
  INNER JOIN rental r
  ON c.customer_id = r.customer_id
WHERE date(r.rental_date) = '2005-06-14'
ORDER BY c.last_name;

%%sql
SELECT c.first_name, c.last_name,
  time(r.rental_date) rental_time
FROM customer c
  INNER JOIN rental r
  ON c.customer_id = r.customer_id
WHERE date(r.rental_date) = '2005-06-14'
ORDER BY c.last_name, c.first_name;

Сортировка по возрастанию и убыванию¶

desc - ключевое слово для сортировки по убыванию

%%sql
SELECT c.first_name, c.last_name,
  time(r.rental_date) rental_time
FROM customer c
  INNER JOIN rental r
  ON c.customer_id = r.customer_id
WHERE date(r.rental_date) = '2005-06-14'
ORDER BY time(r.rental_date) desc;

Сортировка с помощью номера столбца¶

%%sql
SELECT c.first_name, c.last_name,
  time(r.rental_date) rental_time
FROM customer c
  INNER JOIN rental r
  ON c.customer_id = r.customer_id
WHERE date(r.rental_date) = '2005-06-14'
ORDER BY 3 desc;

Упражнения¶

Упражнение 3.1¶

Получите идентификатор актера, а также имя и фамилию для всех актеров. Отсортируйте вывод сначала по фамилии, а затем по имени.

import polars as pl
print(pl.__version__)

1.43.1

%config SqlMagic.autopolars = True

%%sql
SELECT actor_id, first_name, last_name
FROM actor
ORDER BY last_name, first_name;

%config SqlMagic.autopolars = False

Упражнение 3.2¶

Получите идентификатор, имя и фамилию актера для всех актеров, чьи фамилии ‘WILLIAMS’ или ‘DAVIS’.

%%sql
SELECT actor_id, first_name, last_name
FROM actor
WHERE last_name IN ('WILLIAMS', 'DAVIS');

Упражнение 3.3¶

Напишите запрос к таблице rental, который возвращает идентификаторы клиентов, бравших фильмы напрокат 5 июля 2005 года (используйте столбец rental.rental_date; можете также использовать функцию date(), чтобы игнорировать компонент времени). Выведите по одной строке для каждого уникального идентификатора клиента.

%%sql
SELECT DISTINCT customer_id
FROM rental
WHERE date(rental_date) = '2005-07-05'

Упражнение 3.4¶

Заполните пропущенные места (обозначенные как <#>) в следующем многотабличном запросе, чтобы получить показанные результаты:

SELECT c.email, r.return_date
FROM customer c
  INNER JOIN rental <1>
  ON c.customer_id = <2>
WHERE date(r.rental_date) = '2005-06-14'
ORDER BY <3> <4>;

| email                                 | return date         |
| ------------------------------------- | ------------------- |
| DANIEL.CABRAL@sakilacustomer.org      | 2005-06-23 22:00:38 |
| TERRANCE.ROUSH@sakilacustomer.org     | 2005-06-23 21:53:46 |
| MIRIAM.MCKINNEY@sakilacustomer.org    | 2005-06-21 17:12:08 |
| GWENDOLYN.MAY@sakilacustomer.org      | 2005-06-20 02:40:27 |
| JEANETTE.GREENE@sakilacustomer.org    | 2005-06-19 23:26:46 |
| HERMAN.DEVORE@sakilacustomer.org      | 2005-06-19 03:20:09 |
| JEFFERY.PINSON@sakilacustomer.org     | 2005-06-18 21:37:33 |
| MATTHEW.MAHAN@sakilacustomer.org      | 2005-06-18 05:18:58 |
| MINNIE.ROMERO@sakilacustomer.org      | 2005-06-18 01:58:34 |
| SONIA.GREGORY@sakilacustomer.org      | 2005-06-17 21:44:11 |
| TERRENCE.GUNDERSON@sakilacustomer.org | 2005-06-17 05:28:35 |
| ELMER.NOE@sakilacustomer.org          | 2005-06-17 02:11:13 |
| JOYCE.EDWARDS@sakilacustomer.org      | 2005-06-16 21:00:26 |
| AMBER.DIXON@sakilacustomer.org        | 2005-06-16 04:02:56 |
| CHARLES.KOWALSKI@sakilacustomer.org   | 2005-06-16 02:26:34 |
| CATHERINE.CAMPBELL@sakilacustomer.org | 2005-06-15 20:43:03 |

16 rows in set (0.03 sec)

%%sql
SELECT c.email, r.return_date
FROM customer c
  INNER JOIN rental r
  ON c.customer_id = r.customer_id
WHERE date(r.rental_date) = '2005-06-14'
ORDER BY r.return_date desc;