검색 여닫기
메뉴 여닫기
80
0
4
1.8천
데브카페
개인 메뉴 토글
로그인하지 않음
만약 지금 편집한다면 당신의 IP 주소가 공개될 수 있습니다.

Pyqt db 연결

데브카페
다른 명령
Devcafe (토론 | 기여)님의 2024년 10월 4일 (금) 23:10 판 (새 문서: == PYQT5 SQL CRUD 프로그램 == === 테이블 DDL === SQLITE3 <source lang=sql> create table field ( id Integer primary key autoincrement, Name Text, Surname Text, DOB Text, Phone Text ); </source> === main.py [메인 APP] === <source lang=python> import sys from ui import * from PyQt5.QtWidgets import QApplication, QMainWindow, QMessageBox, QTableView from PyQt5 import QtSql from PyQt5 import QtCore class form(QMainWindow): def __init__(self): super().__...)
(차이) ← 이전 판 | 최신판 (차이) | 다음 판 → (차이)

PYQT5 SQL CRUD 프로그램

테이블 DDL

SQLITE3 

create table field
(
	id Integer
		primary key autoincrement,
	Name Text,
	Surname Text,
	DOB Text,
	Phone Text
);

main.py [메인 APP]

import sys
from ui import *
from PyQt5.QtWidgets import QApplication, QMainWindow, QMessageBox, QTableView
from PyQt5 import QtSql
from PyQt5 import QtCore

class form(QMainWindow):
    def __init__(self):
        super().__init__()
        self.ui = Ui_MainWindow()
        self.ui.setupUi(self)

        """ 1.DB 접속 
        QMYSQL	MySQL Driver
        QOCI	Oracle Call Interface Driver
        QODBC	ODBC Driver (includes Microsoft SQL Server)
        QPSQL	PostgreSQL Driver
        QSQLITE	SQLite version 3 or above
        QSQLITE2	SQLite version 2       
        """
        self.db = QtSql.QSqlDatabase.addDatabase('QSQLITE')
        self.db.setDatabaseName('fieldlist.db')

        """ 2.테이블 선택 """
        self.model = QtSql.QSqlTableModel()
        self.model.setTable('field')
        self.model.setEditStrategy(QtSql.QSqlTableModel.OnFieldChange)

        """ 3. SELECT 쿼리 """
        self.model.select()

        """ 4. 그리드 헤더 """
        self.model.setHeaderData(0, QtCore.Qt.Horizontal,"id")
        self.model.setHeaderData(1, QtCore.Qt.Horizontal,"Name")
        self.model.setHeaderData(2, QtCore.Qt.Horizontal, "Surname")
        self.model.setHeaderData(3, QtCore.Qt.Horizontal, "DOB")
        self.model.setHeaderData(4, QtCore.Qt.Horizontal,"Phone")

        """ 5.테이블 위젯에 데이터 바인딩 처리 """
        self.ui.tableWidget.setModel(self.model)

        """ 6.버튼 추가/수정/삭제 이벤트 처리 """
        self.ui.pushButton.clicked.connect(self.addToDb)
        self.show()
        self.ui.pushButton_2.clicked.connect(self.updaterow)
        self.ui.pushButton_3.clicked.connect(self.delrow)

        """ 7.테이블 건수 조회 / 화면에 표시 """
        self.i = self.model.rowCount()
        self.ui.lcdNumber.display(self.i)

        print(self.ui.tableWidget.currentIndex().row())

    def addToDb(self):
        print(self.i)
        self.model.insertRows(self.i,1)
        self.model.setData(self.model.index(self.i,1),self.ui.lineEdit.text())
        self.model.setData(self.model.index(self.i, 2), self.ui.lineEdit_2.text())
        self.model.setData(self.model.index(self.i,4), self.ui.lineEdit_3.text())
        self.model.setData(self.model.index(self.i,3), self.ui.dateEdit.text())
        self.model.submitAll()
        self.i += 1
        self.ui.lcdNumber.display(self.i)

    def delrow(self):
        if self.ui.tableWidget.currentIndex().row() > -1:
            self.model.removeRow(self.ui.tableWidget.currentIndex().row())
            self.i -= 1
            self.model.select()
            self.ui.lcdNumber.display(self.i)
        else:
            QMessageBox.question(self,'Message', "Please select a row would you like to delete", QMessageBox.Ok)
            self.show()

    def updaterow(self):
        if self.ui.tableWidget.currentIndex().row() > -1:
            record = self.model.record(self.ui.tableWidget.currentIndex().row())
            record.setValue("Name",self.ui.lineEdit.text())
            record.setValue("Surname",self.ui.lineEdit_2.text())
            record.setValue("DOB", self.ui.dateEdit.text())
            record.setValue("Phone", self.ui.lineEdit_3.text())
            self.model.setRecord(self.ui.tableWidget.currentIndex().row(), record)
        else:
            QMessageBox.question(self,'Message', "Please select a row would you like to update", QMessageBox.Ok)
            self.show()

if __name__ == '__main__':
    app = QApplication(sys.argv)
    frm = form()
    sys.exit(app.exec_())

ui.py [화면 UI]

# -*- coding: utf-8 -*-

# Form implementation generated from reading ui file 'E:\uipython\pyqtex\pyqtdb\crud.ui'
#
# Created by: PyQt5 UI code generator 5.8.2
#
# WARNING! All changes made in this file will be lost!

from PyQt5 import QtCore, QtGui, QtWidgets

class Ui_MainWindow(object):
    def setupUi(self, MainWindow):
        MainWindow.setObjectName("MainWindow")
        MainWindow.resize(673, 578)
        self.centralwidget = QtWidgets.QWidget(MainWindow)
        self.centralwidget.setObjectName("centralwidget")
        self.groupBox = QtWidgets.QGroupBox(self.centralwidget)
        self.groupBox.setGeometry(QtCore.QRect(0, 0, 671, 261))
        self.groupBox.setObjectName("groupBox")
        self.lcdNumber = QtWidgets.QLCDNumber(self.groupBox)
        self.lcdNumber.setGeometry(QtCore.QRect(520, 10, 151, 81))
        self.lcdNumber.setObjectName("lcdNumber")
        self.label = QtWidgets.QLabel(self.groupBox)
        self.label.setGeometry(QtCore.QRect(10, 20, 47, 13))
        self.label.setObjectName("label")
        self.lineEdit = QtWidgets.QLineEdit(self.groupBox)
        self.lineEdit.setGeometry(QtCore.QRect(10, 40, 411, 20))
        self.lineEdit.setObjectName("lineEdit")
        self.label_2 = QtWidgets.QLabel(self.groupBox)
        self.label_2.setGeometry(QtCore.QRect(10, 70, 47, 13))
        self.label_2.setObjectName("label_2")
        self.lineEdit_2 = QtWidgets.QLineEdit(self.groupBox)
        self.lineEdit_2.setGeometry(QtCore.QRect(10, 90, 411, 20))
        self.lineEdit_2.setObjectName("lineEdit_2")
        self.label_3 = QtWidgets.QLabel(self.groupBox)
        self.label_3.setGeometry(QtCore.QRect(10, 120, 47, 13))
        self.label_3.setObjectName("label_3")
        self.lineEdit_3 = QtWidgets.QLineEdit(self.groupBox)
        self.lineEdit_3.setGeometry(QtCore.QRect(10, 140, 411, 20))
        self.lineEdit_3.setObjectName("lineEdit_3")
        self.label_4 = QtWidgets.QLabel(self.groupBox)
        self.label_4.setGeometry(QtCore.QRect(10, 180, 47, 13))
        self.label_4.setObjectName("label_4")
        self.dateEdit = QtWidgets.QDateEdit(self.groupBox)
        self.dateEdit.setGeometry(QtCore.QRect(10, 200, 411, 22))
        self.dateEdit.setObjectName("dateEdit")
        self.tableWidget = QtWidgets.QTableView(self.centralwidget)
        self.tableWidget.setGeometry(QtCore.QRect(0, 260, 671, 261))
        self.tableWidget.setObjectName("tableWidget")
        self.scrollArea = QtWidgets.QScrollArea(self.centralwidget)
        self.scrollArea.setGeometry(QtCore.QRect(0, 520, 671, 41))
        self.scrollArea.setWidgetResizable(True)
        self.scrollArea.setObjectName("scrollArea")
        self.scrollAreaWidgetContents = QtWidgets.QWidget()
        self.scrollAreaWidgetContents.setGeometry(QtCore.QRect(0, 0, 669, 39))
        self.scrollAreaWidgetContents.setObjectName("scrollAreaWidgetContents")
        self.pushButton = QtWidgets.QPushButton(self.scrollAreaWidgetContents)
        self.pushButton.setGeometry(QtCore.QRect(10, 10, 75, 23))
        self.pushButton.setObjectName("pushButton")
        self.pushButton_2 = QtWidgets.QPushButton(self.scrollAreaWidgetContents)
        self.pushButton_2.setGeometry(QtCore.QRect(110, 10, 75, 23))
        self.pushButton_2.setObjectName("pushButton_2")
        self.pushButton_3 = QtWidgets.QPushButton(self.scrollAreaWidgetContents)
        self.pushButton_3.setGeometry(QtCore.QRect(210, 10, 75, 23))
        self.pushButton_3.setObjectName("pushButton_3")
        self.scrollArea.setWidget(self.scrollAreaWidgetContents)
        MainWindow.setCentralWidget(self.centralwidget)
        self.statusbar = QtWidgets.QStatusBar(MainWindow)
        self.statusbar.setObjectName("statusbar")
        MainWindow.setStatusBar(self.statusbar)

        self.retranslateUi(MainWindow)
        QtCore.QMetaObject.connectSlotsByName(MainWindow)

    def retranslateUi(self, MainWindow):
        _translate = QtCore.QCoreApplication.translate
        MainWindow.setWindowTitle(_translate("MainWindow", "Simple CRUD(Create, Update, Delete)"))
        self.groupBox.setTitle(_translate("MainWindow", "Data"))
        self.label.setText(_translate("MainWindow", "Name"))
        self.label_2.setText(_translate("MainWindow", "Surname"))
        self.label_3.setText(_translate("MainWindow", "Phone"))
        self.label_4.setText(_translate("MainWindow", "DOB"))
        self.pushButton.setText(_translate("MainWindow", "Add"))
        self.pushButton_2.setText(_translate("MainWindow", "Update"))
        self.pushButton_3.setText(_translate("MainWindow", "Delete"))

레퍼런스 URL

https://doc.qt.io/qtforpython/PySide2/QtSql/QSqlQueryModel.html# https://doc.qt.io/qt-5/qsqldatabase.html

Comments

원본 주소 "https://devcafe.co.kr/w/index.php?title=Pyqt_db_연결&oldid=17"