반응형
CustomSql.py
#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
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
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
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
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
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_())반응형
'언어 자료구조 알고리즘 > 프로그래밍 실습' 카테고리의 다른 글
| 제어문 기본 실습 [C#] - 반도체 장비 및 제어시스템 개발자 양성 (0) | 2021.05.12 |
|---|---|
| [C#, Windows Forms, 소켓 통신] 부재 입출고 자동 관리 창고 시스템 시뮬레이션 - Smart Factory 실습 (0) | 2020.11.27 |
| [C#, Widows Form 실습] Wafer 등록기 - 반도체 제어시스템 시뮬 (0) | 2020.11.24 |
| [python] 고객 관리 - QT, MSSQL (0) | 2020.11.06 |
| [python] 상품 조회 응용 - MSSQL, QT 사용 (0) | 2020.11.05 |
| [python] QT, MSSQL 활용 - 상품 등록하기 (0) | 2020.11.05 |
| [python] 쇼핑몰 구현 - MSSQL이용한 콘솔 응용 (0) | 2020.11.04 |
| [python] OOP 실습 - 커뮤니트 시뮬레이션 만들기 (0) | 2020.10.30 |
| [python] 상속 실습 - 커뮤니티(게시글, 비밀게시글) (0) | 2020.10.30 |
| [python] 상속 실습 - 상품과 할인 상품 (0) | 2020.10.30 |