[python] 상품 조회 응용 - MSSQL, QT 사용

데이터베이스 다이어그램

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
    def GetProductsID(self):
        products = list()
        conn = pymssql.connect("localhost","sa","1234","BigPro")
        cursor = conn.cursor()
        query = str.format("select PID from Product")
        cursor.execute(query)
        row = cursor.fetchone()
        while row:
            products.append(row[0])
            row = cursor.fetchone()
        conn.close()
        return products
    def FindProdctInfo(self,pid):
        conn = pymssql.connect("localhost","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 None,0

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.psql = ProductSql()
        self.lbox_product = QListWidget(self)
        self.lbox_product.resize(400,1000)
        self.resize(1000,1000)
        lb_info_pname = QLabel("상품명:",self)
        lb_info_pname.move(420,100)
        self.lb_pname = QLabel("",self)
        self.lb_pname.resize(200,80)
        self.lb_pname.move(550,100)
        lb_info_price = QLabel("가격:", self)
        lb_info_price.move(420,200)
        self.lb_price = QLabel("",self)
        self.lb_price.resize(200,80)
        self.lb_price.move(550,200)        
        self.lbox_product.currentItemChanged.connect(self.OnChangeProduct)
        self.ListProduct()        
    def OnChangeProduct(self):
        pid = self.lbox_product.currentItem().text()
        pname, price = self.psql.FindProdctInfo(pid)
        self.lb_pname.setText(pname)
        self.lb_price.setText(str(price))
    def ListProduct(self):
        self.lbox_product.clear()
        products = self.psql.GetProductsID()
        for product in products:
            self.lbox_product.addItem(product)

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