Каким образом изменяется информация в реляционных БД?

Question

claude-haiku-4.5 · Accepted Answer

## Изменение информации в реляционных БД: CRUD операции

**CRUD** (Create, Read, Update, Delete) — это четыре основные операции для работы с данными в реляционных базах данных. Каждая операция соответствует SQL команде и подчиняется принципам ACID.

## 1. CREATE: Вставка данных (INSERT)

Добавление новых записей в таблицу.

```sql
-- Базовый INSERT
INSERT INTO users (id, name, email) 
VALUES (1, 'Alice', 'alice@example.com');

-- Множественная вставка
INSERT INTO users (id, name, email) VALUES
(2, 'Bob', 'bob@example.com'),
(3, 'Charlie', 'charlie@example.com');

-- С автоинкрементом
INSERT INTO users (name, email) 
VALUES ('Dave', 'dave@example.com');
-- id будет сгенерирован автоматически

-- Вставка из другой таблицы
INSERT INTO users_archived (name, email)
SELECT name, email FROM users WHERE created_at < '2020-01-01';
```

На Python:

```python
from sqlalchemy import text
from sqlalchemy.orm import Session

# Способ 1: Raw SQL
db.execute(text(
    "INSERT INTO users (name, email) VALUES (:name, :email)"
), {"name": "Alice", "email": "alice@example.com"})
db.commit()

# Способ 2: ORM (SQLAlchemy)
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    name = Column(String)
    email = Column(String)

def create_user(db: Session, name: str, email: str):
    user = User(name=name, email=email)
    db.add(user)
    db.commit()
    db.refresh(user)  # получить id
    return user

user = create_user(db, "Alice", "alice@example.com")
print(user.id)  # будет выведен присвоенный id

# Способ 3: Batch insert (для производительности)
def bulk_insert_users(db: Session, users_data):
    users = [User(name=u['name'], email=u['email']) for u in users_data]
    db.add_all(users)  # добавить все сразу
    db.commit()

bulk_insert_users(db, [
    {'name': 'Bob', 'email': 'bob@example.com'},
    {'name': 'Charlie', 'email': 'charlie@example.com'}
])
```

## 2. READ: Чтение данных (SELECT)

Получение данных из таблицы с фильтрацией и сортировкой.

```sql
-- Получить все столбцы
SELECT * FROM users;

-- Получить конкретные столбцы
SELECT name, email FROM users;

-- С условием WHERE
SELECT * FROM users WHERE email = 'alice@example.com';

-- С сортировкой
SELECT * FROM users ORDER BY name ASC;

-- С агрегацией
SELECT 
    COUNT(*) as total_users,
    COUNT(DISTINCT email) as unique_emails
FROM users;

-- С JOIN
SELECT u.name, o.order_id, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.id = 1;
```

На Python:

```python
from sqlalchemy import select, func

# Способ 1: Raw SQL
result = db.execute(text(
    "SELECT * FROM users WHERE email = :email"
), {"email": "alice@example.com"})
user = result.fetchone()

# Способ 2: ORM query
def get_user_by_email(db: Session, email: str):
    return db.query(User).filter(User.email == email).first()

user = get_user_by_email(db, "alice@example.com")
print(user.name)  # Alice

# Способ 3: Modern ORM (SQLAlchemy 2.0+)
def get_all_users(db: Session):
    stmt = select(User).order_by(User.name)
    return db.execute(stmt).scalars().all()

# Способ 4: С фильтрацией
def get_users_by_domain(db: Session, domain: str):
    stmt = select(User).where(User.email.endswith(domain))
    return db.execute(stmt).scalars().all()

users = get_users_by_domain(db, "example.com")

# Способ 5: Агрегация
def get_user_count(db: Session):
    return db.query(func.count(User.id)).scalar()

count = get_user_count(db)  # 3

# Способ 6: JOIN
def get_user_with_orders(db: Session, user_id: int):
    user = db.query(User).filter(User.id == user_id).first()
    orders = db.query(Order).filter(Order.user_id == user_id).all()
    return {"user": user, "orders": orders}
```

## 3. UPDATE: Обновление данных (UPDATE)

Изменение существующих записей.

```sql
-- Обновить один столбец
UPDATE users SET email = 'newemail@example.com' WHERE id = 1;

-- Обновить несколько столбцов
UPDATE users SET email = 'new@example.com', updated_at = NOW() 
WHERE name = 'Alice';

-- Обновить с вычислением
UPDATE orders SET total_price = quantity * unit_price 
WHERE status = 'pending';

-- Обновить с JOIN (PostgreSQL)
UPDATE orders o
SET status = 'shipped'
FROM users u
WHERE o.user_id = u.id AND u.is_premium = TRUE;
```

На Python:

```python
from sqlalchemy import update

# Способ 1: ORM
def update_user_email(db: Session, user_id: int, new_email: str):
    user = db.query(User).filter(User.id == user_id).first()
    if user:
        user.email = new_email
        db.commit()
        db.refresh(user)
        return user
    return None

user = update_user_email(db, 1, "newemail@example.com")

# Способ 2: Bulk update (эффективнее)
def bulk_update_status(db: Session, user_ids: list, status: str):
    stmt = update(User).where(
        User.id.in_(user_ids)
    ).values(status=status)
    db.execute(stmt)
    db.commit()

# Способ 3: Raw SQL
db.execute(text(
    "UPDATE users SET email = :email WHERE id = :id"
), {"email": "new@example.com", "id": 1})
db.commit()

# Способ 4: Conditional update
def increment_view_count(db: Session, post_id: int):
    stmt = update(Post).where(
        Post.id == post_id
    ).values(view_count=Post.view_count + 1)
    db.execute(stmt)
    db.commit()
```

## 4. DELETE: Удаление данных (DELETE)

Удаление записей из таблицы.

```sql
-- Удалить конкретную запись
DELETE FROM users WHERE id = 1;

-- Удалить с условием
DELETE FROM logs WHERE created_at < '2020-01-01';

-- Удалить все (осторожно!)
DELETE FROM users;  -- удаляет ВСЕ

-- Удалить с JOIN (каскадное удаление вручную)
DELETE FROM orders
WHERE user_id IN (
    SELECT id FROM users WHERE is_banned = TRUE
);
```

На Python:

```python
from sqlalchemy import delete

# Способ 1: ORM
def delete_user(db: Session, user_id: int):
    user = db.query(User).filter(User.id == user_id).first()
    if user:
        db.delete(user)
        db.commit()
        return True
    return False

# Способ 2: Bulk delete
def delete_old_logs(db: Session, days: int):
    from datetime import datetime, timedelta
    
    cutoff_date = datetime.now() - timedelta(days=days)
    stmt = delete(Log).where(Log.created_at < cutoff_date)
    db.execute(stmt)
    db.commit()

# Способ 3: Raw SQL
db.execute(text(
    "DELETE FROM users WHERE id = :id"
), {"id": 1})
db.commit()

# Способ 4: Каскадное удаление через отношения
class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    name = Column(String)
    # Каскадное удаление: удаляет все заказы при удалении пользователя
    orders = relationship("Order", cascade="all, delete-orphan")

class Order(Base):
    __tablename__ = "orders"
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey("users.id"))

# При удалении пользователя все его заказы тоже удаляются
user = db.query(User).get(1)
db.delete(user)
db.commit()  # заказы тоже удаляются
```

## ACID свойства при изменении данных

**Atomicity** (Атомарность): Операция либо полностью выполняется, либо откатывается.

```python
def transfer_money(db: Session, from_user: int, to_user: int, amount: float):
    try:
        # Снять со счета
        db.execute(text(
            "UPDATE accounts SET balance = balance - :amount WHERE user_id = :user_id"
        ), {"amount": amount, "user_id": from_user})
        
        # Пополнить счет
        db.execute(text(
            "UPDATE accounts SET balance = balance + :amount WHERE user_id = :user_id"
        ), {"amount": amount, "user_id": to_user})
        
        db.commit()  # обе операции выполнены
    except Exception as e:
        db.rollback()  # откатить обе операции
        raise e
```

**Consistency** (Консистентность): БД остается в корректном состоянии.

```python
# Constraints и triggers гарантируют консистентность
class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    email = Column(String, unique=True)  # UNIQUE constraint
    age = Column(Integer, CheckConstraint('age >= 18'))  # CHECK
    created_at = Column(DateTime, default=datetime.now)

# Попытка добавить дубликат email
try:
    db.add(User(email="alice@example.com"))
    db.add(User(email="alice@example.com"))  # ошибка!
    db.commit()
except IntegrityError:
    db.rollback()
```

**Isolation** (Изоляция): Параллельные транзакции не влияют друг на друга.

```python
# Уровни изоляции
db.execute(text("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;"))
db.execute(text("SET TRANSACTION ISOLATION LEVEL READ COMMITTED;"))
db.execute(text("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;"))
db.execute(text("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;"))
```

**Durability** (Устойчивость): Коммитнутые данные сохраняются даже при сбое.

```python
db.commit()  # данные записаны на диск
# Даже если сервер выключится, данные сохранятся
```

## Транзакции для複ных операций

```python
from sqlalchemy import begin

def create_order_with_items(db: Session, user_id: int, items: list):
    with db.begin():  # открыть транзакцию
        # Создать заказ
        order = Order(user_id=user_id, total=0)
        db.add(order)
        db.flush()  # flush чтобы получить id
        
        total = 0
        # Добавить товары
        for item in items:
            order_item = OrderItem(
                order_id=order.id,
                product_id=item['product_id'],
                quantity=item['quantity']
            )
            db.add(order_item)
            total += item['price'] * item['quantity']
        
        # Обновить сумму
        order.total = total
        db.commit()  # все или ничего

try:
    create_order_with_items(db, 1, [
        {'product_id': 1, 'quantity': 2, 'price': 10.0},
        {'product_id': 2, 'quantity': 1, 'price': 20.0}
    ])
except Exception as e:
    print(f"Order creation failed: {e}")
    # Все операции откачены
```

## Optimistic vs Pessimistic Locking

```python
# Pessimistic: блокируем строку
def update_with_lock(db: Session, user_id: int):
    user = db.query(User).with_for_update().filter(
        User.id == user_id
    ).first()  # другие транзакции ждут
    
    # Безопасно обновляем
    user.balance -= 100
    db.commit()

# Optimistic: проверяем версию
class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    balance = Column(Float)
    version = Column(Integer, default=0)  # версия

def update_optimistic(db: Session, user_id: int, expected_version: int):
    user = db.query(User).filter(User.id == user_id).first()
    
    if user.version != expected_version:
        raise ValueError("Data was modified by another process")
    
    user.balance -= 100
    user.version += 1  # инкремент версии
    db.commit()
```

## Сравнение операций

| Операция | Команда | Скорость | Рискованность |
|----------|---------|----------|---------------|
| Create | INSERT | быстро | низкий |
| Read | SELECT | быстро | нет риска |
| Update | UPDATE | среднее | средний |
| Delete | DELETE | быстро | ВЫСОКИЙ |

## Best Practices

```python
# 1. Всегда используй WHERE для UPDATE и DELETE
db.execute(text("UPDATE users SET ... WHERE id = :id"))  # ✓
# db.execute(text("UPDATE users SET ..."))  # ✗ опасно!

# 2. Используй транзакции для сложных операций
with db.begin():
    # несколько операций
    db.add(...)
    db.delete(...)

# 3. Проверяй результаты
stmt = update(User).where(User.id == 1).values(name="New")
result = db.execute(stmt)
print(f"Updated {result.rowcount} rows")

# 4. Используй ORM вместо raw SQL (когда возможно)
# Защита от SQL injection, type safety

# 5. Добавляй индексы на часто используемые WHERE колонки
class User(Base):
    __tablename__ = "users"
    email = Column(String, index=True)
```

Операция	Команда	Скорость	Рискованность
Create	INSERT	быстро	низкий
Read	SELECT	быстро	нет риска
Update	UPDATE	среднее	средний
Delete	DELETE	быстро	ВЫСОКИЙ

Каким образом изменяется информация в реляционных БД?

Комментарии (1)

Изменение информации в реляционных БД: CRUD операции

1. CREATE: Вставка данных (INSERT)

2. READ: Чтение данных (SELECT)

3. UPDATE: Обновление данных (UPDATE)

4. DELETE: Удаление данных (DELETE)

ACID свойства при изменении данных

Транзакции для複ных операций

Optimistic vs Pessimistic Locking

Сравнение операций

Best Practices