🖥 Что нового в SQLAlchemy 2.0?

В начале 2023 года вышла SQLAlchemy 2.0 — библиотека на Python для работы с реляционными СУБД, которая работает с Object Relational Mapper (объектно-реляционным отображением). Основная задача SQLAlchemy — синхронизация объектов Python с данными в БД.

То есть с помощью SQLAlchemy можно описывать структуры БД и работать с их данными на объектно-ориентированном коде на Python без использования чистого SQL. Другая важная особенность SQLAlchemy — код для работы с базой данных будет одинаковым вне зависимости от БД, которую использует разработчик. Такой подход позволяет без проблем мигрировать с одной базой данных на другую.

Полный список нововведений в SQLAlchemy 2.0 можно посмотреть в официальной документации сервиса.

▪ Читать

@sqlhub

🤔 10 аргументов “за” и “против” SQL 

• Вторая по обсуждаемости тема в среде специалистов по SQL: как произносить то, что большинство людей называют Sequel ([сиквел] вместо [эс кью эл]). Это удивительно, поскольку язык существует более 40 лет.

• Но, пожалуй, самая обсуждаемая тема, связанная с SQL, — это вопрос о том, считается ли он языком программирования.

Почему SQL не является языком программирования

🟢 Создание и выполнение запросов, а не скриптов
🟢 Не изменяет состояние
🟢 Отсутствие циклирования
🟢 Репутация в отрасли

Почему SQL является языком программирования

🟣 Соответствие критерию Тьюринга
🟣 Переменные, условная логика, определения функций
🟣 Возможность создавать приложения (хотя это сложно)
🟣 Так считают в Google

Подробное разъяснение читай тут

@sqlhub

⭐️ 100 датасетов для анализа данных на все случаи жизни.

▪Список
▪Зеркало

@sqlhub

🖥 Простой скрипт для резервного копирования всех баз данных SQL Server

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

Одной из таких задач может быть необходимость создания резервных копий всех баз данных на вашем сервере. Если баз не много, это не проблема, но я видел несколько серверов, где на одном экземпляре SQL Server было 100+ баз данных.

Вы можете использовать SQL Server Management Studio для резервного копирования баз данных или даже использовать Maintenance Plans, но использование T-SQL — гораздо более простой и быстрый подход.

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

Как создать резервные копии всех баз данных SQL Server
▪Укажите путь для хранения бэкапов баз данных
▪Укажите формат имени файла резервной копии
▪Выберите список баз данных для резервного копирования
▪Цикличность
▪Программно создаем команду резервного копирования базы данных, используя имя базы данных, путь и формат имени файла

Формат имени файла бэкапа базы данных DBname_YYYYDDMM.BAK
Вот скрипт, который позволит вам создать резервную копию каждой базы данных в вашем экземпляре SQL Server. Вам нужно будет изменить @path на соответствующий директорию резервного копирования.

DECLARE @name NVARCHAR(256) -- database name
DECLARE @path NVARCHAR(512) -- path for backup files
DECLARE @fileName NVARCHAR(512) -- filename for backup
DECLARE @fileDate NVARCHAR(40) -- used for file name

-- specify database backup directory
SET @path = 'C:\test\'

-- specify filename format
SELECT @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb') -- exclude these databases
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor
Формат имени файла бэкапа базы данных DBname_YYYYDDMM_HHMMSS.BAK
Если вы хотите также включить время в название файла, вы можете заменить эту строку в приведенном выше сценарии:

-- specify filename format
SELECT @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112)
этой строкой:

-- specify filename format
SELECT @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(NVARCHAR(20),GETDATE(),108),':','')
Добавление дополнительных элементов в команду резервного копирования
Чтобы добавить дополнительные параметры, такие как прогресс резервного копирования (статистика) и сжатие, вы можете изменить эту строку в скрипте:

BACKUP DATABASE @name TO DISK = @fileName
этой строкой:

BACKUP DATABASE @name TO DISK = @fileName WITH STATS=10, COMPRESSION

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

Кроме того, если вы хотите обойти некоторые пользовательские базы данных, вы можете включить их в раздел NOT IN.

Следующие шаги
Добавьте этот сценарий в свой набор инструментов (toolbox)
Измените этот сценарий и сделайте его хранимой процедурой, чтобы включить один или несколько параметров
Усовершенствуйте сценарий для использования дополнительных опций BACKUP

@sqlhub

🖥 Скрытые ошибки SQL, допускаемые опытными аналитиками данных

Написание неправильного синтаксиса SQL не так страшно, потому что вы сразу же узнаете об этом, исправите то, что упустили, и вуаля, все работает как ни в чем не бывало.

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

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

customers table:
+-------------+---------------+-------------+
| customer_id | customer_name | visit_count |
+-------------+---------------+-------------+
| 1 | John Doe | 5 |
| 2 | Jane Smith | 8 |
| 3 | Mike Johnson | 3 |
+-------------+---------------+-------------+

orders table:
+----------+-------------+------------+--------------+
| order_id | customer_id | order_date | total_amount |
+----------+-------------+------------+--------------+
| 1 | 1 | 2023-07-10 | 50.00 |
| 2 | 1 | 2023-07-12 | 75.00 |
| 3 | 2 | 2023-07-11 | 120.50 |
| 4 | 3 | 2023-07-15 | 25.75 |
+----------+-------------+------------+--------------+

Чаще всего мы работаем с таблицами не по отдельности, а с их объединениями. Приведенные выше таблицы можно объединить по столбцу customer_id.

• Теперь, если мы захотим вычислить что-то простое, например, общее количество посещений сайта или общее количество посещений по пользователям, то, если мы не будем очень внимательны, то, сами того не замечая, получим неверные результаты.
SELECT SUM(c.visit_count) AS total_visits
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id

В результате будет получено 21 посещение, что неверно.

• И даже когда мы пытаемся просуммировать общее количество посещений на одного пользователя :
SELECT c.customer_id, c.customer_name, SUM(c.visit_count) AS total_visits
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

• Мы получаем неверные значения для одного из клиентов (клиента, имеющего более одного заказа):
customers table:
+-------------+---------------+--------------+
| customer_id | customer_name | total_visits |
+-------------+---------------+--------------+
| 1 | John Doe | 10 |
| 2 | Jane Smith | 8 |
| 3 | Mike Johnson | 3 |
+-------------+---------------+--------------+

• Мы можем увидеть, что пошло не так, взглянув на таблицу, полученную в результате объединения:
+-------------+---------------+--------------+----------+------------+--------------+
| customer_id | customer_name | visit_count | order_id | order_date | total_amount |
+-------------+---------------+--------------+----------+------------+--------------+
| 1 | John Doe | 5 | 1 | 2023-07-10 | 50.00 |
| 1 | John Doe | 5 | 2 | 2023-07-12 | 75.00 |
| 2 | Jane Smith | 8 | 3 | 2023-07-11 | 120.50 |
| 3 | Mike Johnson | 3 | 4 | 2023-07-15 | 25.75 |
+-------------+---------------+--------------+----------+------------+--------------+

Это явление называется fanout, и его трудно заметить, поскольку результаты получаются частично корректными.

📌 Продолжение

@sqlhub

📊 VisiData v2.11

VisiDatahttps://github.com/saulpw/visidata является многофункциональным инструментом для обработки и просмотра табличных данных.

Этот интерактивный и эффективный инструмент предоставляет возможность работать с большими объемами данных.

VisiData работает с CSV файлами, электронными таблицами Excel, базами данных SQL и многими другими источниками данных.

pip3 install visidata

▪Github
▪Проект

@sqlhub

📌 Возможности SQLite, которые вы могли пропустить

🔵Частичные индексы (Partial Indexes)
При построении индекса можно указать условие попадания строки в индекс, к примеру, одна из колонок не пустая, а другая равна заданному значению.
create index idx_partial on tab1(a, b) where a is not null and b = 5;
select * from tab1 where a is not null and b = 5; --> search table tab1 using index

🔵Индексы на выражение (Indexes On Expressions)
Если в запросах к таблице часто используется выражение, то можно построить индекс по нему. Однако следует иметь в виду, что пока оптимизатор не очень гибок и перестановка столбцов в выражении приведет к отказу от использования индекса.
create index idx_expression on tab1(a + b);
select * from tab1 where a + b > 10; --> search table tab1 using index ...
select * from tab1 where b + a > 10; --> scan table

🔵Вычисляемые колонки (Generated Columns)
Если данные столбца представляют собой результат вычисления выражения по другим столбцам, то можно создать виртуальный столбец. Есть два вида: VIRTUAL (вычисляется каждый раз при чтении таблицы и не занимает места) и STORED (вычисляется при записи данных в таблицу и место занимает). Разумеется записывать данные в такие столбцы напрямую нельзя.
create table tab1 (
a integer primary key,
b int,
c text,
d int generated always as (a * abs(b)) virtual,
e text generated always as (substr(c, b, b + 1)) stored
);

🔵 R-Tree индекс
Индекс предназначен для быстрого поиска в диапазоне значений/вложенности объектов, т.е. задачи типичной для гео-систем, когда объекты-прямоугольники заданы своей позицией и размером и требуется найти все объекты, которые пересекаются с текущим. Данный индекс реализован в виде виртуальной таблицы (см. ниже) и это индекс только по своей сути. Для поддержки R-Tree индекса требуется собрать SQLite с флагом SQLITE_ENABLE_RTREE (по умолчанию не установлен).
create virtual table idx_rtree using rtree (
id, -- ключ
minx, maxx, -- мин и макc x координаты
miny, maxy, -- мин и макc y координаты
data -- дополнительные данные
);

insert into idx_rtree values (1, -80.7749, -80.7747, 35.3776, 35.3778);
insert into idx_rtree values (2, -81.0, -79.6, 35.0, 36.2);

select id from idx_rtree
where minx >= -81.08 and maxx <= -80.58 and miny >= 35.00 and maxy <= 35.44;

Остальные возможности можно тут

@sqlhub

➡️ Использование составных триггеров (compound triggers) Oracle

▪️Примеры таких триггеров в основном связаны с логированием изменений таблицы, я решила рассмотреть пример из двух таблиц, которые изменяют друг друга.

Дано: две таблицы orders и orders_detail.

CREATE TABLE TMP.orders
(
ID NUMBER, -- № заказа
DATE_DOC DATE, -- дата заказа
AMOUNT NUMBER, -- сумма заказа
DISCOUNT NUMBER -- скидка от 0 до 100%
);
CREATE TABLE TMP.orders_detail
(
ID NUMBER,
ID_ORDER NUMBER, -- № заказа
ID_GOODS NUMBER, -- идентификатор товара
PRICE NUMBER, -- цена товара
QTY NUMBER, -- количество
SUM_ORDER NUMBER -- стоимость заказа
);

Перед нами стоят задачи:

1) при обновлении поля orders.discount пересчитать orders_detail.sum_order, а также обновить поле orders.amount по всему заказу;
2) при изменении orders_detail.price и(или) orders_detail.qty пересчитать orders_detail.sum_order, а также обновить поле orders.amount по всему заказу;
3) orders_detail.sum_order считается по формуле: orders_detail.sum_order = orders_detail.price * orders_detail.qty * (1- orders.discount/100).

При таких задачах возникает ошибка мутирования таблиц ORA-04091: table is mutating.

Эта ошибка встречается тогда, когда триггер уровня строк пытается вы­полнить чтение или запись в таблицу, для которой сработал триггер.

• Например, нам нужно пересчитать поле orders_detail.sum_order, а для этого нам нужно прочитать поле orders.discount(которое изменилось).
• Начиная с Oracle Database 11g можно использовать compound(составные) триггеры. Они представляют собой четыре различных триггера, объединенных в один.

Вот пример синтаксиса такого триггера. ⤵️

Продолжение

@sqlhub

🦊 Kali Linux для продвинутого тестирования на проникновение. Sqlmap.

Наиболее распространенной и используемой уязвимостью на веб-сайтах является SQL-инъекция.

• Одним из самых полезных и мощных инструментов для оценки уязвимостей SQL-инъекций является sqlmap. Инструмент написан на Python, который автоматизирует разведку и эксплуатацию Firebird, Microsoft SQL, MySQL (теперь называется MariaDB, разработанный сообществом и коммерчески поддерживаемый форк MySQL), Базы данных Oracle, PostgreSQL, Sybase и SAP MaxDB.

Мы продемонстрируем атаку SQL-инъекцией на базу данных Mutillidae.

1) Определить веб-сервер, внутреннюю систему управления базами данных и доступные базы данных.

2) Получите доступ к веб-сайту Mutillidae и просмотрите веб-страницы, чтобы определить те, которые принимают пользовательский ввод (например, форма входа пользователя, которая принимает имя пользователя и пароль от удаленного пользователя); эти страницы могут быть уязвимы для SQL-инъекций.

3) Затем откройте Kali и из терминала введите следующее (используя соответствующий целевой IP-адрес):
root@kali:~# sqlmap -u 'http://targetip/mutillidae/index.php?page=user- info.php&username=admin&password=&user-info-php-submitbutton=

sqlmap вернет данные, как показано на *рисунке 1.

• Наиболее вероятной базой данных для хранения данных приложения, является БД Mutillidae;
поэтому мы проверим все таблицы этой базы данных, используя следующую команду:
root@kali:~# sqlmap -u "http://yourip/mutillidae/index.php?page=user-info.php&username=&password=&user-info-php-submit-button=View+Account+Details" -D mutillidae --tables

Данные, возвращаемые при выполнении этой команды, показаны на *рисунке 2.

📌Читать далее

@sqlhub

🐱 Переход на PgCat — прокси-сервер Postgres следующего поколения

PgCat  — это open source прокси-сервер Postgresql, в развитии которого мы участвуем и который используем на продакшене. Функционал PgCat для кластеров Postgresql: пул соединений, балансировка нагрузки, отработка отказа реплики.

• В Instacart мы применяем Postgresql почти для всех задач баз данных: эффективным кешированием, индексированием, оптимизацией запросов и вертикальным масштабированием экземпляров выжимаем из одного экземпляра Postgres немало производительности.

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

Почему PgCat?

📌 Разбираем подробно

@sqlhub

🖥 [Тестовое Тинькофф] Звонки по дням

Дано
Дана следующая структура таблиц:

Задание
Напишите запрос, который посчитает количество звонков, сделанных между 2020-10-01 и сегодняшним днем. Полученная таблица должна состоять из двух столбцов: date и cnt_calls. В столбце date должен находиться день вызова (без времени), а в столбце "cnt_calls" должно отображаться количество звонков, совершенных в этот день.

Столбцы в результате

date
cnt_calls
Примечание: для корректной работы вашего запроса обязательно указывайте схему таблиц - tinkoff.

Например, FROM tinkoff.employees.

Пишите свое решение в комментариях👇

@sqlhub

🤓 Набор обучающих материалов по базам данных

1. Статьи.
2. Блоги.
3. Курсы.
4. Выступления.
5. Книги.

Дает возможность углубленно изучить работу с базами данных.

👇

📌 GitHub

@Sqlhub

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

Машинное обучение: t.me/ai_machinelearning_big_data
Python: t.me/pythonl
C#: t.me/csharp_ci
C/C++/ t.me/cpluspluc
Data Science: t.me/data_analysis_ml
Devops: t.me/devOPSitsec
Go: t.me/Golang_google
Базы данных: t.me/sqlhub
Rust: t.me/rust_code
Javascript: t.me/javascriptv
React: t.me/react_tg
PHP: t.me/phpshka
Android: t.me/android_its
Мобильная разработка: t.me/mobdevelop
Linux: t.me/+A8jY79rcyKJlYWY6
Big Data: t.me/bigdatai
Хакинг: t.me/linuxkalii
Тестирование: https://t.me/+F9jPLmMFqq1kNTMy
Java: t.me/javatg

💼 Папка с вакансиями: t.me/addlist/_zyy_jQ_QUsyM2Vi
Папка Go разработчика: t.me/addlist/MUtJEeJSxeY2YTFi
Папка Python разработчика: t.me/addlist/eEPya-HF6mkxMGIy

Папка машинное обучение: https://t.me/addlist/_FjtIq8qMhU0NTYy

📕 Бесплатные Книги для программистов: https://t.me/addlist/YZ0EI8Ya4OJjYzEy

🎞 YouTube канал: https://www.youtube.com/@uproger

😆ИТ-Мемы: t.me/memes_prog

🇬🇧Английский: t.me/english_forprogrammers

🖥 NoSQL: виды, особенности и применение

NoSQL — это семейство нереляционных баз данных. В них разработчики отошли от использования традиционной табличной модели представления информации.

• Уже само название заявляет, что управлять данными можно не только с помощью Structured Query Language (SQL), т. е. языка структурированных запросов.

• Модель NoSQL появилась в ответ на необходимость оперативно обрабатывать действительно огромные объёмы данных. Поэтому NoSQL по большей части заточена под масштабирование по горизонтали и работу с недостаточно структурированными или постоянно меняющимися данными.

Почему появилась модель NoSQL
Расцвет реляционных (relation — «связь, взаимосвязь») баз данных пришёлся на 80-е годы, когда в БД в основном хранили текстовые документы и изображения. Однако с развитием технологий и ростом объёма обрабатываемой информации реляционные СУБД перестали справляться со всеми задачами одинаково хорошо. Термин NoSQL впервые прозвучал в 1998 году: его применил итальянский учёный Карло Строцци для описания своей open source СУБД. При разработке он отказался от SQL, а также от основного принципа реляционных СУБД — ACID (atomicity, consistency, isolation, durability).

В начале XXI века NoSQL БД стали популярны, в том числе у корпораций. Чтобы решить проблемы параллельных вычислений с очень большим объёмом информации и масштабируемости, Google построила на основе модели распределённых вычислений MapReduce колоночное хранилище. На базе этих технологий выросло целое семейство высокодоступных open source СУБД.

Основные причины появления NoSQL:

📌 Читать

@sqlhub

🖥 Продвинутые темы SQL для дата-инженеров

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

Пример набора данных
Имеется три таблицы:

• product_table с данными различных продуктов — название, цена, категория;
• category_table с данными о категориях продуктов;
• order_table с данными о заказах: заказанный продукт, его количество, дата заказа.

Вот схема каждой таблицы:
product_table
+----+----------------------+-------+-------------+
| id | Название | Цена | category_id |
+----+----------------------+-------+-------------+
| 1 | iPhone 13 | 1000 | 2 |
| 2 | Galaxy S21 | 800 | 2 |
| 3 | Pixel 6 | 700 | 2 |
| 4 | MacBook Air | 1000 | 4 |
| 5 | Surface Pro 8 | 1200 | 4 |
| 6 | iPad Pro | 800 | 3 |
| 7 | iPad Mini | 400 | 9 |
| 8 | Smart TV | 800 | 1 |
| 9 | Home Theater System | 600 | 1 |
| 10 | Galaxy S21 Ultra | 1400 | 2 |
| 11 | iPhone 13 Pro Max | 1300 | 2 |
| 12 | Pixel 6 Pro | 1000 | 2 |
+----+----------------------+-------+-------------+

category_table
+----+-------------+
| id | Название |
+----+-------------+
| 1 | Электроника |
| 2 | Телефоны |
| 3 | Планшеты |
| 4 | Ноутбуки |
| 5 | Бытовая техника |
| 6 | Одежда |
| 7 | Спорттовары |
| 8 | Игрушки |
| 9 | Аксессуары |
+----+-------------+

order_table
+----+------------+-------------+------------+
| id | product_id | Количество | Дата |
+----+------------+-------------+------------+
| 1 | 1 | 2 | 2022-03-28 |
| 2 | 2 | 1 | 2022-03-28 |
| 3 | 3 | 3 | 2022-03-29 |
| 4 | 4 | 1 | 2022-03-30 |
| 5 | 5 | 2 | 2022-03-31 |
| 6 | 6 | 3 | 2022-03-31 |
| 7 | 7 | 2 | 2022-03-31 |
| 8 | 8 | 1 | 2022-03-31 |
| 9 | 9 | 1 | 2022-03-31 |
+--------------------------------------------+

Фильтрация данных
Предложение WHERE — это важный SQL-функционал для фильтрования данных по конкретным условиям. WHERE, которым определяется условие включения данных в результаты, добавляется в конце оператора SELECT.

Пример. Чтобы получить все продукты дороже 1000 $, применяем WHERE для их фильтрации по цене, в результаты включаются только продукты дороже 1000 $:
SELECT *
FROM product_table
WHERE price > 1000;

Возвращаемый результат:
+----+---------------------+-------+-------------+
| id | Название | Цена | category_id |
+----+---------------------+-------+-------------+
| 5 | Surface Pro 8 | 1200 | 4 |
| 10 | Galaxy S21 Ultra | 1400 | 2 |
| 11 | iPhone 13 Pro Max | 1300 | 2 |
+----+---------------------+-------+-------------+

Объединения
Чтобы иметь полную картину о данных нескольких таблиц, эти таблицы объединяют. Для этого в SQL имеются внутренние, левые, правые и полные внешние объединения.

Пример. Чтобы получить список всех заказов и название заказанного продукта, с помощью объединения в SQL объединяем таблицы: в одной данные о заказе, в другой — о продукте:
SELECT order_table.*, product_table.name
FROM order_table
INNER JOIN product_table ON order_table.product_id = product_table.id;

📌 Читать дальше

@sqlhub