Как переписать SQL-запросы на Python с помощью Pandas

В этой статье June Tao Ching рассказал, как с помощью Pandas добиться на Python такого же результата, как в SQL-запросах.

Rus https://habr.com/ru/companies/plarium/articles/513316/

Eng https://towardsdatascience.com/how-to-rewrite-your-sql-queries-in-python-with-pandas-8d5b01ab8e31#--responses

#db

👉 @database_info

Базы данных SQL уроки для начинающих. SELECT, JOINS, GROUP BY, INSERT, UPDATE, WHERE

00:00 - План на урок
00:50 - Виды баз данных
02:53 - О реляционных БД
03:15 - 1 нормальная форма
03:45 - 2 нормальная форма
04:50 - Связи между таблицами
06:12 - Начало работы с MySQL
06:47 - SHOW DATABASES
07:00 - CREATE DATABASE
07:16 - DROP DATABASE
07:40 - SHOW TABLES
07:55 - CREATE TABLE
08:50 - SHOW COLUMNS
10:10 - INSERT INTO
10:45 - SELECT *
11:15 - SELECT id, surname
11:40 - DISTINCT
12:10 - WHERE
12:45 - LIMIT
13:07 - AS
13:30 - ORDER BY
14:04 - ALTER TABLE
14:40 - UPDATE SET
15:30 - LIKE
16:20 - where AND OR
17:25 - BETWEEN
17:45 - DELETE
18:10 - Multiple INSERT
19:10 - Объединение таблиц \ JOINS
20:05 - INNER JOIN
21:00 - LEFT OUTER JOIN
21:15 - RIGHT OUTER JOIN
21:30 - UNION
22:04 - Агрегатные функции
23:10 - GROUP BY

#sql

👉 @database_info

ClickHouse: теоретические основы работы

ClickHouse — это open-source OLAP база данных, ориентированная на колонки.
Пётр, DevOps-инженер компании Nixys, пишет серию статей, в которой разбирает все нюансы работы с этой БД. В первой части он погрузит вас в теорию: расскажет о строчных и колоночных архитектурах СУБД, через призму теоремы CAP объяснит особенности ClickHouse и покажет его преимущества. Также автор затронет тему табличных движков, среди которых уделит особое внимание семейству MergeTree.

#db

👉 @database_info

Управляйте базой данных лучше

Легко управляйте таблицами, просматривайте данные, пишите SQL и выполняйте запросы.

Поддерживаемые базы данных

Cloudflare D1
CockroachDB
libSQL
MariaDB
MySQL
PostgreSQL
Rqlite
SQLCipher
SQLite
SQL Server


https://dataflare.app/

#db

👉 @database_info

Выбор данных из MySQL таблиц с одинаковой структурой

Быстрый ответ
Для объединения данных из таблиц, имеющих идентичное оформление, используйте оператор UNION ALL, приемлемой является наличие повторяющихся данных. Для исключения повторений, используйте UNION. Например, запрос для извлечения name из table1, table2 и table3 будет следующим:

SELECT name FROM table1
UNION ALL
SELECT name FROM table2
UNION ALL
SELECT name FROM table3;

UNION позволит извлечь только уникальные имена.
При объединении таблиц необходимо учесть:
- Открыто указание столбцов в сложных запросах, что повышает производительность и сокращает объем передаваемых данных.
- Применение псевдонимов таблиц для улучшения читаемости запросов, особенно когда есть JOINы и сложные условия отфильтровывания.

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

Создание индекса для категориального столбца улучшает производительность при работе с большими объемами данных. Пример запроса:
SELECT noscript FROM movies_en WHERE genre = 'Sci-Fi'
UNION ALL
SELECT noscript FROM movies_es WHERE genre = 'Ciencia Ficción';

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

Визуализация
Представьте различные наборы данных как продукты, размещенные в шкафах, имеющих одинаковые полки:
🗄️1️⃣: [Спагетти 🍝, Рис 🍚]
🗄️2️⃣: [Фасоль 🥫, Томатный соус 🍅]
🗄️3️⃣: [Песто 🌿, Пармезан 🧀]

Их объединение должно выглядеть так:
SELECT * FROM cupboard1
UNION ALL
SELECT * FROM cupboard2
UNION ALL
SELECT * FROM cupboard3;

Результат — пир для гурмана:
🥘: [🍝, 🍚, 🥫, 🍅, 🌿, 🧀]

Примечание: UNION ALL соединяет все записи, включая повторы, не пропуская ни одного элемента.

Путь к оптимизированным запросам и потенциальные препятствия
Избегайте двусмысленных JOINов. Ясно указывайте таблицы или их псевдонимы, чтобы исключить возможные ошибки в WHERE и ORDER BY.

Пример четкого запроса:
SELECT t1.name FROM table1 AS t1
WHERE t1.active = 1
UNION ALL
SELECT t2.name FROM table2 AS t2
WHERE t2.active = 1
ORDER BY name;

#db

👉 @database_info

Горизонтальное и вертикальное шардирование

Существует два высокоуровневых подхода:

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

Горизонтальное разделение: Разделение базы данных на разделы строк, равномерно распределенные по нескольким серверам.

Некоторые методы горизонтального разделения:

1. На основе диапазона: Сегментирование строк на основе диапазона значений, например возрастных групп. Может привести к неравномерному распределению данных и появлению "горячих точек".

2. На основе каталога: Использование каталога для поиска строк. Обеспечивается гибкость, но возникает риск возникновения единой точки отказа.

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

#db

👉 @database_info

Ranking Functions: ROW_NUMBER

Четыре функции ранжирования: ROW_NUMBER, RANK, DENSE_RANK и NTILE появились в SQL Server 2005 и отличаются от обычных скалярных функций тем, что результат, который они выдают для строки, зависит от других строк выборки. От агрегатных функций они отличаются тем, что возвращают только одну строку для каждой строки на входе, т. е. они не объединяют набор строк в одну. В этой статье мы рассмотрим ROW_NUMBER — самую простую из всех функций ранжирования.

Для примеров будем использовать следующий набор данных:

CREATE TABLE T (PK INT IDENTITY, A INT, B INT, C INT)
CREATE UNIQUE CLUSTERED INDEX TPK ON T(PK)
INSERT T VALUES (0, 1, 😍
INSERT T VALUES (0, 3, 6)
INSERT T VALUES (0, 5, 4)
INSERT T VALUES (0, 7, 2)
INSERT T VALUES (0, 9, 0)
INSERT T VALUES (1, 0, 9)
INSERT T VALUES (1, 2, 7)
INSERT T VALUES (1, 4, 5)
INSERT T VALUES (1, 6, 3)
INSERT T VALUES (1, 8, 1)

https://habr.com/ru/articles/803211/

#db

👉 @database_info

Статический анализ структуры базы данных (часть 1)

Статический анализ структуры базы данных — это процесс выявления ошибок, нерекомендуемых практик и потенциальных проблем в базе данных только на основе структуры, типов данных, свойств объектов. Статический анализ структуры не задействует ни пользовательские данные, ни статистику по таким данным.

https://habr.com/ru/articles/800121/

#db

👉 @database_info

Статический анализ структуры базы данных (часть 2)

Продолжим разбор проверок структуры базы данных, на примере PostgeSQL. Данная статья будет посвящена проверкам связанным с ограничением FOREIGN KEY (FK). Часть проверок целесообразно выполнять на регулярной основе, а некоторые позволяют лучше понять структуру проекта при первом знакомстве и применяются только один раз.

https://habr.com/ru/articles/803841/

#db

👉 @database_info

Главное — порядок в данных, а остальное СУБД.

Приглашаем на вебинар «PostgreSQL, MongoDB, Redis в облаке: с чего начать». Валерий Ишутин, руководитель направления развития платформенных сервисов T1 Cloud, расскажет:

🔵 Как развернуть базу данных нажатием одной кнопки
🔵 Что включает в себя услуга Managed Services
🔵 Что делать, если Junior-разработчик удалил часть базы данных
🔵 О распределении зон ответственности T1 Cloud и клиента

💬 В конце вебинара можно будет задать вопросы спикеру.

Дата: 16 апреля 2024 (вторник)
Время: 11:00 – 12:00 МСК

➡️ Зарегистрироваться и добавить вебинар в календарь можно прямо сейчас.

Реклама. Информация о рекламодателе

PostgreSQL. Устройство карты свободного пространства

В этой статье мы разберемся, как в СУБД PostgreSQL реализован поиск страниц с необходимым свободным пространством для быстрой вставки записей в таблицу.

Для понимания статьи необходимо:
- Понимание основных механизмов работы PostgreSQL
- Базовое понимание языков Си и SQL
- Базовые навыки работы с терминалом на ОС Linux

https://habr.com/ru/articles/806415/

#db

👉 @database_info

SQL за полтора часа в одном видео! Полный базовый курс по SQL. Базы данных курс для начинающих!

00:00:00 Введение
00:02:43 Платные курсы и что изучать дальше
00:04:55 Коротко об онлайн редакторе
00:07:11 Что такое база данных
00:08:10 Что такое CRUD
00:10:05 Запрос на создание первой таблицы
00:11:57 Типы данных у атрибутов(колонок) (DATA TYPES)
00:17:26 Прописываем атрибуты(колонки) первой таблицы
00:21:07 Создаем первую таблицу (CREATE TABLE)
00:21:38 Проверка на наличие таблицы при создании (IF NOT EXISTS)
00:22:18 Запрос на удаление таблицы(DROP TABLE)
00:22:33 Проверка на наличие таблицы при удалении(IF EXISTS)
00:22:57 Запрос на добавление объекта в таблицу (INSER INTO table)
00:23:35 Запрос на чтение(получение) объектов из таблицы(SELECT * FROM table)
00:24:18 Модификаторы для атрибутов(колонок) таблицы(NOT NULL, DEFAULT, UNIQUE)
00:29:26 Изменение уже существующей таблицы(ALTER TABLE, ADD, DROP, RENAME, MODIFY COLUMN)
00:36:08 Удаление объектов из таблицы(DELETE FROM table)
00:37:06 Редактирование объекта в таблице(UPDATE table)
00:39:03 Первичный ключ(PRIMARY KEY)
00:45:37 Композиция в бд
00:50:38 "Иностранный" ключ(FOREIGN KEY)
00:57:26 Индексы в бд(INDEX)
00:59:24 Готовим данные для темы алиас, юнион и слияние таблиц
01:03:30 Слияние таблиц(INNER JOIN)
01:06:02 Слияние таблиц(LEFT JOIN)
01:06:53 Слияние таблиц(RIGHT JOIN)
01:07:26 Слияние таблиц(FULL JOIN/OUTER JOIN) и Union
01:09:04 Алиас(table AS alias)
01:12:18 Select Distinct в SQL
01:13:42 AND OR NOT в SQL
01:16:18 ORDER BY и LIMIT в SQL
01:17:35 MIN и MAX в SQL
01:18:25 COUNT SUM AVG в SQL
01:19:29 LIKE в SQL
01:21:31 IN и BETWEEN в SQL
01:22:50 GROUP BY в SQL
01:25:17 EXISTS в SQL
01:27:37 ANY и SOME в SQL
01:29:00 INSERT INTO в SQL
01:30:40 Отношения в базе данных
01:32:11 Отношения один к одному
01:33:09 Отношения один ко многим
01:34:09 Отношения многие ко многим
01:37:10 Отношения один к одному и один ко многим "через"
01:39:10 SQL инъекции

источник

#db

👉 @database_info

Как посчитать для каждой строки таблицы количество вхождений этой строки в другую таблицу?

Есть две таблицы course и history
В таблице course: id и name
В таблице history: id, course_id, status
Нужно для каждой строчки course посчитать количество строк в history

На выходе должно быть см. скрин

Ответ

SELECT name AS course_name, COUNT(history.id) AS quantity
FROM course
LEFT JOIN history ON course_id = course.id AND status = 1
GROUP BY name

#db

👉 @database_info

Хранимые процедуры в SQL

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

https://telegra.ph/Hranimye-procedury-v-SQL-04-16

#SQL

👉 @database_info

Скрипт для создания бекапов БД с помощью Percona XtraBackup

Скрипт предназначен для создания резервных копий баз данных (в субботу — полная резервная копия, в остальные дни — инкрементальные).

#!/bin/bash
# Скрипт делает резервную копию БД
# если сервер MySQL не запущен, то выходим

MYADMIN="/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf"
ping_output=`$MYADMIN ping 2>&1`; ping_alive=$(( ! $? ))

if [ $ping_alive = 0 ]; then
logger -t mysql-backup 'MYADMIN ping FAIL'
exit
fi

BACKUP="/usr/bin/innobackupex-1.5.1 --no-lock"
workdir="/usr/local/tmp/"

DofW=`date +'%u'`
full=0
incr=0
# если суббота - делаем полный бекап, иначе инкрементальный
if [ "$DofW" = "6" ];
then
full=1
logger -t mysql_backup 'make full backup'
else
incr=1
logger -t mysql_backup 'make incremental backup'
fi

if [ $full == '1' ];
then
logger -t mysql_backup 'start mysql full backup'
dump="mysql"`date +%Y%m%d`"full.tar"
ext=".gz"
# при распаковке использовать ключ -i, например tar -ixvf backup.tar
$BACKUP --user=USER --password=PASSWORD --stream=tar ./ | pigz -p 5 > ${workdir}${dump}${ext}
logger -t mysql_backup 'finish mysql full backup, start rsync'
# с помощью rsync копируем резервную копию на удаленный сервер
rsync --bwlimit=5000 ${workdir}${dump}${ext} 192.168.0.4::backup
logger -t mysql_backup 'finish rsync'
rm -f ${workdir}/mysql*
fi

if [ $incr == '1' ];
then
logger -t mysql_backup 'start mysql incremental backup'
# получаем значение LSN из последнего лога бекапа
LSN=`cat /usr/local/sbin/innobackupex.log | grep "The latest check point (for incremental):" | awk -F"'" '{print $2}' | tail -n 1`
echo $LSN > /usr/local/sbin/lsn
dump="mysql"`date +%Y%m%d`"incr"
archiv="mysql"`date +%Y%m%d`"incr.tar.gz"
ext=".tar.gz"
$BACKUP --user=USER --password=PASSWORD --incremental --incremental-lsn=$LSN --stream=xbstream ./ > ${workdir}${dump}.xbstream
logger -t mysql_backup 'finish mysql incremental backup, start tar'
cd ${workdir}
tar -czf $archiv $dump.xbstream
rm -f ${workdir}/*.xbstream
logger -t mysql_backup 'finish tar, start rsync'
# с помощью rsync копируем резервную копию на удаленный сервер
rsync --bwlimit=5000 ${workdir}${dump}${ext} 192.168.0.4::backup
logger -t mysql_backup 'finish rsync'
rm -f ${workdir}/mysql*
fi


#Percona #XtraBackup

👉 @database_info

Работа с json в PostgreSQL

JSON (JavaScript Object Notation) является одним из наиболее распространенных форматов для передачи и хранения данных. PostgreSQL поддерживает работу с JSON, что позволяет эффективно использовать его возможности для обработки и анализа данных.

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

источник

#db

👉 @database_info

Пример рекурсивного запроса для работы с иерархией в postgres

СУБД postgres предоставляет специальный синтаксис для работы с иерархиями с помощью рекурсивного запроса. Такой запрос начинается с ключевых слов with recursive.

В этом видео мы создадим простую иерархию географических объектов в виде таблицы. Затем напишем рекурсивный запрос, чтобы наглядно отобразить весь путь от одного из самых низкоуровневых элементов и до самой вершины иерархии. Таким образом, мы как бы собираем по пути "хлебные крошки" (breadcrumbs), которые могут пригодиться, если этот путь мы хотим отобразить пользователю.

источник

#db

👉 @database_info

Вы инженер связи, слаботочник или ИТ-шник, или безопасник? Обратите внимание на эту выставку, не пропускаю ее никогда. Мало вообще посещаю мероприятия не-онлайн, но тут делаю исключения.
Точка притяжения для телеком-специалистов, инженеров и сисадминов. Связь, телеком, интернет вещей, ЦОДы и оборудование.
На @sviaz_expocentr вход свободный по электронной регистрации на сайте.
Если ИТ и телекоммуникации ваша работа, увидимся на "Связи" 2024.

👉 @database_info