pyqt/databases/widget_mapper.py

import os
import sys
from PyQt5.QtCore import QSize, Qt
from PyQt5.QtSql import QSqlDatabase, QSqlTableModel, QSqlQuery
from PyQt5.QtWidgets import (QApplication, QComboBox, QDataWidgetMapper,
                             QDoubleSpinBox, QFormLayout, QLabel, QLineEdit,
                             QMainWindow, QSpinBox, QWidget, QVBoxLayout,
                             QHBoxLayout, QPushButton, QMessageBox, QStatusBar)

basedir = os.path.dirname(__file__)


class MainWindow(QMainWindow):

    def __init__(self):
        super().__init__()
        self.setWindowTitle("Track Editor")

        # Initialize database first
        if not self.initialize_database():
            return

        form = QFormLayout()

        # Create widgets
        self.track_id = QSpinBox()
        self.track_id.setRange(0, 2147483647)
        self.track_id.setDisabled(True)
        self.name = QLineEdit()
        self.album = QComboBox()
        self.genre = QComboBox()
        self.composer = QLineEdit()

        self.milliseconds = QSpinBox()
        self.milliseconds.setRange(0, 2147483647)
        self.milliseconds.setSingleStep(1)

        self.bytes = QSpinBox()
        self.bytes.setRange(0, 2147483647)
        self.bytes.setSingleStep(1)

        self.unit_price = QDoubleSpinBox()
        self.unit_price.setRange(0, 999)
        self.unit_price.setSingleStep(0.01)
        self.unit_price.setPrefix("$")

        # Populate ComboBoxes
        self.populate_combo_boxes()

        # Add form fields
        form.addRow(QLabel("Track ID"), self.track_id)
        form.addRow(QLabel("Track name"), self.name)
        form.addRow(QLabel("Album"), self.album)
        form.addRow(QLabel("Genre"), self.genre)
        form.addRow(QLabel("Composer"), self.composer)
        form.addRow(QLabel("Milliseconds"), self.milliseconds)
        form.addRow(QLabel("Bytes"), self.bytes)
        form.addRow(QLabel("Unit price"), self.unit_price)

        # Setup model and mapper
        self.model = QSqlTableModel(db=self.db)
        self.model.setTable("Track")
        self.model.select()

        self.mapper = QDataWidgetMapper()
        self.mapper.setModel(self.model)
        self.mapper.addMapping(self.track_id, 0)
        self.mapper.addMapping(self.name, 1)
        self.mapper.addMapping(self.album, 2)
        self.mapper.addMapping(self.genre, 3)
        self.mapper.addMapping(self.composer, 5)
        self.mapper.addMapping(self.milliseconds, 6)
        self.mapper.addMapping(self.bytes, 7)
        self.mapper.addMapping(self.unit_price, 8)

        self.mapper.toFirst()

        # Create controls
        controls = QHBoxLayout()
        prev_rec = QPushButton("Previous")
        prev_rec.clicked.connect(self.previous_record)
        next_rec = QPushButton("Next")
        next_rec.clicked.connect(self.next_record)
        save_rec = QPushButton("Save Changes")
        save_rec.clicked.connect(self.save_changes)

        controls.addWidget(prev_rec)
        controls.addWidget(next_rec)
        controls.addWidget(save_rec)

        # Setup layouts
        form.setLabelAlignment(Qt.AlignmentFlag.AlignLeft
                               | Qt.AlignmentFlag.AlignTrailing
                               | Qt.AlignmentFlag.AlignVCenter)
        layout = QVBoxLayout()
        layout.addLayout(form)
        layout.addLayout(controls)

        widget = QWidget()
        widget.setLayout(layout)
        self.setCentralWidget(widget)
        self.setMinimumSize(QSize(400, 400))

        # Add status bar
        self.status_bar = QStatusBar()
        self.setStatusBar(self.status_bar)
        self.update_status()

    def initialize_database(self):
        """Initialize database connection with error handling"""
        self.db = QSqlDatabase.addDatabase("QSQLITE")
        self.db.setDatabaseName(os.path.join(basedir, "Chinook_Sqlite.sqlite"))

        if not self.db.open():
            QMessageBox.critical(
                self, "Database Error",
                "Could not open database file: " + self.db.lastError().text())
            return False
        return True

    def populate_combo_boxes(self):
        """Populate album and genre combo boxes from database"""
        # Populate Albums
        album_query = QSqlQuery("SELECT Title FROM Album ORDER BY Title")
        while album_query.next():
            self.album.addItem(album_query.value(0))

        # Populate Genres
        genre_query = QSqlQuery("SELECT Name FROM Genre ORDER BY Name")
        while genre_query.next():
            self.genre.addItem(genre_query.value(0))

    def update_status(self):
        """Update status bar with current record position"""
        current = self.mapper.currentIndex() + 1
        total = self.model.rowCount()
        self.status_bar.showMessage(f"Record {current} of {total}")

    def previous_record(self):
        """Navigate to previous record and update status"""
        self.mapper.toPrevious()
        self.update_status()

    def next_record(self):
        """Navigate to next record and update status"""
        self.mapper.toNext()
        self.update_status()

    def save_changes(self):
        """Save changes with validation"""
        if not self.name.text():
            QMessageBox.warning(self, "Validation Error",
                                "Track name cannot be empty")
            return

        if self.mapper.submit():
            self.status_bar.showMessage("Changes saved successfully", 3000)
        else:
            QMessageBox.warning(self, "Save Error", "Failed to save changes")

    def closeEvent(self, event):
        """Clean up database connection when window closes"""
        self.db.close()
        super().closeEvent(event)


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