언어 자료구조 알고리즘/프로그래밍 실습

[python] EH 쇼핑몰 - 고객 관리, 상품 관리 - MSSQL, QT

언제나 휴일 언제나휴일 2020. 11. 6. 16:30
반응형

데이터 베이스 다이어그램

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("")

ProductSql.py

ProductSql.py
0.00MB

#ProductSql.py
import  pymssql
class ProductSql:
    def AddProduct(self, pid,pname,price):
        conn = pymssql.connect("127.0.0.1:1433","sa","1234","BigPro")
        cursor = conn.cursor()
        query =str.format("insert into Product (PID, PNAME, Price) values('{0}','{1}',{2})", pid,pname,price)
        cursor.execute(query)
        conn.commit()
        conn.close()
    def RemoveProduct(self,pkey):
        conn = pymssql.connect("127.0.0.1:1433","sa","1234","BigPro")
        cursor = conn.cursor()
        query = str.format("delete from Product where Pkey={0}",pkey)
        cursor.execute(query)
        conn.commit()
        conn.close()
    def FindProduct(self,key):
        conn = pymssql.connect("127.0.0.1:1433","sa","1234","BigPro")
        cursor = conn.cursor()
        query = str.format("select * from Product where Pkey={0}",key)
        cursor.execute(query)
        row = cursor.fetchone()
        while row:
            print("상품ID:{0} 상품이름:{1} 가격:{2}".format(row[0], row[1],row[2]))
            row =cursor.fetchone()
        conn.commit()
        conn.close()
    def FindKeyByName(self,pname):
        conn = pymssql.connect("127.0.0.1:1433","sa","1234","BigPro")
        cursor = conn.cursor()
        query = str.format("select PKey from Product where PName='{0}'",pname)
        cursor.execute(query)
        row = cursor.fetchone()
        while row:
            return row[0]
        conn.close()
        return None
    def ViewProductList(self):
        conn = pymssql.connect("127.0.0.1:1433","sa","1234","BigPro")
        cursor = conn.cursor()
        query = str.format("select PID,PNAME,PRICE from Product")
        cursor.execute(query)
        row = cursor.fetchone()
        while row:
            print("상품ID:{0} 상품명:{1} 상품 가격:{2}".format(row[0], row[1],row[2]))
            row = cursor.fetchone()
        conn.close()
    def FindKeyByID(self,pid):
        conn = pymssql.connect("127.0.0.1:1433","sa","1234","BigPro")
        cursor = conn.cursor()
        query = str.format("select PKey from Product where PID='{0}'",pid)
        cursor.execute(query)
        row = cursor.fetchone()
        while row:
            return row[0]
        conn.close()
        return 0
    def FindProductID(self,pkey):
        conn = pymssql.connect("127.0.0.1:1433","sa","1234","BigPro")
        cursor = conn.cursor()
        query = str.format("select PID from Product where PKey={0}",pkey)
        cursor.execute(query)
        row = cursor.fetchone()
        while row:
            return row[0]
        conn.close()
        return None
    def ProductList(self):
        products = list()
        conn = pymssql.connect("127.0.0.1:1433","sa","1234","BigPro")
        cursor = conn.cursor()
        query = str.format("select PID,PName,Price, Pkey from Product")
        cursor.execute(query)
        row = cursor.fetchone()
        while row:
            products.append(row)
            row = cursor.fetchone()
        conn.close()
        return products
    def RemoveProducByID(self,pid):
        try:
           pkey = self.FindKeyByID(pid)
           self.RemoveProduct(pkey)
        except:
            return False
        return True
    def FindProductByID(self,pid):
        conn = pymssql.connect("127.0.0.1:1433","sa","1234","BigPro")
        cursor = conn.cursor()
        query = str.format("select PName,Price from Product where Pid='{0}' ",pid)
        cursor.execute(query)
        row = cursor.fetchone()
        while row:
            return row[0],row[1]
        conn.close()
        return "",0

PMainWidget.py

PMainWidget.py
0.00MB

#PMainWidget.py
from PAddDlg import PAddDlg
from ProductSql import ProductSql
from PyQt5.QtWidgets import *
from PyQt5.QtGui import *
class PMainWidget(QWidget):
    def __init__(self):
        super().__init__()
        self.psql = ProductSql()
        self.setWindowTitle("상품 관리 창")
        self.resize(760,500)
        lb_pl = QLabel("상품 목록", self)
        lb_pl.move(20,20)
        self.lbox_product = QListWidget(self)
        self.lbox_product.move(20,60)
        self.lbox_product.resize(300,430)
        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_pname = QLabel("[고구마]",self)
        self.lb_pname.move(460,280)
        self.lb_pname.resize(300,80)
        lb_price = QLabel("가격:",self)
        lb_price.move(340,360)
        self.lb_price2 = QLabel("0",self) 
        self.lb_price2.move(460,360)
        self.lb_price2.resize(300,80)
        self.btn_add.clicked.connect(self.AddProduct)
        self.btn_del.clicked.connect(self.DelProduct)
        self.lbox_product.currentItemChanged.connect(self.OnSelectChange)
        self.ListProduct()
    def ListProduct(self):
        self.lbox_product.clear()
        products = self.psql.ProductList()
        for product in products:
            self.lbox_product.addItem(product[0])
    def SAddProduct(self,pid):
        self.lbox_product.addItem(pid)
    def AddProduct(self):
        pad = PAddDlg(self)
        pad.show()
    def DelProduct(self):
         item = self.lbox_product.currentItem()
         re = self.psql.RemoveProducByID(item.text())#테이블에서 삭제
         if re:
             index = self.lbox_product.currentRow()
             self.lbox_product.takeItem(index)
         else:
             mbox = QMessageBox()
             mbox.setText("판매 기록을 먼저 삭제해야 합니다.")
             mbox.exec_()
    def OnSelectChange(self):
        item = self.lbox_product.currentItem()
        pid = item.text()
        try:
            product = self.psql.FindProductByID(pid)
        except:
            self.lb_pname.setText("")
            self.lb_price2.setText("")
        else:
            self.lb_pname.setText(product[0])
            self.lb_price2.setText(str(product[1]))

PAddDlg.py

PAddDlg.py
0.00MB

#PAddDlg.py
from ProductSql import ProductSql
from PyQt5.QtWidgets import *
from PyQt5.QtGui import *
class PAddDlg(QDialog):
    def __init__(self,pa):
        super().__init__(pa)        
        self.psql = ProductSql()
        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)
        lb_price = QLabel("가격:",  self)
        lb_price.move(20,180)
        self.te_price = QTextEdit(self)
        self.te_price.move(200,180)
        self.te_price.resize(200,40)
        self.btn_add = QPushButton("추가",self)
        self.btn_add.move(20,260)
        self.btn_add.resize(160,40)
        self.btn_del = QPushButton("취소",self)
        self.btn_del.move(200,260)
        self.btn_del.resize(160,40)
        self.btn_add.clicked.connect(self.AddProduct)
        self.btn_del.clicked.connect(self.Cancel)
    def AddProduct(self):
        pid = self.te_id.toPlainText()
        pname = self.te_name.toPlainText()
        price = 0
        try:
            price = int(self.te_price.toPlainText())
        except:
            mbox = QMessageBox()
            mbox.setText("가격은 0으로 설정합니다.")
            mbox.exec_()
        try:
            self.psql.AddProduct(pid,pname,price)
        except:
             mbox = QMessageBox()
             mbox.setText("이미 존재하는 상품 ID입니다.")
             mbox.exec_()
        else:
            pa = self.parent()
            pa.SAddProduct(pid)
        self.Cancel()
    def Cancel(self):
        self.te_id.setText("")
        self.te_name.setText("")
        self.te_price.setText("")


Main.py

import sys
from PyQt5.QtWidgets import QApplication
from CMainWidget import CMainWidget
from PMainWidget import PMainWidget
app = QApplication(sys.argv)
cmw = CMainWidget()
cmw.show()
pmw = PMainWidget()
pmw.show()
sys.exit(app.exec_())
반응형