Database/tables_init.py - master - LazyCoder/Demka

Database/tables_init.py
95 строк3 KB
Andreev A
сделана демка
05 фев 2025, 09:59
05 фев 2025, 09:5982c9c0c
from Database.connection import Base, engine, SessionLocal
from openpyxl import load_workbook

from Entities.MaterialType import MaterialType
from Entities.ProductType import ProductType
from Entities.Product import Product
from Entities.Partner import Partner
from Entities.SalesHistory import SalesHistory

base_directory = "Excel/"
product_type_file = base_directory + "Product_type_import.xlsx"
products_file = base_directory + "Products_import.xlsx"
partner_file = base_directory + "Partners_import.xlsx"
sales_history_file = base_directory + "Partner_products_import.xlsx"
material_type_file = base_directory + "Material_type_import.xlsx"

Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
session = SessionLocal()

try:
    wb =load_workbook(product_type_file)
    sheet = wb.active
    for row in sheet.iter_rows(min_row=2, values_only=True):
        production_type, coefficient = row

        if not production_type or not coefficient:
            continue

        session.add(ProductType(production_type = production_type, coefficient = coefficient))
    session.commit()
    print("ProductType imported")

    # Импорт продуктов
    wb = load_workbook(products_file)
    sheet = wb.active
    for row in sheet.iter_rows(min_row=2, values_only=True):
        product_type_name, name, article_number, minimum_cost = row
        product_type = session.query(ProductType).filter_by(production_type=product_type_name).first()
        session.add(Product(
            product_type_id=product_type.id,
            name=name,
            article_number=article_number,
            minimum_cost=minimum_cost))
    session.commit()
    print("Продукты добавлены.")

    # Импорт партнёров
    wb = load_workbook(partner_file)
    sheet = wb.active
    for row in sheet.iter_rows(min_row=2, values_only=True):
        partner_type, name, director, email, phone, legal_address, inn, rating = row
        session.add(Partner(
            partner_type=partner_type,
            name=name,
            director=director,
            email=email,
            phone=phone,
            legal_address=legal_address,
            inn=inn,
            rating=rating
        ))
    session.commit()
    print("Партнёры добавлены.")

    # Импорт истории продаж
    wb = load_workbook(sales_history_file)
    sheet = wb.active
    for row in sheet.iter_rows(min_row=2, values_only=True):
        product_name, partner_name, quantity, sale_date = row
        product = session.query(Product).filter_by(name=product_name).first()
        partner = session.query(Partner).filter_by(name=partner_name).first()
        session.add(SalesHistory(
            partner_id=partner.id,
            product_id=product.id,
            quantity=quantity,
            sale_date=sale_date
        ))
    session.commit()
    print("История продаж добавлена.")

    wb = load_workbook(material_type_file)
    sheet = wb.active
    for row in sheet.iter_rows(min_row=2, values_only=True):
        material_type, defective_percentage = row

        session.add(MaterialType(material_type = material_type, defective_percentage = defective_percentage))
    session.commit()
    print("material_type imported")

except Exception as e:
    session.rollback()
    print(f"Ошибка при импорте данных: {e}")
finally:
    session.close()