*이 글을 읽기전에 작성자 개인의견이 있으니, 다른 블로그와 교차로 읽는것을 권장합니다.*
파이썬 + 데이터베이스를 활용하여 프로젝트를 진행합니다.
- 1. 기획 작성 ( 주제선정, 메뉴)
- 2. 데이터베이스 설계 (ERD entity 표기)
주제 선정: 배달음식 추천
프로젝트 진행
데이터베이스 만들기
MySQL에서 RDBMS로 임의의 데이터베이스를 만듭니다.
# database 생성
create database osan;
use osan;
# database table 설계
create table customer( #고객 테이블- 열 6개
customer_id int(5) primary key, #고객 고유번호
name varchar(100) not null, #고객이름
gender varchar(10) not null, #성별
age int(3) not null, #나이
food_item varchar(100) not null, #선호하는 음식종류
restaurant varchar(100) not null #선호하는 음식점
);
create table order_table( #주문 테이블- 열 6개
order_id int(5) primary key, #주문 고유번호
customer_id int(5), #고객 고유번호, foregin key는 옆에 붙이는
food varchar(100) not null, #주문한 음식
price int(15) not null, #주문한 음식가격
order_count int(5) not null, #주문 횟수
total_price int(20) not null, #전체 주문가격
foreign key (customer_id) references customer(customer_id)
#foreginkey설정
);python환경에서 database를 연결하고 table에 value값을 넣어줍니다. data를 database에 적재하기 위해서 data를 기입하는 프로그램을 작성해줌으로써 data를 적재합니다. database.table이 총 2개이니, 2개 프로그램을 만들어줍니다.
osan.customer table 생성
#mysqlclient설치
!pip install mysqlclient
#모듈설치
import MySQLdb
class Words:
def __init__(self, customer_id, name, gender, age, food_item, restaurant): #행 6개 설정
self.customer_id=customer_id
self.name=name
self.gender=gender
self.age=age
self.food_item=food_item
self.restaurant=restaurant
def setCustomer_id(self, customer_id):
self.customer_id=customer_id
def getCustomer_id(self):
return self.customer_id
def setName(self, name):
self.name=name
def getName(self):
return self.name
def setGender(self,gender):
self.gender=gender
def getGender(self):
return self.gender
def setAge(self,age):
self.age=age
def getAge(self):
return self.age
def setFood_item(self,food_item):
self.food_item=food_item
def getFood_item(self):
return self.food_item
def setRestaurant(self,restaurant):
self.restaurant=restaurant
def getRestaurant(self):
return self.restaurant###### WordsDao객체 생성
class WordsDao: #database table의 class문 WordsDao객체
def __init__(self): #self.db의 초기 메모리값 None 반환하도록 설정
self.db=None
def connect(self): #database 연결함수
self.db=MySQLdb.connect('localhost','root','1234','osan') #'osan' database에 연결
def disconnect(self): #database 연결중단 함수
self.db.close()
def insert(self,word): # word변수에 class객체값 저장(추가)하기
self.connect()
cur = self.db.cursor() #커서객체 설정
sql = 'insert into customer (customer_id, name, gender, age, food_item, restaurant) values (%s, %s,%s,%s,%s,%s)' #추가문 설정
data = (word.getCustomer_id(), word.getName(), word.getGender(), word.getAge(), word.getFood_item(), word.getRestaurant()) #반환값 변수 설정
cur.execute(sql,data) #추가문+리턴값 실행메서드
self.db.commit() #데이터베이스에 저장
cur.close() #커서 닫기
self.disconnect() #데이터베이스 연결해제
def selectAll(self): #읽기(출력)
self.connect() #database 연결
cur=self.db.cursor(MySQLdb.cursors.DictCursor) #database 딕셔너리형자료 지정
sql = 'select customer_id, name, gender, age, food_item, restaurant from customer order by customer_id asc'
cur.execute(sql) #sql 실행
row = cur.fetchall() #모든 딕셔너리자료값 행 출력
cur.close() #커서닫기
self.disconnect() #database 닫기
return row #처리된 행 반환(리턴)
def search(self, customer_id): # 수정됐는지 검색 함수
self.connect() #database 연결
cur = self.db.cursor(MySQLdb.cursors.DictCursor) #딕셔너리자료형 커서
sql = "select customer_id, name, gender, age, food_item, restaurant from customer where customer_id like concat('%%',%s,'%%')"
# SQL에서는 %%-> % 치환, %s->SQL에선 s 치환, 즉, SQL에선 %(와일드카드) 1개가 없는 것으로 생각해야함.
# ('%%',%s,'%%') -> %s% 가 되고, SQL에서 %s%는 (입력한) 값을 기준으로 포함된 모든 열을 가져옵니다.
data = (customer_id,) #튜플형 1개만 설정
cur.execute(sql,data) #sql실행
row=cur.fetchall() #모든 행 지정
cur.close() #커서 닫기
self.disconnect() #database닫기
return row #행을 리턴
def update(self, word): #word변수 수정하는 함수
self.connect() #database연결
cur = self.db.cursor() #커서
sql = 'update customer set name=%s, gender=%s, age=%s, food_item=%s, restaurant=%s where customer_id=%s'
data = (word.getName(), word.getGender(), word.getAge(), word.getFood_item(), word.getRestaurant(), word.getCustomer_id())
result = cur.execute(sql,data) #리턴된 word class 쿼리로 실행
self.db.commit() #database 저장
if result > 0: # 행 수정 완료
print('수정되었습니다')
else: #행 수정 못함
print('에러!')
cur.close() #커서닫
self.disconnect() #database닫
def delete(self,customer_id): #삭제함수
self.connect() #database 연결
cur = self.db.cursor() #커서
sql = 'delete from customer where customer_id=%s' #입력된 값 삭제
data = (customer_id,) # 해당
result = cur.execute(sql,data) #쿼리 실행
self.db.commit() #database저장
if result > 0: #삭제완료
print('삭제되었습니다')
else: #삭제못함
print('오류!')
cur.close() #커서닫
self.disconnect() #database닫class WordsService: #word클래스를 입력,검색,수정,삭제하는 걸 WordService 객체안에 저장
def __init__(self): # Wordsdao()를 self.dao에 저장하는 생성자함수
self.dao=WordsDao()
def insertWord(self): #word변수안에 클래스 입력하는 함수
customer_id = input('고객 고유번호를 입력하세요') #customer_id입력
name = input('고객이름을 입력하세요') #name입력
gender = input('성별을 입력하세요') #gender입력
age = input('나이를 입력하세요')
food_item = input('선호하는 음식종류를 입력하세요')
restaurant = input('선호하는 음식점을 입력하세요')
word = Words(customer_id, name, gender, age, food_item, restaurant) # 위에 입력한 것들 word변수안에 저장
self.dao.insert(word) #저장한 word객체값
def printAll(self): #등록(저장)한 word클래스 값 출력하는 함수
datas = self.dao.selectAll() #전체 출력->datas변수에 저장
for data in datas: #저장한 클래스 반복문
print(f"고객 고유번호:{data['customer_id']}, 고객 이름:{data['name']}, 성별:{data['gender']}, 나이:{['age']}, 선호하는 음식종류:{['food_item']}, 선호하는 음식점:{['restaurant']}")
#f-String을 사용할때 따옴표는 안,바깥을 구분해두어여 함.
# 등록한 word값들 전부 출력
def searchWord(self): #입력한 Word클래스를 검색하는 함수
customer_id = input('검색할 고객 고유번호를 입력하세요') #검색
data = self.dao.selectAll(customer_id) #word클래스를 검색->data변수에 저장
if datas: #datas변수 if문
for data in datas: #datas변수안에서 data라는이름으로 변수 출력
print(f"고객 고유번호:{data['customer_id']}, 고객 이름:{data['name']}, 성별:{data['gender']}, 나이:{['age']}, 선호하는 음식종류:{['food_item']}, 선호하는 음식점:{['restaurant']}")
#출력
else:
print('찾는 고객 고유번호가 없습니다.') #요청한 word클래스 없음
def editWord(self): # word클래스 수정하는 함수
customer_id = input('수정할 고객 고유번호를 입력하세요') #수정하기
word = self.dao.search(customer_id) #검색하기
if not word:
print('수정할 고객 고유번호가 없습니다')
else:
name = input('새로운 고객 이름을 입력하세요')
gender = input('새로운 성별을 입력하세요')
age = input('새로운 나이를 입력하세요')
food_item = input('새로운 선호하는 음식종류를 입력하세요')
restaurant = input('새로운 선호하는 음식점을 입력하세요')
word = Words(customer_id, name ,gender, age, food_item, restaurant)
self.dao.update(word)
def delWord(self): #검색해서 나온게 있는 Word클래스 삭제하는 함수
customer_id = input('삭제할 고객 고유번호를 입력하세요') #삭제할 것입력
word = self.dao.search(customer_id) #검색한 것 word에 저장
if not word: #검색한게 없을때
print('삭제할 고객 고유번호가 없습니다')
else: #검색한게 있을 때 삭제
self.dao.delete(customer_id)# 1~6번까지 실행하는 class문 Menu객체 생성
class Menu:
def __init__(self): # 생성자함수
self.service = WordsService() # WordService객체에 있는 기능을 self.service에 저장
def run(self):
while True: #while True는 무한반복문, True값일때 계속반복, False값일때 반복실행
try: #예외처리
menu = int(input('1.등록하기 2. 출력하기 3. 검색하기 4.수정하기 5. 삭제하기 6. 종료하기'))
if menu == 1:
self.service.insertWord()
elif menu ==2:
self.service.printAll()
elif menu ==3:
self.service.searchWord()
elif menu ==4:
self.service.editWord()
elif menu ==5:
self.service.delWord()
elif menu ==6:
print('프로그램을 종료합니다')
break
except Exception as e: #모든 예외처리 타입을 alias e로 처리
print(e) #alias e 출력
print('다시 입력하세요')
# Menu class 실행 객체
start = Menu()
start.run()어문 작동 잘되는지 확인
order_table table 생성
# 생성자 함수 class문 Words객체 생성
class Words:
def __init__(self, order_id, customer_id, food, price, order_count, total_price): #행 6개 설정
self.order_id=order_id
self.customer_id=customer_id
self.food=food
self.price=price
self.order_count=order_count
self.total_price=total_price
def setOrder_id(self, order_id):
self.order_id=order_id
def getOrder_id(self):
return self.order_id
def setCustomer_id(self, customer_id):
self.customer_id=customer_id
def getCustomer_id(self):
return self.customer_id
def setFood(self,food):
self.food=food
def getFood(self):
return self.food
def setPrice(self,price):
self.price=price
def getPrice(self):
return self.price
def setOrder_count(self,order_count):
self.order_count=order_count
def getOrder_count(self):
return self.order_count
def setTotal_price(self,total_price):
self.total_price=total_price
def getTotal_price(self):
return self.total_pri###### WordsDao객체 생성
class WordsDao: #database table의 class문 WordsDao객체
def __init__(self): #self.db의 초기 메모리값 None 반환하도록 설정
self.db=None
def connect(self): #database 연결함수
self.db=MySQLdb.connect('localhost','root','1234','osan') #'osan' database에 연결
def disconnect(self): #database 연결중단 함수
self.db.close()
def insert(self,word): # word변수에 class객체값 저장(추가)하기
self.connect()
cur = self.db.cursor() #커서객체 설정
sql = 'insert into order_table (order_id, customer_id, food, price, order_count, total_price) values (%s, %s,%s,%s,%s,%s)' #추가문 설정
data = (word.getOrder_id(), word.getCustomer_id(), word.getFood(), word.getPrice(), word.getOrder_count(), word.getTotal_price()) #반환값 변수 설정
cur.execute(sql,data) #추가문+리턴값 실행메서드
self.db.commit() #데이터베이스에 저장
cur.close() #커서 닫기
self.disconnect() #데이터베이스 연결해제
def selectAll(self): #읽기(출력)
self.connect() #database 연결
cur=self.db.cursor(MySQLdb.cursors.DictCursor) #database 딕셔너리형자료 지정
sql = 'select order_id, customer_id, food, price, order_count, total_price from order_table order by order_id asc'
cur.execute(sql) #sql 실행
row = cur.fetchall() #모든 딕셔너리자료값 행 출력
cur.close() #커서닫기
self.disconnect() #database 닫기
return row #처리된 행 반환(리턴)
def search(self, customer_id): # 수정됐는지 검색 함수
self.connect() #database 연결
cur = self.db.cursor(MySQLdb.cursors.DictCursor) #딕셔너리자료형 커서
sql = "select order_id, customer_id, food, price, order_count, total_price from order_table where order_id like concat('%%',%s,'%%')"
# SQL에서는 %%-> % 치환, %s->SQL에선 s 치환, 즉, SQL에선 %(와일드카드) 1개가 없는 것으로 생각해야함.
# ('%%',%s,'%%') -> %s% 가 되고, SQL에서 %s%는 (입력한) 값을 기준으로 포함된 모든 열을 가져옵니다.
data = (order_id,) #튜플형 1개만 설정
cur.execute(sql,data) #sql실행
row=cur.fetchall() #모든 행 지정
cur.close() #커서 닫기
self.disconnect() #database닫기
return row #행을 리턴
def update(self, word): #word변수 수정하는 함수
self.connect() #database연결
cur = self.db.cursor() #커서
sql = 'update order_table set customer_id=%s, food=%s, price=%s, order_count=%s, total_price=%s where order_id=%s'
data = (word.getCustomer_id(), word.getFood(), word.getPrice(), word.getOrder_count(), word.getTotal_price(), word.getOrder_id())
result = cur.execute(sql,data) #리턴된 word class 쿼리로 실행
self.db.commit() #database 저장
if result > 0: # 행 수정 완료
print('수정되었습니다')
else: #행 수정 못함
print('에러!')
cur.close() #커서닫
self.disconnect() #database닫
def delete(self,order_id): #삭제함수
self.connect() #database 연결
cur = self.db.cursor() #커서
sql = 'delete from order_table where order_id=%s' #입력된 값 삭제
data = (order_id,) # 해당
result = cur.execute(sql,data) #쿼리 실행
self.db.commit() #database저장
if result > 0: #삭제완료
print('삭제되었습니다')
else: #삭제못함
print('오류!')
cur.close() #커서닫
self.disconnect() #database닫class WordsService: #word클래스를 입력,검색,수정,삭제하는 걸 WordService 객체안에 저장
def __init__(self): # Wordsdao()를 self.dao에 저장하는 생성자함수
self.dao=WordsDao()
def insertWord(self): #word변수안에 클래스 입력하는 함수
order_id = input('주문 고유번호를 입력하세요')
customer_id = input('고객 고유번호을 입력하세요')
food = input('주문한 음식을 입력하세요')
price = input('주문한 음식가격를 입력하세요')
order_count = input('주문 횟수를 입력하세요')
total_price = input('전체 주문가격을 입력하세요')
word = Words(order_id, customer_id, food, price, order_count, total_price) # 위에 입력한 것들 word변수안에 저장
self.dao.insert(word) #저장한 word객체값
def printAll(self): #등록(저장)한 word클래스 값 출력하는 함수
datas = self.dao.selectAll() #전체 출력->datas변수에 저장
for data in datas: #저장한 클래스 반복문
print(f"주문 고유번호:{data['order_id']}, 고객 고유번호:{data['customer_id']}, 주문한 음식:{data['food']}, 주문한 음식가격:{['price']}, 주문 횟수:{['order_count']}, 전체 주문가격:{['total_price']}")
#f-String을 사용할때 따옴표는 안,바깥을 구분해두어여 함.
# 등록한 word값들 전부 출력
def searchWord(self): #입력한 Word클래스를 검색하는 함수
order_id = input('검색할 주문 고유번호를 입력하세요') #검색
data = self.dao.selectAll(order_id) #word클래스를 검색->data변수에 저장
if datas: #datas변수 if문
for data in datas: #datas변수안에서 data라는이름으로 변수 출력
print(f"주문 고유번호:{data['order_id']}, 고객 고유번호:{data['customer_id']}, 주문한 음식:{data['food']}, 주문한 음식가격:{['price']}, 주문 횟수:{['order_count']}, 전체 주문가격:{['total_price']}")
#출력
else:
print('찾는 주문 고유번호가 없습니다.') #요청한 word클래스 없음
def editWord(self): # word클래스 수정하는 함수
order_id = input('수정할 주문 고유번호를 입력하세요') #수정하기
word = self.dao.search(order_id) #검색하기
if not word:
print('수정할 주문 고유번호가 없습니다')
else:
customer_id = input('새로운 고객 고유번호을 입력하세요')
food = input('새로운 주문한 음식을 입력하세요')
price = input('새로운 주문한 음식가격를 입력하세요')
order_count = input('새로운 주문 횟수를 입력하세요')
total_price = input('새로운 전체 주문가격을 입력하세요')
word = Words(order_id, customer_id, food, price, order_count, total_price)
self.dao.update(word)
def delWord(self): #검색해서 나온게 있는 Word클래스 삭제하는 함수
order_id = input('삭제할 주문 고유번호를 입력하세요') #삭제할 것 입력
word = self.dao.search(order_id) #검색한 것 word에 저장
if not word: #검색한게 없을때
print('삭제할 주문 고유번호가 없습니다')
else: #검색한게 있을 때 삭제
self.dao.delete(order_id)# 1~6번까지 실행하는 class문 Menu객체 생성
class Menu:
def __init__(self): # 생성자함수
self.service = WordsService() # WordService객체에 있는 기능을 self.service에 저장
def run(self):
while True: #while True는 무한반복문, True값일때 계속반복, False값일때 반복실행
try: #예외처리
menu = int(input('1.등록하기 2. 출력하기 3. 검색하기 4.수정하기 5. 삭제하기 6. 종료하기'))
if menu == 1:
self.service.insertWord()
elif menu ==2:
self.service.printAll()
elif menu ==3:
self.service.searchWord()
elif menu ==4:
self.service.editWord()
elif menu ==5:
self.service.delWord()
elif menu ==6:
print('프로그램을 종료합니다')
break
except Exception as e: #모든 예외처리 타입을 alias e로 처리
print(e) #alias e 출력
print('다시 입력하세요')
# Menu class 실행 객체
start = Menu()
start.run()
Diagram 구조 확인
데이터 적재: 각각 table에 데이터를 50개의 행씩 넣어줍니다.
customer table 확인
order_table table 확인
이상 데이터를 모두 적재하였다면 본격적으로 메뉴 추천 기능을 프로그램으로 제작합니다. sql database를 python에서 활용하기 위해서 sqlalchemy 라이브러리와 pandas 라이브러리를 설치합니다.
- SQLAlchemy는 데이터베이스 연결, 쿼리 실행, 스키마 생성 및 조작 등 다양한 데이터베이스 관련 작업을 수행할 수 있습니다.
- Pandas는 데이터프레임이라는 자료구조를 중심으로 작동합니다. 데이터프레임은 엑셀 스프레드시트와 같은 테이블 형식의 데이터를 다루기에 적합한 자료구조입니다. pandas는 데이터 로딩, 저장, 정제, 변환, 조작, 분석 등 다양한 데이터 처리 작업을 수행합니다.
음식 추천 프로그램 알고리즘 과정은 다음과 같습니다.
- 고객 table과 주문 table을 database에서 가져옵니다.
- 고객의 성별, 나이에 따라 선호 음식과 음식점을 파악합니다
- 가장 많이 주문한 음식을 해당 성별, 나이 그룹에 대해 음식을 추천합니다.
- 추천된 음식을 고객에게 제공합니다.
#sqlalchemy 라이브러리 설치
!pip install sqlalchemy
# create_engine모듈 설치
from sqlalchemy import create_engine
#pandas 라이브러리 설치
!pip install pandas
#pandas 모듈설치
import pandas as pd# sqlalchemy라이브러리를 이용해서 database연결
db_engine = create_engine('mysql://root:1234@localhost:3306/osan')
#데이터베이스에서 고객 및 주문 데이터 가져오기
customers_query = "SELECT * FROM customer" #customer table 출력
orders_query = "SELECT * FROM order_table" #order_table 출력
#pandas.real_sql()함수는 데이터프레임으로 읽어오는 함수
customer_df = pd.read_sql(customers_query, db_engine) #customer table을 osan이라는 database에서 실행
order_table_df = pd.read_sql(orders_query, db_engine) #order_table을 database에서 실행
# 나이와 성별에 따른 선호하는 음식 및 음식점 파악
# pandas.merge()는 데이터프레임 2개를 합치는 함수
customer_orders_df = pd.merge(customer_df, order_table_df, on='customer_id')
#customer_id가 table 2개 연결점(primary key-foreign key 연결)
# 추천 알고리즘: 가장 많이 주문된 음식을 추천
def recommend_food(age, gender):
# 해당 연령 및 성별의 주문 데이터 필터링
# 데이터프레임 2개를 엮어 1개로 제작
filtered_orders = customer_orders_df[(customer_orders_df['age'] == age) & (customer_orders_df['gender'] == gender)]
# 가장 많이 주문된 음식 찾기
# ['food'].value_counts()는 'food'열의 value값의 횟수를 Series로 반환
top_food = filtered_orders['food'].value_counts().idxmax()
#.idmax()는 series에서 가장 큰 값 반환
return top_food
# 사용자에게 추천된 음식 제공
age=int(input('나이를 입력하세요'))
gender=input('성별을 입력하세요')
recommended_food = recommend_food(age, gender)
print(f"나이 {age}세, 성별 {gender}, 고객님에게 추천하는 음식은 {recommended_food}입니다
!pip install mysqlclient
import MySQLdb
# column을 선언하는 class객체 선언
class Var:
def __init__(self,age):
self.age=age
def setAge(self,age):
self.age=age
def getAge(self):
return self.age
# 데이터베이스 class객체 선언
class Database:
def __init__(self): #기본 데이터베이스 생성자 초기값 None설정
self.db=None
def connect(self): #데이터베이스 연결
self.db= MySQLdb.connect('localhost','root','1234','osan')
def disconnect(self): #데이터베이스 끊기
self.db.close()
def select(self, age):
self.connect()
cur=self.db.cursor(MySQLdb.cursors.DictCursor)
if 19< age <30:
sql = 'select food, count(food) as 주문횟수 from customer as c inner join order_table as o on c.customer_id = o.customer_id where age>=%s and age <%s group by food order by 주문횟수 desc;'
elif 29< age <40:
sql = 'select food, count(food) as 주문횟수 from customer as c inner join order_table as o on c.customer_id = o.customer_id where age>=%s and age <%s group by food order by 주문횟수 desc;'
else:
sql = 'select food, count(food) as 주문횟수 from customer as c inner join order_table as o on c.customer_id = o.customer_id where age>=%s and age <%s group by food order by 주문횟수 desc;'
cur.execute(sql, age )
row = cur.fetchall()
cur.close()
self.disconnect()
return row
# inner join으로 food,count(food)출력, 연령별로 나눠서 주문횟수가 2이상만 내림차순
class Service:
def __init__(self):
self.ddd=Database()
def select1(self):
age = int(input('나이를 입력하세요.'))
var = Var(age)
self.ddd.select(var)
start=Service()
result=start.select1()
print(result)class Food:
def __init__(self,age):
self.age=age
def setAge(self,age):
self.age=age
def getAge(self):
return self.age
class DataBase:
def __init__(self):
self.db=None
def connect(self):
self.db=MySQLdb.connect('localhost','root','1234','osan')
def disconnect(self):
self.db.close()
def select(self):
self.connect()
cur = self.db.cursor(MySQLdb.cursors.DictCursor)
sql = 'select * from customer '
cur.execute(sql)
row = cur.fetchall()
cur.close()
self.disconnect()
return row
class Service:
def __init__(self):
self.dao=DataBase()
def search(self):
print('1')
age=int(input('나이를 입력하세요'))
datas=self.dao.select()
for i in datas:
print(f"고객번호: {i['customer_id']}, 이름: {i['name']}, 성별: {i['gender']}, 나이: {i['age']}, 음식: {i['food_item']}, 식당: {i['restaurant']}")
class Turnon:
def __init__(self):
self.service=Service()
def run(self):
while True:
try:
menu = int(input('1.입력 2.종료'))
if menu == 1:
self.service.search()
print('2')
elif menu ==2:
print('프로그램 종료')
break
except Exception as e:
print(e)
print('다시 입력하세요')
start=Turnon()
start.run()'프로젝트' 카테고리의 다른 글
| 프로젝트(2)-시험문제 (0) | 2024.04.04 |
|---|