파이썬 Flask와 PostgreSQL를 이용한 쇼핑몰 구현 프로젝트

DB Term Project

 

 

주제        쇼핑몰 사이트 제작하기

 

 

제출일         2021. 12. 05

 

 

목차

1.    과제 목적 및 목표

2.   DB application에 대한 설명

3.   Schema diagram

4.    코드와 사용한 SQL문에 대한 설명

 

1              과제 목적 및 목표

 

 

 

 

 

1.1     목적

파이썬 플라스크와 Postgresql을 사용하여 쇼핑몰 사이트 제작

 

 

 

 

 

 

 

 

 

 

 

1.1      목표

1. 3개 이상의 페이지 (HTML 파일) 사용

 

2. 6개 이상의 테이블을 사용

 

3. 모든 테이블은 foreign key (referencing or referenced)로 연결되어 있어야 함

 

4. insert, delete, update, cartesian product, aggregate, nested subquery, join이 모두 사용되어 야 함

 

2              DB Application에 대한 설명

 

 

html 페이지는 총 6개로 목록은 다음과 같다. 이후 하나씩 각각의 페이지들이 어떤 역할을 하는지 설명하겠다.

 

•         main.html

•         Signup.html

•         Login.html

•         Shopping Page.html

•         Cost.html

•         complete.html

 

 

 

 

 

 

2.1     main.html

그림 2-1

main.html은 쇼핑몰의 기본 index 페이지로 그림 2-1과 같이 페이지에 접속하면 로그인과 회원가입 창이 출력된다. 로그인하기를 누르면 Login.html 화면으로 이동하고 sign up을 클릭하면 Signup.html로 이동한다.

 

 

 

 

 

2.2     Signup.html

그림 2-2

main.html에서 sign up을 클릭하여 Signup.html로 이동하면 그림 2-2와 같이 생성할 id와 password 값을 입력하고 추천인을 입력해야 한다. 추천인을 입력하면 해당 추천인은 100점의 포인트가 추가되도록 설계하였다. 예를 들어서 추천인에 dd라는 id계정을 입력하고 아이디를 생성하면 dd라는 계정의 포인트가 100점 추가된다. 만약 추천인이 없으면 추천인을 입력하지 않아도 된다. 추천인과 달리 아이디와 패스워드 중 하나라도 입력하지 않거나 이미 가입된 id를 입력하면 main.html로 이동한다. 만약 회원가입에 성공한다면 success.html로 이동한다.

 

 

 

 

 

2.3     success.html

그림 2-3

회원가입에 성공하면 success.html로 이동하여 Return을 클릭하면 다시 login.html으로 이동해서 로그인할 수 있다.

 

 

 

 

 

2.4    Login.html

그림 2-4

main.html에서 로그인하기를 누르거나 Signup.html에서 아이디를 생성하고 success.html에서 return을 클릭하면 Login.html로 이동한다. id와 password를 입력하고 해당 정보를 데이터베이스 users 테이블의 id와 password를 비교하여 일치하는 레코드가 있으면 Shopping Page.html로 이동한다.

 

 

 

 

 

2.5     Shopping Page.html

Login.html에서 로그인에 성공하면 Shopping Page.html 페이지로 이동한다.  그림 2‑5 에서 위에서부터 아래방향으로 순서대로 설명하면 가장 윗부분에는 로그인한 user의 id가 출력되고 user의 포인트가 출력된다. 그 아래에 구매 가능한 목록이 출력 되는데 쇼핑몰에는 A와 B 두 가지 상품이 존재한다. A와 B를 클릭하면 장바구니에 A와 B를 클릭한 개수만큼 저장되고 장바구니 비우기를 클릭하면 장바구니의 A, B 개수가 0으로 초기화 된다. 예상 금액 계산을 누르면 Cost.html 페이지로 이동하고 구매를 누르면 complete.html 페이지로 넘어간다. 가장 아래의 제일 처음 화면으로 버튼을 클릭하면 main.html 페이지로 이동한다.

 

 

 

 

 

 

2.6     Cost.html

그림 2-6

Shopping Page.html에서 예상 금액 계산을 누르면 Cost.html 페이지로 이동한다. Cost.html 페이지에서는 장바구니에 저장된 A와 B의 개수가 출력된다. A의 가격은 7000원이고 B의 가격 은 8000원으로 총합 비용이 페이지 위에 예상 금액으로 출력된다. 그리고 Return 버튼을 누르면 다시 Shopping Page.html 페이지로 이동한다.

 

 

 

 

 

2.7     complete.html

그림 2-7

Shopping Page.html에서 구매 버튼을 클릭하면 complete.html로 이동한다. complete.html 페이지로 이동했다는 것은 구매가 완료되고 현재 로그인한 사용자의 최근 주문 내역을 10개까지 출력하는 페이지이다.

 

 

 

 

 

 

3              Schema diagram

그림 3-1

데이터베이스의 스키마 구조는 그림 3‑1 과 같다.

 

users 테이블은 사용자들의 정보를 저장하는 테이블로 id와 password 정보가 있고 추천을 받는 경우 point가 100점이 추가된다. 회원가입을 하면 Id와 Password 값에 아이디와 비밀번호가 저장되고 Point는 기본값으로 0이 저장된다.

 

Nowuser 테이블은 현재 로그인해 있는 사용자의 id를 저장하기 위한 테이블로 state 칼럼을 통해 현재 로그인해 있는 상태라면 1을 저장하고 로그아웃 한 경우 0을 저장하여 한 사용자에 대한 세션을 유지하는 역할을 한다.

 

Basket 테이블은 장바구니를 위한 테이블이고 사용자의 id와 선택한 아이템의 item_id를 저장한다. 사용자가 클릭한 아이템을 클릭한 횟수만큼 Basket에 저장한다.

 

item 테이블은 item을 구별하는 item_id와 가격을 나타내는 price 칼럼으로 구성되어 있다.

 

recommender 테이블은 회원 가입하는 아이디와 추천인의 아이디를 저장한다. 해당 테이블을 통해 누가 누구를 추천했는지 알 수 있고 회원가입할 때만 추천을 할 수 있기 때문에 Id가 Primary 키가 된다.

History 테이블은 사용자의 이전 아이템 구매 기록을 저장한다. 구매를 하면 최근에 구매한 상품 10개를 페이지에 출력한다.

 

 

 

create table users(id varchar(20), password varchar(20),point numeric(10,0) default 0,chance numeric(2,0) default 1, primary key(id));

create table nowuser(id varchar(20), state numeric(7,2), primary key(id), foreign key(id) references users on delete cascade);

create table item(item_id varchar(20) not null , price numeric(8,2), primary key (item_id));

create table basket(id varchar(20), item_id varchar(20),  foreign key(id) references users on delete cascade, foreign key(item_id) references item);

create table history(id varchar(20), item_id varchar(20),  foreign key(id) references users on delete cascade, foreign key(item_id) references item);

create table recommender(id varchar(20), recommender_id varchar(20), primary key(id), foreign key(id) references users on delete cascade, foreign key(recommender_id) references users on delete cascade);

 

 

위 표는 6개 테이블의 sql 생성문이다. Reference를 살펴보면 nowuser, basket, history, recommender 4개의 테이블이 users 테이블의 id를 reference하고 있고 basket, history의 item_id는 item 테이블의 item_id를 reference하고 있다.

 

users의 id가 삭제되는 경우 reference하는 id는 delete cascade로 삭제하도록 설정하였고 item의 item_id의 경우는 따로 delete에 대한 설정을 하지 않았다.

 

 

 

 

 

 

 

4              코드와 사용한 SQL문에 대한 설명

그림 4-1 main() 함수

쇼핑몰에 접근하면 기본 index 페이지로 접근하게 되는데 그림 4-1의 main() 함수가 실행된 다. 아직 로그인하지 않았으므로 ‘update nowuser set state=0” sql문을 통해 nowuser의 state를 0으로 초기화한다. commit을 하고 main.html 페이지를 출력한다.

 

 

 

 

 

그림 4-2 main.html

그림 4-2는 main.html로 로그인하기를 누르면 /login으로 이동 후 login.html을 출력하고 sign up 버튼을 누르면 /signup으로 이동 후 signup.html 페이지를 출력한다.

 

 

 

 

 

 

그림 4-3 signup() 함수

회원가입 페이지에 접속하면 signup() 함수가 실행되는데 사용자가 입력하는 값을 POST 방식으로 받아와서 id, password, recommender을 저장하고 만약 이미 id값이 존재한다면 main.html로 이동하고 아닌 경우 insert 구문을 통해 users 테이블에 사용자를 등록하고 nowuser 테이블에 사용자를 등록한다. state 값은 아직 로그인 전이므로 0을 저장한다. 만약 recommender가 있다면 recommender 테이블에 회원가입하는 id와 추천인 정보를 저장하고 select문으로 users와 recommender의 Cartesian product를 통해 추천인 정보와 일치하는 사용자의 id를 user_id에 저장 후 해당 user_id와 일치하는 사용자의 포인트를 100으로 초기화한다. 마지 막으로 트랜잭션을 commit하고 login.html 페이지를 출력한다.

 

 

 

 

 

 

그림 4-4 register() 함수

login.html에서 아이디와 패스워드를 입력하면 그림 4-4와 같이 id와 password값을 받아와서 select문을 이용하여 일치하는 id와 password 값을 찾는다. 그리고 일치하는 결과가 있으면 nowuser에 state 값을 1로 수정하고 commit을 한다. 그리고 사용자의 포인트, 아이템 가격 정보와 함께 Shopping Page.html로 이동한다. 일치하는 결과가 없으면 다시 login.html로 돌아간다.

 

 

 

 

 

그림 4-5 buying() 함수

쇼핑몰 페이지로 접속하면 select문으로 현재 접속해 있는 사용자의 id와 포인트, 아이템 정보를 가져와서 각각 변수에 저장한다. 다음으로 경우의 수가 나뉜다. 순서대로 설명하면 첫번째로 A를 클릭하는 경우 insert문을 통해 장바구니에 사용자 id와 item_id를 저장한다. B를 클릭하는 경우도 마찬가지다.

 

장바구니 비우기를 클릭하는 경우 delete문을 이용해 장바구니에 있는 레코드를 모두 지운다.

 

예상금액계산을 클릭하는 경우 select문에서 Aggregate function 중 하나인 sum을 이용하여 가격을 계산하는데 item과 basket을 natural join하여 해당 id의 장바구니에 있는 아이템의 가격을 모두 더해서 총 금액을 구할 수 있다.

 

장바구니의 개별 아이템 개수를 계산하기 위해서 아래와 같은 구문을 사용하였다.

 

with count(number,item_id) as (select count(item_id),item_id from basket where id='{}' group by item_id) select count.number, price, item.item_id from item,count where

 

item.item_id=count.item_id order by item.item_id;

 

with 문으로 count라는 별칭의 view를 가져와서 Cartesian product를 통해 아이템 개수, 아이템 가격, 아이템 id 형태로 결과를 result에 저장한다. 경우를 A와 B 중 하나만 구매한 경우와 모두 구매한 경우 마지막으로 아무것도 구매하지 않는 경우로 나눠서 result에 값을 적절히 Cost.html 에 전달한다.

 

 

구매 버튼을 누르는 경우 select가 nested subquery로 들어있는 insert문을 통해 history에 사용자 id와 장바구니에 있는 item_id를 저장한다. commit을 하고 select 문으로 history에서 최근 구매한 item_id를 10개까지 가져와서 history에 저장한 후 complete.html로 전달한다.

 

 

 

 

 

그림 4-6 calculate_cost() 함수

예상 금액 계산을 누르고 Cost.html에서 다시 Shopping Page.html로 돌아오기 위해서는 사용자 id, point, 아이템 가격 데이터가 전달되야 하므로 calculate_cost() 함수에서 select문을 통해 user_id, point, price 값을 가져와서 Shopping Page.html로 돌아가기 위해서 인자로 전달한다.