반응형
- 데이터 베이스
DBMS: MSSQL
테이블: 상품, 고객, 판매
상품: 상품 ID(유일), 상품 이름, 가격, 상품 일련번호(Primary Key)
고객: 고객 ID(유일), 고객 이름, 고객 일련번호(Primary Key)
판매: 상품 일련변호(외래 Key), 고객 일련번호(외래 키), 판매 개수, 판매 일련번호(Primary Key)
*CKey, PKey, SKey는 IDENTITY(1,1)
- 제공할 기능
쇼핑몰 시나리오
1. 고객 추가
2. 상품 추가
3. 판매 추가
4. 상품 제거
5. 고객 탈퇴
6. 판매 취소
6.1 판매키로 취소 6.2 상품으로 취소 6.3 고객으로 취소
7. 구입 현황 조회
8. 판매 현황 조회
9. 전체 고객 보기
10. 전체 상품 보기
11. 전체 판매 보기ProductSql.py
#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 NoneCustomSql.py
#CustomSql.py
import pymssql
class CustomSql:
def AddCustom(self, cid, cname):
conn = pymssql.connect("localhost","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("localhost","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("localhost","sa","1234","BigPro")
cursor = conn.cursor()
query = str.format("select * from Custom where Ckey={0}",ckey)
cursor.execute(query)
row = cursor.fetchone()
while row:
print("고객ID:{0} 고객이름:{1}".format(row[0], row[1]))
row =cursor.fetchone()
conn.commit()
conn.close()
def FindKeyByName(self, cname):
conn = pymssql.connect("localhost","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("localhost","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("localhost","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("localhost","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 NoneSaleSql.py
#SaleSql.py
import pymssql
from ProductSql import ProductSql
from CustomSql import CustomSql
class SaleSql:
def __init__(self):
self.psql = ProductSql()
self.csql = CustomSql()
def AddSale(self,ckey,pkey,scount):
conn = pymssql.connect("localhost","sa","1234","BigPro")
cursor = conn.cursor()
query =str.format("insert into Sale (CKey,PKey,SCount) values({0},{1},{2})", ckey,pkey,scount)
cursor.execute(query)
conn.commit()
conn.close()
def CancelSale(self,skey):
conn = pymssql.connect("localhost","sa","1234","BigPro")
cursor = conn.cursor()
query =str.format("delete from Sale where skey={0}", skey)
cursor.execute(query)
conn.commit()
conn.close()
def FindProductsByCustom(self,ckey):
sales = list()
conn = pymssql.connect("localhost","sa","1234","BigPro")
cursor = conn.cursor()
query = str.format("select * from Sale where Ckey={0}",ckey)
cursor.execute(query)
row = cursor.fetchone()
while row:
sales.append( [row[0], row[1], row[2], row[3] ] )
row =cursor.fetchone()
conn.commit()
conn.close()
return sales
def FindCustomsByProduct(self,pkey):
sales = list()
conn = pymssql.connect("localhost","sa","1234","BigPro")
cursor = conn.cursor()
query = str.format("select * from Sale where Pkey={0}",pkey)
cursor.execute(query)
row = cursor.fetchone()
while row:
sales.append( [row[0], row[1], row[2], row[3] ] )
row =cursor.fetchone()
conn.commit()
conn.close()
return sales
def CancelSaleByProduct(self,pkey):
conn = pymssql.connect("localhost","sa","1234","BigPro")
cursor = conn.cursor()
query = str.format("delete from Sale where Pkey={0}",pkey)
cursor.execute(query)
conn.commit()
conn.close()
def CancelSaleByCustom(self,ckey):
conn = pymssql.connect("localhost","sa","1234","BigPro")
cursor = conn.cursor()
query = str.format("delete from Sale where Ckey={0}",ckey)
cursor.execute(query)
conn.commit()
conn.close()
def ViewSaleList(self):
conn = pymssql.connect("localhost","sa","1234","BigPro")
cursor = conn.cursor()
query = str.format("select pkey, ckey, scount from Sale")
cursor.execute(query)
row = cursor.fetchone()
print("{0:15}{1:15}{2:15}".format("Product ID","Custom ID", "Sale Count"))
while row:
pid = self.psql.FindProductID(row[0])
cid = self.csql.FindCustomId(row[1])
print("{0:15}{1:15}{2:<15}".format(pid, cid,row[2]))
row = cursor.fetchone()
conn.close()EHMall.py
#EHMall.py
import _mssql
from CustomSql import CustomSql
from ProductSql import ProductSql
from SaleSql import SaleSql
import os
class EHMall:
def __init__(self):
print("EH Mall을 시작합니다.")
input("엔터 키를 누르세요.")
self.csql = CustomSql()
self.psql = ProductSql()
self.ssql = SaleSql()
def Run(self):
while True:
key = self.SelectMenu()
if key == '1':
self.AddCustom()
elif key == '2':
self.AddProduct()
elif key == '3':
self.AddSale()
elif key == '4':
self.RemoveProduct()
elif key == '5':
self.RemoveCustom()
elif key=='6':
self.CancelSale()
elif key == '7':
self.ViewPurchase()
elif key == '8':
self.ViewSaleProduct()
elif key == '9':
self.ViewCustomList()
elif key == '10':
self.ViewProductList()
elif key == '11':
self.ViewSaleList()
elif key =='0':
break
else:
print("잘못 선택하였습니다.")
input("엔터 키를 누르세요.")
def SelectMenu(self):
os.system("cls")
print('1. 고객 추가')
print('2. 상품 추가')
print('3. 판매 추가')
print('4. 상품 제거')
print('5. 고객 탈퇴')
print('6. 판매 취소')
print('7. 구입 현황 조회')
print('8. 상품 판매 조회')
print('9. 전체 고객 보기')
print('10. 전체 상품 보기')
print('11. 전체 판매 보기')
return input("메뉴 선택:")
def AddCustom(self):
try:
print("고객 추가")
cid = input("고객 ID:")
cname = input("고객 이름:")
self.csql.AddCustom(cid,cname)
except:
print("이미 존재하는 ID입니다.")
def AddProduct(self):
print("상품 추가")
try:
pid = input("상품 ID:")
pname = input("상품 이름:")
price = 0
try:
price = int(input("상품 가격:"))
except:
print("상품 가격은 0원으로 처리합니다.")
self.psql.AddProduct(pid,pname,price)
#except Exception as e:
#except IntegrityError:
#except pymssql.MSSQLDatabaseException:
except:
print("이미 존재하는 상품 ID입니다.")
def AddSale(self):
print("판매 추가")
ckey = self.SelectCustom()
if ckey==0:
print("잘못 선택하였습니다.")
return
pkey = self.SelectProduct()
if pkey == 0:
print("잘못 선택하였습니다.")
return
count = 1
try:
count = int(input("판매 개수:"))
except:
print("판매 개수를 1로 처리하였습니다.")
self.ssql.AddSale(ckey,pkey,count)
def SelectCustom(self):
self.csql.ViewCustomList()
cid = input("선택할 고객 ID:")
return self.csql.FindKeyByID(cid)
def SelectProduct(self):
self.psql.ViewProductList()
pid = input("선택할 상품 ID:")
return self.psql.FindKeyByID(pid)
def RemoveProduct(self):
print("상품 제거")
pkey = self.SelectProduct()
if pkey == 0:
print("잘못 선택하였습니다.")
return
try:
self.psql.RemoveProduct(pkey)
except:
print("해당 상품 관련 판매 정보를 먼저 제거하세요.")
def RemoveCustom(self):
print("고객 탈퇴")
ckey = self.SelectCustom()
if ckey==0:
print("잘못 선택하였습니다.")
return
try:
self.csql.RemoveCustom(ckey)
except:
print("해당 고객 관련 판매 정보를 먼저 제거하세요.")
def CancelSale(self):
print("판매 취소")
key = self.SelectCancelMenu()
if key=='1':
self.CancelSaleBySkey()
elif key == '2':
self.CancelSaleByProduct()
elif key == '3':
self.CancelSaleByCustom()
else:
print("잘못 선택하였습니다.")
def SelectCancelMenu(self):
print("1:단위 판매 취소 2:특정 상품 관련 판매 취소 3:특정 고객 관련 판매 취소")
return input("선택:")
def CancelSaleBySkey(self):
skey = 0
try:
skey = int(input("판매 번호:"))
except:
print("잘못 입력하였습니다.")
try:
self.ssql.CancelSale(skey)
except:
print("존재하지 않는 판매 번호입니다.")
def CancelSaleByProduct(self):
print("상품으로 판매 취소(주의:해당 상품의 모든 판매 정보 삭제됨)")
pkey = self.SelectProduct()
if pkey == 0:
print("잘못 선택하였습니다.")
return
self.ssql.CancelSaleByProduct(pkey)
def CancelSaleByCustom(self):
print("고객으로 판매 취소(주의:해당 고객의 모든 판매 정보 삭제됨)")
ckey = self.SelectCustom()
if ckey == 0:
print("잘못 선택하였습니다.")
return
self.ssql.CancelSaleByCustom(ckey)
def ViewPurchase(self):
print("구입 현황 조회")
ckey = self.SelectCustom()
if ckey == 0:
print("잘못 선택하였습니다.")
return
sales = self.ssql.FindProductsByCustom(ckey)
for sale in sales:
self.psql.FindProduct(sale[1])
print("판매 개수:",sale[3])
print("=======")
def ViewSaleProduct(self):
print("상품 판매 조회")
pkey = self.SelectProduct()
if pkey == 0:
print("잘못 선택하였습니다.")
return
sales = self.ssql.FindCustomsByProduct(pkey)
for sale in sales:
self.csql.FindCustom(sale[0])
print("판매 개수:",sale[3])
print("=======")
def ViewCustomList(self):
print("전체 고객 보기")
self.csql.ViewCustomList()
def ViewProductList(self):
print("전체 상품 보기")
self.psql.ViewProductList()
def ViewSaleList(self):
print("전체 판매 보기")
self.ssql.ViewSaleList()
Main.py
from EHMall import EHMall
ehmall = EHMall()
ehmall.Run()반응형
'언어 자료구조 알고리즘 > 프로그래밍 실습' 카테고리의 다른 글
| [C#, Widows Form 실습] Wafer 등록기 - 반도체 제어시스템 시뮬 (0) | 2020.11.24 |
|---|---|
| [python] EH 쇼핑몰 - 고객 관리, 상품 관리 - MSSQL, QT (0) | 2020.11.06 |
| [python] 고객 관리 - QT, MSSQL (0) | 2020.11.06 |
| [python] 상품 조회 응용 - MSSQL, QT 사용 (0) | 2020.11.05 |
| [python] QT, MSSQL 활용 - 상품 등록하기 (0) | 2020.11.05 |
| [python] OOP 실습 - 커뮤니트 시뮬레이션 만들기 (0) | 2020.10.30 |
| [python] 상속 실습 - 커뮤니티(게시글, 비밀게시글) (0) | 2020.10.30 |
| [python] 상속 실습 - 상품과 할인 상품 (0) | 2020.10.30 |
| [python] 상속 개요 - 책과 프로그래밍 책 (0) | 2020.10.30 |
| [python] 상속 실습 - 음악가, 피아니스트 (0) | 2020.10.30 |