May 19 at 00:59

Как я решил демоэкзамен по разработке: система учёта партнёров на Flask + PostgreSQL

Easy

Invite pending

Категории:

Python
Flask
PostgreSQL
SQL
Образование
Веб-разработка

В рамках демоэкзамена по модулю «Разработка, администрирование и защита баз данных» мне нужно было реализовать подсистему для работы с партнёрами, включая расчёт скидок, вывод истории продаж и учёт параметров продукции.

В этой статье я расскажу, как я реализовал это задание на Python с использованием Flask, PostgreSQL и HTML-шаблонов.

Постановка задачи

Необходимо было реализовать веб-приложение с такими функциями:

Список партнёров (название, тип, директор, телефон, рейтинг, скидка)
Добавление и редактирование данных о партнёре
Просмотр истории продаж конкретного партнёра
Реализация метода расчёта необходимого материала для продукции
Визуальный интерфейс, соответствующий руководству по стилю

Структура базы данных

Используемые таблицы:

partners — данные о партнёрах
partner_types — типы партнёров
products — продукция
sales — таблица продаж
product_types — для коэффициента расчёта материала
material_types — для процента брака

Схема связей:

partners.type_id → partner_types.id
sales.partner_id → partners.id
sales.product_id → products.id
products.type_id → product_types.id

Скрипт создания БД create_db.sql

-- Типы партнёров
CREATE TABLE partner_types (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- Партнёры
CREATE TABLE partners (
    id SERIAL PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    type_id INTEGER REFERENCES partner_types(id),
    address TEXT,
    director VARCHAR(100),
    phone VARCHAR(20),
    email VARCHAR(100),
    inn VARCHAR(20) UNIQUE,
    rating INTEGER CHECK (rating >= 0),
    logo_path VARCHAR(255)
);

-- Типы продукции
CREATE TABLE product_types (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    coefficient NUMERIC(5, 2)
);

-- Продукция
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(150),
    product_type_id INTEGER REFERENCES product_types(id),
    min_price NUMERIC(10, 2) NOT NULL
);

-- История продаж
CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    partner_id INTEGER REFERENCES partners(id),
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER CHECK (quantity > 0),
    sale_date DATE NOT NULL DEFAULT CURRENT_DATE
);

-- Типы материалов
CREATE TABLE material_types (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    defect_percent NUMERIC(5, 2)
);

-- Продукция, которую продаёт партнёр (import)
CREATE TABLE partner_products (
    id SERIAL PRIMARY KEY,
    partner_id INTEGER REFERENCES partners(id),
    product_id INTEGER REFERENCES products(id)
);

Вставка данных в БД

import pandas as pd
import psycopg2

# Подключение к БД
conn = psycopg2.connect(
    dbname="postgres",
    user="postgres",
    password="",
    host="localhost",
    port=5429
)
cur = conn.cursor()

# Очистка всех таблиц
cur.execute("""
    TRUNCATE TABLE
        sales,
        partner_products,
        partners,
        partner_types,
        products,
        product_types,
        material_types
    RESTART IDENTITY CASCADE
""")
conn.commit()
print("Все таблицы очищены")

def get_safe(row, column_name, default=None):
    return row.get(column_name, default)

def normalize_columns(df):
    df.columns = [c.strip().lower() for c in df.columns]
    return df

# === 1. Импорт partner_types ===
partner_types = ["ООО", "ЗАО", "ПАО", "ОАО"]
for pt in partner_types:
    cur.execute("INSERT INTO partner_types (name) VALUES (%s) ON CONFLICT DO NOTHING", (pt,))
print("Импорт partner_types завершён")

# === 2. Импорт product_types ===
df_types = pd.read_excel("Product_type_import.xlsx")
df_types = normalize_columns(df_types)
for _, row in df_types.iterrows():
    cur.execute("""
        INSERT INTO product_types (name, coefficient)
        VALUES (%s, %s)
    """, (
        get_safe(row, "тип продукции"),
        get_safe(row, "коэффициент типа продукции")
    ))
print("Импорт product_types завершён")

# === 3. Импорт products ===
df_products = pd.read_excel("Products_import.xlsx")
df_products = normalize_columns(df_products)
for _, row in df_products.iterrows():
    cur.execute("""
        INSERT INTO products (name, min_price)
        VALUES (%s, %s)
    """, (
        str(get_safe(row, "наименование продукции")).strip(),
        get_safe(row, "минимальная стоимость для партнера")
    ))
print("Импорт products завершён")

# === 4. Импорт material_types ===
df_materials = pd.read_excel("Material_type_import.xlsx")
df_materials = normalize_columns(df_materials)
for _, row in df_materials.iterrows():
    cur.execute("""
        INSERT INTO material_types (name, defect_percent)
        VALUES (%s, %s)
    """, (
        get_safe(row, "тип материала"),
        get_safe(row, "процент брака материала") or get_safe(row, "процент брака")
    ))
print("Импорт material_types завершён")

# === 5. Импорт partners ===
df_partners = pd.read_excel("Partners_import.xlsx")
df_partners = normalize_columns(df_partners)
cur.execute("SELECT id, name FROM partner_types")
type_map = {name.lower(): id for id, name in cur.fetchall()}

for _, row in df_partners.iterrows():
    type_name = str(get_safe(row, "тип партнера")).strip().lower()
    type_id = type_map.get(type_name)

    if not type_id:
        print(f" Не найден тип партнёра: {type_name}, строка пропущена")
        continue

    cur.execute("""
        INSERT INTO partners (name, type_id, address, director, phone, email, inn, rating)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
    """, (
        str(get_safe(row, "наименование партнера")).strip(),
        type_id,
        get_safe(row, "юридический адрес партнера"),
        get_safe(row, "директор"),
        get_safe(row, "телефон партнера"),
        get_safe(row, "электронная почта партнера"),
        get_safe(row, "инн"),
        get_safe(row, "рейтинг")
    ))
print("Импорт partners завершён")

# === 6. Импорт sales ===
df_sales = pd.read_excel("Partner_products_import.xlsx")
df_sales = normalize_columns(df_sales)

# Маппинг названий на ID
cur.execute("SELECT id, name FROM partners")
partner_map = {name.strip().lower(): id for id, name in cur.fetchall()}
cur.execute("SELECT id, name FROM products")
product_map = {name.strip().lower(): id for id, name in cur.fetchall()}

for _, row in df_sales.iterrows():
    partner_name = str(row.get("наименование партнера", "")).strip().lower()
    product_name = str(row.get("продукция", "")).strip().lower()
    quantity = row.get("количество продукции", 0)
    sale_date = row.get("дата продажи")

    partner_id = partner_map.get(partner_name)
    product_id = product_map.get(product_name)

    if not partner_id or not product_id:
        print(f" Пропущена строка: партнёр={partner_name}, продукция={product_name}")
        continue

    cur.execute("""
        INSERT INTO sales (partner_id, product_id, quantity, sale_date)
        VALUES (%s, %s, %s, %s)
    """, (partner_id, product_id, quantity, sale_date))
print("Импорт sales завершён")

# === 7. Импорт partner_products ===
for _, row in df_sales.iterrows():
    partner_name = str(row.get("наименование партнера", "")).strip().lower()
    product_name = str(row.get("продукция", "")).strip().lower()

    partner_id = partner_map.get(partner_name)
    product_id = product_map.get(product_name)

    if not partner_id or not product_id:
        print(f" Пропущена связка: {partner_name} ⇔ {product_name}")
        continue

    cur.execute("""
        INSERT INTO partner_products (partner_id, product_id)
        VALUES (%s, %s)
    """, (partner_id, product_id))
print("Импорт partner_products завершён")

# Завершение
conn.commit()
cur.close()
conn.close()
print("Импорт завершён успешно!")

Основной backend:

app.py

from flask import Flask, render_template, request, redirect, url_for, flash
import psycopg2
from partner_discount import calculate_discount
from material_calc import calculate_required_material, get_product_coef, get_material_defect_percent

app = Flask(__name__)
app.secret_key = "secret-key"

def get_connection():
    return psycopg2.connect(
        dbname="postgres",
        user="postgres",
        password="",
        host="localhost",
        port=5429
    )

@app.route("/")
def index():
    conn = get_connection()
    cur = conn.cursor()

    cur.execute("""
        SELECT p.id, p.name, pt.name, p.director, p.phone, p.rating,
               COALESCE(SUM(s.quantity * pr.min_price), 0) as total_sales
        FROM partners p
        JOIN partner_types pt ON p.type_id = pt.id
        LEFT JOIN sales s ON s.partner_id = p.id
        LEFT JOIN products pr ON s.product_id = pr.id
        GROUP BY p.id, pt.name
    """)
    result = cur.fetchall()

    partners = []
    for pid, name, type_name, director, phone, rating, total_sales in result:
        partners.append({
            "id": pid,
            "name": name,
            "type": type_name,
            "director": director,
            "phone": phone,
            "rating": rating,
            "discount": calculate_discount(total_sales)
        })

    cur.close()
    conn.close()

    return render_template("index.html", partners=partners)

@app.route("/partner/add", methods=["GET", "POST"])
def add_partner():
    if request.method == "POST":
        try:
            name = request.form['name']
            type_id = int(request.form['type_id'])
            address = request.form['address']
            director = request.form['director']
            phone = request.form['phone']
            email = request.form['email']
            inn = request.form['inn']
            rating = int(request.form['rating'])

            if rating < 0:
                raise ValueError("Рейтинг должен быть неотрицательным")

            conn = get_connection()
            cur = conn.cursor()
            cur.execute("""
                INSERT INTO partners (name, type_id, address, director, phone, email, inn, rating)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
            """, (name, type_id, address, director, phone, email, inn, rating))
            conn.commit()
            cur.close()
            conn.close()
            return redirect(url_for("index"))
        except Exception as e:
            flash("Ошибка: " + str(e), "error")
            return redirect(url_for("add_partner"))

    return render_template("edit_partner.html", mode="add", partner=None)

@app.route("/partner/<int:id>/edit", methods=["GET", "POST"])
def edit_partner(id):
    conn = get_connection()
    cur = conn.cursor()

    if request.method == "POST":
        try:
            name = request.form['name']
            type_id = int(request.form['type_id'])
            address = request.form['address']
            director = request.form['director']
            phone = request.form['phone']
            email = request.form['email']
            inn = request.form['inn']
            rating = int(request.form['rating'])

            if rating < 0:
                raise ValueError("Рейтинг должен быть неотрицательным")

            cur.execute("""
                UPDATE partners SET name=%s, type_id=%s, address=%s, director=%s,
                    phone=%s, email=%s, inn=%s, rating=%s WHERE id=%s
            """, (name, type_id, address, director, phone, email, inn, rating, id))
            conn.commit()
            return redirect(url_for("index"))
        except Exception as e:
            flash("Ошибка: " + str(e), "error")
            return redirect(url_for("edit_partner", id=id))

    cur.execute("SELECT name, type_id, address, director, phone, email, inn, rating FROM partners WHERE id=%s", (id,))
    row = cur.fetchone()
    partner = {
        'name': row[0],
        'type_id': row[1],
        'address': row[2],
        'director': row[3],
        'phone': row[4],
        'email': row[5],
        'inn': row[6],
        'rating': row[7]
    }
    cur.close()
    conn.close()
    return render_template("edit_partner.html", mode="edit", partner=partner)

@app.route("/partner/<int:id>/sales")
def partner_sales(id):
    conn = get_connection()
    cur = conn.cursor()

    cur.execute("SELECT name FROM partners WHERE id = %s", (id,))
    partner_name = cur.fetchone()
    if not partner_name:
        flash("Партнёр не найден", "error")
        return redirect(url_for("index"))

    cur.execute("""
        SELECT pr.name, s.quantity, s.sale_date
        FROM sales s
        JOIN products pr ON s.product_id = pr.id
        WHERE s.partner_id = %s
        ORDER BY s.sale_date DESC
    """, (id,))
    sales = cur.fetchall()
    cur.close()
    conn.close()

    return render_template("partner_sales.html", partner_name=partner_name[0], sales=sales)

if __name__ == "__main__":
    app.run(debug=True)

Расчёт скидки: partner_discount.py

def calculate_discount(total_sales: float) -> int:
    if total_sales < 10_000:
        return 0
    elif total_sales < 50_000:
        return 5
    elif total_sales < 300_000:
        return 10
    return 15

Расчёт необходимого материала: material_calc.py

def get_product_coef(product_type_id):
    conn = get_connection()
    cur = conn.cursor()
    cur.execute("SELECT coefficient FROM product_types WHERE id = %s", (product_type_id,))
    row = cur.fetchone()
    cur.close()
    conn.close()
    return row[0] if row else None

def get_material_defect_percent(material_type_id):
    conn = get_connection()
    cur = conn.cursor()
    cur.execute("SELECT defect_percent FROM material_types WHERE id = %s", (material_type_id,))
    row = cur.fetchone()
    cur.close()
    conn.close()
    return row[0] if row else None


def calculate_required_material(product_type_id: int, material_type_id: int,
                                 quantity: int, param1: float, param2: float) -> int:
    if quantity <= 0 or param1 <= 0 or param2 <= 0:
        return -1

    coef = get_product_coef(product_type_id)
    defect_percent = get_material_defect_percent(material_type_id)

    if coef == -1 or defect_percent == -1:
        return -1

    base_amount = quantity * param1 * param2 * coef
    total_with_defect = base_amount * (1 + defect_percent / 100)
    return int(round(total_with_defect))

HTML-шаблоны

Главная: index.html

<!DOCTYPE html>
<html lang="ru">
  <head>
    <meta charset="UTF-8" />
    <title>Список партнёров | Мастер пол</title>
    <link rel="icon" href="/static/favicon.ico" />
    <style>
      body {
        font-family: "Segoe UI", sans-serif;
        background-color: #ffffff;
        margin: 0;
        padding: 0;
      }
      .history-btn {
        background-color: #67ba80;
        color: white;
        text-decoration: none;
        padding: 6px 12px;
        font-size: 14px;
        border-radius: 5px;
        display: inline-block;
        transition: background-color 0.3s ease;
      }
      .actions {
        display: flex;
        flex-direction: column;
        align-items: flex-end;
        justify-content: center;
        gap: 6px;
      }

      .discount {
        font-size: 18px;
        font-weight: bold;
        color: #67ba80;
      }

      .history-btn:hover {
        background-color: #5aa16f;
      }

      header {
        background-color: #f4e8d3;
        padding: 20px;
        display: flex;
        align-items: center;
        justify-content: space-between;
      }
      header img {
        height: 50px;
        margin-right: 20px;
      }
      header h1 {
        font-size: 24px;
        margin: 0;
        flex-grow: 1;
      }
      .container {
        max-width: 800px;
        margin: 20px auto;
        padding: 0 20px;
      }
      .card {
        border: 1px solid #ccc;
        border-radius: 8px;
        padding: 15px 20px;
        background-color: #f4e8d3;
        display: flex;
        justify-content: space-between;
        margin-bottom: 15px;
        cursor: pointer;
      }
      .card .info {
        line-height: 1.6;
      }
      .card .discount {
        font-size: 24px;
        font-weight: bold;
        color: #67ba80;
        align-self: center;
      }
      .add-btn {
        background-color: #67ba80;
        color: white;
        border: none;
        padding: 10px 20px;
        font-size: 16px;
        border-radius: 6px;
        cursor: pointer;
        margin-bottom: 20px;
      }
      .add-btn:hover {
        background-color: #5aa16f;
      }
    </style>
  </head>
  <body>
    <header>
      <div style="display: flex; align-items: center">
        <img src="/static/logo.png" alt="Логотип Мастер пол" />
        <h1>Список партнёров</h1>
      </div>
      <a href="{{ url_for('add_partner') }}">
        <button class="add-btn">Добавить партнёра</button>
      </a>
    </header>
    <div class="container">
      {% for partner in partners %}
      <div class="card">
        <div
          class="info"
          onclick="window.location.href='/partner/{{ partner.id }}/edit'"
        >
          <strong>{{ partner.type }} | {{ partner.name }}</strong><br />
          Директор {{ partner.director }}<br />
          {{ partner.phone }}<br />
          Рейтинг: {{ partner.rating }}
        </div>
        <div class="actions">
          <a
            href="{{ url_for('partner_sales', id=partner.id) }}"
            class="history-btn"
          >
            📊 История продаж
          </a>
          <div class="discount">{{ partner.discount }}%</div>
        </div>
      </div>

      {% endfor %}
    </div>
  </body>
</html>

Форма добавления/редактирования: edit_partner.html

<!DOCTYPE html>
<html lang="ru">
  <head>
    <meta charset="UTF-8" />
    <title>
      {{ 'Редактировать партнёра' if mode == 'edit' else 'Добавить партнёра' }}
      | Мастер пол
    </title>
    <link rel="icon" href="/static/favicon.ico" />
    <style>
      body {
        font-family: "Segoe UI", sans-serif;
        background-color: #ffffff;
        margin: 0;
        padding: 0;
      }
      header {
        background-color: #f4e8d3;
        padding: 20px;
        display: flex;
        align-items: center;
      }
      header img {
        height: 50px;
        margin-right: 20px;
      }
      header h1 {
        font-size: 24px;
        margin: 0;
      }
      .container {
        max-width: 600px;
        margin: 30px auto;
        padding: 20px;
        background-color: #f4e8d3;
        border-radius: 8px;
      }
      label {
        display: block;
        margin-top: 10px;
        font-weight: bold;
      }
      input,
      select {
        width: 100%;
        padding: 8px;
        margin-top: 4px;
        border-radius: 4px;
        border: 1px solid #ccc;
        font-size: 14px;
      }
      button {
        margin-top: 20px;
        padding: 10px 20px;
        background-color: #67ba80;
        color: white;
        border: none;
        border-radius: 6px;
        font-size: 16px;
        cursor: pointer;
      }
      button:hover {
        background-color: #57a86e;
      }
      .back-link {
        display: inline-block;
        margin-top: 20px;
        text-decoration: none;
        color: #333;
      }
    </style>
  </head>
  <body>
    <header>
      <img src="/static/logo.png" alt="Логотип Мастер пол" />
      <h1>
        {{ 'Редактировать партнёра' if mode == 'edit' else 'Добавить партнёра'
        }}
      </h1>
    </header>
    <div class="container">
      <form method="POST">
        <label>Наименование:</label>
        <input
          name="name"
          value="{{ partner.name if partner else '' }}"
          required
        />

        <label>Тип партнёра (id):</label>
        <input
          name="type_id"
          type="number"
          value="{{ partner.type_id if partner else '' }}"
          required
        />

        <label>Юридический адрес:</label>
        <input
          name="address"
          value="{{ partner.address if partner else '' }}"
        />

        <label>ФИО директора:</label>
        <input
          name="director"
          value="{{ partner.director if partner else '' }}"
        />

        <label>Телефон:</label>
        <input name="phone" value="{{ partner.phone if partner else '' }}" />

        <label>Email:</label>
        <input
          name="email"
          type="email"
          value="{{ partner.email if partner else '' }}"
        />

        <label>ИНН:</label>
        <input name="inn" value="{{ partner.inn if partner else '' }}" />

        <label>Рейтинг (целое ≥ 0):</label>
        <input
          name="rating"
          type="number"
          min="0"
          value="{{ partner.rating if partner else 0 }}"
          required
        />

        <button type="submit">Сохранить</button>
      </form>
      <a href="{{ url_for('index') }}" class="back-link"
        >← Назад к списку партнёров</a
      >
    </div>
  </body>
</html>

История продаж: partner_sales.html

<!DOCTYPE html>
<html lang="ru">
  <head>
    <meta charset="UTF-8" />
    <title>История продаж | Мастер пол</title>
    <link rel="icon" href="/static/favicon.ico" />
    <style>
      body {
        font-family: "Segoe UI", sans-serif;
        background-color: #ffffff;
        margin: 0;
      }
      header {
        background-color: #f4e8d3;
        padding: 20px;
        display: flex;
        align-items: center;
      }
      header img {
        height: 50px;
        margin-right: 20px;
      }
      h1 {
        font-size: 24px;
        margin: 0;
      }
      .container {
        max-width: 800px;
        margin: 20px auto;
        padding: 0 20px;
      }
      table {
        width: 100%;
        border-collapse: collapse;
        margin-top: 20px;
      }
      th,
      td {
        border: 1px solid #ccc;
        padding: 10px;
      }
      th {
        background-color: #f4e8d3;
      }
      a.back-link {
        display: inline-block;
        margin-top: 20px;
        text-decoration: none;
        color: #333;
      }
    </style>
  </head>
  <body>
    <header>
      <img src="/static/logo.png" alt="Логотип Мастер пол" />
      <h1>История реализации</h1>
    </header>
    <div class="container">
      <table>
        <thead>
          <tr>
            <th>Продукция</th>
            <th>Количество</th>
            <th>Дата продажи</th>
          </tr>
        </thead>
        <tbody>
          {% for sale in sales %}
          <tr>
            <td>{{ sale.product }}</td>
            <td>{{ sale.quantity }}</td>
            <td>{{ sale.date }}</td>
          </tr>
          {% endfor %}
        </tbody>
      </table>
      <a class="back-link" href="{{ url_for('index') }}">← Назад к партнёрам</a>
    </div>
  </body>
</html>

История покупок history.html

<!DOCTYPE html>
<html lang="ru">
  <head>
    <meta charset="UTF-8" />
    <title>История продаж | Мастер пол</title>
    <link rel="icon" href="/static/favicon.ico" />
    <style>
      body {
        font-family: "Segoe UI", sans-serif;
        background-color: #ffffff;
        margin: 0;
      }
      header {
        background-color: #f4e8d3;
        padding: 20px;
        display: flex;
        align-items: center;
      }
      header img {
        height: 50px;
        margin-right: 20px;
      }
      h1 {
        font-size: 24px;
        margin: 0;
      }
      .container {
        max-width: 800px;
        margin: 20px auto;
        padding: 0 20px;
      }
      table {
        width: 100%;
        border-collapse: collapse;
        margin-top: 20px;
      }
      th,
      td {
        border: 1px solid #ccc;
        padding: 10px;
      }
      th {
        background-color: #f4e8d3;
      }
      a.back-link {
        display: inline-block;
        margin-top: 20px;
        text-decoration: none;
        color: #333;
      }
    </style>
  </head>
  <body>
    <header>
      <img src="/static/logo.png" alt="Логотип Мастер пол" />
      <h1>История реализации</h1>
    </header>
    <div class="container">
      <table>
        <thead>
          <tr>
            <th>Продукция</th>
            <th>Количество</th>
            <th>Дата продажи</th>
          </tr>
        </thead>
        <tbody>
          {% for sale in sales %}
          <tr>
            <td>{{ sale.product }}</td>
            <td>{{ sale.quantity }}</td>
            <td>{{ sale.date }}</td>
          </tr>
          {% endfor %}
        </tbody>
      </table>
      <a class="back-link" href="{{ url_for('index') }}">← Назад к партнёрам</a>
    </div>
  </body>
</html>

Примеры данных

При проверке системы использовались следующие тестовые данные:

Партнёр: ООО Паркет, тип: ОАО, рейтинг: 5
Продажа: 5 штук продукта по цене 2000 → total_sales = 10000 → скидка 5%

Выводы

В результате реализовано:

Полноценное CRUD-приложение
Функции расчёта скидок и материала с подключением к БД
Простая адаптивная верстка
Обработка ошибок и подсказки пользователю

Статья может быть полезна студентам, готовящимся к демоэкзамену, а также начинающим разработчикам Flask-приложений.

Tags:

гиа

Hubs: