Документация — PyQt5 + MySQL

Разбор функций

def load_products(self):
  ①while self.layout.count():
      item = self.layout.takeAt(0)
      if item.widget():
          item.widget().deleteLater()

  ②sort = self.sort_comboBox.currentText()
  if "возрастанию" in sort:
      sort_param = "ORDER BY quantity ASC"
  elif "убыванию" in sort:
      sort_param = "ORDER BY quantity DESC"
  else:
      sort_param = ""

  ③search_param = f"%{self.search_lineEdit.text().lower()}%"
  supplier = self.category_comboBox.currentText()

  connection = db_connect()
  try:
      with connection.cursor() as cursor:
          ④if supplier == "Все поставщики":
              cursor.execute(
                  f"SELECT ... WHERE lower(name) LIKE %s {sort_param}",
                  ⑤(search_param,))
          else:
              cursor.execute(
                  f"SELECT ... WHERE supplier=%s AND lower(name) LIKE %s {sort_param}",
                  (supplier, search_param))
          products = cursor.fetchall()

      ⑥for data in products:
          card = ProductCard(data)
          card.edit_request.connect(self.edit_product)
          self.layout.addWidget(card)
      self.layout.addStretch()
  finally:
      connection.close()

def save(self):
  ①status = self.status_comboBox.currentData()
  pp       = self.pp_comboBox.currentData()
  recipient= self.recipient_comboBox.currentData()
  order_date = self.order_date_dateEdit.date()
                  .toPyDate()

  ②if not all([status, pp, recipient]):
      QMessageBox.warning(...)
      return
  if not self.items:
      QMessageBox.warning(...)
      return

  connection = db_connect()
  try:
      with connection.cursor() as cursor:
          ③if self.order_id:
              cursor.execute(
                  "UPDATE orders SET ... WHERE order_number=%s",
                  (..., self.order_id))
              cursor.execute(
                  "DELETE FROM order_products WHERE order_id=%s",
                  (self.order_id,))
              ④order_id = self.order_id
          else:
              cursor.execute(
                  "INSERT INTO orders (...) VALUES (...)", (...))
              ⑤order_id = cursor.lastrowid

          ⑥for pid, _, qty in self.items:
              cursor.execute(
                  "INSERT INTO order_products VALUES (%s,%s,%s)",
                  (pid, order_id, qty))

      connection.commit()
      self.accept()
  except Exception as e:
      print(e)
      QMessageBox.critical(...)
  finally:
      connection.close()

def load_order_data(self):
  connection = db_connect()
  try:
      with connection.cursor() as cursor:
          cursor.execute(
              "SELECT ... FROM orders WHERE order_number=%s",
              (self.order_id,))
          ①o = cursor.fetchone()

          ②self.date_edit.setDate(
              QDate.fromString(str(o[0]), 'yyyy-MM-dd'))

          self.receipt_edit.setText(str(o[4]))

          ③for box, val in [
              (self.pp_comboBox, o[2]),
              (self.recipient_comboBox, o[3]),
              (self.status_comboBox, o[5])
          ]:
              idx = box.findData(val)
              ④if idx >= 0:
                  box.setCurrentIndex(idx)

          ⑤cursor.execute("""
              SELECT op.product_id,
                CONCAT_WS(' - ',p.article,p.product_name),
                op.quantity
              FROM order_products op
              JOIN products p ON op.product_id=p.product_id
              WHERE op.order_id=%s""", (self.order_id,))
          ⑥for pid, pname, qty in cursor.fetchall():
              self.items.append((pid, pname, qty))
          self.refresh_list()
  finally:
      connection.close()

①connection = None
try:
    ②connection = db_connect()
    ③with connection.cursor() as cursor:
        cursor.execute("SELECT ...")
        ④data = cursor.fetchall()

    ⑤connection.commit()
    self.accept()

⑥except Exception as e:
    print(e)
    QMessageBox.critical(self, "Ошибка", "...")

⑦finally:
    if connection:
        connection.close()

def setup_ui_by_role(self):
  ①self.add_btn.hide()
  self.delete_btn.hide()
  self.orders_btn.hide()
  self.search_edit.hide()
  self.sort_box.hide()
  self.filter_box.hide()

  ②if self.role_id == 2:  # Администратор
      self.add_btn.show()
      self.delete_btn.show()
      self.orders_btn.show()
      self.search_edit.show()
      self.sort_box.show()
      self.filter_box.show()

  ③elif self.role_id == 3:  # Менеджер
      self.orders_btn.show()
      self.search_edit.show()
      self.sort_box.show()
      self.filter_box.show()
  # role_id == None → гость → всё скрыто

def auth_login(self):
  login    = self.login_edit.text()
  password = self.password_edit.text()

  connection = None
  try:
      connection = db_connect()
      with connection.cursor() as cursor:
          cursor.execute(
              'SELECT full_name, role_id FROM users'
              ' WHERE login=%s AND password=%s',
              (login, password))
          ①user_data = cursor.fetchone()

      ②if user_data:
          full_name, role_id = user_data
          ③from products_window import ProductsWindow
          ④self.pw = ProductsWindow(full_name, role_id)
          self.pw.show()
          self.close()
      else:
          QMessageBox.critical(..., 'Неверный логин')

  except Exception as e:
      print(e)
      QMessageBox.critical(..., 'Нет подключения')
  finally:
      if connection:
          connection.close()

Структура проекта

# Корень проекта
main.py              # Запуск
db.py                # Подключение к БД
login_window.py      # Авторизация
products_window.py   # Главное окно
product_card.py      # Карточка товара
add_product_form.py  # Форма товара
orders_window.py     # Окно заказов
order_card.py        # Карточка заказа
add_order_form.py    # Форма заказа
UI/                  # Сгенерированные UI файлы

products       product_id, article, product_name,
               unit, price, supplier_id,
               manufacture_id, category_id,
               discount, quantity_in_stock,
               description, photo_path

orders         order_number, order_date,
               delivery_date, pickup_point_id,
               user_recipient_id, receipt_code,
               order_status_id

order_products order_products_id, product_id,
               order_id, quantity

order_statuses status_id, status_name
pickup_points  pickup_point_id, postal_code,
               city, street, house
suppliers      supplier_id, supplier_name
manufacture    manufacture_id, manufacture_name
category       category_id, category
users          user_id, full_name, login,
               password, role_id

db.py

import pymysql

def db_connect():
    connection = pymysql.connect(
        host='localhost',
        port=3306,
        user='root',
        passwd='root',
        database='demka_1'
    )
    return connection

main.py

import sys
import traceback
from PyQt5.QtWidgets import QApplication
from login_window import LoginWindow

def except_hook(exc_type, exc_value, exc_traceback):
    # PyQt5 глотает ошибки — это перехватывает их и выводит в консоль
    error_text = "".join(traceback.format_exception(exc_type, exc_value, exc_traceback))
    print(error_text)

if __name__ == "__main__":
    app = QApplication(sys.argv)
    sys.excepthook = except_hook
    window = LoginWindow()
    window.show()
    sys.exit(app.exec_())

login_window.py

from PyQt5.QtWidgets import QWidget, QMessageBox
from UI.login_ui import Ui_Dialog
from db import db_connect

class LoginWindow(QWidget, Ui_Dialog):
    def __init__(self, parent=None):
        super().__init__(parent)
        self.setupUi(self)
        self.login_pushButton.clicked.connect(self.auth_login)

    def auth_login(self):
        login = self.login_lineEdit.text()
        password = self.password_lineEdit.text()
        connection = None
        try:
            connection = db_connect()
            with connection.cursor() as cursor:
                cursor.execute(
                    'SELECT full_name, role_id FROM users WHERE login=%s AND password=%s',
                    (login, password)
                )
                user_data = cursor.fetchone()

            if user_data:
                full_name, role_id = user_data
                from products_window import ProductsWindow
                self.products_window = ProductsWindow(full_name=full_name, role_id=role_id)
                self.products_window.show()   # self. — иначе окно закроется сразу
                self.close()
            else:
                QMessageBox.critical(self, 'Ошибка', 'Неверный логин или пароль')

        except Exception as e:
            print(e)
            QMessageBox.critical(self, 'Ошибка', 'Нет подключения к БД')
        finally:
            if connection:
                connection.close()

products_window.py

from PyQt5.QtWidgets import QMainWindow
from UI.main_ui import Ui_MainWindow
from db import db_connect
from product_card import ProductCard

class ProductsWindow(QMainWindow, Ui_MainWindow):
    def __init__(self, full_name="Гость", role_id=None):
        super().__init__()
        self.setupUi(self)
        self.role_id = role_id
        self.selected_card = None
        self.selected_product_id = None
        self.user_fio.setText(full_name)

        self.setup_ui_by_role()
        self.load_filter()    # 1. заполнить комбобоксы
        self.load_sort()
        self.load_products()   # 2. загрузить данные

        # 3. подключить сигналы ПОСЛЕ заполнения — иначе сработают раньше времени
        self.category_comboBox.currentTextChanged.connect(self.load_products)
        self.sort_comboBox.currentTextChanged.connect(self.load_products)
        self.search_lineEdit.textChanged.connect(self.load_products)
        self.add_product_pushButton.clicked.connect(self.add_product)
        self.delete_product_pushButton.clicked.connect(self.delete_product)
        self.logout_pushButton.clicked.connect(self.logout)
        self.orders_pushButton.clicked.connect(self.open_orders)

    def load_products(self):
        self.selected_card = None
        self.selected_product_id = None

        # Очистка layout
        while self.products_list_verticalLayout.count():
            item = self.products_list_verticalLayout.takeAt(0)
            if item.widget():
                item.widget().deleteLater()

        supplier_filter = self.category_comboBox.currentText()
        search = self.search_lineEdit.text().lower()
        search_param = f"%{search}%"

        sort = self.sort_comboBox.currentText()
        if "возрастанию" in sort:
            sort_param = "ORDER BY p.quantity_in_stock ASC"
        elif "убыванию" in sort:
            sort_param = "ORDER BY p.quantity_in_stock DESC"
        else:
            sort_param = ""

        base_query = """SELECT p.product_id, p.article, p.product_name, p.unit, p.price,
            s.supplier_name, m.manufacture_name, c.category, p.discount,
            p.quantity_in_stock, p.description, p.photo_path
            FROM products p
            JOIN suppliers s ON p.supplier_id = s.supplier_id
            JOIN manufacture m ON p.manufacture_id = m.manufacture_id
            JOIN category c ON p.category_id = c.category_id"""

        connection = db_connect()
        try:
            with connection.cursor() as cursor:
                if supplier_filter == "Все поставщики" or not supplier_filter:
                    cursor.execute(f"{base_query} WHERE lower(p.product_name) LIKE %s {sort_param}", (search_param,))
                else:
                    cursor.execute(f"{base_query} WHERE s.supplier_name=%s AND lower(p.product_name) LIKE %s {sort_param}",
                                   (supplier_filter, search_param))
                products = cursor.fetchall()

            for data in products:
                card = ProductCard(data)
                card.edit_request.connect(self.edit_product)
                card.select_request.connect(self.select_product)
                self.products_list_verticalLayout.addWidget(card)
            self.products_list_verticalLayout.addStretch()

        finally:
            connection.close()

    def setup_ui_by_role(self):
        # Сначала всё скрыть
        self.search_lineEdit.hide()
        self.category_comboBox.hide()
        self.sort_comboBox.hide()
        self.add_product_pushButton.hide()
        self.delete_product_pushButton.hide()
        self.orders_pushButton.hide()

        if self.role_id == 2:  # Администратор
            self.search_lineEdit.show()
            self.category_comboBox.show()
            self.sort_comboBox.show()
            self.add_product_pushButton.show()
            self.delete_product_pushButton.show()
            self.orders_pushButton.show()
        elif self.role_id == 3:  # Менеджер
            self.search_lineEdit.show()
            self.category_comboBox.show()
            self.sort_comboBox.show()
            self.orders_pushButton.show()

    def load_filter(self):
        self.category_comboBox.clear()
        self.category_comboBox.addItem("Все поставщики")
        connection = db_connect()
        try:
            with connection.cursor() as cursor:
                cursor.execute("SELECT supplier_id, supplier_name FROM suppliers")
                for sid, sname in cursor.fetchall():
                    self.category_comboBox.addItem(sname, sid)
        finally:
            connection.close()

    def load_sort(self):
        self.sort_comboBox.addItem("Без сортировки")
        self.sort_comboBox.addItem("Количество по возрастанию")
        self.sort_comboBox.addItem("Количество по убыванию")

    def select_product(self, card):
        if self.selected_card:
            self.selected_card.set_selected(False)
        self.selected_card = card
        self.selected_product_id = card.product_id
        card.set_selected(True)

    def add_product(self):
        from add_product_form import AddProductForm
        form = AddProductForm(parent=self)
        if form.exec_():
            self.load_products()

    def edit_product(self, product_id):
        if self.role_id != 2:
            return
        from add_product_form import AddProductForm
        form = AddProductForm(product_id=product_id, parent=self)
        if form.exec_():
            self.load_products()

    def delete_product(self):
        if not self.selected_product_id:
            return
        connection = db_connect()
        try:
            with connection.cursor() as cursor:
                cursor.execute("SELECT COUNT(*) FROM order_products WHERE product_id=%s", (self.selected_product_id,))
                if cursor.fetchone()[0] > 0:
                    QMessageBox.critical(self, "Ошибка", "Товар участвует в заказах")
                    return
                cursor.execute("DELETE FROM products WHERE product_id=%s", (self.selected_product_id,))
                connection.commit()
        finally:
            connection.close()
        self.selected_product_id = None
        self.selected_card = None
        self.load_products()

    def open_orders(self):
        from orders_window import OrdersWindow
        from PyQt5.QtCore import Qt
        self.orders_window = OrdersWindow(role_id=self.role_id)
        self.orders_window.setWindowModality(Qt.ApplicationModal)
        self.orders_window.show()

    def logout(self):
        from login_window import LoginWindow
        self.login_window = LoginWindow()
        self.login_window.show()
        self.close()

product_card.py

from PyQt5 import QtGui
from PyQt5.QtCore import pyqtSignal
from PyQt5.QtWidgets import QFrame
from UI.product_form_ui import Ui_Frame

class ProductCard(QFrame, Ui_Frame):
    edit_request   = pyqtSignal(int)   # передаёт product_id при двойном клике
    select_request = pyqtSignal(object) # передаёт саму карточку для выделения

    def __init__(self, data, parent=None):
        super().__init__(parent)
        self.setupUi(self)
        (self.product_id, article, product_name, unit, price,
         supplier_name, manufacture_name, category, discount,
         quantity_in_stock, description, photo_path) = data

        self.setup_labels(article, product_name, price, discount,
                          quantity_in_stock, supplier_name)
        self.set_photo(photo_path)

    def setup_labels(self, article, product_name, price, discount,
                       quantity_in_stock, supplier_name):
        self.article_label.setText(article)
        self.name_label.setText(product_name)
        self.price_label.setText(f"{price} руб.")
        self.discount_label.setText(f"{discount}%")
        self.stock_label.setText(f"{quantity_in_stock} шт.")
        self.supplier_label.setText(supplier_name)

    def set_photo(self, photo_path):
        if photo_path:
            self.photo_label.setPixmap(QtGui.QPixmap(photo_path))
        else:
            self.photo_label.setPixmap(QtGui.QPixmap("import/picture.png"))

    def set_selected(self, selected: bool):
        if selected:
            self.setStyleSheet("QFrame { border: 2px solid #4fc3f7; }")
        else:
            self.setStyleSheet("")

    def mousePressEvent(self, event):
        self.select_request.emit(self)           # выделение
        self.edit_request.emit(self.product_id)   # редактирование
        super().mousePressEvent(event)

add_product_form.py

from PyQt5.QtWidgets import QDialog, QMessageBox, QFileDialog
from UI.add_product_ui import Ui_Dialog
from db import db_connect

class AddProductForm(QDialog, Ui_Dialog):
    def __init__(self, parent=None, product_id=None):
        super().__init__(parent)
        self.setupUi(self)
        self.product_id = product_id

        self.load_comboboxes()
        if self.product_id:
            self.load_data()

        self.save_pushButton.clicked.connect(self.save)
        self.cancel_pushButton.clicked.connect(self.reject)
        self.photo_pushButton.clicked.connect(self.select_photo)

    def load_comboboxes(self):
        connection = db_connect()
        try:
            with connection.cursor() as cursor:
                cursor.execute("SELECT category_id, category FROM category")
                for cid, cname in cursor.fetchall():
                    self.category_comboBox.addItem(cname, cid)

                cursor.execute("SELECT manufacture_id, manufacture_name FROM manufacture")
                for mid, mname in cursor.fetchall():
                    self.manufacture_comboBox.addItem(mname, mid)

                cursor.execute("SELECT supplier_id, supplier_name FROM suppliers")
                for sid, sname in cursor.fetchall():
                    self.supplier_comboBox.addItem(sname, sid)
        finally:
            connection.close()

    def load_data(self):
        connection = db_connect()
        try:
            with connection.cursor() as cursor:
                cursor.execute("SELECT * FROM products WHERE product_id=%s", (self.product_id,))
                p = cursor.fetchone()

            self.article_lineEdit.setText(p[1])
            self.name_lineEdit.setText(p[2])
            self.price_lineEdit.setText(str(p[4]))
            self.discount_spinBox.setValue(float(p[8]))
            self.stock_spinBox.setValue(int(p[9]))
            self.description_textEdit.setPlainText(p[10] or "")
            self.photo_lineEdit.setText(p[11] or "")

            # Установка комбобоксов по ID
            for box, val in [
                (self.supplier_comboBox, p[5]),
                (self.manufacture_comboBox, p[6]),
                (self.category_comboBox, p[7])
            ]:
                idx = box.findData(val)
                if idx >= 0:
                    box.setCurrentIndex(idx)
        finally:
            connection.close()

    def select_photo(self):
        path, _ = QFileDialog.getOpenFileName(self, "Выберите фото", "", "Images (*.jpg *.png)")
        if path:
            self.photo_lineEdit.setText(path)

    def save(self):
        article  = self.article_lineEdit.text()
        name     = self.name_lineEdit.text()
        price    = float(self.price_lineEdit.text() or 0)
        discount = float(self.discount_spinBox.value())
        stock    = int(self.stock_spinBox.value())
        desc     = self.description_textEdit.toPlainText()
        photo    = self.photo_lineEdit.text()
        category = self.category_comboBox.currentData()
        manufacture = self.manufacture_comboBox.currentData()
        supplier = self.supplier_comboBox.currentData()

        connection = db_connect()
        try:
            with connection.cursor() as cursor:
                if self.product_id:
                    cursor.execute("""UPDATE products SET article=%s, product_name=%s,
                        unit='шт.', price=%s, supplier_id=%s, manufacture_id=%s,
                        category_id=%s, discount=%s, quantity_in_stock=%s,
                        description=%s, photo_path=%s WHERE product_id=%s""",
                        (article, name, price, supplier, manufacture, category,
                         discount, stock, desc, photo, self.product_id))
                else:
                    cursor.execute("""INSERT INTO products (article, product_name, unit, price,
                        supplier_id, manufacture_id, category_id, discount,
                        quantity_in_stock, description, photo_path)
                        VALUES (%s,%s,'шт.',%s,%s,%s,%s,%s,%s,%s,%s)""",
                        (article, name, price, supplier, manufacture, category,
                         discount, stock, desc, photo))
            connection.commit()
            QMessageBox.information(self, "Успех", "Сохранено")
            self.accept()
        except Exception as e:
            print(e)
            QMessageBox.critical(self, "Ошибка", "Не удалось сохранить")
        finally:
            connection.close()

orders_window.py

from PyQt5.QtWidgets import QMainWindow
from UI.orders_form_ui import Ui_MainWindow
from db import db_connect
from order_card import OrderCard

class OrdersWindow(QMainWindow, Ui_MainWindow):
    def __init__(self, role_id=None):
        super().__init__()
        self.setupUi(self)
        self.role_id = role_id
        self.load_orders()
        self.add_order_pushButton.clicked.connect(self.add_order)

    def load_orders(self):
        while self.orders_verticalLayout.count():
            item = self.orders_verticalLayout.takeAt(0)
            if item.widget():
                item.widget().deleteLater()

        connection = db_connect()
        try:
            with connection.cursor() as cursor:
                cursor.execute("""SELECT o.order_number, o.order_date, o.delivery_date,
                    os.status_name,
                    CONCAT_WS(', ', TRIM(pp.postal_code), TRIM(pp.city),
                              TRIM(pp.street), TRIM(pp.house)) as address
                    FROM orders o
                    JOIN order_statuses os ON o.order_status_id = os.status_id
                    JOIN pickup_points pp ON o.pickup_point_id = pp.pickup_point_id""")
                orders = cursor.fetchall()

        finally:
            connection.close()

        for data in orders:
            card = OrderCard(data)
            card.card_request.connect(self.edit_order)
            self.orders_verticalLayout.addWidget(card)
        self.orders_verticalLayout.addStretch()

    def add_order(self):
        from add_order_form import AddOrderForm
        form = AddOrderForm(parent=self)
        if form.exec_():
            self.load_orders()

    def edit_order(self, order_id):
        from add_order_form import AddOrderForm
        form = AddOrderForm(parent=self, order_id=order_id)
        if form.exec_():
            self.load_orders()

order_card.py

from PyQt5.QtCore import pyqtSignal
from PyQt5.QtWidgets import QFrame
from UI.order_card_ui import Ui_Frame

class OrderCard(QFrame, Ui_Frame):
    card_request = pyqtSignal(int)

    def __init__(self, data, parent=None):
        super().__init__(parent)
        self.setupUi(self)
        (self.order_id, order_date, delivery_date, status, address) = data
        self.number_label.setText(f"Заказ №{self.order_id}")
        self.status_label.setText(status)
        self.address_label.setText(address)
        self.order_date_label.setText(str(order_date))
        self.delivery_date_label.setText(str(delivery_date))

    def mousePressEvent(self, event):
        self.card_request.emit(self.order_id)
        super().mousePressEvent(event)

add_order_form.py

from PyQt5.QtCore import QDate
from PyQt5.QtWidgets import QDialog, QMessageBox
from UI.add_order_ui import Ui_Dialog
from db import db_connect

class AddOrderForm(QDialog, Ui_Dialog):
    def __init__(self, parent=None, order_id=None):
        super().__init__(parent)
        self.setupUi(self)
        self.order_id = order_id
        self.items = []   # список (product_id, text, qty)

        self.load_comboboxes()
        if self.order_id:
            self.load_order_data()

        self.add_product_pushButton.clicked.connect(self.add_item)
        self.delete_product_pushButton.clicked.connect(self.delete_item)
        self.save_pushButton.clicked.connect(self.save)
        self.cancel_pushButton.clicked.connect(self.reject)

    def load_comboboxes(self):
        self.status_comboBox.addItem("Выберите статус", None)
        self.recipient_comboBox.addItem("Выберите получателя", None)
        self.pp_comboBox.addItem("Выберите адрес", None)
        connection = db_connect()
        try:
            with connection.cursor() as cursor:
                cursor.execute("SELECT status_id, status_name FROM order_statuses")
                for sid, sname in cursor.fetchall():
                    self.status_comboBox.addItem(sname, sid)

                cursor.execute("SELECT product_id, article, product_name FROM products")
                for pid, art, pname in cursor.fetchall():
                    self.product_comboBox.addItem(f"{art} - {pname}", pid)

                cursor.execute("SELECT user_id, full_name FROM users")
                for uid, fname in cursor.fetchall():
                    self.recipient_comboBox.addItem(fname, uid)

                cursor.execute("""SELECT pickup_point_id,
                    CONCAT_WS(', ', TRIM(postal_code), TRIM(city), TRIM(street), TRIM(house))
                    FROM pickup_points""")
                for ppid, addr in cursor.fetchall():
                    self.pp_comboBox.addItem(addr, ppid)
        finally:
            connection.close()

    def load_order_data(self):
        connection = db_connect()
        try:
            with connection.cursor() as cursor:
                cursor.execute("""SELECT order_date, delivery_date, pickup_point_id,
                    user_recipient_id, receipt_code, order_status_id
                    FROM orders WHERE order_number=%s""", (self.order_id,))
                o = cursor.fetchone()

                self.order_date_dateEdit.setDate(QDate.fromString(str(o[0]), 'yyyy-MM-dd'))
                self.delivery_date_dateEdit.setDate(QDate.fromString(str(o[1]), 'yyyy-MM-dd'))
                self.receipt_lineEdit.setText(str(o[4]))

                for box, val in [
                    (self.pp_comboBox, o[2]),
                    (self.recipient_comboBox, o[3]),
                    (self.status_comboBox, o[5])
                ]:
                    idx = box.findData(val)
                    if idx >= 0:
                        box.setCurrentIndex(idx)

                # Загрузка товаров заказа
                cursor.execute("""SELECT op.product_id,
                    CONCAT_WS(' - ', p.article, p.product_name), op.quantity
                    FROM order_products op
                    JOIN products p ON op.product_id = p.product_id
                    WHERE op.order_id=%s""", (self.order_id,))
                for pid, pname, qty in cursor.fetchall():
                    self.items.append((pid, pname, qty))
                self.refresh_list()
        finally:
            connection.close()

    def add_item(self):
        product_id = self.product_comboBox.currentData()
        if not product_id:
            return
        text = self.product_comboBox.currentText()
        qty = self.qty_spinBox.value()
        self.items.append((product_id, text, qty))
        self.refresh_list()

    def delete_item(self):
        row = self.products_listWidget.currentRow()
        if row < 0:
            return
        self.items.pop(row)
        self.refresh_list()

    def refresh_list(self):
        self.products_listWidget.clear()
        for _, name, qty in self.items:
            self.products_listWidget.addItem(f"{name} x {qty} шт.")

    def save(self):
        status    = self.status_comboBox.currentData()
        pp        = self.pp_comboBox.currentData()
        recipient = self.recipient_comboBox.currentData()
        receipt   = self.receipt_lineEdit.text()
        order_date    = self.order_date_dateEdit.date().toPyDate()
        delivery_date = self.delivery_date_dateEdit.date().toPyDate()

        if not all([status, pp, recipient]):
            QMessageBox.warning(self, "Ошибка", "Заполните все поля")
            return
        if not self.items:
            QMessageBox.warning(self, "Ошибка", "Добавьте товары")
            return

        connection = db_connect()
        try:
            with connection.cursor() as cursor:
                if self.order_id:
                    cursor.execute("""UPDATE orders SET order_date=%s, delivery_date=%s,
                        pickup_point_id=%s, user_recipient_id=%s, receipt_code=%s,
                        order_status_id=%s WHERE order_number=%s""",
                        (order_date, delivery_date, pp, recipient, receipt, status, self.order_id))
                    cursor.execute("DELETE FROM order_products WHERE order_id=%s", (self.order_id,))
                    order_id = self.order_id
                else:
                    cursor.execute("""INSERT INTO orders (order_date, delivery_date,
                        pickup_point_id, user_recipient_id, receipt_code, order_status_id)
                        VALUES (%s,%s,%s,%s,%s,%s)""",
                        (order_date, delivery_date, pp, recipient, receipt, status))
                    order_id = cursor.lastrowid  # ID только что созданного заказа

                for pid, _, qty in self.items:
                    cursor.execute(
                        "INSERT INTO order_products (product_id, order_id, quantity) VALUES (%s,%s,%s)",
                        (pid, order_id, qty))

            connection.commit()
            QMessageBox.information(self, "Успех", "Сохранено")
            self.accept()
        except Exception as e:
            print(e)
            QMessageBox.critical(self, "Ошибка", "Не удалось сохранить")
        finally:
            connection.close()

Ключевые паттерны

# Окно закроется сразу:
window = ProductsWindow()
window.show()

# Правильно:
self.products_window = ProductsWindow()
self.products_window.show()

QDialog   → exec_()   # блокирует, возвращает 0/1
QMainWindow → show()  # не блокирует

result = form.exec_()
if result:   # 1 = accept(), 0 = reject()
    self.load_products()

# 1. Объявление в классе карточки:
edit_request = pyqtSignal(int)

# 2. Отправка сигнала:
def mousePressEvent(self, event):
    self.edit_request.emit(self.product_id)
    super().mousePressEvent(event)

# 3. Подписка при создании карточки:
card.edit_request.connect(self.edit_product)

# 4. Обработчик принимает product_id:
def edit_product(self, product_id):
    ...

# addItem(текст, данные) — данные = ID из БД
comboBox.addItem("Название", supplier_id)

# Установить по ID при загрузке:
idx = comboBox.findData(supplier_id)
if idx >= 0:   # не if idx: — 0 тоже валидный индекс!
    comboBox.setCurrentIndex(idx)

# Получить выбранный ID:
supplier_id = comboBox.currentData()

# Из БД в виджет:
dateEdit.setDate(
    QDate.fromString(str(date_from_db), 'yyyy-MM-dd')
)

# Из виджета в Python:
py_date = dateEdit.date().toPyDate()

# ID строки только что вставленной через INSERT
cursor.execute("INSERT INTO orders ...")
order_id = cursor.lastrowid

# Нужен чтобы привязать order_products к заказу:
cursor.execute(
    "INSERT INTO order_products ... VALUES (%s,%s,%s)",
    (product_id, order_id, qty)
)

while self.layout.count():
    item = self.layout.takeAt(0)
    if item.widget():
        item.widget().deleteLater()
# item — QLayoutItem, не виджет!
# item.widget() — достать виджет из него

# ВАЖНО: connect ПОСЛЕ load_filter
# иначе addItem → сигнал → load_products раньше времени
self.load_filter()
self.load_sort()
self.load_products()
self.comboBox.currentTextChanged.connect(self.load_products)

from PyQt5.QtCore import Qt
self.orders_window = OrdersWindow()
self.orders_window.setWindowModality(
    Qt.ApplicationModal  # блокирует все окна
)
self.orders_window.show()

path, _ = QFileDialog.getOpenFileName(
    self,
    "Выберите фото",   # заголовок
    "",                # начальная директория
    "Images (*.jpg *.png)"  # фильтр
)
if path:
    self.photo_lineEdit.setText(path)

SQL справочник

-- SELECT
SELECT поля FROM таблица WHERE условие

-- INSERT
INSERT INTO таблица (поля)
VALUES (%s, %s, %s)

-- UPDATE (без скобок после SET!)
UPDATE таблица SET поле=%s, поле2=%s
WHERE id=%s

-- DELETE
DELETE FROM таблица WHERE id=%s

-- % = любые символы
WHERE name LIKE '%бот%'   -- содержит
WHERE name LIKE 'бот%'    -- начинается

-- Через параметры (нельзя f-строку с %s!)
search_param = f"%{text.lower()}%"
cursor.execute(
    "WHERE lower(name) LIKE %s",
    (search_param,)   # запятая обязательна!
)

# ORDER BY нельзя передать как %s параметр
# Только через f-строку:
sort = "ORDER BY quantity_in_stock ASC"
cursor.execute(f"SELECT ... {sort}")

CONCAT_WS(', ',
    TRIM(postal_code),
    TRIM(city),
    TRIM(street),
    TRIM(house)
) as address
-- TRIM убирает пробелы
-- CONCAT_WS пропускает NULL поля

-- AND выполняется раньше OR (как * перед +)
-- Без скобок — НЕВЕРНО:
WHERE supplier='Kari' AND name LIKE '%x%'
    OR desc LIKE '%x%'
-- Читается: (supplier AND name) OR desc

-- Со скобками — ВЕРНО:
WHERE supplier='Kari'
    AND (name LIKE '%x%' OR desc LIKE '%x%')

SELECT op.product_id,
    CONCAT_WS(' - ', p.article, p.product_name),
    op.quantity
FROM order_products op
JOIN products p ON op.product_id = p.product_id
WHERE op.order_id = %s

Частые ошибки

# Это не кортеж — просто скобки:
(product_id)

# Кортеж — нужна запятая:
(product_id,)

# Неверно — 0 это falsy:
if idx:
    box.setCurrentIndex(idx)

# Верно:
if idx >= 0:
    box.setCurrentIndex(idx)

# Неверно — курсор закрыт:
with cursor as c:
    c.execute(...)
data = c.fetchall()  # ошибка!

# Верно — внутри with:
with cursor as c:
    c.execute(...)
    data = c.fetchall()

# Ничего не делает — это сравнение:
supplier_filter == ""

# Присвоение:
supplier_filter = ""

# Это не Python:
if photo_path not Null:

# Правильно:
if photo_path:
if photo_path is not None:

# Неверно — строка не QPixmap:
label.setPixmap(photo_path)

# Верно:
from PyQt5 import QtGui
label.setPixmap(QtGui.QPixmap(photo_path))

# Неверно — вызовет load_products:
def cancel(self):
    self.accept()

# Верно:
def cancel(self):
    self.reject()

# PyCharm добавляет автоматически — удаляй:
from django.db import connection
from itertools import product
from re import search

# Qt Designer пишет относительно .ui файла:
"../import/Icon.jpg"  # неверно при запуске

# Верно — относительно рабочей директории:
"import/Icon.jpg"

MySQL Workbench — лайфхаки

1. Правой кнопкой на таблицу
2. Table Data Import Wizard
3. Выбрать CSV файл
4. Проверить маппинг колонок
5. Next → Next → Finish

-- Правой кнопкой на таблицу →
-- Send to SQL Editor →
-- Insert Statement
-- Получишь готовый шаблон:
INSERT INTO `table` (`col1`, `col2`)
VALUES ('val1', 'val2');

-- Показать все поля таблицы:
DESCRIBE products;

-- Показать CREATE TABLE:
SHOW CREATE TABLE products;

-- Все таблицы в БД:
SHOW TABLES;

-- Если удалил все строки и хочешь
-- чтоб ID снова с 1:
TRUNCATE TABLE order_products;
-- TRUNCATE удаляет всё и сбрасывает счётчик
-- DELETE FROM — только удаляет, ID продолжается

-- Если мешают foreign key при вставке:
SET FOREIGN_KEY_CHECKS = 0;

INSERT INTO ...

SET FOREIGN_KEY_CHECKS = 1;
-- Не забудь включить обратно!

Excel / LibreOffice — подготовка данных

=ВПР(что_ищем; где_ищем; номер_столбца; 0)

Пример: по названию поставщика найти его ID
=ВПР(A2; $F$2:$G$10; 2; 0)
  A2         — название поставщика в текущей строке
  $F$2:$G$10 — таблица ID|Название ($ = фиксация)
  2          — взять второй столбец (ID)
  0          — точное совпадение

Дано: таблица товаров со столбцом "Поставщик" (текст)
Нужно: столбец supplier_id (число)

1. На отдельном листе сделай справочник:
   ID | Поставщик
   1  | Kari
   2  | Обувь для вас

2. В столбце supplier_id пиши:
=ВПР(C2; Лист2.$A$1:$B$10; 1; 0)
   C2 = ячейка с названием поставщика

3. Протяни формулу вниз на все строки

Без $ — диапазон сдвигается при протягивании:
=ВПР(A2; F2:G10; 2; 0)  ← неверно

С $ — диапазон фиксирован:
=ВПР(A2; $F$2:$G$10; 2; 0)  ← верно

Быстро поставить $: выдели диапазон в формуле → F4

После ВПР в столбце формулы, а нам нужны числа:

1. Выдели столбец с ВПР
2. Ctrl+C
3. Правой кнопкой → Специальная вставка
4. Выбрать "Значения" (только числа/текст)
5. OK

Теперь можно удалить справочник — значения останутся

1. Файл → Сохранить как → CSV
2. Разделитель: точка с запятой или запятая
3. Кодировка: UTF-8

В Workbench:
Table Data Import Wizard → выбрать CSV
Проверить что колонки совпадают!

ВПР вернёт #Н/Д если не нашёл — это сломает импорт

Защита от ошибки:
=ЕСЛИОШИБКА(ВПР(A2; $F$2:$G$5; 2; 0); "")

Или в английском Excel:
=IFERROR(VLOOKUP(A2; $F$2:$G$5; 2; 0); "")

Протянуть формулу на весь столбец:
Кликни на ячейку с формулой →
двойной клик на квадратик в правом нижнем углу

Найти и заменить (чистка данных):
Ctrl+H → заменить лишние пробелы, символы

Удалить дубликаты:
Данные → Удалить дубликаты

Можно собрать SQL прямо в ячейке:
="INSERT INTO products VALUES ("
 &A2& ", '"&B2&"', '"&C2&"')"

Протяни вниз → скопируй столбец →
вставь в Workbench → выполни

Удобно для быстрого заполнения таблиц

Порядок заполнения БД из Excel

ВАЖНО: заполнять в правильном порядке — сначала справочники, потом зависимые таблицы

1. suppliers      ← нет зависимостей
2. manufacture    ← нет зависимостей
3. category       ← нет зависимостей
4. users          ← нет зависимостей
5. pickup_points  ← нет зависимостей
6. order_statuses ← нет зависимостей
7. products       ← зависит от suppliers, manufacture, category
8. orders         ← зависит от users, pickup_points, order_statuses
9. order_products ← зависит от orders, products

Если нарушить порядок — FOREIGN KEY ошибка при INSERT