큰 CSV를 엑셀 대신 SQL로 다루기, SQLite에 불러와 조회하는 방법

수천 행짜리 CSV에서 도시별 매출 합계나 특정 조건의 주문만 뽑아내려면, 엑셀에서는 피벗 테이블과 필터를 여러 번 거쳐야 합니다. 이럴 때 CSV를 SQLite라는 가벼운 데이터베이스에 한 번 넣어 두면, SQL 한 줄로 원하는 집계를 바로 꺼낼 수 있습니다.

SQLite는 서버를 따로 설치할 필요 없이 파일 하나로 동작하는 데이터베이스입니다. 파이썬에는 기본으로 들어 있어 별도 설치도 필요 없습니다. CSV를 넣고 조회하는 과정을 실제 데이터로 해 보겠습니다.

 

 

CSV를 SQLite로 불러와 조회하는 흐름 도식. sales.csv 8행을 to_sql로 SQLite sales 테이블에 적재하고, 도시별 합계 SQL을 실행해 서울 292000, 대구 242000, 부산 228000 결과 표를 얻는 과정

 

다룰 CSV

주문 내역을 담은 간단한 CSV를 예로 씁니다.

실제로는 수천, 수만 행이어도 방법은 같습니다.

order_id,city,product,amount
1,서울,키보드,32000
2,부산,마우스,18000
3,서울,모니터,210000
...  (모두 8행)

 

1단계: CSV를 SQLite 테이블로 넣기

pandas로 CSV를 읽어 to_sql로 넘기면, 테이블이 자동으로 만들어지며 데이터가 채워집니다.

컬럼 이름과 자료형도 알아서 잡힙니다.

import sqlite3, pandas as pd

df = pd.read_csv("sales.csv")
conn = sqlite3.connect("sales.db")     # 없으면 새로 만들어짐
df.to_sql("sales", conn, if_exists="replace", index=False)

이제 sales.db라는 파일 안에 sales라는 테이블이 생겼습니다.

여기까지가 준비의 전부입니다.

---

2단계: SQL로 조회하기

도시별로 묶어 매출 합계를 구하고 큰 순서로 정렬해 보겠습니다.

엑셀의 피벗에 해당하는 작업이 SQL로는 몇 줄입니다.

SELECT city, COUNT(*) AS 건수, SUM(amount) AS 매출합
FROM sales
GROUP BY city
ORDER BY 매출합 DESC

실제로 실행하면 이런 결과가 나옵니다.

서울   건수 4   매출합 292,000
대구   건수 2   매출합 242,000
부산   건수 2   매출합 228,000

도시마다 몇 건이 있고 매출이 얼마인지 한 번의 질의로 정리됐습니다.

조건을 바꾸고 싶으면 SQL만 고치면 됩니다. 예를 들어 10만 원 이상 주문만 보려면 이렇게 씁니다.

SELECT order_id, city, product, amount
FROM sales
WHERE amount >= 100000
ORDER BY amount DESC

주문 3  서울  모니터  210,000원
주문 5  부산  모니터  210,000원
주문 7  대구  모니터  210,000원

---

3단계: 결과를 다시 표로 받기

조회 결과를 파이썬에서 이어서 다루고 싶다면 read_sql로 받으면 곧바로 표(DataFrame)가 됩니다.

df2 = pd.read_sql(
    "SELECT product, COUNT(*) AS cnt, SUM(amount) AS total "
    "FROM sales GROUP BY product ORDER BY total DESC",
    conn,
)
print(df2)

product  cnt   total
   모니터    3  630000
   키보드    3   96000
   마우스    2   36000

이렇게 하면 CSV로 시작해 SQL로 집계하고 다시 표로 받는 흐름이 하나로 이어집니다.

 

코드 없이 열어 보고 싶다면

만들어진 sales.db 파일은 DB Browser for SQLite 같은 무료 프로그램으로 열면

표 형태로 보면서 SQL을 입력할 수 있습니다.

코드가 부담스러우면 이 도구로 같은 조회를 마우스와 SQL 입력창만으로 할 수 있습니다.

 

자주 묻는 질문

Q. 굳이 SQLite에 넣는 이유가 있나요? 그냥 pandas로 하면 안 되나요?
데이터가 아주 크거나, 같은 데이터를 여러 번 조건 바꿔 조회할 때 SQL이 편합니다.

한 번 넣어 두면 파일로 남아 다음에 다시 읽어 바로 질의할 수 있습니다.

 

Q. 한글이 깨지지 않나요?
SQLite는 내부적으로 UTF-8을 쓰기 때문에 한글이 그대로 저장됩니다.

CSV를 읽을 때 인코딩만 맞으면 조회 결과에도 한글이 정상으로 나옵니다.

 

Q. 테이블을 다시 만들면 기존 데이터는요?
위 코드의 if_exists="replace"는 같은 이름 테이블을 지우고 새로 만듭니다.

기존 데이터에 이어 붙이려면 "append"로 바꾸면 됩니다.

 

CSV를 SQLite에 한 번 올려 두면, 복잡한 집계도 질문을 SQL로 바꿔 던지기만 하면 됩니다.

반복해서 뒤져 봐야 할 데이터가 있다면 이 방법이 시간을 크게 줄여 줍니다.

우선 지금 가진 CSV 하나를 to_sql로 넣어 보는 것부터 시작해 보세요.