[python] QT, MSSQL 활용 - 상품 등록하기

Database 다이어그램

Product 테이블

CREATE TABLE [dbo].[Product] (
    [PId]   VARCHAR (50) NOT NULL,
    [PName] VARCHAR (50) NOT NULL,
    [Price] INT          NOT NULL,
    [PKey]  INT          IDENTITY (1, 1) NOT NULL,
    CONSTRAINT [PK__Product__C57755405495D0B5] PRIMARY KEY CLUSTERED ([PKey] ASC),
    CONSTRAINT [AK_PID] UNIQUE NONCLUSTERED ([PId] ASC)
);

---

ProductSql.py

ProductSql.py

0.00MB

#ProductSql.py
import  pymssql
class ProductSql:
    def AddProduct(self, pid,pname,price):
        conn = pymssql.connect("localhost","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("localhost","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("localhost","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("localhost","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("localhost","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("localhost","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("localhost","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

MyWidget.py

MyWidget.py

0.00MB

#MyWidget.py
from ProductSql import ProductSql
from PyQt5.QtWidgets import *
from PyQt5.QtGui import *
class MyWidget(QWidget):
    def __init__(self):
        super().__init__()
        self.setWindowTitle("상품 등록 창")
        self.resize(1000,600)
        lb_id = QLabel("상품 ID:",self)
        lb_id.move(0,30)
        lb_name = QLabel("상품명:",self)
        lb_name.move(0,130)
        lb_price = QLabel("상품 가격:",self)
        lb_price.move(0,230)
        self.te_id = QTextEdit(self)
        self.te_id.move(180,20)
        self.te_id.resize(500,60)
        self.te_name = QTextEdit(self)
        self.te_name.move(180,120)
        self.te_name.resize(500,60)
        self.te_price = QTextEdit(self)
        self.te_price.move(180,220)
        self.te_price.resize(500,60)
        self.btn_add = QPushButton("추가",self)
        self.btn_add.resize(200,90)
        self.btn_add.move(180,310)
        self.btn_cancel = QPushButton("취소",self)
        self.btn_cancel.resize(200,90)
        self.btn_cancel.move(420,310)
        self.btn_add.clicked.connect(self.AddProduct)
        self.btn_cancel.clicked.connect(self.Clear)
        self.psql = ProductSql()
    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("가격 정보는 정수로...")
            mbox.exec_()
        else:
            try:
                self.psql.AddProduct(pid,pname,price)
            except:
                mbox = QMessageBox()
                mbox.setText("이미 존재하는 상품 ID입니다.")
                mbox.exec_()
        self.Clear()
    def Clear(self):
        self.te_id.setText("")
        self.te_name.setText("")
        self.te_price.setText("")

Main.py

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