session = Session()
# Пример чтения данных через ORM
users = session.query(User).filter(User.age > 30).all()
for user in users:
print(f"Имя: {user.name}, Возраст: {user.age}, Email: {user.email}")
```
Этот подход особенно удобен, если вы предпочитаете объектно-ориентированный стиль работы с базой данных.
Пример: Анализ данных с SQLAlchemy и Pandas
Представьте, что у вас есть база данных с информацией о продажах, и вы хотите найти города, в которых средняя сумма покупок превышает 5000.
1. Создадим таблицу:
```python
sales = Table(
'sales', metadata,
Column('id', Integer, primary_key=True),
Column('city', String),
Column('amount', Integer)
)
metadata.create_all(engine)
# Добавим данные
conn.execute(insert(sales).values([
{'city': 'New York', 'amount': 7000},
{'city': 'Los Angeles', 'amount': 3000},
{'city': 'New York', 'amount': 8000},
{'city': 'Los Angeles', 'amount': 2000},
{'city': 'Chicago', 'amount': 6000}
]))
```
2. Выгрузим данные и найдем среднюю сумму по городам:
```python
# Чтение данных из таблицы sales
query = "SELECT * FROM sales"
sales_df = pd.read_sql(query, engine)
# Вычисление средней суммы по городам
avg_sales = sales_df.groupby('city')['amount'].mean().reset_index()
# Фильтрация городов с средней суммой > 5000
filtered_sales = avg_sales[avg_sales['amount'] > 5000]
print(filtered_sales)
```
Результат:
```
city amount
0 Chicago 6000.0
1 New York 7500.0
```
3. Сохраним результат в таблицу:
```python
filtered_sales.to_sql('high_avg_sales', engine, if_exists='replace', index=False)
```
Теперь обработанные данные сохранены в базе, и вы можете использовать их в дальнейшем.
SQLAlchemy предоставляет мощные возможности для работы с базами данных, а интеграция с Pandas делает обработку данных ещё более удобной и гибкой. Вы можете быстро выгружать данные из базы, анализировать их с помощью Pandas и сохранять обратно, что упрощает создание аналитических решений и автоматизацию работы с данными.
Задачи для практики
Задача 1: Создание базы данных пользователей и извлечение данных
Описание:
Создайте базу данных `users.db` с таблицей `users`, содержащей следующие столбцы:
– `id` – уникальный идентификатор пользователя.
– `name` – имя пользователя.
– `age` – возраст пользователя.
– `email` – электронная почта.
Добавьте в таблицу данные о пяти пользователях и извлеките всех пользователей старше 30 лет.
Решение:
```python
from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData
import pandas as pd
# Создаем подключение к базе данных SQLite
engine = create_engine('sqlite:///users.db', echo=False)
metadata = MetaData()
# Определяем таблицу users
users = Table(
'users', metadata,
Column('id', Integer, primary_key=True),
Column('name', String),
Column('age', Integer),
Column('email', String)
)
# Создаем таблицу
metadata.create_all(engine)
# Добавляем данные
with engine.connect() as conn:
conn.execute(users.insert(), [
{'name': 'Alice', 'age': 25, 'email': 'alice@example.com'},
{'name': 'Bob', 'age': 35, 'email': 'bob@example.com'},
{'name': 'Charlie', 'age': 32, 'email': 'charlie@example.com'},
{'name': 'Diana', 'age': 28, 'email': 'diana@example.com'},
{'name': 'Eve', 'age': 40, 'email': 'eve@example.com'}
])
# Извлечение пользователей старше 30 лет
query = "SELECT * FROM users WHERE age > 30"
df = pd.read_sql(query, engine)
print(df)
```
Результат:
```
id name age email
1 2 Bob 35 bob@example.com
2 3 Charlie 32 charlie@example.com
4 5 Eve 40 eve@example.com
```
Задача 2: Подсчет пользователей по возрастным группам
Описание:
Используя базу данных `users.db`, разделите пользователей на две группы: младше 30 лет и 30 лет и старше. Посчитайте количество пользователей в каждой группе.
Решение:
```python
# Чтение данных из таблицы
df = pd.read_sql("SELECT * FROM users", engine)
# Добавление возрастной группы
df['age_group'] = df['age'].apply(lambda x: 'Under 30' if x < 30 else '30 and above')
# Подсчет пользователей по группам
group_counts = df.groupby('age_group')['id'].count().reset_index()
print(group_counts)
```
Результат:
```
age_group id
0 30 and above 3
1 Under 30 2
```
Задача 3: Сохранение агрегированных данных в новую таблицу
Описание:
Сохраните результаты подсчета пользователей по возрастным группам в новую таблицу `age_groups` в базе данных `users.db`.
Решение:
```python
# Сохранение в новую таблицу
group_counts.to_sql('age_groups', engine, if_exists='replace', index=False)
# Проверка сохраненных данных
saved_data = pd.read_sql("SELECT * FROM age_groups", engine)
print(saved_data)
```
Результат:
```
age_group id
0 30 and above 3
1 Under 30 2
```
Задача 4: Поиск наиболее популярных доменов электронной почты
Описание:
Добавьте данные о пользователях с разными адресами электронной почты. Найдите, какие домены (`example.com`, `gmail.com` и т.д.) встречаются чаще всего.
Решение:
```python
# Добавление новых данных
with engine.connect() as conn:
conn.execute(users.insert(), [
{'name': 'Frank', 'age': 29, 'email': 'frank@gmail.com'},
{'name': 'Grace', 'age': 37, 'email': 'grace@gmail.com'},
{'name': 'Helen', 'age': 33, 'email': 'helen@example.com'}
])
# Чтение данных
df = pd.read_sql("SELECT * FROM users", engine)
# Выделение доменов
df['email_domain'] = df['email'].apply(lambda x: x.split('@')[1])
# Подсчет частоты доменов
domain_counts = df['email_domain'].value_counts().reset_index()
domain_counts.columns = ['email_domain', 'count']
print(domain_counts)
```
Результат:
```
email_domain count
0 example.com 5
1 gmail.com 2
```
Задача 5: Создание таблицы продаж и анализ доходов
Описание:
Создайте таблицу `sales`, содержащую данные о продажах:
– `id` – идентификатор продажи.
– `product` – название продукта.
– `price` – цена продукта.
– `quantity` – количество проданных единиц.
Рассчитайте общий доход для каждого продукта и сохраните результаты в новую таблицу `product_revenues`.
Решение:
```python
# Определение таблицы sales
sales = Table(
'sales', metadata,
Column('id', Integer, primary_key=True),
Column('product', String),
Column('price', Integer),
Column('quantity', Integer)
)
metadata.create_all(engine)
# Добавление данных
with engine.connect() as conn:
conn.execute(sales.insert(), [
{'product': 'Laptop', 'price': 1000, 'quantity': 3},
{'product': 'Phone', 'price': 500, 'quantity': 5},
{'product': 'Tablet', 'price': 300, 'quantity': 7}
])
# Чтение данных
sales_df = pd.read_sql("SELECT * FROM sales", engine)
# Расчет общего дохода
sales_df['revenue'] = sales_df['price'] * sales_df['quantity']
revenues = sales_df.groupby('product')['revenue'].sum().reset_index()
# Сохранение в новую таблицу
revenues.to_sql('product_revenues', engine, if_exists='replace', index=False)
# Проверка сохраненных данных
saved_revenues = pd.read_sql("SELECT * FROM product_revenues", engine)
print(saved_revenues)
```
Результат:
```
product revenue
0 Laptop 3000
1 Phone 2500
2 Tablet 2100
```
Задача 6: Фильтрация данных по динамическому запросу
Описание:
Создайте функцию, которая принимает минимальную цену и возвращает список продуктов, стоимость которых выше указанного значения.
Решение:
```python
def filter_products_by_price(min_price):
query = f"SELECT * FROM sales WHERE price > {min_price}"
result_df = pd.read_sql(query, engine)
return result_df
# Фильтрация продуктов с ценой выше 400
filtered_products = filter_products_by_price(400)
print(filtered_products)
```
Результат:
```
id product price quantity
0 1 Laptop 1000 3
1 2 Phone 500 5
```
Задача 7: Определение наиболее активных пользователей
Описание:
В таблице `activity_log` содержатся данные о действиях пользователей:
– `id` – идентификатор записи.
– `user_id` – идентификатор пользователя.
– `action` – выполненное действие.
– `timestamp` – время выполнения действия.
Определите, кто из пользователей совершил наибольшее количество действий.
Решение:
```python
from sqlalchemy import Table, Column, Integer, String, DateTime
from datetime import datetime
# Определение таблицы activity_log
activity_log = Table(
'activity_log', metadata,
Column('id', Integer, primary_key=True),
Column('user_id', Integer),
Column('action', String),
Column('timestamp', DateTime)
)
metadata.create_all(engine)
# Добавление данных
with engine.connect() as conn:
conn.execute(activity_log.insert(), [
{'user_id': 1, 'action': 'login', 'timestamp': datetime(2025, 1, 1, 10, 0)},
{'user_id': 1, 'action': 'purchase', 'timestamp': datetime(2025, 1, 1, 10, 5)},
{'user_id': 2, 'action': 'login', 'timestamp': datetime(2025, 1, 1, 11, 0)},
{'user_id': 1, 'action': 'logout', 'timestamp': datetime(2025, 1, 1, 10, 10)},
{'user_id': 2, 'action': 'purchase', 'timestamp': datetime(2025, 1, 1, 11, 5)},
{'user_id': 2, 'action': 'logout', 'timestamp': datetime(2025, 1, 1, 11, 10)}
])
# Чтение данных
activity_df = pd.read_sql("SELECT * FROM activity_log", engine)
# Подсчет количества действий по пользователям
user_activity = activity_df.groupby('user_id')['id'].count().reset_index()
user_activity.columns = ['user_id', 'action_count']
# Поиск самого активного пользователя
most_active_user = user_activity.loc[user_activity['action_count'].idxmax()]
print(most_active_user)
```
Результат:
```
user_id 1
action_count 3
```
Задача 8: Подсчет действий по типу
Описание: Для каждого типа действия из таблицы `activity_log` подсчитайте, сколько раз оно выполнялось.
Решение:
```python
# Подсчет количества каждого типа действия
action_counts = activity_df['action'].value_counts().reset_index()
action_counts.columns = ['action', 'count']
print(action_counts)
```
Результат:
```
action count
0 login 2
1 purchase 2
2 logout 2
```
Задача 9: Анализ временных меток
Описание: Определите, в какие часы дня пользователи наиболее активны.
Решение:
```python
# Извлечение часа из временных меток
activity_df['hour'] = activity_df['timestamp'].dt.hour
# Подсчет действий по часам
hourly_activity = activity_df.groupby('hour')['id'].count().reset_index()
hourly_activity.columns = ['hour', 'action_count']
print(hourly_activity)
```
Результат:
```
hour action_count
0 10 3
1 11 3
```
Задача 10: Создание таблицы доходов от пользователей
Описание: Используя таблицу `sales`, определите, сколько дохода принёс каждый пользователь, и сохраните результаты в таблицу `user_revenues`.
Решение:
```python
# Добавление данных о продажах с указанием user_id
with engine.connect() as conn:
conn.execute(sales.insert(), [
{'product': 'Laptop', 'price': 1000, 'quantity': 1, 'user_id': 1},
{'product': 'Phone', 'price': 500, 'quantity': 2, 'user_id': 1},
{'product': 'Tablet', 'price': 300, 'quantity': 3, 'user_id': 2}
])
# Чтение данных из sales
sales_df = pd.read_sql("SELECT * FROM sales", engine)
# Расчёт дохода для каждого пользователя
sales_df['revenue'] = sales_df['price'] * sales_df['quantity']
user_revenues = sales_df.groupby('user_id')['revenue'].sum().reset_index()
# Сохранение в новую таблицу
user_revenues.to_sql('user_revenues', engine, if_exists='replace', index=False)
# Проверка результатов
saved_user_revenues = pd.read_sql("SELECT * FROM user_revenues", engine)
print(saved_user_revenues)
```
Результат:
```
user_id revenue
0 1 2000
1 2 900
```
Задача 11: Поиск последнего действия пользователей
Описание:Для каждого пользователя из таблицы `activity_log` найдите его последнее действие.
Решение:
```python
# Поиск последнего действия
last_actions = activity_df.sort_values('timestamp').groupby('user_id').last().reset_index()
last_actions = last_actions[['user_id', 'action', 'timestamp']]
print(last_actions)
```
Результат:
```
user_id action timestamp
0 1 logout 2025-01-01 10:10:00
1 2 logout 2025-01-01 11:10:00
```
Задача 12: Фильтрация пользователей с высоким доходом
Описание: Используя таблицу `user_revenues`, выберите всех пользователей, чей доход превышает 1500.
Решение:
```python
# Чтение данных из user_revenues
user_revenues = pd.read_sql("SELECT * FROM user_revenues", engine)
# Фильтрация пользователей с доходом > 1500
high_revenue_users = user_revenues[user_revenues['revenue'] > 1500]
print(high_revenue_users)
```
Результат:
```
user_id revenue
0 1 2000
```
Задача 13: Распределение доходов по продуктам
Описание: Определите, какой процент от общего дохода приносит каждый продукт.
Решение:
```python
# Подсчет общего дохода
total_revenue = sales_df['revenue'].sum()
# Расчет процента дохода по продуктам
sales_df['revenue_percent'] = (sales_df['revenue'] / total_revenue) * 100
product_revenue_percent = sales_df.groupby('product')['revenue_percent'].sum().reset_index()
print(product_revenue_percent)
```
Результат:
```
product revenue_percent
0 Laptop 50.793651
1 Phone 25.396825
2 Tablet 23.809524
```
Эти задачи демонстрируют, как SQLAlchemy и Pandas могут использоваться вместе для создания, управления и анализа данных в базах данных. Они покрывают такие аспекты, как фильтрация данных, выполнение группировок и агрегатов, интеграция данных и сохранение результатов. Эти примеры помогут вам освоить основные техники работы с базами данных в Python.
О проекте
О подписке