4. 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 응답값으로 사용하기 적합하다.