Pandas SQL 쿼리 이용하기

Pandas SQL 쿼리 이용하기

Database
Pandas
Pandas SQL 쿼리 이용하기
Author

gabriel yang

Published

September 11, 2024


이번 블로그 글에서는 Pandas의 read_sql_query 함수에 대해 설명하고, 이를 활용하여 SQL 쿼리 결과를 데이터프레임으로 불러오는 방법을 예제와 함께 살펴보겠습니다. Pandas는 강력한 데이터 처리 도구이며, SQL 데이터베이스에서 데이터를 가져와 분석할 때 매우 유용하게 사용할 수 있습니다.

1. read_sql_query 함수란?

Pandas의 read_sql_query 함수는 SQL 쿼리의 결과를 데이터프레임(DataFrame)으로 변환해주는 함수입니다. SQL을 사용하여 데이터베이스에서 데이터를 조회하고, 그 결과를 Pandas 데이터프레임으로 불러와 데이터 분석을 쉽게 수행할 수 있습니다.

이 함수는 SQLite, MySQL, PostgreSQL 등 다양한 관계형 데이터베이스와 함께 사용할 수 있으며, SQLAlchemy를 통해 데이터베이스 연결을 관리합니다.

2. read_sql_query 함수의 기본 구조

read_sql_query 함수의 기본적인 구조는 다음과 같습니다:

pandas.read_sql_query(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, chunksize=None)
  • sql: 실행할 SQL 쿼리 문자열입니다.
  • con: 데이터베이스 연결 객체입니다. sqlite3, SQLAlchemy, 또는 pymysql 같은 데이터베이스 드라이버를 사용할 수 있습니다.
  • index_col (선택사항): 데이터프레임의 인덱스로 사용할 열을 지정합니다.
  • params (선택사항): 쿼리에 전달할 파라미터입니다.
  • chunksize (선택사항): 데이터프레임을 여러 청크로 분할하여 읽을 수 있는 크기입니다.

3. read_sql_query 함수 사용 예제

SQLite 데이터베이스에서 데이터를 조회하여 Pandas 데이터프레임으로 불러오는 예제를 통해 read_sql_query 함수의 활용 방법을 알아보겠습니다.

3.1 SQLite 데이터베이스 설정

우선, SQLite 데이터베이스에 간단한 테이블을 생성하고 데이터를 삽입한 후, 이를 Pandas를 사용해 조회하는 예제를 살펴보겠습니다.

3.1.1 SQLite 데이터베이스에 테이블 생성

import sqlite3

# SQLite 데이터베이스 연결
conn = sqlite3.connect('example.db')

# 커서 생성
cursor = conn.cursor()

# 테이블 내용 삭제
cursor.execute('''
    DELETE from users
''')

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

# 데이터 삽입
cursor.execute('INSERT INTO users (name, age) VALUES (?, ?)', ('Alice', 25))
cursor.execute('INSERT INTO users (name, age) VALUES (?, ?)', ('Bob', 30))
cursor.execute('INSERT INTO users (name, age) VALUES (?, ?)', ('Charlie', 22))

# 변경사항 저장
conn.commit()

# 연결 종료
conn.close()

3.2 Pandas의 read_sql_query로 데이터 조회

이제 Pandas의 read_sql_query를 사용해 위에서 생성한 users 테이블의 데이터를 조회하고, 그 결과를 데이터프레임으로 가져오는 예제를 보겠습니다.

import sqlite3
import pandas as pd

# SQLite 데이터베이스 연결
conn = sqlite3.connect('example.db')

# SQL 쿼리 실행 및 데이터프레임으로 변환
df = pd.read_sql_query("SELECT * FROM users", conn)

# 연결 종료
conn.close()

# 데이터프레임 출력
print(df)
   id     name  age
0  67    Alice   25
1  68      Bob   30
2  69  Charlie   22

코드 설명:

  1. SQLite 데이터베이스 연결: sqlite3.connect()를 사용해 SQLite 데이터베이스 파일에 연결합니다.
  2. SQL 쿼리 실행: pd.read_sql_query()를 사용해 SQL 쿼리를 실행하고, 그 결과를 데이터프레임으로 변환합니다. 여기서 SELECT * FROM users 쿼리는 users 테이블의 모든 데이터를 조회합니다.
  3. 연결 종료: conn.close()로 데이터베이스 연결을 종료합니다.
  4. 데이터프레임 출력: print(df)로 쿼리 결과를 확인할 수 있습니다.

4. 파라미터를 사용한 read_sql_query 예제

SQL 쿼리에 파라미터를 전달할 수 있습니다. 예를 들어, 특정 나이 이상의 사용자만 조회하는 쿼리를 실행해보겠습니다.

import sqlite3
import pandas as pd

# SQLite 데이터베이스 연결
conn = sqlite3.connect('example.db')

# 특정 나이 이상의 사용자만 조회 (파라미터 사용)
min_age = 25
df = pd.read_sql_query("SELECT * FROM users WHERE age >= ?", conn, params=(min_age,))

# 연결 종료
conn.close()

# 데이터프레임 출력
print(df)
   id   name  age
0  67  Alice   25
1  68    Bob   30

코드 설명:

  1. 파라미터 전달: params 매개변수를 사용해 SQL 쿼리에 파라미터(min_age)를 전달합니다. 이 예제에서는 나이가 25 이상인 사용자만 조회합니다.
  2. ? 플레이스홀더 사용: SQL 쿼리에서 파라미터 자리에 ?를 사용하여, 파라미터 값이 안전하게 삽입되도록 합니다.

5. 데이터프레임에서 날짜 형식으로 변환하기

SQL 쿼리에서 날짜 데이터를 다룰 때는 parse_dates 파라미터를 사용하여 특정 열을 날짜 형식으로 자동 변환할 수 있습니다.

import sqlite3
import pandas as pd

# SQLite 데이터베이스 연결
conn = sqlite3.connect('example.db')

cursor = conn.cursor()
# 테이블 내용 삭제
cursor.execute('''
    DELETE from orders
''')

# 테이블 생성 및 날짜 데이터 삽입
cursor.execute('''
    CREATE TABLE IF NOT EXISTS orders (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        order_date TEXT NOT NULL,
        amount REAL NOT NULL
    )
''')
cursor.execute('INSERT INTO orders (order_date, amount) VALUES (?, ?)', ('2023-09-01', 100.50))
cursor.execute('INSERT INTO orders (order_date, amount) VALUES (?, ?)', ('2023-09-05', 200.00))
conn.commit()

# 변환없이 데이터 로딩
df_without_parse_dates = pd.read_sql_query("SELECT * FROM orders", conn)
# 날짜 데이터를 포함한 쿼리 실행
df = pd.read_sql_query("SELECT * FROM orders", conn, parse_dates=['order_date'])

# 연결 종료
conn.close()
df
id order_date amount
0 33 2023-09-01 100.5
1 34 2023-09-05 200.0 
# 데이터프레임 출력
print("변환없이 데이터 로딩")
display(df_without_parse_dates.info())
변환없이 데이터 로딩
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 2 entries, 0 to 1
Data columns (total 3 columns):
 #   Column      Non-Null Count  Dtype  
---  ------      --------------  -----  
 0   id          2 non-null      int64  
 1   order_date  2 non-null      object 
 2   amount      2 non-null      float64
dtypes: float64(1), int64(1), object(1)
memory usage: 180.0+ bytes
None

위의 결과는 parse_dates를 사용하지 않고 로딩한 데이터 프레임의 type 정보를 보여줍니다. 변환하지 않은 order_date열은 object타입입니다.

print("날짜 데이터를 포함한 쿼리 실행")
display(df.info())
날짜 데이터를 포함한 쿼리 실행
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 2 entries, 0 to 1
Data columns (total 3 columns):
 #   Column      Non-Null Count  Dtype         
---  ------      --------------  -----         
 0   id          2 non-null      int64         
 1   order_date  2 non-null      datetime64[ns]
 2   amount      2 non-null      float64       
dtypes: datetime64[ns](1), float64(1), int64(1)
memory usage: 180.0 bytes
None

반면 변환을 통해 얻은 데이터프레임의 order_date열은 datetime64 타입으로 저장됩니다.

코드 설명:

  1. parse_dates 파라미터: parse_dates=['order_date']를 사용해 order_date 열을 날짜 형식으로 자동 변환합니다.
  2. 날짜 형식 처리: Pandas는 parse_dates를 사용하여 문자열로 저장된 날짜 데이터를 datetime 형식으로 변환합니다. 이를 통해 날짜 데이터를 더욱 쉽게 분석할 수 있습니다.

6. 데이터 청크 단위로 읽기

대용량 데이터를 처리할 때 메모리 효율을 위해 chunksize 파라미터를 사용하여 데이터를 청크 단위로 읽을 수 있습니다.

import sqlite3
import pandas as pd

# SQLite 데이터베이스 연결
conn = sqlite3.connect('example.db')

# 청크 단위로 데이터 읽기
chunksize = 2
for chunk in pd.read_sql_query("SELECT * FROM users", conn, chunksize=chunksize):
    print(chunk)

# 연결 종료
conn.close()
   id   name  age
0  67  Alice   25
1  68    Bob   30
   id     name  age
0  69  Charlie   22

코드 설명:

  1. chunksize 파라미터: 데이터를 청크 단위로 읽어 메모리 효율성을 높입니다. 이 예제에서는 chunksize=2로 설정하여 두 개의 행씩 데이터를 불러옵니다.
  2. 청크 반복 처리: for chunk in pd.read_sql_query() 구문을 사용해 각 청크를 반복 처리합니다.

7. 결론

Pandas의 read_sql_query 함수는 SQL 쿼리의 결과를 데이터프레임으로 변환하는 매우 유용한 도구입니다. SQLite, MySQL, PostgreSQL 등 다양한 데이터베이스와 연동하여 SQL 쿼리 결과를 쉽게 분석할 수 있습니다.