Создадим таблицу для хранения информации о пользователях:

```python

from sqlalchemy import Table, Column, Integer, String, MetaData

# Создаем метаданные

metadata = MetaData()

# Определяем таблицу

users = Table(

'users', metadata,

Column('id', Integer, primary_key=True),

Column('name', String),

Column('age', Integer),

Column('email', String)

)

# Создаем таблицу в базе данных

metadata.create_all(engine)

```

Теперь таблица `users` создана в базе данных.

Для добавления данных используем объект подключения:

```python

from sqlalchemy import insert

# Подключаемся к базе данных

conn = engine.connect()

# Добавляем данные

insert_query = insert(users).values([

{'name': 'Alice', 'age': 25, 'email': 'alice@example.com'},

{'name': 'Bob', 'age': 30, 'email': 'bob@example.com'},

{'name': 'Charlie', 'age': 35, 'email': 'charlie@example.com'}

])

conn.execute(insert_query)

print("Данные добавлены в таблицу.")

```

Чтение данных и интеграция с Pandas

Чтобы выгрузить данные из базы данных в Pandas, SQLAlchemy предоставляет удобный метод. Используем Pandas для выполнения SQL-запроса:

```python

import pandas as pd

# Чтение данных из таблицы users

query = "SELECT * FROM users"

df = pd.read_sql(query, engine)

print(df)

```

Вывод будет выглядеть так:

```

id name age email

0 1 Alice 25 alice@example.com

1 2 Bob 30 bob@example.com

2 3 Charlie 35 charlie@example.com

```

Теперь данные из базы данных доступны в формате DataFrame, и вы можете применять к ним все мощные инструменты анализа, которые предоставляет Pandas.

Обработка данных с использованием Pandas

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

```python

# Фильтрация пользователей старше 30 лет

filtered_df = df[df['age'] > 30]

# Добавление нового столбца с доменом электронной почты

filtered_df['email_domain'] = filtered_df['email'].apply(lambda x: x.split('@')[1])

print(filtered_df)

```

Результат будет выглядеть так:

```

id name age email email_domain

2 3 Charlie 35 charlie@example.com example.com

```

Сохранение данных обратно в базу

После обработки данных в Pandas мы можем сохранить их обратно в базу данных. Для этого Pandas предоставляет метод `to_sql`:

```python

# Сохранение отфильтрованных данных в новую таблицу filtered_users

filtered_df.to_sql('filtered_users', engine, if_exists='replace', index=False)

print("Данные сохранены в таблицу filtered_users.")

```

Теперь в базе данных появилась новая таблица `filtered_users`, содержащая обработанные данные.

Работа с ORM

Для более сложных сценариев SQLAlchemy поддерживает ORM, позволяющий работать с таблицами как с Python-классами.

Определим класс для таблицы `users`:

```python

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class User(Base):

__tablename__ = 'users'

id = Column(Integer, primary_key=True)

name = Column(String)

age = Column(Integer)

email = Column(String)

# Создаем сессию для работы с ORM

Session = sessionmaker(bind=engine)

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: Фильтрация данных по динамическому запросу