Skip to content

Instantly share code, notes, and snippets.

@MikyPo

MikyPo/sql_venn.py

Last active April 29, 2025 08:12
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save MikyPo/40174e52f8c926d1d72ae383a894a980 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save MikyPo/40174e52f8c926d1d72ae383a894a980 to your computer and use it in GitHub Desktop.
Download ZIP
Объединение и визуализация SQL JOIN
Raw
sql_venn.py
# Developed by MikyPo
# More code for DA here: https://dzen.ru/mikypo
# Импортируем библиотеки
import matplotlib.pyplot as plt
import sqlite3
import pandas as pd
from sqlalchemy import create_engine
from matplotlib import pyplot as plt
from matplotlib_venn import venn2, venn2_circles
'''Для постройки диаграмм Венна'''
# Настройка стиля
plt.figure(figsize=(15, 10))
plt.suptitle('SQL-соединения в диаграммах Венна', fontsize=16)
# Цветовые параметры
BORDER_COLOR = '#0e7cd6' # Цвет обводки кругов
FILL_COLOR = '#0e7cd6' # Цвет заливки результирующих областей
BG_COLOR = 'white' # Фоновый цвет
# Функция для создания диаграммы
def create_venn(ax, subsets, set_labels, active_regions, title):
# Создаем диаграмму
v = venn2(subsets=subsets, set_labels=set_labels, ax=ax)
# Убираем цифры
for text in v.subset_labels:
if text is not None:
text.set_text('')
# Настраиваем обводку кругов
c = venn2_circles(subsets=subsets, linestyle='solid', linewidth=2, color=BORDER_COLOR, ax=ax)
# Закрашиваем все области белым по умолчанию
for region in ['10', '01', '11']:
if v.get_patch_by_id(region):
v.get_patch_by_id(region).set_color(BG_COLOR)
v.get_patch_by_id(region).set_alpha(1.0)
# Закрашиваем активные области синим
for region in active_regions:
if v.get_patch_by_id(region):
v.get_patch_by_id(region).set_color(FILL_COLOR)
v.get_patch_by_id(region).set_alpha(0.5) # Полупрозрачность
ax.set_title(title)
# Тестовые датафреймы
users = pd.DataFrame({'id': [1, 2, 3, 4, 5, 6, 7, 8, ], 'name': ['Геннадий Серый', 'Шаурман Белый', 'Дени Духовкин', 'Игорь Поттов', 'Рома Патисон',
'Николай Теслов', 'Илья Масковский', 'Фёдор Сумкин']})
display(users)
'''Запись датафрейма в таблицу'''
def excel_to_sqlite(df, db_path, table_name):
# Подключаемся к базе
with sqlite3.connect(db_path) as conn:
# Записываем данные в таблицу
df.to_sql(table_name, conn, if_exists='replace', index=False)
# Проверяем что данные записались
check = pd.read_sql(f"SELECT * FROM {table_name}", conn)
print('Проверка записи')
display(check)
# Запись таблицы users
excel_to_sqlite(users, 'demo3.db', 'users')
# Запись таблицы items
excel_to_sqlite(items, 'demo3.db', 'items')
# Функция для запросов в БД SQLite
def query_sql(query_str):
conn = sqlite3.connect('demo3.db')
print(pd.read_sql(query_str, conn))
conn.close()
# Запрос для получения списка всех таблиц в базе данных
tables_query = "SELECT name FROM sqlite_master WHERE type='table';"
# Используем функцию query_sql для одного запроса
query_sql(tables_query)
'''1. LEFT INCLUSIVE JOIN (это просто LEFT JOIN)'''
sql = '''
SELECT t.*, i.item_name
FROM users t
LEFT JOIN items i ON t.id = i.user_id
'''
# LEFT INCLUSIVE JOIN (LEFT JOIN)
create_venn(plt.subplot(2, 4, 1),
subsets=(1, 1, 1),
set_labels=('A', 'B'),
active_regions=['10', '11'],
title='LEFT INCLUSIVE JOIN\n(Всё A + пересечение A с В)')
# Выполнение запроса
query_sql(sql)
'''2. LEFT EXCLUSIVE JOIN'''
sql = '''
SELECT t.*
FROM users t
LEFT JOIN items i ON t.id = i.user_id
WHERE i.user_id IS NULL
'''
# LEFT EXCLUSIVE JOIN
create_venn(plt.subplot(2, 4, 2),
subsets=(1, 1, 1),
set_labels=('A', 'B'),
active_regions=['10'],
title='LEFT EXCLUSIVE JOIN\n(Только A не в B)')
# Выполнение запроса
query_sql(sql)
'''3. FULL OUTER INCLUSIVE JOIN (эмулируется в SQLite)'''
sql = '''
SELECT t.*, i.item_name
FROM users t
LEFT JOIN items i ON t.id = i.user_id
UNION ALL
SELECT t.*, i.item_name
FROM items i
LEFT JOIN users t ON t.id = i.user_id
WHERE t.id IS NULL
'''
# FULL OUTER INCLUSIVE JOIN
create_venn(plt.subplot(2, 4, 6),
subsets=(1, 1, 1),
set_labels=('A', 'B'),
active_regions=['10', '01', '11'],
title='FULL OUTER INCLUSIVE\n(Все записи А и В)')
# Выполнение запроса
query_sql(sql)
'''4. INNER JOIN'''
sql = '''
SELECT t.*, i.item_name
FROM users t
INNER JOIN items i ON t.id = i.user_id
'''
# INNER JOIN
create_venn(plt.subplot(2, 4, 3),
subsets=(1, 1, 1),
set_labels=('A', 'B'),
active_regions=['11'],
title='INNER JOIN\n(Только пересечение А и В)')
# Выполнение запроса
query_sql(sql)
'''5. RIGHT INCLUSIVE JOIN (эмулируется в SQLite)'''
sql = '''
SELECT t.*, i.item_name
FROM items i
LEFT JOIN users t ON i.user_id = t.id
'''
# RIGHT INCLUSIVE JOIN
create_venn(plt.subplot(2, 4, 4),
subsets=(1, 1, 1),
set_labels=('A', 'B'),
active_regions=['01', '11'],
title='RIGHT INCLUSIVE JOIN\n(Всё B + пересечение В с А)')
# Выполнение запроса
query_sql(sql)
'''6. RIGHT EXCLUSIVE JOIN (эмулируется в SQLite)'''
sql = '''
SELECT i.*
FROM items i
LEFT JOIN users t ON i.user_id = t.id
WHERE t.id IS NULL
'''
# RIGHT EXCLUSIVE JOIN
create_venn(plt.subplot(2, 4, 5),
subsets=(1, 1, 1),
set_labels=('A', 'B'),
active_regions=['01'],
title='RIGHT EXCLUSIVE JOIN\n(Только B не в A)')
# Выполнение запроса
query_sql(sql)
'''7. FULL OUTER EXCLUSIVE JOIN (эмулируется в SQLite)'''
sql = '''
-- Пользователи без товаров (LEFT EXCLUSIVE)
SELECT t.id, t.name, NULL as item_name, NULL as value
FROM users t
LEFT JOIN items i ON t.id = i.user_id
WHERE i.user_id IS NULL
UNION ALL
-- Товары без пользователей (RIGHT EXCLUSIVE)
SELECT NULL as id, NULL as name, i.item_name, i.value
FROM items i
LEFT JOIN users t ON i.user_id = t.id
WHERE t.id IS NULL
'''
# FULL OUTER EXCLUSIVE JOIN
create_venn(plt.subplot(2, 4, 7),
subsets=(1, 1, 1),
set_labels=('A', 'B'),
active_regions=['10', '01'],
title='FULL OUTER EXCLUSIVE\n(Всё вне пересечения A с B)')
# Выполнение запроса
query_sql(sql)
'''Все диаграммы на одной картинке с сохранением в PNG-файл'''
# Настройка стиля
plt.figure(figsize=(15, 10))
plt.suptitle('SQL-соединения в диаграммах Венна', fontsize=16)
# Цветовые параметры
BORDER_COLOR = '#0e7cd6' # Цвет обводки кругов
FILL_COLOR = '#0e7cd6' # Цвет заливки результирующих областей
BG_COLOR = 'white' # Фоновый цвет
# Функция для создания диаграммы
def create_venn(ax, subsets, set_labels, active_regions, title):
# Создаем диаграмму
v = venn2(subsets=subsets, set_labels=set_labels, ax=ax)
# Убираем цифры
for text in v.subset_labels:
if text is not None:
text.set_text('')
# Настраиваем обводку кругов
c = venn2_circles(subsets=subsets, linestyle='solid', linewidth=2, color=BORDER_COLOR, ax=ax)
# Закрашиваем все области белым по умолчанию
for region in ['10', '01', '11']:
if v.get_patch_by_id(region):
v.get_patch_by_id(region).set_color(BG_COLOR)
v.get_patch_by_id(region).set_alpha(1.0)
# Закрашиваем активные области синим
for region in active_regions:
if v.get_patch_by_id(region):
v.get_patch_by_id(region).set_color(FILL_COLOR)
v.get_patch_by_id(region).set_alpha(0.5) # Полупрозрачность
ax.set_title(title)
# LEFT INCLUSIVE JOIN (LEFT JOIN)
create_venn(plt.subplot(2, 4, 1),
subsets=(1, 1, 1),
set_labels=('A', 'B'),
active_regions=['10', '11'],
title='LEFT INCLUSIVE JOIN\n(Всё A + пересечение A с В)')
# LEFT EXCLUSIVE JOIN
create_venn(plt.subplot(2, 4, 2),
subsets=(1, 1, 1),
set_labels=('A', 'B'),
active_regions=['10'],
title='LEFT EXCLUSIVE JOIN\n(Только A не в B)')
# INNER JOIN
create_venn(plt.subplot(2, 4, 3),
subsets=(1, 1, 1),
set_labels=('A', 'B'),
active_regions=['11'],
title='INNER JOIN\n(Только пересечение А и В)')
# RIGHT INCLUSIVE JOIN
create_venn(plt.subplot(2, 4, 4),
subsets=(1, 1, 1),
set_labels=('A', 'B'),
active_regions=['01', '11'],
title='RIGHT INCLUSIVE JOIN\n(Всё B + пересечение В с А)')
# RIGHT EXCLUSIVE JOIN
create_venn(plt.subplot(2, 4, 5),
subsets=(1, 1, 1),
set_labels=('A', 'B'),
active_regions=['01'],
title='RIGHT EXCLUSIVE JOIN\n(Только B не в A)')
# FULL OUTER INCLUSIVE JOIN
create_venn(plt.subplot(2, 4, 6),
subsets=(1, 1, 1),
set_labels=('A', 'B'),
active_regions=['10', '01', '11'],
title='FULL OUTER INCLUSIVE\n(Все записи А и В)')
# FULL OUTER EXCLUSIVE JOIN
create_venn(plt.subplot(2, 4, 7),
subsets=(1, 1, 1),
set_labels=('A', 'B'),
active_regions=['10', '01'],
title='FULL OUTER EXCLUSIVE\n(Всё вне пересечения A с B)')
# Убираем пустой субплот
plt.delaxes(plt.subplot(2, 4, 8))
plt.tight_layout()
plt.savefig('venn_diagrams.png', dpi=300, bbox_inches='tight')
plt.show()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment