[python] 고객 관리 - QT, MSSQL

Database 다이어그램

CustomSql.py

CustomSql.py

0.00MB

#CustomSql.py
import pymssql
class CustomSql:
    def AddCustom(self, cid, cname):
        conn = pymssql.connect("127.0.0.1:1433","sa","1234","BigPro")
        cursor = conn.cursor()
        query =str.format("insert into Custom (CID, CNAME) values('{0}','{1}')", cid,cname)
        cursor.execute(query)
        conn.commit()
        conn.close()
    def RemoveCustom(self, ckey):
        conn = pymssql.connect("127.0.0.1:1433","sa","1234","BigPro")
        cursor = conn.cursor()
        query = str.format("delete from Custom where Ckey={0}",ckey)
        cursor.execute(query)
        conn.commit()
        conn.close()
    def FindCustom(self,ckey):
        conn = pymssql.connect("127.0.0.1:1433","sa","1234","BigPro")
        cursor = conn.cursor()
        query = str.format("select CID,CNAME from Custom where Ckey={0}",ckey)
        cursor.execute(query)
        row = cursor.fetchone()
        while row:            
            return row[0], row[1]
        conn.close()
        return "",""
    def FindKeyByName(self, cname):
        conn = pymssql.connect("127.0.0.1:1433","sa","1234","BigPro")
        cursor = conn.cursor()
        query = str.format("select CKey from Custom where CName='{0}'",cname)
        cursor.execute(query)
        row = cursor.fetchone()
        while row:
            return row[0]
        conn.close()
        return None
    def ViewCustomList(self):
        conn = pymssql.connect("127.0.0.1:1433","sa","1234","BigPro")
        cursor = conn.cursor()
        query = str.format("select CID,CNAME from Custom")
        cursor.execute(query)
        row = cursor.fetchone()
        while row:
            print("고객 ID:{0}, 고객 이름:{1}".format(row[0],row[1]))
            row = cursor.fetchone()
        conn.close()
    def FindKeyByID(self,cid):
        conn = pymssql.connect("127.0.0.1:1433","sa","1234","BigPro")
        cursor = conn.cursor()
        query = str.format("select CKey from Custom where CID='{0}'",cid)
        cursor.execute(query)
        row = cursor.fetchone()
        while row:
            return row[0]
        conn.close()
        return 0
    def FindCustomId(self,ckey):
        conn = pymssql.connect("127.0.0.1:1433","sa","1234","BigPro")
        cursor = conn.cursor()
        query = str.format("select CID from Custom where CKey={0}",ckey)
        cursor.execute(query)
        row = cursor.fetchone()
        while row:
            return row[0]
        conn.close()
        return None
    def CustomList(self):
        customs = list()
        conn = pymssql.connect("127.0.0.1:1433","sa","1234","BigPro")
        cursor = conn.cursor()
        query = str.format("select CID, CNAME, CKEY from Custom")
        cursor.execute(query)
        row = cursor.fetchone()
        while row:
            customs.append(row)
            row = cursor.fetchone()
        conn.close()
        return customs
    def RemoveCustomByID(self,cid):
        ckey = self.FindKeyByID(cid)
        self.RemoveCustom(ckey)
    def FindCustomName(self,cid):
        conn = pymssql.connect("127.0.0.1:1433","sa","1234","BigPro")
        cursor = conn.cursor()
        query = str.format("select CNAME from Custom where cid='{0}' ",cid)
        cursor.execute(query)
        row = cursor.fetchone()
        while row:
            return row[0]
        conn.close()
        return ""

CMainWidget.py

CMainWidget.py

0.00MB

#CMainWidget.py
from CAddDlg import CAddDlg
from CustomSql import CustomSql
from PyQt5.QtWidgets import *
from PyQt5.QtGui import *
class CMainWidget(QWidget):
    def __init__(self):
        super().__init__()
        self.csql = CustomSql()
        self.setWindowTitle("고객 관리 창")
        self.resize(760,400)
        lb_cl = QLabel("고객 목록", self)
        lb_cl.move(20,20)
        self.lbox_custom = QListWidget(self)
        self.lbox_custom.move(20,60)
        self.lbox_custom.resize(300,330)
        self.btn_add = QPushButton("고객 추가",self)
        self.btn_add.move(340,60)
        self.btn_add.resize(400,80)
        self.btn_del = QPushButton("고객 삭제",self)
        self.btn_del.move(340,180)
        self.btn_del.resize(400,80)
        lb_name = QLabel("이름:",self)
        lb_name.move(340,280)
        self.lb_cname = QLabel("[홍길동]",self)
        self.lb_cname.move(460,280)
        self.btn_add.clicked.connect(self.AddCustom)
        self.btn_del.clicked.connect(self.DelCustom)
        self.lbox_custom.currentItemChanged.connect(self.OnSelectChange)
        self.ListCustom()
    def ListCustom(self):
        self.lbox_custom.clear()
        customs = self.csql.CustomList()
        for custom in customs:
            self.lbox_custom.addItem(custom[0])
    def AddCustom(self):
        caw = CAddDlg(self)
        caw.show()
    def SAddCustom(self, cid):
        self.lbox_custom.addItem(cid)
    def DelCustom(self):
         item = self.lbox_custom.currentItem()
         index = self.lbox_custom.currentRow()
         self.lbox_custom.takeItem(index)
         self.csql.RemoveCustomByID(item.text())
    def OnSelectChange(self):
        try:
             item = self.lbox_custom.currentItem()
             cname = self.csql.FindCustomName(item.text())
             self.lb_cname.setText(cname)
        except:
            self.lb_cname.setText("[홍길동]")

CAddDlg.py

CAddDlg.py

0.00MB

#CAddDlg.py
from CustomSql import CustomSql
from PyQt5.QtWidgets import *
from PyQt5.QtGui import *
class CAddDlg(QDialog):
    def __init__(self,pa):
        super().__init__(pa)        
        self.csql = CustomSql()
        self.setWindowTitle("고객 등록 창")
        lb_id = QLabel("고객 ID:",self)
        lb_id.move(20,20)
        lb_name = QLabel("고객 이름:",self)
        lb_name.move(20,100)
        self.te_id = QTextEdit(self)
        self.te_id.move(200,20)
        self.te_id.resize(200,40)
        self.te_name =QTextEdit(self)
        self.te_name.move(200,100)
        self.te_name.resize(200,40)
        self.btn_add = QPushButton("추가",self)
        self.btn_add.move(20,180)
        self.btn_add.resize(160,40)
        self.btn_del = QPushButton("취소",self)
        self.btn_del.move(200,180)
        self.btn_del.resize(160,40)
        self.btn_add.clicked.connect(self.AddCustom)
        self.btn_del.clicked.connect(self.Cancel)
    def AddCustom(self):
        cid = self.te_id.toPlainText()
        cname = self.te_name.toPlainText()
        try:
            self.csql.AddCustom(cid,cname)
        except:
             mbox = QMessageBox()
             mbox.setText("이미 존재하는 고객 ID입니다.")
             mbox.exec_()
        else:
            pa = self.parent()
            pa.SAddCustom(cid)
       #    mbox = QMessageBox()
       #    mbox.setText("추가하였습니다.")
       #    mbox.exec_()
        self.Cancel()
    def Cancel(self):
        self.te_id.setText("")
        self.te_name.setText("")

Main.py

import sys
from PyQt5.QtWidgets import QApplication
from CMainWidget import CMainWidget
app = QApplication(sys.argv)
myw = CMainWidget()
myw.show()
sys.exit(app.exec_())