[프로젝트] 안면 인식 기반 추천 프로그램 - (7)

julian·2025년 7월 13일

CNN Database Deep Learning machine learning python sqlite

python

목록 보기

66/74

기본 기능은 구현완료한 상태로 이제 다듬는 단계다.

먼저 구현하고자 하는 내용은 다음과 같다.
1. 현재 메뉴가 하나만 선택이 가능한데, order_id 를 두어 여러 메뉴를 선택가능하도록 변경
2. 관리자용, 사용자용 두 개로 나누어 관리자용에서는 분석 보기, 사용자용에서는 주문하기 구현
3. 분석 보기 년, 월, 일, 요일 등 다양한 분석 가능하도록 다양화

1.1. create_db.py

def create_table(cursor, table_name):
    cursor.execute(f"""
        CREATE TABLE IF NOT EXISTS "{table_name}" (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            order_id TEXT,
            timestamp TEXT,
            gender TEXT,
            age_group TEXT,
            menu_name TEXT,
            quantity INTEGER
        )
    """)

먼저 db생성 부터 구조에서 order_id 컬럼을 추가해줬다.

1.2. src/db_utils.py

def insert_visitor_log(db_path, table_name, order_id, timestamp, gender, age_group, menu_name, quantity):
    conn=sqlite3.connect(db_path)
    cursor=conn.cursor()

    cursor.execute(f"""
        INSERT INTO "{table_name}" (order_id, timestamp, gender, age_group, menu_name, quantity)
        VALUES (?, ?, ?, ?, ?, ?)
    """, (order_id, timestamp, gender, age_group, menu_name, quantity))

    conn.commit()
    conn.close()

또한 실제 기록을 insert 할 때도 해당 내용에 맞추기 위해 db_utils도 수정해준다.

1.3. analyze.html

<!DOCTYPE html>
<html lang="ko">
<head>
    <meta charset="UTF-8">
    <title>분석 페이지</title>
</head>
<body>
    <h1>방문자 분석 결과</h1>

    {% if logs %}
        <table border="1">
            <tr>
                <th>주문번호</th>
                <th>시간</th>
                <th>성별</th>
                <th>연령대</th>
                <th>메뉴</th>
                <th>수량</th>
            </tr>
            {% for log in logs %}
            <tr>
                <td>{{ log.order_id }}</td>
                <td>{{ log.timestamp }}</td>
                <td>{{ log.gender }}</td>
                <td>{{ log.age_group }}</td>
                <td>{{ log.menu_name }}</td>
                <td>{{ log.quantity }}</td>
            </tr>
            {% endfor %}
        </table>
    {% else %}
        <p>저장된 기록이 없습니다!</p>
    {% endif %}

    <a href="/">홈으로 돌아가기</a>
</body>
</html>

1.4. backend/routes.py

@app.route("/order", methods=["GET", "POST"])
    def order():
        if request.method=="GET":
            gender, age_group=capture_face_from_webcam()
            return render_template("order.html", gender=gender, age_group=age_group)

        elif request.method=="POST":
            gender=request.form["gender"]
            age_group=request.form["age_group"]
            menu_list=request.form.getlist("menu")

            db_path=os.path.join("database", "visitor_logs.db")
            timestamp=datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            year=datetime.now().strftime("%Y")
            year_month=datetime.now().strftime("%Y%m")

            order_id=f"{datetime.now().strftime('%Y%m%d%H%M%S')}"

            for menu_name in menu_list:
                quantity_str=request.form.get(f"quantity_{menu_name}", "1")
                try:
                    quantity=int(quantity_str)
                except ValueError:
                    quantity=1

                for table in ["master", f"{year}master", year_month]:
                    insert_visitor_log(
                        db_path=db_path,
                        table_name=table,
                        order_id=order_id,
                        timestamp=timestamp,
                        gender=gender,
                        age_group=age_group,
                        menu_name=menu_name,
                        quantity=quantity
                    )

            return render_template("order_done.html")

이제 실질적으로 multi order가 가능하도록 수정해줬다.
order_done.html 은 그대로 뒀고, order.html 부분을 다음과 같이 수정했다.

order.html

  <form method="POST">
          <input type="hidden" name="gender" value="{{ gender }}">
          <input type="hidden" name="age_group" value="{{ age_group }}">

          <h2>메뉴를 선택하세요</h2>

          <div>
              <label>
                  <input type="checkbox" name="menu" value="아메리카노" onchange="toggleQuantity(this)"> 아메리카노
              </label>
              수량: <input type="number" name="quantity_아메리카노" value="1" min="1" disabled>
          </div>

          <div>
              <label>
                  <input type="checkbox" name="menu" value="카페라떼" onchange="toggleQuantity(this)"> 카페라떼
              </label>
              수량: <input type="number" name="quantity_카페라떼" value="1" min="1" disabled>
          </div>

          <div>
              <label>
                  <input type="checkbox" name="menu" value="녹차라떼" onchange="toggleQuantity(this)"> 녹차라떼
              </label>
              수량: <input type="number" name="quantity_녹차라떼" value="1" min="1" disabled>
          </div>

          <br>
          <input type="submit" value="주문하기">
      </form>

      <script>
      function toggleQuantity(checkbox) {
          const quantityInput=checkbox.parentElement.parentElement.querySelector("input[type='number']");
          quantityInput.disabled=!checkbox.checked;
      }
      </script>

이와 같이 체크박스를 두어 여러 메뉴를 선택하고 수량도 변경할 수 있도록 하였다.

2. 사용자-관리자 분리

이제 router를 이용하여 사용자 화면과 관리자 전용 분석 페이지를 분리해보자.
즉 analyze를 admin/analyze 로 라우팅 변경해준다.
따라서 새로운 관리자 메인 페이지인 admin을 추가해주고, 링크로 분석 페이지로 이동하도록 하며, 사용자 화면에는 분석 버튼을 제거한다.

먼저 구조를 frontend/templates에 admin 폴더를 따뤄 뒀다.
이로 원래 있던 analyze.html 파일을 admin 폴더로 옮겼다.
현재 admin/index.html 파일은 지금은 무시해도 된다. 후에 기능 추가용으로 우선 남겨두었다.
관리자 페이지에서는 ID/PW가 일치하면 접속하여 분석 페이지로 이동하도록 했다.

2.1. 관리자

먼저 routes.py 를 보자면,

from dotenv import load_dotenv
load_dotenv()
ADMIN_ID=os.getenv("ADMIN_ID")
ADMIN_PW=os.getenv("ADMIN_PW")

# 관리자 로그인
    @app.route("/admin/login", methods=["GET", "POST"])
    def admin_login():
        if request.method=="POST":
            username=request.form["username"]
            password=request.form["password"]

            if username==ADMIN_ID and password==ADMIN_PW:
                session["admin_logged_in"]=True
                return redirect("/admin/analyze")  # 바로 분석 페이지로 이동
            else:
                return render_template("admin/login.html", error=True)  # 실패 시 에러 전달
            
        return render_template("admin/login.html")
    
    # 관리자 로그아웃
    @app.route("/admin/logout")
    def admin_logout():
        session.pop("admin_logged_in", None)
        return redirect("/admin/login")

admin/login.html

<!DOCTYPE html>
<html lang="ko">
<head>
    <meta charset="UTF-8">
    <title>관리자 로그인</title>
    <style>
        body {
            display: flex;
            justify-content: center;
            align-items: center;
            height: 100vh;
        }
        .login-box {
            text-align: center;
        }
    </style>
</head>
<body>
    <div class="login-box">
        <h2>관리자 로그인</h2>
        <form method="POST">
            <input type="text" name="username" placeholder="ID" required><br><br>
            <input type="password" name="password" placeholder="PW" required><br><br>
            <button type="submit">로그인</button>
        </form>
    </div>

    {% if error %}
    <script>
        alert("로그인 실패! ID/PW를 확인하세요.");
    </script>
    {% endif %}
</body>
</html>

.env

ADMIN_ID=admin
ADMIN_PW=1234
SECRET_KEY=abc123

이렇게 하드코딩 대신 .env 를 통해 불러와서 비교하도록 하였다.
원래 main.py 에서 사용했던 SECRET_KEY 도 또한 여기에 정의했다.

main.py

import os
from flask import Flask
from backend.routes import init_routes
from dotenv import load_dotenv

app=Flask(__name__, template_folder="frontend/templates")

load_dotenv()
app.config["SECRET_KEY"]=os.getenv("SECRET_KEY")

# router 등록
init_routes(app)

if __name__=="__main__":
    app.run(debug=True)

password가 다르다면 error=True 로 login.html 로 전달한다.
따라서 login.html에서

{% if error %}
    <script>
        alert("로그인 실패! ID/PW를 확인하세요.");
    </script>
{% endif %}

이를 만나 alert를 띄운다.

접속시
ID/PW 틀릴시
ID/PW 일치시

2.2. 사용자

그리고 사용자 페이지를 보면 이렇게 주문하기 버튼만 두도록 했다.

3. 리팩토링

이제 기능 부분을 조금 다듬을 때가 됐다.

다음과 같은 부분으로 조정했다.

가격에 대한 정보도 없고 직접 코드를 짜놔서 보여주기만 했기 때문에 메뉴테이블을 따로 생성
이후 VIEW를 이용해서 나누도록
순서는 다음과 같이 진행
1. create_table.py
2. insert_menu.py
3. create_view.py
4. main.py

3.1. create_table.py

import os
import sqlite3

BASE_DIR=os.path.dirname(os.path.abspath(__file__))
DB_PATH=os.path.abspath(os.path.join(BASE_DIR, "../database/store_analysis.db"))

def create_tables(db_path=DB_PATH):
    conn=sqlite3.connect(db_path)
    cursor=conn.cursor()

    # 메뉴 테이블
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS menu (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL UNIQUE,
            price INTEGER NOT NULL,
            display_order INTEGER DEFAULT 100
        )
    """)

    # 판매 기록 테이블
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS sales (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            timestamp TEXT NOT NULL,
            sex TEXT NOT NULL,
            age_group TEXT NOT NULL,
            menu_id INTEGER NOT NULL,
            order_id TEXT,  --  주문 단위 식별자
            FOREIGN KEY (menu_id) REFERENCES menu(id)
        )
    """)
    
    # master 테이블
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS master (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            timestamp TEXT,
            sex TEXT,
            age_group TEXT,
            menu_id INTEGER,
            FOREIGN KEY (menu_id) REFERENCES menu(id)
        )
    """)

    conn.commit()
    conn.close()
    print("DB 테이블 생성 완료")

if __name__=="__main__":
    create_tables()

3.2. insert_menu.py

import os
import sqlite3

BASE_DIR=os.path.dirname(os.path.abspath(__file__))
DB_PATH=os.path.abspath(os.path.join(BASE_DIR, "../database/store_analysis.db"))

def insert_menu(db_path=DB_PATH):
    conn=sqlite3.connect(db_path)
    cursor=conn.cursor()

    print("\n=== 🔧 메뉴 관리 도구 🔧 ===")

    while True:
        print("\n실행할 동작을 선택해주세요:")
        print("[1] 메뉴 추가")
        print("[2] 메뉴 수정 (메뉴 이름/가격 수정)")
        print("[3] 종료")

        choice=input("선택 [1/2/3] >>> ").strip()

        # 메뉴 추가
        if choice=="1":
            try:
                name=input("메뉴 이름 입력 >>> ").strip()
                price=int(input("메뉴 가격 입력 >>> ").strip())

                # 중복 확인
                cursor.execute("SELECT * FROM menu WHERE name=?", (name,))
                if cursor.fetchone():
                    print("이미 존재하는 메뉴입니다!")
                else:
                    cursor.execute("INSERT INTO menu (name, price) VALUES (?, ?)", (name, price))
                    conn.commit()
                    print(f"추가 완료: {name} ({price}원)")
            except ValueError:
                print("가격은 숫자로 입력해주세요.")

        # 메뉴 수정
        elif choice=="2":
            try:
                old_name=input("수정할 메뉴명을 입력해주세요 >>> ").strip()

                cursor.execute("SELECT * FROM menu WHERE name=?", (old_name,))
                row=cursor.fetchone()
                if not row:
                    print("해당 메뉴가 존재하지 않습니다!")
                    continue

                new_name=input("새 메뉴 이름 입력 >>> ").strip()
                new_price=int(input("새 가격 입력 >>> ").strip())

                cursor.execute("UPDATE menu SET name=?, price=? WHERE name=?", (new_name, new_price, old_name))
                conn.commit()
                print("수정 완료!")
            except ValueError:
                print("가격은 숫자로 입력해주세요.")

        # 메뉴 관리 도구 종료
        elif choice=="3":
            print("종료합니다.")
            break
        
        else:
            print("잘못된 입력입니다...")
    conn.close()

if __name__=="__main__":
    insert_menu()

이렇게하여 하드코딩으로 직접 구현하지 않고 입력받아 데이터를 삽입하도록 했다.

3.3. create_view.py

# 매달 1일 실행

import os
import sqlite3
from datetime import datetime

BASE_DIR=os.path.dirname(os.path.abspath(__file__))
DB_PATH=os.path.abspath(os.path.join(BASE_DIR, "../database/store_analysis.db"))

# Yearly VIEW
def create_yearly_view(year: str, db_path=DB_PATH):
    conn=sqlite3.connect(db_path)
    cursor=conn.cursor()

    view_name=f"view_{year}"
    cursor.execute(f"DROP VIEW IF EXISTS {view_name}")
    cursor.execute(f"""
        CREATE VIEW {view_name} AS
        SELECT * FROM sales
        WHERE strftime('%Y', timestamp)='{year}'
    """)
    conn.commit()
    conn.close()
    print(f"{view_name} 뷰 생성 완료")

# Monthly VIEW
def create_monthly_view(year: str, month: str, db_path=DB_PATH):
    conn=sqlite3.connect(db_path)
    cursor=conn.cursor()

    view_name=f"view_{year}{month}"
    cursor.execute(f"DROP VIEW IF EXISTS {view_name}")
    cursor.execute(f"""
        CREATE VIEW {view_name} AS
        SELECT * FROM sales
        WHERE strftime('%Y', timestamp)='{year}'
        AND strftime('%m', timestamp)='{month}'
    """)
    conn.commit()
    conn.close()
    print(f"{view_name} 뷰 생성 완료")

# 오늘 날짜 기준으로 월별 뷰 자동 생성
def create_monthly_view_for_today(db_path=DB_PATH):
    today=datetime.today()
    year=str(today.year)
    month=f"{today.month:02d}"
    create_monthly_view(year, month, db_path)

if __name__=="__main__":
    today=datetime.today()
    create_yearly_view(str(today.year))
    create_monthly_view_for_today()

DB Browser로 확인해보면 다음과 같다.

3.4. db_utils

그리고 이제 db utils 라는 폴더를 따로 만들어서 메뉴 조회, 판매 기록에 관한 부분을 따로 분리했다.

3.4.1. menu.py

# 메뉴 조회
import os
import sqlite3

BASE_DIR=os.path.dirname(os.path.abspath(__file__))
DB_PATH=os.path.abspath(os.path.join(BASE_DIR, "../../database/store_analysis.db"))

def get_all_menus(db_path=DB_PATH):
    conn=sqlite3.connect(db_path)
    cursor=conn.cursor()

    # display_order가 NULL인 경우 대비해 COALESCE 처리
    cursor.execute("""
        SELECT id, name, price FROM menu
        ORDER BY COALESCE(display_order, 100) ASC, id ASC
    """)
    results=cursor.fetchall()

    conn.close()
    return results

def get_menu_by_id(menu_id, db_path=DB_PATH):
    conn=sqlite3.connect(db_path)
    cursor=conn.cursor()
    cursor.execute("SELECT id, name, price FROM menu WHERE id=?", (menu_id,))
    result=cursor.fetchone()
    conn.close()
    return result

def get_menu_by_id_from_name(name, db_path=DB_PATH):
    conn=sqlite3.connect(db_path)
    cursor=conn.cursor()

    cursor.execute("SELECT id, name, price FROM menu WHERE name=?", (name,))
    result=cursor.fetchone()

    conn.close()
    return result

3.4.2. sales.py

# 판매 기록
import os
from datetime import datetime
import sqlite3
import uuid

BASE_DIR=os.path.dirname(os.path.abspath(__file__))
DB_PATH=os.path.abspath(os.path.join(BASE_DIR, "../../database/store_analysis.db"))

def insert_sales(sex, age_group, menu_id, db_path=DB_PATH, order_id=None):
    conn=sqlite3.connect(db_path)
    cursor=conn.cursor()

    timestamp=datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    if order_id is None:
        order_id=str(uuid.uuid4())

    cursor.execute("""
        INSERT INTO sales (timestamp, sex, age_group, menu_id, order_id)
        VALUES (?, ?, ?, ?, ?)
    """, (timestamp, sex, age_group, menu_id, order_id))

    conn.commit()
    conn.close()
    print(f"판매 기록 저장 완료! {timestamp}, {sex}, {age_group}, menu_id={menu_id}, order_id={order_id}")

def get_sales_analysis(db_path=DB_PATH):
    from collections import OrderedDict

    today=datetime.today()
    year=str(today.year)
    month=f"{today.month:02d}"
    view_name=f"view_{year}{month}"

    conn=sqlite3.connect(db_path)
    cursor=conn.cursor()

    # 주문 단위로 정렬된 raw 결과 가져오기
    # 시간 오름차순으로 정렬
    cursor.execute(f"""
        SELECT s.order_id, MIN(s.timestamp), s.sex, s.age_group, m.name, COUNT(*) as quantity
        FROM {view_name} s
        JOIN menu m ON s.menu_id=m.id
        GROUP BY s.order_id, s.sex, s.age_group, m.name
        ORDER BY MIN(s.timestamp) ASC
    """)
    raw=cursor.fetchall()
    conn.close()

    # order_id -> 정렬순서로 번호 부여
    order_map=OrderedDict()
    result=[]

    current_order_number=1
    for row in raw:
        order_id=row[0]
        if order_id not in order_map:
            order_map[order_id]=current_order_number
            current_order_number+=1

        readable_order_num=order_map[order_id]

        result.append((
            readable_order_num,  # 1, 2, 3...
            row[1],  # timestamp
            row[2],  # sex
            row[3],  # age_group
            row[4],  # menu name
            row[5]   # quantity
        ))

    return result

이렇게 데이터베이스 부분을 정리했고, 남은 부분은 router를 건드려줬다.

3.5. routes.py

먼저 주문하는 부분에서 메뉴를 선택하지 않았을 때를 대비해서 경고 메세지를 남기도록 한 부분 만들어줬고,

@app.route("/order", methods=["GET", "POST"])
    def order():
        if request.method=="GET":
            gender, age_group=capture_face_from_webcam()
            menu_list=get_all_menus(DB_PATH)
            return render_template("order.html", gender=gender, age_group=age_group, menu_list=menu_list)

        elif request.method=="POST":
            gender=request.form["gender"]
            age_group=request.form["age_group"]
            menu_list=request.form.getlist("menu")

            if not menu_list:
                menu_list_all = get_all_menus(DB_PATH)
                return render_template(
                    "order.html",
                    gender=gender,
                    age_group=age_group,
                    menu_list=menu_list_all,
                    error="하나 이상의 메뉴를 선택해주세요!"
                )

            import uuid
            order_id=str(uuid.uuid4())  # 주문 전체에 동일한 ID 부여

            for menu_name in menu_list:
                quantity_str=request.form.get(f"quantity_{menu_name}", "1")
                try:
                    quantity=int(quantity_str)
                except ValueError:
                    quantity=1

                menu_info=get_menu_by_id_from_name(menu_name, DB_PATH)
                if menu_info is None:
                    print(f"존재하지 않는 메뉴: {menu_name}")
                    continue

                menu_id=menu_info[0]

                for _ in range(quantity):
                    insert_sales(sex=gender, age_group=age_group, menu_id=menu_id, db_path=DB_PATH, order_id=order_id)

            return render_template("order_done.html")

먼저 메뉴 선택을 하지 않았을 때를 대비해서 경고 메세지를 남기도록 했고,

order.html 에서 이와 같이 설정해줬다. 또한 체크 박스 이후 + 와 -를 통해 수량을 선택하도록 만들었다.

로그인 이후에는 상단에 탭을 구현하여 메인, 주문 내역, 메뉴 순서 조정, 로그아웃 으로 달아뒀다.

# 관리자 대시보드
    @app.route("/admin/dashboard")
    def admin_dashboard():
        if not session.get("admin_logged_in"):
            return redirect(url_for("admin_login"))
        return render_template("admin/dashboard.html")
    
    # 관리자 메인화면
    @app.route("/admin")
    def admin_home()<:
        return redirect("/admin/login")
    
    # 관리자 분석 페이지
    @app.route("/admin/analyze")
    def admin_analyze():
        if not session.get("admin_logged_in"):
            return redirect(url_for("admin_login"))

        result=get_sales_analysis()
        return render_template("admin/analyze.html", result=result)
    
    @app.route("/admin/menu_edit", methods=["GET", "POST"])
    def menu_edit():
        if not session.get("admin_logged_in"):
            return redirect("/admin/login")

        db_path=DB_PATH

        if request.method=="POST":
            menu_ids=request.form.getlist("menu_id")
            
            conn=sqlite3.connect(db_path)
            cursor=conn.cursor()
            for idx, menu_id in enumerate(menu_ids):
                cursor.execute("UPDATE menu SET display_order=? WHERE id=?", (idx + 1, int(menu_id)))
            conn.commit()
            conn.close()

            return redirect("/admin/dashboard")
        
        from src.db_utils.menu import get_all_menus
        menu_list=get_all_menus(db_path)
        return render_template("admin/menu_edit.html", menu_list=menu_list)

실제 구현된 내용을 보면

메인(대시보드)
주문 내역
메뉴 순서 조정

html 부분까지 따로 다루지는 않겠다.

4. 정리

이제 어느정도 안정화가 되었다.
더 건드릴 부분은 이제 분석 부분정도 일 거 같다.
구상은 이제 기간을 선택 후 -> 년도별/월별/요일별/시간별 -> 남성/여성/전체 -> 0s/10s/20s/.../50s/60+/전체 -> 매출순/판매량순 이런 식으로 선택하여 분석을 볼 수 있도록 구현하는 부분인데 조금 고민 중이다. 너무 주객전도 되는 거 같기도 하고..🤔

julian

AI Model Developer

이전 포스트

[프로젝트] 안면 인식 기반 추천 프로그램 - (6)

다음 포스트