Pandas를 활용한 SQL 명령문 실행

1. 인트로

pandas(판다스)는 데이터를 표(DataFrame(데이터프레임)) 형태로 다루는 파이썬 라이브러리이다. sqlite3와 pandas를 함께 사용하면 SQL 조회 결과를 엑셀처럼 보기 좋게 확인하고, 분석하고, 저장할 수 있다.

---

2. DB 데이터 일괄 추가 실습

2.1. 실습용 DB 준비

import sqlite3
import pandas as pd

conn = sqlite3.connect("store.db")
cur  = conn.cursor()

cur.execute("""
    CREATE TABLE IF NOT EXISTS products (
        id       INTEGER PRIMARY KEY AUTOINCREMENT,
        name     TEXT    NOT NULL,
        category TEXT,
        price    INTEGER,
        stock    INTEGER DEFAULT 0
    )
""")
conn.commit()

코드 설명:

• 2행: pandas(판다스) 라이브러리를 pd라는 별명으로 불러온다.
• 4행: store.db 파일을 생성하거나 연결한다.
• 7~14행: 상품 테이블을 생성한다. 이미 존재하면 건너뛴다.

2.2. CSV(씨에스브이) 파일로 대량 데이터 삽입

엑셀 또는 CSV 파일의 데이터를 DB에 한 번에 넣는 방법이다. 먼저 pandas로 샘플 데이터를 만들어 삽입한다.

# 샘플 데이터 생성
data = {
    "name"    : ["아이패드", "갤럭시탭", "맥북프로", "갤럭시북", "아이폰15", "갤럭시S24"],
    "category": ["태블릿", "태블릿", "노트북", "노트북", "스마트폰", "스마트폰"],
    "price"   : [1200000, 800000, 2800000, 1500000, 1350000, 1250000],
    "stock"   : [30, 45, 15, 20, 60, 55],
}

df = pd.DataFrame(data)

# DataFrame(데이터프레임)을 DB에 한 번에 삽입
df.to_sql("products", conn, if_exists="append", index=False)
conn.commit()

print(f"총 {len(df)}개 상품 삽입 완료")
print(df)

코드 설명:

• 2~7행: 딕셔너리(Dictionary(딕셔너리)) 형태로 샘플 데이터를 만든다.
• 9행: pd.DataFrame()으로 표 형태의 데이터프레임을 생성한다.
• 12행: to_sql()은 데이터프레임을 DB 테이블에 바로 저장한다.
  • "products": 저장할 테이블 이름
  • if_exists="append": 테이블이 이미 있으면 데이터를 추가한다. "replace"는 덮어쓴다.
  • index=False: 데이터프레임의 인덱스(0, 1, 2...)는 저장하지 않는다.

---

3. Pandas를 활용한 DB 테이블의 확인

3.1. read_sql() — SQL 결과를 데이터프레임으로

# 전체 상품 조회
df_all = pd.read_sql("SELECT * FROM products", conn)
print(df_all)

실행 결과:

   id    name category    price  stock
0   1  아이패드    태블릿  1200000     30
1   2  갤럭시탭    태블릿   800000     45
2   3  맥북프로    노트북  2800000     15
3   4  갤럭시북    노트북  1500000     20
4   5  아이폰15  스마트폰  1350000     60
5   6  갤럭시S24  스마트폰  1250000     55

코드 설명:

• 2행: pd.read_sql(SQL문, 연결객체)는 SQL 실행 결과를 바로 데이터프레임으로 변환한다.
• 3행: 주피터 노트북에서는 print() 없이 변수명만 입력해도 표 형태로 보인다.

3.2. 조건 필터링

# 100만원 이상 상품만 조회
df_high = pd.read_sql(
    "SELECT name, price FROM products WHERE price >= 1000000",
    conn
)
print(df_high)

3.3. 데이터프레임으로 분석

# 카테고리별 평균 가격 계산
df_all = pd.read_sql("SELECT * FROM products", conn)

avg_price = df_all.groupby("category")["price"].mean()
print("=== 카테고리별 평균 가격 ===")
print(avg_price)

실행 결과:

=== 카테고리별 평균 가격 ===
category
노트북      2150000.0
스마트폰    1300000.0
태블릿      1000000.0

코드 설명:

• 4행: groupby("category")는 category 값이 같은 행끼리 묶는다.
• 4행: ["price"].mean()은 묶인 그룹에서 price 컬럼의 평균을 계산한다.

3.4. 데이터프레임 → CSV 저장

df_all = pd.read_sql("SELECT * FROM products", conn)
df_all.to_csv("products_backup.csv", index=False, encoding="utf-8-sig")
print("CSV 저장 완료!")

코드 설명:

• 2행: to_csv()로 데이터프레임을 CSV 파일로 저장한다.
• encoding="utf-8-sig": 엑셀에서 한글이 깨지지 않도록 BOM(봄) 인코딩을 사용한다.

---

4. 활용 Plus

4.1. 외부 CSV → DB 저장

실제 데이터 파일을 DB에 저장하는 실무 패턴이다.

# 외부 CSV를 읽어 DB에 저장
df_csv = pd.read_csv("외부데이터.csv")
df_csv.to_sql("external_table", conn, if_exists="replace", index=False)
conn.commit()
print("외부 데이터 저장 완료")

코드 설명:

• 2행: pd.read_csv()로 CSV 파일을 데이터프레임으로 읽는다.
• 3행: if_exists="replace"는 테이블이 있으면 삭제 후 새로 만들어 저장한다.

4.2. 리액트 연동과의 관계

리액트에서 상품 목록 페이지를 만들 때 파이썬 API 서버는 아래와 같이 동작한다.

리액트 → GET /api/products
        → SELECT * FROM products (pandas 또는 sqlite3)
        → JSON 변환 (df.to_dict('records'))
        → 리액트로 반환

df.to_dict('records')는 데이터프레임을 JSON(제이슨) 형태로 변환하는 핵심 메서드이다.

df = pd.read_sql("SELECT * FROM products", conn)
json_data = df.to_dict("records")
print(json_data[:2])

실행 결과:

[{'id': 1, 'name': '아이패드', 'category': '태블릿', 'price': 1200000, 'stock': 30},
 {'id': 2, 'name': '갤럭시탭', 'category': '태블릿', 'price': 800000, 'stock': 45}]

---

5. 문제풀기

Q1. pd.read_sql()과 cur.execute() + fetchall()의 차이점은?

cur.execute() + fetchall()은 결과를 튜플 리스트로 반환한다. pd.read_sql()은 결과를 데이터프레임(표 형태)으로 반환하여 분석 및 가공이 쉽다.

Q3. 한글 CSV 파일을 엑셀에서 열 때 한글이 깨지지 않게 저장하려면?

to_csv() 호출 시 encoding='utf-8-sig' 옵션을 사용한다.

Q4. 데이터프레임을 JSON 형태로 변환하는 메서드는?

df.to_dict('records') 리스트 안에 딕셔너리 형태로 변환되어 API 응답값으로 사용하기 적합하다.