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

[python] 쇼핑몰 구현 - MSSQL이용한 콘솔 응용

언제나 휴일 언제나휴일 2020. 11. 4. 14:41
반응형

- 데이터 베이스

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
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

CustomSql.py

CustomSql.py
0.00MB

#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 None

SaleSql.py

SaleSql.py
0.00MB

#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
0.01MB

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

[기술 포트폴리오]EH 쇼핑몰 - DBMS(MSSQL) 활용.docx
0.07MB

반응형