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

📌 개념 정리

PyMySQL은 Python에서 MySQL 데이터베이스와 상호작용할 수 있게 해주는 라이브러리입니다. MySQL 데이터베이스에 연결하여 SQL 쿼리를 실행하고 데이터를 조회하거나 수정할 수 있습니다. 이를 통해 Python 애플리케이션에서 데이터베이스 관리 작업을 자동화하거나 효율적으로 처리할 수 있습니다.

에어비앤비의 데이터베이스를 예로 들어 3개의 테이블(Products, Customers, Orders)을 생성하고, PyMySQL을 사용하여 다양한 작업을 수행하는 방법을 알아보겠습니다. 이 예제에서는 제품 추가, 고객 조회, 재고 업데이트, 주문 처리 등 여러 가지 실전 문제를 해결할 것입니다.

---

🚦 동작 원리 및 구조

1. MySQL 데이터베이스와의 연결
   PyMySQL을 사용하기 위해서는 먼저 데이터베이스에 연결해야 합니다. pymysql.connect() 메서드를 사용하여 MySQL 서버에 접속하고, cursor() 메서드를 통해 SQL 쿼리를 실행할 수 있습니다.
2. SQL 쿼리 실행
   PyMySQL에서는 SQL 쿼리를 실행하기 위해 execute() 메서드를 사용합니다. 데이터를 조회할 때는 fetchall()을 사용하여 결과를 받아올 수 있습니다. 데이터 삽입, 업데이트, 삭제 작업도 SQL 쿼리를 통해 처리합니다.
3. 트랜잭션 관리
   데이터베이스의 변경 작업은 트랜잭션을 관리하며, 작업이 완료되면 commit()을 호출하여 반영합니다. 만약 에러가 발생하면 rollback()을 호출하여 변경 사항을 취소할 수 있습니다.

---

💻 코드 예시 및 흐름 분석

• 1. 새로운 제품 추가:
  'Products' 테이블에 새로운 제품을 추가하는 코드입니다

import pymysql

# 데이터베이스 연결
connection = pymysql.connect(host='localhost',
                             user='root',
                             password='password',
                             database='airbnb')

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

•  흐름 설명:
  • pymysql.connect()로 MySQL 서버와 연결합니다.
  • cursor.execute()로 SQL 쿼리를 실행하여 'Products' 테이블에 데이터를 삽입합니다.
  • connection.commit()을 호출하여 데이터베이스에 변경 사항을 반영합니다.

2. 고객 목록 조회:
'Customers' 테이블에서 모든 고객 정보를 조회하는 코드입니다.

import pymysql

# 데이터베이스 연결
connection = pymysql.connect(host='localhost',
                             user='root',
                             password='password',
                             database='airbnb')

try:
    with connection.cursor() as cursor:
        # 모든 고객 정보 조회
        sql = "SELECT * FROM Customers"
        cursor.execute(sql)
        result = cursor.fetchall()
        for row in result:
            print(row)
finally:
    connection.close()

 

흐름 설명: 

• cursor.execute()로 SELECT 쿼리를 실행하여 모든 고객 정보를 조회합니다.
• fetchall()로 결과를 받아오고, 결과를 출력합니다.

---

🧪 실전 사례

다음은 실전에서 자주 발생할 수 있는 문제들을 해결하는 예제입니다:

 

1. 제품 재고 업데이트:
주문이 들어오면 해당 제품의 재고를 감소시켜야 합니다. Orders 테이블의 주문 정보를 기반으로 'Products' 테이블의 재고를 업데이트하는 코드입니다.

import pymysql

def update_stock(order_id):
    connection = pymysql.connect(host='localhost',
                                  user='root',
                                  password='password',
                                  database='airbnb')

    try:
        with connection.cursor() as cursor:
            # 주문 정보 조회
            sql = "SELECT productID, quantity FROM Orders WHERE orderID = %s"
            cursor.execute(sql, (order_id,))
            order = cursor.fetchone()

            # 제품의 재고 업데이트
            sql_update = "UPDATE Products SET stockQuantity = stockQuantity - %s WHERE productID = %s"
            cursor.execute(sql_update, (order['quantity'], order['productID']))
            connection.commit()
            print(f"주문 ID {order_id}에 대한 재고가 업데이트되었습니다.")
    finally:
        connection.close()

 

흐름 설명:

주문 ID를 사용하여 Orders 테이블에서 제품 정보와 주문 수량을 조회합니다.

해당 제품의 재고를 감소시키는 SQL 쿼리를 실행합니다.

 

2.고객별 총 주문 금액 계산:
각 고객별로 총 주문 금액을 계산하는 코드입니다.

import pymysql

# 고객별 총 주문 금액 계산
def calculate_total_amount():
    connection = pymysql.connect(host='localhost',
                                  user='root',
                                  password='password',
                                  database='airbnb')

    try:
        with connection.cursor() as cursor:
            # 고객별 총 주문 금액 조회
            sql = """
            SELECT c.customerName, SUM(o.totalAmount) AS totalSpent
            FROM Orders o
            JOIN Customers c ON o.customerID = c.customerID
            GROUP BY c.customerName
            """
            cursor.execute(sql)
            result = cursor.fetchall()
            for row in result:
                print(f"Customer: {row['customerName']}, Total Spent: {row['totalSpent']}")
    finally:
        connection.close()

흐름 설명:

Orders 테이블과 Customers 테이블을 조인하여 고객별로 총 주문 금액을 계산합니다.

GROUP BY 구문을 사용하여 각 고객의 총 금액을 구합니다.

 

---

🧠 고급 팁 or 자주 하는 실수

트랜잭션 관리
PyMySQL을 사용할 때 여러 SQL 쿼리를 한 번에 실행하는 경우, 트랜잭션을 명확히 관리해야 합니다. 작업 중 오류가 발생하면 rollback()을 사용해 이전 상태로 되돌리는 것이 중요합니다.

커서(Cursor) 관리
cursor()를 사용할 때, SQL 쿼리 실행 후 커서를 반드시 닫아야 합니다. 이는 데이터베이스 자원을 절약하고, 잠금을 방지하는 데 중요합니다. with 구문을 사용하면 자동으로 커서를 닫을 수 있습니다.

---

✅ 마무리 요약 및 복습 포인트

PyMySQL을 사용하여 MySQL 데이터베이스와 연결하고, SQL 쿼리를 실행하여 데이터를 삽입, 조회, 업데이트, 삭제할 수 있습니다.

각 실전 문제를 해결하기 위한 Python 스크립트 예제를 제공했으며, 데이터베이스 테이블 구조와 쿼리 작성을 이해할 수 있었습니다.

PyMySQL을 활용하여 더 복잡한 데이터베이스 작업을 수행할 수 있으며, 트랜잭션 관리와 커서 관리 등의 고급 기능을 활용하는 것이 중요합니다.