Pandas SQL 파일 포맷 다루기

Pandas SQL 파일 포맷 다루기

Python의 pandas 라이브러리는 다양한 데이터 포맷을 지원하며, 특히 SQL 데이터베이스와의 연동이 강력한 기능 중 하나입니다. 데이터 분석 과정에서 관계형 데이터베이스에 저장된 데이터를 불러오거나 가공한 후 다시 저장하는 일이 빈번하게 발생합니다. 이번 포스팅에서는 pandas를 이용해 SQL 데이터베이스의 데이터를 로드하고 저장하는 방법을 살펴보겠습니다.

1. SQLite와 pandas 연동

SQL 데이터베이스 중에서도 SQLite는 가벼운 파일 기반 데이터베이스로, 별도의 서버 설정 없이 사용할 수 있습니다. pandas는 SQLite와의 연동을 기본적으로 지원하며, sqlite3 또는 SQLAlchemy를 활용하여 연결할 수 있습니다.

1.1 SQLite 데이터베이스 생성 및 연결

SQLite 데이터베이스를 생성하고 pandas에서 활용하려면 sqlite3 모듈을 사용합니다.

import sqlite3
import pandas as pd

# 데이터베이스 연결 (파일이 없으면 자동 생성됨)
conn = sqlite3.connect("example.db")
cursor = conn.cursor()

# 테이블 생성
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER,
    city TEXT
)
''')
conn.commit()

위 코드에서는 example.db라는 SQLite 데이터베이스 파일을 생성하고, users라는 테이블을 만듭니다. 테이블이 이미 존재할 경우 오류를 방지하기 위해 IF NOT EXISTS 옵션을 추가했습니다.

1.2 데이터 삽입

SQLite에 데이터를 삽입하려면 INSERT INTO 문을 사용할 수 있습니다.

# 샘플 데이터 삽입
users_data = [
    (1, "Alice", 25, "New York"),
    (2, "Bob", 30, "Los Angeles"),
    (3, "Charlie", 28, "Chicago")
]

cursor.executemany("INSERT INTO users VALUES (?, ?, ?, ?)", users_data)
conn.commit()

위 코드에서는 executemany()를 사용해 여러 개의 데이터를 한 번에 삽입합니다.

2. SQL에서 데이터 로딩

SQL 데이터베이스에서 데이터를 pandas DataFrame으로 불러오려면 pd.read_sql_query() 또는 pd.read_sql()을 사용할 수 있습니다.

# 데이터 로드
query = "SELECT * FROM users"
df = pd.read_sql_query(query, conn)
print(df)

출력 결과는 다음과 같습니다.

   id    name  age         city
0   1  Alice   25    New York
1   2    Bob   30  Los Angeles
2   3 Charlie   28     Chicago

위 방법을 사용하면 SQL 데이터를 pandas의 DataFrame 형식으로 변환하여 데이터 분석에 활용할 수 있습니다.

3. DataFrame을 SQL 테이블로 저장

pandas의 to_sql() 메서드를 사용하면 DataFrame을 SQL 데이터베이스에 쉽게 저장할 수 있습니다.

# 새로운 데이터 생성
data = {
    "id": [4, 5],
    "name": ["David", "Emma"],
    "age": [35, 27],
    "city": ["San Francisco", "Boston"]
}

df_new = pd.DataFrame(data)

# DataFrame을 users 테이블에 저장
df_new.to_sql("users", conn, if_exists="append", index=False)

여기서 if_exists="append" 옵션을 사용하면 기존 테이블에 데이터를 추가할 수 있습니다. 만약 테이블을 새로 생성하고 싶다면 if_exists="replace" 옵션을 사용할 수 있습니다.

4. SQLAlchemy를 이용한 데이터베이스 연결

SQLite 외에도 MySQL, PostgreSQL 등 다양한 데이터베이스와 연결하려면 SQLAlchemy 라이브러리를 사용할 수 있습니다.

from sqlalchemy import create_engine

# SQLite 엔진 생성
engine = create_engine("sqlite:///example.db")

# SQL 쿼리 실행
df = pd.read_sql("SELECT * FROM users", engine)
print(df)

create_engine()을 사용하면 다양한 데이터베이스와의 연결이 간편해집니다. MySQL 또는 PostgreSQL과 연결하려면 다음과 같이 데이터베이스 URL을 지정하면 됩니다.

# MySQL 연결 예제
engine = create_engine("mysql+pymysql://user:password@host:port/database")

5. SQL 데이터 변환 및 저장 활용 예제

SQL 데이터를 pandas로 불러와 간단한 분석 후 다시 저장하는 과정을 예제로 살펴보겠습니다.

# 데이터 로드
df = pd.read_sql("SELECT * FROM users", conn)

# 특정 조건에 맞는 데이터 필터링
df_filtered = df[df["age"] > 28]

# 필터링된 데이터를 새로운 테이블로 저장
df_filtered.to_sql("filtered_users", conn, if_exists="replace", index=False)

위 코드에서는 나이가 28세 이상인 사용자만 필터링하여 filtered_users 테이블에 저장합니다.

6. 데이터베이스 연결 종료

작업이 끝난 후에는 데이터베이스 연결을 닫아야 합니다.

conn.close()

마무리

이번 포스팅에서는 pandas를 이용해 SQL 데이터베이스와 상호작용하는 방법을 다뤘습니다. SQLite를 사용하여 데이터베이스를 생성하고, 데이터를 삽입하고, 불러오고, 저장하는 과정을 살펴보았으며, SQLAlchemy를 활용한 확장 가능한 접근법도 소개했습니다. pandas의 SQL 연동 기능을 활용하면 데이터베이스에서 데이터를 쉽게 분석하고 가공할 수 있습니다. 다양한 데이터베이스 환경에서 pandas를 활용해 보다 효율적인 데이터 분석을 수행해 보시길 바랍니다.