Skip to content
튜토리얼
Pandas
Pandas to_sql() 메소드: 효율적인 SQL 작성을 위한 팁

Pandas to_sql() 메소드: 효율적인 SQL 작성을 위한 팁

Matt Popovic
Name
Matt Popovic

Published on

pandas의 to_sql() 메소드는 DataFrame에서 SQL 데이터베이스로 데이터를 작성하기 위한 강력한 도구입니다. 배치 기계 학습 예측 결과를 다시 보내는, 메트릭을 추적하는, 준비된 데이터세트를 저장하는 등의 시나리오에서 일반적으로 사용됩니다. 그러나 to_sql()을 효과적으로 사용하기 위해서는 해당 매개변수와 잠재적인 실수에 대한 좋은 이해가 필요합니다.

이 글은 pandas에서 to_sql() 메소드를 사용하는 방법에 대한 포괄적인 안내서를 제공하며, 안전하고 효율적인 SQL 작성을 위한 모베인 사례와 팁에 집중합니다. 데이터베이스에 연결하는 것부터 대용량 데이터 세트 처리와 성능 향상까지 모든 내용을 다룰 것입니다.

파이썬 판다스 데이터프레임에서 빠르게 데이터 시각화를 생성하려면의 No code?

PyGWalker는 시각화를 통한 탐색적 데이터 분석을 위한 파이썬 라이브러리입니다. PyGWalker (opens in a new tab)는 판다스 데이터프레임을 (그리고 polars 데이터프레임) Tableau 스타일의 사용자 인터페이스로 변환하여 Jupyter Notebook 데이터 분석 및 데이터 시각화 워크플로우를 단순화할 수 있습니다.

PyGWalker를 사용한 데이터 시각화 (opens in a new tab)

파트 1: 요구 사항 설치 및 데이터베이스 연결

to_sql() 메소드를 사용하기 전에, pandas와 sqlalchemy라는 필수 패키지를 설치해야 합니다. 각 프로젝트에 대해 별도의 Python 환경을 생성하는 것이 좋습니다. 이를 위해 Conda를 사용할 수 있습니다:

conda create -n env_name python=3.9 pandas sqlalchemy
conda activate env_name

필수 패키지를 설치한 후에는 다음 단계로 데이터베이스에 연결을 생성해야 합니다. 이 연결 객체는 sqlite3 또는 sqlalchemy에서 제공될 수 있습니다. 이 가이드에서는 pandas에서 권장하는 방법인 sqlalchemy를 사용할 것입니다.

from sqlalchemy import create_engine
 
# 데이터베이스 계정과 사용자를 사용하여 Snowflake에 연결 생성
account_identifier = '<계정 식별자>'
user = '<사용자 로그인 이름>'
password = '<비밀번호>'
conn_string = f"snowflake://{user}:{password}@{account_identifier}/"
engine = create_engine(conn_string)

파트 2: to_sql() 메소드의 매개변수 이해하기

to_sql() 메소드에는 데이터베이스로 전송되는 기능과 데이터를 조정하는 여러 가지 매개변수가 있습니다. 이에는 다음과 같은 것들이 포함됩니다:

  • name: SQL 테이블 이름 (필수)
  • con: sqlite 또는 sqlalchemy에서 제공하는 연결 (필수)
  • schema: SQL 데이터베이스 스키마
  • if_exists: 테이블이 이미 존재하는 경우 수행할 작업, 'fail', 'replace' 또는 'append' 중 하나
  • index: DataFrame의 인덱스 열을 테이블에 작성할 지 여부
  • index_label: 인덱스 열의 이름 (테이블에 작성되는 경우)
  • chunksize: 한 번에 작성할 행 수
  • dtype: 모든 열과 해당 데이터 유형의 사전
  • method: SQL 삽입 문을 지정

이러한 매개변수를 이해하는 것은 to_sql() 메소드를 효과적으로 사용하는 데 중요합니다.

파트 3: to_sql()을 사용하여 데이터베이스에 작성하기

요구 사항을 설치하고 연결을 생성하며 메소드에 관련된 매개변수를 이해한 후, 이제 데이터베이스에 데이터를 작성할 수 있습니다. 이 예제에서는 매우 간단한 DataFrame을 Snowflake의 테이블에 작성하겠습니다:

import pandas as pd
from sqlalchemy import create
 
```python
# DataFrame 생성
table_name = 'jobs'
df = pd.DataFrame(data=[['Stephen', '데이터 과학자'], ['Jane', '데이터 분석가']], columns=['Name', 'Job'])
 
# 테이블이 이미 존재한다면 어떻게 할 것인지? replace, append, 또는 fail?
if_exists = 'replace'
 
# 데이터를 Snowflake에 작성
with engine.connect() as con:
 df.to_sql(
 name=table_name.lower(),
 con=con,
 if_exists=if_exists
 )

이 코드는 'Name'과 'Job' 두 개의 열과 두 개의 데이터 행을 가진 DataFrame을 생성합니다. 그런 다음 이 DataFrame을 Snowflake의 테이블에 작성합니다. if_exists 매개변수는 'replace'로 설정되어 있으므로, 테이블이 이미 존재한다면 새로운 DataFrame으로 대체됩니다.

파트 4: 데이터베이스 삽입 속도 향상

to_sql() 메소드는 사용하기 쉽고 편리하지만, 대규모 테이블 작업 시 속도가 느릴 수 있습니다. 이는 기본적으로 to_sql()이 한 번에 한 행씩 데이터베이스에 데이터를 작성하기 때문입니다. 이는 대용량 데이터 세트에 대해 비효율적일 수 있으며, 각 삽입 작업에는 상당한 오버헤드가 포함됩니다.

삽입 속도를 향상시키기 위한 한 가지 방법은 일부 데이터베이스 커넥터에서 사용 가능한 fast_executemany 옵션을 사용하는 것입니다. 이 옵션은 to_sql()이 한 번에 한 행이 아닌 일괄적으로 데이터를 데이터베이스에 작성할 수 있도록 해줍니다. 이렇게 하면 삽입 작업의 오버헤드를 크게 줄일 수 있으며, 성능을 크게 향상시킬 수 있습니다.

fast_executemany를 사용하려면 다음과 같이 fast_executemany=True 옵션을 사용하여 엔진을 생성해야 합니다:

engine = create_engine(conn_string, fast_executemany=True)

이 옵션을 활성화하면, to_sql()은 일괄 작업으로 데이터를 데이터베이스에 작성하므로, 특히 대용량 데이터 세트의 경우 한 번에 한 행씩 작성하는 것보다 훨씬 빠를 수 있습니다.

파트 5: NULL 또는 NaN 값 처리하기

to_sql() 메소드를 사용하여 DataFrame을 SQL 데이터베이스에 작성할 때, NULL 또는 NaN 값이 어떻게 처리되는지를 고려하는 것이 중요합니다. 기본적으로 pandas는 DataFrame의 NaN 값을 데이터베이스에 작성할 때 NULL로 변환합니다. 일반적으로 이는 데이터베이스가 데이터 유형과 제약 조건에 일관된 방식으로 누락된 값을 처리할 수 있도록 해줍니다. 그러나 일부 경우에는 NaN 값을 다르게 처리하고 싶을 수 있습니다. 예를 들어 데이터베이스에 쓰기 전에 NaN 값을 특정 값으로 대체하고 싶을 수 있습니다. 이를 위해 판다스의 fillna() 메서드를 사용할 수 있습니다:

df = df.fillna(value)

이 코드는 DataFrame의 모든 NaN 값을 지정된 값으로 대체합니다.

부분 6: 기존 테이블에 레코드 추가하기

to_sql() 메서드는 SQL 데이터베이스의 기존 테이블에 레코드를 추가하는 편리한 방법을 제공합니다. 이를 위해서는 if_exists 매개변수를 'append'로 설정하기만 하면 됩니다:

df.to_sql(name=table_name, con=con, if_exists='append')

이 코드는 DataFrame의 레코드를 기존 테이블에 추가합니다. 테이블이 존재하지 않으면 테이블이 생성됩니다.

부분 7: 기존 레코드 업데이트하기

to_sql() 메서드는 SQL 데이터베이스에서 기존 레코드를 직접적으로 업데이트하는 기능을 제공하지 않지만, to_sql()을 다른 SQL 작업과 결합하여 이를 달성할 수 있습니다.

예를 들어, to_sql()을 사용하여 DataFrame을 데이터베이스의 임시 테이블에 작성한 다음 임시 테이블의 레코드를 기반으로 대상 테이블의 레코드를 업데이트하기 위해 SQL UPDATE 문을 사용할 수 있습니다.

다음은 이를 수행하는 방법의 예시입니다:

# DataFrame을 임시 테이블에 작성하기
df.to_sql(name='temp_table', con=con, if_exists='replace')
 
# SQL UPDATE 문 생성하기
update_sql = """
UPDATE target_table
SET target_table.column1 = temp_table.column1,
    target_table.column2 = temp_table.column2,
    ...
FROM temp_table
WHERE target_table.id = temp_table.id
"""
 
# UPDATE 문 실행하기
with engine.connect() as con:
    con.execute(update_sql)

이 코드는 먼저 DataFrame을 데이터베이스의 임시 테이블에 작성합니다. 그런 다음 임시 테이블의 레코드를 기반으로 대상 테이블의 레코드를 업데이트하는 SQL UPDATE 문을 구성합니다. 마지막으로, 연결 객체를 사용하여 UPDATE 문을 실행합니다.

자주 묻는 질문

  1. pandas의 to_sql() 메서드는 무엇인가요?

pandas의 to_sql() 메서드는 DataFrame의 데이터를 SQL 데이터베이스에 쓸 수 있는 함수입니다. pandas 데이터를 데이터베이스에 장기간 저장하거나 다른 시스템과 공유하거나 SQL로 추가 처리하기 위한 편리한 방법을 제공합니다.

  1. to_sql()을 사용할 때 NULL 또는 NaN 값을 어떻게 처리하나요?

기본적으로 pandas는 DataFrame의 NaN 값을 데이터베이스에 쓸 때 NULL로 변환합니다. NaN 값을 다르게 처리하려면 pandas의 fillna() 메서드를 사용하여 NaN 값을 데이터베이스에 쓰기 전에 특정 값으로 대체할 수 있습니다.

  1. to_sql()을 사용하여 기존 테이블에 레코드를 추가할 수 있나요?

네, to_sql()을 사용하여 SQL 데이터베이스의 기존 테이블에 레코드를 추가할 수 있습니다. 이를 위해서는 to_sql()을 호출할 때 if_exists 매개변수를 'append'로 설정하기만 하면 됩니다.

Pandas set_index 함수 사용 방법Pandas 데이터프레임에서 NaN 값 확인하는 방법