소개
실제 기업 비즈니스 환경에서는 대부분의 데이터가 텍스트나 엑셀 파일 형태로 저장되지 않습니다. Oracle, SQL Server, PostgreSQL, MySQL과 같은 SQL 기반 관계형 데이터베이스가 널리 사용되고 있으며, 그 외의 대체 데이터베이스들 역시 상당한 인기를 얻고 있습니다.
데이터베이스 선택은 일반적으로 애플리케이션의 성능, 데이터 무결성, 확장성 요구 사항에 따라 달라집니다.
SQLite3 데이터베이스 생성 방법
이번 예제에서는 SQLite3 데이터베이스를 생성하는 방법을 살펴보겠습니다. SQLite는 파이썬 설치 시 기본적으로 포함되어 있으므로 별도의 설치 과정이 필요하지 않습니다. 확실하지 않다면 아래 코드를 직접 실행해 확인해 보세요. 또한 Pandas 라이브러리도 함께 임포트합니다.
SQL 데이터베이스에서 DataFrame으로 데이터를 불러오는 작업은 매우 간단하며, pandas는 이 과정을 손쉽게 처리할 수 있는 다양한 함수를 제공합니다.
import sqlite3
import pandas as pd
print(f"Output \n {sqlite3.version}")실행 결과
2.6.0
연결 객체 생성 및 샘플 데이터 준비
먼저 데이터베이스 연결 객체를 만들고, 고객 정보를 담은 DataFrame을 생성합니다.
# 연결 객체 생성
conn = sqlite3.connect("example.db")
# 고객 데이터
customers = pd.DataFrame({
"customerID" : ["a1", "b1", "c1", "d1"]
, "firstName" : ["Person1", "Person2", "Person3", "Person4"]
, "state" : ["VIC", "NSW", "QLD", "WA"]
})
print(f"Output \n *** Customers info -\n {customers}")실행 결과
*** Customers info - customerID firstName state 0 a1 Person1 VIC 1 b1 Person2 NSW 2 c1 Person3 QLD 3 d1 Person4 WA
다음으로 주문 데이터를 담은 DataFrame을 생성합니다.
# 주문 데이터
orders = pd.DataFrame({
"customerID" : ["a1", "a1", "a1", "d1", "c1", "c1"]
, "productName" : ["road bike", "mountain bike", "helmet", "gloves", "road bike", "glasses"]
})
print(f"Output \n *** orders info -\n {orders}")실행 결과
*** orders info - customerID productName 0 a1 road bike 1 a1 mountain bike 2 a1 helmet 3 d1 gloves 4 c1 road bike 5 c1 glasses
데이터베이스에 데이터 저장하기
pandas의 to_sql() 메서드를 사용하면 DataFrame을 데이터베이스 테이블로 손쉽게 저장할 수 있습니다. if_exists="replace" 옵션은 동일한 이름의 테이블이 이미 존재할 경우 기존 테이블을 대체하라는 의미입니다.
# 데이터베이스에 저장
customers.to_sql("customers", con=conn, if_exists="replace", index=False)
orders.to_sql("orders", conn, if_exists="replace", index=False)SQL 쿼리 작성 및 실행
이제 두 테이블을 조인하여 고객별 구매 수량을 집계하는 SQL 쿼리를 작성합니다.
# 데이터를 조회할 SQL 쿼리 작성
q = """
select orders.customerID, customers.firstName, count(*) as productQuantity
from orders
left join customers
on orders.customerID = customers.customerID
group by customers.firstName;
"""pandas의 read_sql_query() 함수를 사용하면 SQL 쿼리 실행 결과를 곧바로 DataFrame으로 받아볼 수 있습니다.
# SQL 실행
pd.read_sql_query(q, con=conn)전체 코드 정리
지금까지 살펴본 과정을 하나의 완전한 예제로 정리하면 다음과 같습니다.
import sqlite3
import pandas as pd
print(f"Output \n {sqlite3.version}")
# 연결 객체 생성
conn = sqlite3.connect("example.db")
# 고객 데이터
customers = pd.DataFrame({
"customerID" : ["a1", "b1", "c1", "d1"]
, "firstName" : ["Person1", "Person2", "Person3", "Person4"]
, "state" : ["VIC", "NSW", "QLD", "WA"]
})
print(f"*** Customers info -\n {customers}")
# 주문 데이터
orders = pd.DataFrame({
"customerID" : ["a1", "a1", "a1", "d1", "c1", "c1"]
, "productName" : ["road bike", "mountain bike", "helmet", "gloves", "road bike", "glasses"]
})
print(f"*** orders info -\n {orders}")
# 데이터베이스에 저장
customers.to_sql("customers", con=conn, if_exists="replace", index=False)
orders.to_sql("orders", conn, if_exists="replace", index=False)
# 데이터를 조회할 SQL 쿼리 작성
q = """
select orders.customerID, customers.firstName, count(*) as productQuantity
from orders
left join customers
on orders.customerID = customers.customerID
group by customers.firstName;
"""
# SQL 실행
pd.read_sql_query(q, con=conn)최종 실행 결과
2.6.0 *** Customers info - customerID firstName state 0 a1 Person1 VIC 1 b1 Person2 NSW 2 c1 Person3 QLD 3 d1 Person4 WA *** orders info - customerID productName 0 a1 road bike 1 a1 mountain bike 2 a1 helmet 3 d1 gloves 4 c1 road bike 5 c1 glasses customerID firstName productQuantity ____________________________________ 0 a1 Person1 3 1 c1 Person3 2 2 d1 Person4 1