Database - Chapter 5-28. PyMySQL 연습(3/3)

 

 

💻 PyMySQL을 이용한 데이터베이스 조작 실습

이 포스팅에서는 PyMySQL을 사용하여 MySQL 데이터베이스에서 다양한 작업을 수행하는 방법을 실습해보겠습니다. 주어진 문제를 해결하는 방법을 코드와 함께 설명합니다.

---

📌 목표

1. 새로운 제품 추가
2. 고객 목록 조회
3. 제품 재고 업데이트
4. 고객별 총 주문 금액 계산
5. 고객 이메일 업데이트
6. 주문 취소
7. 특정 제품 검색
8. 특정 고객의 모든 주문 조회
9. 가장 많이 주문한 고객 찾기

---

⚙️ 코드 구현

python

 

import pymysql

# 데이터베이스 연결 설정
conn = pymysql.connect(
    host='localhost',
    user='root',
    password='oz-password',
    db='airbnb',
)

try:
    with conn.cursor() as cursor:
        
        # 문제 1: 새로운 제품 추가
        sql = "INSERT INTO Products (productName, price, stockQuantity) VALUES (%s, %s, %s)"
        cursor.execute(sql, ('Python Book', 29.99, 50))
        conn.commit()
        print("새로운 제품이 추가되었습니다.")

        # 문제 2: 고객 목록 조회
        sql = "SELECT * FROM Customers"
        cursor.execute(sql)
        for row in cursor.fetchall():
            print(row)

        # 문제 3: 제품 재고 업데이트
        product_id = 1  # 예시로 첫 번째 제품을 업데이트
        quantity_sold = 5  # 판매된 수량
        sql = "UPDATE Products SET stockQuantity = stockQuantity - %s WHERE productID = %s"
        cursor.execute(sql, (quantity_sold, product_id))
        conn.commit()
        print("제품 재고가 업데이트되었습니다.")

        # 문제 4: 고객별 총 주문 금액 계산
        sql = "SELECT customerID, SUM(totalAmount) FROM Orders GROUP BY customerID"
        cursor.execute(sql)
        for row in cursor.fetchall():
            print(f"Customer ID: {row[0]}, Total Spent: {row[1]}")

        # 문제 5: 고객 이메일 업데이트
        customer_id = 1  # 이메일을 업데이트할 고객 ID
        new_email = "newemail@example.com"
        sql = "UPDATE Customers SET email = %s WHERE customerID = %s"
        cursor.execute(sql, (new_email, customer_id))
        conn.commit()
        print(f"Customer ID {customer_id}의 이메일이 업데이트되었습니다.")

        # 문제 6: 주문 취소
        order_id = 1  # 취소할 주문 ID
        sql = "DELETE FROM Orders WHERE orderID = %s"
        cursor.execute(sql, (order_id,))
        conn.commit()
        print(f"Order ID {order_id}가 취소되었습니다.")

        # 문제 7: 특정 제품 검색
        sql = "SELECT * FROM Products WHERE productName LIKE %s"
        cursor.execute(sql, ('%Book%'))
        for row in cursor.fetchall():
            print(row)

        # 문제 8: 특정 고객의 모든 주문 조회
        customer_id = 1  # 특정 고객 ID
        sql = "SELECT * FROM Orders WHERE customerID = %s"
        cursor.execute(sql, (customer_id,))
        for row in cursor.fetchall():
            print(row)

        # 문제 9: 가장 많이 주문한 고객 찾기
        sql = """
        SELECT customerID, COUNT(*) as orderCount 
        FROM Orders 
        GROUP BY customerID 
        ORDER BY orderCount DESC 
        LIMIT 1
        """
        cursor.execute(sql)
        top_customer = cursor.fetchone()
        print(f"Top Customer ID: {top_customer[0]}, Orders: {top_customer[1]}")

finally:
    conn.close()

---

🔍 문제 분석 및 설명

1️⃣ 새로운 제품 추가

sql = "INSERT INTO Products (productName, price, stockQuantity) VALUES (%s, %s, %s)"
cursor.execute(sql, ('Python Book', 29.99, 50))
conn.commit()

• 목표: 새로운 제품을 'Products' 테이블에 추가합니다.
• 설명: productName, price, stockQuantity 값을 사용하여 새 제품을 삽입합니다.

2️⃣ 고객 목록 조회

sql = "SELECT * FROM Customers"
cursor.execute(sql)
for row in cursor.fetchall():
    print(row)

• 목표: 'Customers' 테이블에서 모든 고객의 정보를 조회합니다.
• 설명: SELECT * 쿼리로 모든 고객 정보를 가져옵니다.

3️⃣ 제품 재고 업데이트

sql = "UPDATE Products SET stockQuantity = stockQuantity - %s WHERE productID = %s"
cursor.execute(sql, (quantity_sold, product_id))
conn.commit()

• 목표: 특정 제품의 재고를 업데이트합니다.
• 설명: stockQuantity에서 판매된 수량만큼 차감하여 업데이트합니다.

4️⃣ 고객별 총 주문 금액 계산

sql = "SELECT customerID, SUM(totalAmount) FROM Orders GROUP BY customerID"
cursor.execute(sql)
for row in cursor.fetchall():
    print(f"Customer ID: {row[0]}, Total Spent: {row[1]}")

• 목표: 각 고객이 지출한 총 금액을 계산합니다.
• 설명: GROUP BY를 사용하여 각 고객의 주문 금액 합계를 계산합니다.

5️⃣ 고객 이메일 업데이트

sql = "UPDATE Customers SET email = %s WHERE customerID = %s"
cursor.execute(sql, (new_email, customer_id))
conn.commit()

• 목표: 특정 고객의 이메일을 업데이트합니다.
• 설명: 고객 ID를 기반으로 이메일을 변경합니다.

6️⃣ 주문 취소

sql = "DELETE FROM Orders WHERE orderID = %s"
cursor.execute(sql, (order_id,))
conn.commit()

• 목표: 특정 주문을 취소합니다.
• 설명: DELETE 쿼리를 사용하여 해당 주문을 삭제합니다.

7️⃣ 특정 제품 검색

sql = "SELECT * FROM Products WHERE productName LIKE %s"
cursor.execute(sql, ('%Book%'))
for row in cursor.fetchall():
    print(row)

• 목표: 제품 이름에 "Book"이 포함된 모든 제품을 검색합니다.
• 설명: LIKE 연산자를 사용하여 제품 이름에서 특정 단어를 포함한 제품을 찾습니다.

8️⃣ 특정 고객의 모든 주문 조회

sql = "SELECT * FROM Orders WHERE customerID = %s"
cursor.execute(sql, (customer_id,))
for row in cursor.fetchall():
    print(row)

• 목표: 특정 고객의 모든 주문을 조회합니다.
• 설명: 고객 ID를 기준으로 해당 고객의 모든 주문을 조회합니다.

9️⃣ 가장 많이 주문한 고객 찾기

sql = """
SELECT customerID, COUNT(*) as orderCount 
FROM Orders 
GROUP BY customerID 
ORDER BY orderCount DESC 
LIMIT 1
"""
cursor.execute(sql)
top_customer = cursor.fetchone()
print(f"Top Customer ID: {top_customer[0]}, Orders: {top_customer[1]}")

• 목표: 가장 많은 주문을 한 고객을 찾습니다.
• 설명: COUNT(*)와 GROUP BY를 사용하여 고객별 주문 수를 세고, 내림차순으로 정렬하여 가장 많이 주문한 고객을 찾습니다.

---

✅ 마무리

이 코드 예제에서는 PyMySQL을 사용하여 다양한 데이터베이스 작업을 수행하는 방법을 배웠습니다. 각 문제에 대해 필요한 SQL 쿼리와 Python 코드로 MySQL 데이터베이스를 조작하는 방법을 실습할 수 있었습니다.

🔧 핵심 포인트

• 데이터베이스 연결 및 쿼리 실행 방법
• 데이터 삽입, 조회, 업데이트, 삭제의 기본적인 SQL 쿼리 사용법
• 트랜잭션 처리 (commit() 및 rollback())