[PyQt] QTableWidget과 SQLite3를 연동하여 구현한 CRUD 프로그램

QTableWidget과 SQLite3를 연동하여 구현한 CRUD 프로그램

들어가며

• QTableWidget과 SQLite3를 연동하여 간단하게 구현한 CRUD 프로그램 소스 코드를 정리해본다. 

 

소스 코드

import sys
import sqlite3
from PyQt5.QtWidgets import QApplication, QMainWindow, QWidget, QVBoxLayout, QHBoxLayout, QPushButton, QLineEdit, QTableWidget, QTableWidgetItem, QLabel, QMessageBox


class MainWindow(QMainWindow):
    def __init__(self):
        super().__init__()

        self.setWindowTitle("CRUD with PyQt5 and SQLite3")
        self.setGeometry(200, 200, 600, 400)

        self.central_widget = QWidget()
        self.setCentralWidget(self.central_widget)

        self.layout = QVBoxLayout()
        self.central_widget.setLayout(self.layout)

        self.init_ui()
        self.init_db()

    def init_ui(self):
        # Widgets
        self.table = QTableWidget()
        self.table.setColumnCount(3)
        self.table.setHorizontalHeaderLabels(["ID", "Column1", "Column2"])

        self.load_button = QPushButton("Load Data")
        self.add_button = QPushButton("Add")
        self.update_button = QPushButton("Update")
        self.delete_button = QPushButton("Delete")

        self.column1_input = QLineEdit()
        self.column2_input = QLineEdit()

        # Layout
        form_layout = QHBoxLayout()
        form_layout.addWidget(QLabel("Column1:"))
        form_layout.addWidget(self.column1_input)
        form_layout.addWidget(QLabel("Column2:"))
        form_layout.addWidget(self.column2_input)

        button_layout = QHBoxLayout()
        button_layout.addWidget(self.load_button)
        button_layout.addWidget(self.add_button)
        button_layout.addWidget(self.update_button)
        button_layout.addWidget(self.delete_button)

        self.layout.addWidget(self.table)
        self.layout.addLayout(form_layout)
        self.layout.addLayout(button_layout)

        # Connect signals
        self.load_button.clicked.connect(self.load_data)
        self.add_button.clicked.connect(self.add_data)
        self.update_button.clicked.connect(self.update_data)
        self.delete_button.clicked.connect(self.delete_data)

    def init_db(self):
        self.connection = sqlite3.connect('database.db')
        self.cursor = self.connection.cursor()
        self.cursor.execute('''CREATE TABLE IF NOT EXISTS YourTable (
                               id INTEGER PRIMARY KEY AUTOINCREMENT,
                               column1 TEXT,
                               column2 INTEGER
                           )''')
        self.connection.commit()

    def load_data(self):
        self.table.clearContents()
        self.table.setRowCount(0)

        self.cursor.execute("SELECT * FROM YourTable")
        data = self.cursor.fetchall()

        for row_index, row_data in enumerate(data):
            self.table.insertRow(row_index)
            for column_index, column_data in enumerate(row_data):
                self.table.setItem(row_index, column_index, QTableWidgetItem(str(column_data)))

    def add_data(self):
        column1_value = self.column1_input.text()
        column2_value = self.column2_input.text()

        if column1_value and column2_value:
            self.cursor.execute("INSERT INTO YourTable (column1, column2) VALUES (?, ?)", (column1_value, column2_value))
            self.connection.commit()
            self.load_data()
        else:
            QMessageBox.warning(self, "Warning", "Please fill in all fields.")

    def update_data(self):
        row = self.table.currentRow()
        if row >= 0:
            id_value = self.table.item(row, 0).text()
            column1_value = self.table.item(row, 1).text()  # 첫 번째 열(ID 제외)의 데이터 가져오기
            column2_value = self.table.item(row, 2).text()  # 두 번째 열의 데이터 가져오기

            self.cursor.execute("UPDATE YourTable SET column1 = ?, column2 = ? WHERE id = ?",
                                (column1_value, column2_value, id_value))
            self.connection.commit()
            self.load_data()
        else:
            QMessageBox.warning(self, "Warning", "Please select a row to update.")

    def delete_data(self):
        row = self.table.currentRow()
        if row >= 0:
            id_value = self.table.item(row, 0).text()
            self.cursor.execute("DELETE FROM YourTable WHERE id = ?", (id_value,))
            self.connection.commit()
            self.load_data()
        else:
            QMessageBox.warning(self, "Warning", "Please select a row to delete.")

    def closeEvent(self, event):
        self.connection.close()


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