Blog Full Notice
back to main page
Ch05. (데이터베이스) 쇼핑몰 데이터베이스 만들기 네이버 블로그
motivation: 네이버 블로그
#Ch05. [데이터베이스] 쇼핑몰 데이터베이스 만들기 : 네이버 블로그
1. 프로젝트 개요
</img>
주문 목록, 제품 목록.
판매 제품 리스트
구매 상품 추가
주문 목록 업데이트
</img>
이번 chapter에서는, 표준 라이브러리보다는, sqlite3 를 이용해서 어떻게 sequel문을 어떻게 연동해서 사용하는지 다룰 것이다.
csv파일을 다룰 수 있는 csv라는 라이브러리.
sqlite3는 라이브러리. DB에 연동시켜줄 수 있는 표준 라이브러리.
2. CSV 파일 다루기 - csv
csv 파일
comma-separated values 의 줄임말로 몇 가지 필드를 쉼표(,)로 구분한 텍스트 데이터 및 텍스트 파일
열과 행 구조로 이루어져 있는 대표적인 정형 데이터 형태입니다.
csv 파일을 데이터 베이스 프로그램에 바로 import 해서 테이블로 사용이 가능
실제 상품 수, 데이터 수가 매우 많을 때 csv 를 자주 활용
</img>csv 파일 읽기
import csv
result = []
with open('product_list.csv', 'r', encoding='euc-kr') as f: #이 인코딩을 사용해야 에러 안남
reader = csv.reader(f)
for line in reader:
result.append(line)
print(line)
['ID', '상품명', '가격']
['1', '모자', '15000']
['2', '코트', '200000']
['3', '티셔츠', '20000']
['4', '블라우스', '55000']
['5', '가디건', '45000']
['6', '청바지', '50000']
['7', '구두', '150000']
['8', '가방', '170000']
csv 파일 수정
# 방법 1
with open('product_list.csv', 'w', newline='') as f:
writer = csv.writer(f)
writer.writerows(result) # 이전 레코드
writer.writerow(['9', '양말', '5000']) # 신규 레코드
# 방법 2
result.append(['9', '양말', '5000'])
with open('product_list.csv', 'w', newline='') as f:
writer = csv.writer(f)
writer.writerows(result)
3. 데이터베이스 연결 - sqlite3
에스큐엘라이트3
데이터베이스 종류 여러가지. 각각의 DB가 제공하는 파이썬 API, 라이브러리가 있을 수 있는데, 표준 라이브러리를 사용해서 하자. 파이썬에서 제공하는, sqlite를 사용해보자.
sqlite3
sqlite3 는 SQLite 데이터 베이스를 사용하는데 필요한 인터페이스 파이썬 표준 라이브러리
파이썬 설치 시 SQLite 가 함께 자동으로 설치됨
SQLite는 주로 개발용이나 소규모 프로젝트에서 사용하는 파일 기반의 가벼운 데이터베이스
개발 시에는 SQLite를 사용하여 빠르게 개발하고 실제 운영 시스템에서는 좀 더 규모 있는 데이터베이스를 사용하는 것이 일반적
링크 가서, 데이터베이스는 실제로 깔려 있지만, GUI도 한번 설치해보자.
db browser를 윈도우에서 찾으면, 된다.
sqlite3 임포트
import sqlite3
# 버전 확인
sqlite3.version #라이브러리의 버젼
'2.6.0'
sqlite3.sqlite_version #내장되어 있는 DB의 버젼이다.
'3.40.1'
데이터 베이스 만들기
conn = sqlite3.connect('test.db') #sqlite3.connect() 를 해주면 만들어지는데, test db를 만들겠다. 확장자는 db.
테이블 생성하기
DB는 중복을 허용하지 않는다. 그게 ID다.
# 커서 생성
c = conn.cursor()
#데이터베이스를 조작할 때에는 항상 커서를 생성해야 한다. 커서를 생성해서, 커서에 원하는 쿼리 문을 실행시키는 것이다.
# 쿼리문 작성
query = '''CREATE TABLE test (ID INTEGER PRIMARY KEY, PRODUCT_NAME TEXT, PRICE INTEGER)''' #sql문. 이렇게 지정하고,
c.execute(query) #cursor에, query를 execute라는 문을 쓰면 된다. 이 결과를 확인해보자.
<sqlite3.Cursor at 0x14a31072a40>
그러면 이 결과를, DB GUI 설치한 프로그램에서, open database -> 우리 강의자료 파일 -> test.db. 열면, 벌써 table이 생성이 되었다. 우클릭, browse table하면, 아직 data는 없다. table을 수정하는 방법은, 코드 사용하는 방법은 다음 시간에, GUI 환경에서 또 테이블을 변경할 수 있다. ‘현재 테이블에 새 레코드를 추가합니다.’ 이거를 클릭하면 새 행이 생긴다. 이렇게 하면, DB의 table이 수정이 된 것이다. 저장까지 사실 된 것. DB를 수정하게 되면 바로 반영이 되기 때문에, 이 상태에서 close database를 해보고, 확인해보면, 손으로 수정한 것이 반영이 된 것을 볼 수 있다.
4. 데이터 불러오기, 조회하기 - fetchone, fetchall
data들을 어떻게 확인하는지 보자.
sqlite3 데이터 조회
sqlite3 에서 데이터 조회 방법에는 fetchone(), fetchmany(), fetchall() 3 가지 방법을 사용
SELECT 문을 사용한 조회 결과 범위에서 실제 가져오는 row 수를 결정
fetch라는 개념이, sql문을 사용해서, 우리가 data를 선택한다. 선택한 data에서 여러개를 가져올꺼냐. 그런 개념이다.
DB 연결
import sqlite3
import pandas as pd #데이터 시각화를 위해 쓰겠다.
# 연습용 DB 연결
conn = sqlite3.connect("chadwick.db") #chadwick.db는 sqlite에서 기본적으로 제공을 한다. 처음 DB를 다루는 사람들이 여러 연습을 할 수 있게. 이런 연습 DB를 제공한다. 우리 강의자료에, chadwick.db가 있을 것이다.
# 커서 생성
c = conn.cursor()
# 전체 테이블 현황 조회
c.execute('''SELECT * FROM sqlite_master WHERE type="table"''')
<sqlite3.Cursor at 0x1ac21351f10>
근데 pandas를 사용 안하면 c.fetchall() 하면 data가 이렇게 온다.
# 그래서 판다스 dataframe이라는 객체를 통해서 보면 수월하다.
pd.DataFrame(c.fetchall())
좀더 보기 편하다.
미국에 있는 공원 목록이 나와있는 park table을 사용하겠다.
데이터 조회
# 전체 데이터 조회
c.execute('''SELECT * FROM Parks''')
<sqlite3.Cursor at 0x1e80f21fab0>
# 전체 로우 선택
pd.DataFrame(c.fetchall())
#255 rows × 6 columns
# 1개 로우 선택
pd.DataFrame(c.fetchone())
#이렇게 하면 아무것도 안뜬다. fetch는 SELECT문을 통해 선택한 data 안에서, 몇개 가져올 것인지 정하는 함수다.
# SELECT 문을 사용하고 한번 c.fetchall()을 사용해서 더이상 가져올 것이 없는 것이다. 그럴 때에는, SELECT문을 다시 실행시켜주면 되고, 그 다음 c.fetchone() 하면 첫번째 row가 가져와진다.
# 지정 로우 선택
pd.DataFrame(c.fetchmany(size=3))
# 이거를 하면 두번째 row부터 출력이 된다. 여기서도 마찬가지로, 한번 SELECT 문을 사용했기 때문에, 그 바로 뒤에 나오는 것부터 fetch해오는 것이다. 따라서 select문을 한번 더 실행하면 첫번째부터 3번째까지 나오는 것이다.
특정 데이터 조회
#하나씩 fetch로 가져올 수 있지만, 어떤 조건에 맞는 data를 가져올 때 사용하는 방법
# 컬럼(키) 확인
cur = c.execute('''SELECT * FROM Parks''')
cur.description #테이블의 컬럼을 확인할 수 있다. 이 중에서, 주만 가지고 오고 싶다.
(('park.key', None, None, None, None, None, None),
('park.name', None, None, None, None, None, None),
('park.alias', None, None, None, None, None, None),
('city', None, None, None, None, None, None),
('state', None, None, None, None, None, None),
('country', None, None, None, None, None, None))
# 특정 row 만 가져오기
c.execute('''SELECT * FROM Parks WHERE state = ?''', ('NY',)) #정식 쿼리문은 아닌데, sqlite를 사용해서 where문을 사용할 때에는, 이런 식으로 하면 된다. 기본적으로 ? 사용하서, 콤마 쓰고, 튜플 안에 물음표 안에 있는 것을 쓰면 된다. ‘NY’. 튜플로 사용했는데, 이렇게 사용하면, execute문의 로직이, 튜플을 가져오게 되어 있다.
pd.DataFrame(c.fetchall())
실행하면, NY에만 있는 data들이 나온다.
# DB 연결 해제 #여러군데에서 연결, 연결해제 안하면, 오류가 발생될 수 있다.
conn.close()
5. 데이터 삽입, 수정, 삭제
데이터 베이스 쿼리 기본 구문
SELECT : 데이터 선택
INSERT : 데이터 삽입
UPDATE : 데이터 수정
DELETE : 데이터 삭제
5-5-1. 데이터 삽입 (INSERT)
import sqlite3
# 연습용 DB 연결
conn = sqlite3.connect("test.db")
# 커서 생성
c = conn.cursor()
# 테이블 생성
query = '''CREATE TABLE test (ID INTEGER PRIMARY KEY, PRODUCT_NAME TEXT, PRICE INTEGER)'''
c.execute(query)
<sqlite3.Cursor at 0x2ba6cb22d50>
# 테이블 생성확인
c.execute('''SELECT * FROM sqlite_master WHERE type="table"''')
print(c.fetchall())
[('table', 'test', 'test', 2, 'CREATE TABLE test (ID INTEGER PRIMARY KEY, PRODUCT_NAME TEXT, PRICE INTEGER)')]
INSERT 방법 1. 열(키) 항목 순서를 정확히 알고 있는 경우
# 데이터 삽입
c.execute("INSERT INTO test VALUES(1,'모자',150000)")
<sqlite3.Cursor at 0x2ba6cb22d50>
conn.commit() #이때부터는, DB에 commit을 해줘야, 변경사항이 잘 반영이 된다. commit을 해줘야, 이 위에 쿼리 문을 실제적으로 DB에 반영을 할 수 있다.
# 데이터 삽입
c.execute("INSERT INTO test VALUES(2,'코트',200000)")
conn.commit()
INSERT 방법 2. 열(키) 항목 순서를 정확히 모르는 경우
# 데이터 삽입
c.execute("INSERT INTO test(PRODUCT_NAME, PRICE, ID) VALUES(?,?,?)", ('티셔츠', 20000, 3))
conn.commit()
# 데이터 삽입
c.execute("INSERT INTO test(ID, PRICE, PRODUCT_NAME) VALUES(?,?,?)", (4, 55000, '블라우스'))
conn.commit()
INSERT 방법 3. 여러 데이터를 한번에 삽입하고 싶은 경우
# 테이블 내용 삭제
c.execute("DELETE FROM test")
conn.commit()
# 추가할 상품 리스트
product_list = [[1, '모자', 15000],
[2, '코트', 200000],
[3, '티셔츠', 20000],
[4, '블라우스', 55000],
[5, '가디건', 45000],
[6, '청바지', 50000],
[7, '구두', 150000],
[8, '가방', 170000]]
# 데이터 여러줄 삽입
c.executemany("INSERT INTO test(ID, PRODUCT_NAME, PRICE) VALUES(?,?,?)", product_list)
conn.commit()
5-5-2. 데이터 수정 (UPDATE)
#편한거 사용하면 된다.
UPDATE 방법 1. 튜플 형태로 수정
c.execute("UPDATE test SET PRODUCT_NAME = ? WHERE ID = ?", ('슬랙스', 6)) #ID가 6인 지점에 PRODUCT_NAME을 ‘슬랙스’로 변경한다는 것.
conn.commit()
UPDATE 방법 2. 딕셔너리 형태로 수정
c.execute("UPDATE test SET PRICE = :price WHERE ID = :id", {"price":55000, "id":6})
conn.commit()
UPDATE 방법 3. %s 표시자 사용
c.execute("UPDATE test SET PRODUCT_NAME = '%s' WHERE ID = '%s'" % ('트랜치코트', 2))
conn.commit()
5-5-3. 데이터 삭제 (DELETE)
DELETE 방법 1. 튜플 형태로 삭제
c.execute("DELETE FROM test WHERE ID =?", (8,))
conn.commit()
DELETE 방법 2. 딕셔너리 형태로 삭제
c.execute("DELETE FROM test WHERE PRODUCT_NAME = :product_name", {'product_name':'슬랙스'})
conn.commit()
DELETE 방법 3. 전체 삭제
c.execute("DELETE FROM test")
conn.commit()
# DB 연결 해제
conn.close()
6. 데이터 백업하기
DB를 백업하는 방법을 알아보자. DB를 만들어 놓았는데, testDB처럼 굉장히 작은 DB면 괜찮지만, 실제 운영하는데에는, 큰 여러가지 테이블이 얽혀있고, 복잡한 DB를 관리를 하겠다. DB를 백업하는 것은 필수적인 일이다.
iterdump
데이터 베이스를 백업할 때 사용하는 모듈
.sql 파일 확장자로 테이블을 다시 복원할 수 있는 쿼리문을 저장
import sqlite3
# 연습용 DB 연결
conn = sqlite3.connect("test.db")
# 커서 생성
c = conn.cursor()
# 추가할 상품 리스트
product_list = [[1, '모자', 15000],
[2, '코트', 200000],
[3, '티셔츠', 20000],
[4, '블라우스', 55000],
[5, '가디건', 45000],
[6, '청바지', 50000],
[7, '구두', 150000],
[8, '가방', 170000]]
# 데이터 여러줄 삽입
c.executemany("INSERT OR REPLACE INTO test(ID, PRODUCT_NAME, PRICE) VALUES(?,?,?)", product_list)
conn.commit()
# iterdump 내용 확인
for line in conn.iterdump():
print(line)
BEGIN TRANSACTION;
CREATE TABLE test (ID INTEGER PRIMARY KEY, PRODUCT_NAME TEXT, PRICE INTEGER);
INSERT INTO "test" VALUES(1,'모자',15000);
INSERT INTO "test" VALUES(2,'코트',200000);
INSERT INTO "test" VALUES(3,'티셔츠',20000);
INSERT INTO "test" VALUES(4,'블라우스',55000);
INSERT INTO "test" VALUES(5,'가디건',45000);
INSERT INTO "test" VALUES(6,'청바지',50000);
INSERT INTO "test" VALUES(7,'구두',150000);
INSERT INTO "test" VALUES(8,'가방',170000);
COMMIT;
이렇게, begin transaction, create table insert문이 쭉 나온다. 이거 자체가 사실 sql문이다. sql 쿼리 문인데, 이거를 자동적으로 만들어 준 것이다. 테이블을 이렇게 만들고, 레코드들이 쭉 들어간 것. 이것만 가지고는, test DB에 있는 테이블 정보가 날라가도, 수정되어도, 이거를 다시 실행시키면, 테이블을 새로 만들고, 이전에 있던 레코드를 불러와서, 이전 버젼으로 복구할 수 있다. iterdump()는 이런 식으로 자동적으로 쿼리를 만들어준다.
# 데이터 베이스 백업 파일 생성
with conn:
with open('backup.sql', 'w') as f: #db backup file, 위에 있는 쿼리 문을 backup.sql로 저장
for line in conn.iterdump():
f.write('%s\n' % line)
print('Completed.')
Completed.
경로에 백업이 생겼다.
# 테이블 삭제
c.execute("DROP TABLE test")
conn.commit()
# DROP TABLE은, DELETE table은, 거기에 있는 레코드를 삭제를 하는 것. DROP table은 table 자체를 삭제하는 것이다. 이거를 실행시키면 table이 남아있는 것이 없다.
# 백업 SQL 파일 로딩
with open('backup.sql', 'r') as sql_file:
sql_script = sql_file.read()
sql_script
'BEGIN TRANSACTION;\nCREATE TABLE test (ID INTEGER PRIMARY KEY, PRODUCT_NAME TEXT, PRICE INTEGER);\nINSERT INTO "test" VALUES(1,\'모자\',15000);\nINSERT INTO "test" VALUES(2,\'코트\',200000);\nINSERT INTO "test" VALUES(3,\'티셔츠\',20000);\nINSERT INTO "test" VALUES(4,\'블라우스\',55000);\nINSERT INTO "test" VALUES(5,\'가디건\',45000);\nINSERT INTO "test" VALUES(6,\'청바지\',50000);\nINSERT INTO "test" VALUES(7,\'구두\',150000);\nINSERT INTO "test" VALUES(8,\'가방\',170000);\nCOMMIT;\n'
# SQL 스크립트 실행
c.executescript(sql_script)
conn.commit()
이렇게 하면, 삭제된 테이블이 내용이 복구가 되었다. 잃어버린 내용을 복구를 했다.
7. 프로젝트 실습
진행 순서
데이터 베이스 생성
상품 리스트 테이블 생성
주문 목록 추가
총 구매 가격 산출
5-6-1. 데이터 베이스 생성
import sqlite3
# 쇼핑몰 데이터 베이스
conn = sqlite3.connect("shopping_mall.db")
# 커서 생성
c = conn.cursor()
5-6-2. 상품 리스트 테이블 생성
csv 파일로 테이블 추가
import pandas as pd
product_list = pd.read_csv('product_list.csv', encoding='euc-kr') #한국어가 섞여있어서
print(product_list)
# 이 리스트를 곧바로 DB에 추가할 수 있다. (pd.read반환객체).to_sql(table명, DB객체가 저장된 곳, 이미 이 테이블에 레코드가 있으면 그 뒤에 붙여라, id가 이미 있기 때문에 인덱스는 없는 것으로.)
# 상품 리스트 테이블 추가
product_list.to_sql('productList', conn, if_exists='append', index = False)
여기까지 하면, DB에, product_list table에 9개 record. 추가가 되었다.
튜플(리스트)로 테이블 추가
# 테이블 삭제
c.execute("DROP TABLE productList")
conn.commit()
product_list = [['모자',15000],
['코트', 200000],
['티셔츠',20000],
['블라우스',55000],
['가디건',45000],
['청바지',50000],
['구두',150000],
['가방',170000],
['양말',5000]]
# 테이블 생성
c.execute("CREATE TABLE IF NOT EXISTS productList(ID INTEGER PRIMARY KEY AUTOINCREMENT, 상품명 TEXT, 가격 INTEGER)")
# 레코드 추가
c.executemany("INSERT INTO productList (상품명,가격) values (?,?)", product_list)
conn.commit()
# csv 또는 product_list 로 추가를 하면 된다. 편한걸로 하면 된다.
5-6-3. 주문 목록 추가
# 사용자가 주문을 함에 따라서 업데이트 되는 주문목록을 추가할 것이다. 이때는 orderList라는 이름으로 만들 것이다. CREATE TABLE인데, IF NOT EXISTS 존재하지 않으면 만들어라, 주문목록에서 필요한 ID, 상품ID, 상품명, 상품개수, 가격, 총합.이렇게 해서 테이블을 만들 것.
c.execute("CREATE TABLE IF NOT EXISTS orderList(ID INTEGER PRIMARY KEY AUTOINCREMENT, 상품명 TEXT, 개수 INTEGER, 가격 INTEGER, 총합 INTEGER)")
conn.commit()
# 주문목록 list, order_list에 업데이트되는 로직. while문을 타서, 반복을 수행할 것. 조건이 성립할 때까지.
# 첫번째는 상품목록을 표시한다. 디스플레이 영역에서. 상품리스트에서, 상품번호를 진열해 놓는 식으로.
# 사용자가 상품을 선택할 수 있게끔, 어떤 상품 번호를 선택할지 읿력하라고 한다. 그래서 DB에서, ID에 해당하는 상품에 대한 가격을 가져온다. 고객이 선택한 상품을 저장하는 것까지 진행을 했다. 그 다음 고객이, 상품을 몇개 구매할 것인지 input을 받는다. 구매할 수량을 입력해주세요.
# 정보들을, 주문 목록 table에 상품명, 개수, 가격, 총합을 추가한다.
# 다음은 추가했으니깐, 고객한테 보여줘야 한다.
# 상품을 더 구매할 것인지, 확정지을 것인지. 물어보는 구문이 된다.
while True:
## 상품 목록을 표시
print("------------------상품목록------------------")
for row in c.execute('SELECT ID, 상품명, 가격 FROM productList'):
print('상품번호 :',row[0],', 상품명 :', row[1], ', 가격 :', row[2])
print("--------------------------------------------")
## 사용자 상품 선택
print('')
num = input("구매하실 상품의 번호를 입력해주세요: ")
c.execute("SELECT 상품명, 가격 FROM productList WHERE id = ?",(num,))
result = c.fetchone()
## 상품 번호와 주문 수량을 입력
print('')
count = int(input("구매할 수량을 입력해주세요: "))
total = count * int(result[1])
## 주문 데이터를 db에 추가
c.execute("INSERT INTO orderList (상품명, 개수, 가격, 총합) VALUES (?,?,?,?)", (result[0],count,result[1],total))
## 현재까지 주문 내역을 출력
print('')
print("현재까지 구매한 내역 보기")
print("--------------------주문목록--------------------")
for row in c.execute('SELECT * FROM orderList'):
print('상품명 :',row[1],', 주문수량 :', row[2], ', 단가 :', row[3], ', 구매가격 :', row[4])
print("------------------------------------------------")
## 사용자 추가 구매 여부
print('')
print("상품을 추가 구매하시겠습니까?\n중단하려면 'x'을 눌러주세요.\n계속 하시려면 엔터키를 눌러주세요. ")
if(input() == "x"): break
5-6-4. 총 구매 가격 산출
## 주문 내역에서 총 구매 가격 계산
print("총 구매가격", end='')
for row in c.execute('SELECT sum(총합) FROM orderList'):
print(' : ',row[0],'원')
print('')
## 주문 내역 초기화
c.execute("DELETE FROM orderList")
## 데이터 베이스 연결 해제
conn.close()
5-6-5. 전체 실행
import sqlite3
# 쇼핑몰 데이터 베이스
conn = sqlite3.connect("shopping_mall.db", isolation_level=None)
# 커서 생성
c = conn.cursor()
while True:
## 상품 목록을 표시
print("------------------상품목록------------------")
for row in c.execute('SELECT ID, 상품명, 가격 FROM productList'):
print('상품번호 :',row[0],', 상품명 :', row[1], ', 가격 :', row[2])
print("--------------------------------------------")
## 사용자 상품 선택
print('')
num = input("구매하실 상품의 번호를 입력해주세요: ")
c.execute("SELECT 상품명, 가격 FROM productList WHERE id = ?",(num,))
result = c.fetchone()
## 상품 번호와 주문 수량을 입력
print('')
count = int(input("구매할 수량을 입력해주세요: "))
total = count * int(result[1]) # 주문 수량에 따른 가격
## 주문 데이터를 db에 추가
c.execute("INSERT INTO orderList (상품명, 개수, 가격, 총합) VALUES (?,?,?,?)", (result[0],count,result[1],total))
## 현재까지 주문 내역을 출력
print('')
print("현재까지 구매한 내역 보기")
print("--------------------주문목록--------------------")
for row in c.execute('SELECT * FROM orderList'):
print('상품명 :',row[1],', 주문수량 :', row[2], ', 단가 :', row[3], ', 구매가격 :', row[4])
print("------------------------------------------------")
## 사용자 추가 구매 여부
print('')
print("상품을 추가 구매하시겠습니까?\n중단하려면 'x'을 눌러주세요.\n계속 하시려면 엔터키를 눌러주세요. ")
if(input() == "x"): break
## 주문 내역에서 총 구매 가격 계산
print("총 구매가격", end='')
for row in c.execute('SELECT sum(총합) FROM orderList'):
print(' : ',row[0],'원')
print('')
## 주문 내역 초기화
c.execute("DELETE FROM orderList")
## 데이터 베이스 연결 해제
conn.close()
쇼핑몰에서, 입장해서, 주문목록 업데이트, 최종적으로 어떤 물건 가격이 나오는지까지, DB와 연동을 해서 재고관리하는 차원에서 sample로 보았다.
댓글남기기