04 Pandas를 활용한 SQL 명령문 실행

1. 인트로

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

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

2.1. 실습용 DB 준비

1
import sqlite3
2
import pandas as pd
3

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

7
cur.execute("""
8
    CREATE TABLE IF NOT EXISTS products (
9
        id       INTEGER PRIMARY KEY AUTOINCREMENT,
10
        name     TEXT    NOT NULL,
11
        category TEXT,
12
        price    INTEGER,
13
        stock    INTEGER DEFAULT 0
14
    )
15
""")
16
conn.commit()

코드 설명:

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

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

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

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

9
df = pd.DataFrame(data)
10

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

15
print(f"총 {len(df)}개 상품 삽입 완료")
16
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 결과를 데이터프레임으로

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

실행 결과:

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

코드 설명:

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

3.2. 조건 필터링

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

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

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

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

실행 결과:

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

코드 설명:

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

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

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

코드 설명:

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

4. 활용 Plus

4.1. 외부 CSV → DB 저장

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

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

코드 설명:

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

4.2. 리액트 연동과의 관계

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

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

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

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

실행 결과:

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

5. 문제풀기

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

append: 기존 테이블에 데이터를 추가한다. replace: 기존 테이블을 삭제하고 새로 만들어 저장한다.

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

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