"Pyqt db 연결"의 두 판 사이의 차이
DB CAFE
(새 문서: import os from PyQt5 import QtCore, QtGui, QtWidgets, QtSql class BlobDelegate(QtWidgets.QStyledItemDelegate): def displayText(self, value, locale): if isinstance(value,...) |
|||
| 1번째 줄: | 1번째 줄: | ||
| − | + | == 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().__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_()) | sys.exit(app.exec_()) | ||
| + | </source> | ||
| + | |||
| + | === ui.py [화면 UI] === | ||
| + | <source lang=python> | ||
| + | # -*- 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")) | ||
| + | </source> | ||
| + | [[category:python]] | ||
2020년 5월 12일 (화) 09:49 판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 PYQT5 SQL CRUD 프로그램[편집]
1.1 테이블 DDL[편집]
SQLITE3
create table field
(
id Integer
primary key autoincrement,
Name Text,
Surname Text,
DOB Text,
Phone Text
);1.2 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_())1.3 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"))