PYTHON : pandas 로 MS-SQL 연결하여 EXCEL, CSV 생성하기

PYTHON : pandas 로 MS-SQL 연결하여 EXCEL, CSV 생성하기

 

 

미션

 

2021년 부터 ~ 2024년까지 MSSQL 의 특정 테이블 데이터를 월별로 EXCEL 또는 CSV 로 추출하기

 

 

진행

 

Windows 11 환경에서 Python을 사용하여 MSSQL 쿼리를 실행하고, 그 결과를 Excel 파일로 저장하는 작업을 수행하기 위해 필요한 전체 코드를 제공하겠습니다. 이 코드에서는 pandas, pyodbc, 및 openpyxl 라이브러리를 사용합니다.

 

Python 환경 설정 먼저 필요한 라이브러리들을 설치합니다. 터미널에서 다음 명령어를 실행하세요.

 

pip install pandas pyodbc openpyxl

 

 

 

MS-SQL 에서 pandas 활용하여 EXCEL(XLSX) 저장하기

import pyodbc
import pandas as pd

# MSSQL 서버에 연결하는 함수
def connect_to_mssql(server, database, username, password):
    connection_string = (
        f"DRIVER={{ODBC Driver 17 for SQL Server}};"
        f"SERVER={server};"
        f"DATABASE={database};"
        f"UID={username};"
        f"PWD={password};"
    )
    return pyodbc.connect(connection_string)

# SQL 쿼리를 실행하고 결과를 DataFrame으로 반환하는 함수
def execute_query(connection, query):
    return pd.read_sql(query, connection)

# 결과를 Excel 파일로 저장하는 함수
def save_to_excel(df, file_name):
    df.to_excel(file_name, index=False)

# 메인 작업 함수
def main():
    # 데이터베이스 연결 정보
    server = 'your_server_name'
    database = 'your_database_name'
    username = 'your_username'
    password = 'your_password'

    # MSSQL 연결
    connection = connect_to_mssql(server, database, username, password)

    # 년도와 월별로 쿼리 생성 및 실행
    for year in range(2021, 2025):
        for month in range(1, 13):
            query = f"""
            SELECT *
            FROM [dbo].[UNIV_COM_TrackingUser]
            WHERE YEAR(LocalTimeAccessed) = {year}
            AND MONTH(LocalTimeAccessed) = {month};
            """

            # 쿼리 실행
            df = execute_query(connection, query)

            # 파일명 설정 (예: "2021_01.xlsx")
            file_name = f"{year}_{str(month).zfill(2)}.xlsx"

            # Excel 파일로 저장
            save_to_excel(df, file_name)

            print(f"Saved: {file_name}")

    # 연결 종료
    connection.close()

if __name__ == "__main__":
    main()

 

 

 

1. MSSQL 데이터베이스에 연결하고, 각 쿼리를 실행한 후 결과를 Excel 파일로 저장하는 코드입니다.
2. 코드 설명
   • MSSQL 서버 연결: connect_to_mssql 함수에서 pyodbc를 사용하여 MSSQL 서버에 연결합니다. ODBC Driver 17 for SQL Server 드라이버를 사용합니다. 연결 정보는 server, database, username, password로 입력받습니다.
   • 쿼리 실행 및 데이터프레임으로 변환: execute_query 함수는 주어진 SQL 쿼리를 실행하고 결과를 pandas의 DataFrame으로 변환합니다.
   • Excel 파일로 저장: save_to_excel 함수는 쿼리 결과를 Excel 파일로 저장합니다. 파일명은 년도_월.xlsx 형식으로 설정됩니다.
   • 메인 함수: main 함수는 2021년부터 2024년까지의 각 월별 데이터를 가져와 각각의 Excel 파일로 저장하는 전체 작업을 수행합니다.
3. 실행하면, 해당 디렉터리에 각 년도와 월에 따른 Excel 파일이 생성됩니다. 각 파일은 YYYY_MM.xlsx 형식으로 저장됩니다.

이 방법을 통해 원하는 모든 데이터를 개별 Excel 파일로 저장할 수 있습니다.

 

 

 

MS-SQL 에서 pandas 활용하여 CSV 저장하기

 

 

CSV 파일로 저장하고 날짜 형식을 yyyy-MM-dd HH:mm:ss로 지정하는 Python 코드를 아래에 제공하겠습니다. 이 코드에서는 strftime을 사용하여 날짜 형식을 지정합니다.

 

 

import pyodbc
import pandas as pd

# MSSQL 서버에 연결하는 함수
def connect_to_mssql(server, database, username, password):
    connection_string = (
        f"DRIVER={{ODBC Driver 17 for SQL Server}};"
        f"SERVER={server};"
        f"DATABASE={database};"
        f"UID={username};"
        f"PWD={password};"
    )
    return pyodbc.connect(connection_string)

# SQL 쿼리를 실행하고 결과를 DataFrame으로 반환하는 함수
def execute_query(connection, query):
    return pd.read_sql(query, connection)

# 결과를 CSV 파일로 저장하는 함수
def save_to_csv(df, file_name):
    # 날짜 형식을 yyyy-MM-dd HH:mm:ss로 지정
    df['LocalTimeAccessed'] = df['LocalTimeAccessed'].dt.strftime('%Y-%m-%d %H:%M:%S')
    df.to_csv(file_name, index=False, encoding='utf-8-sig')

# 메인 작업 함수
def main():
    # 데이터베이스 연결 정보
    server = 'your_server_name'
    database = 'your_database_name'
    username = 'your_username'
    password = 'your_password'

    # MSSQL 연결
    connection = connect_to_mssql(server, database, username, password)

    # 년도와 월별로 쿼리 생성 및 실행
    for year in range(2021, 2025):
        for month in range(1, 13):
            query = f"""
            SELECT *
            FROM [dbo].[UNIV_COM_TrackingUser]
            WHERE YEAR(LocalTimeAccessed) = {year}
            AND MONTH(LocalTimeAccessed) = {month};
            """

            # 쿼리 실행
            df = execute_query(connection, query)

            # 파일명 설정 (예: "2021_01.csv")
            file_name = f"{year}_{str(month).zfill(2)}.csv"

            # CSV 파일로 저장
            save_to_csv(df, file_name)

            print(f"Saved: {file_name}")

    # 연결 종료
    connection.close()

if __name__ == "__main__":
    main()

 

 

 

코드 설명:

1. CSV 파일로 저장: save_to_csv 함수는 데이터프레임을 CSV 파일로 저장합니다. utf-8-sig 인코딩을 사용하여 CSV 파일을 저장하며, LocalTimeAccessed 열의 날짜 형식을 strftime을 사용해 yyyy-MM-dd HH:mm:ss 형식으로 변환합니다.
2. 날짜 형식 변환: strftime('%Y-%m-%d %H:%M:%S')를 사용해 날짜와 시간을 원하는 형식으로 변환합니다.
3. CSV 파일 생성: 각 년도와 월에 대해 별도의 CSV 파일을 생성합니다. 파일명은 YYYY_MM.csv 형식으로 지정됩니다.

 

 

이 코드를 실행하면, 각 년도와 월별로 데이터를 포함한 CSV 파일이 생성되며, 날짜와 시간은 지정한 형식(yyyy-MM-dd HH:mm:ss)으로 저장됩니다. 이 방식으로 row 제한 없이 데이터를 저장할 수 있습니다.

 

 

---

부가 설명

 

pandas 라이브러리의 DataFrame 객체를 사용하여 데이터를 Excel 또는 CSV 파일로 저장하는 df.to_excel()과 df.to_csv() 메서드에 대해 자세히 설명하겠습니다.

 

 

1. df.to_excel() 메서드

 

df.to_excel() 메서드는 pandas DataFrame을 Excel 파일로 저장하는 데 사용됩니다. 이 메서드는 다양한 옵션을 제공하여 파일 이름, 시트 이름, 인덱스 포함 여부 등을 지정할 수 있습니다.

주요 매개변수:

• excel_writer: Excel 파일의 이름이나 경로를 지정합니다. .xlsx 또는 .xls 확장자를 사용해야 합니다. 또한, ExcelWriter 객체를 전달할 수도 있습니다.
• sheet_name: 저장할 시트의 이름을 지정합니다. 기본값은 'Sheet1'입니다.
• na_rep: NaN 값을 Excel 파일에서 대체할 문자열을 지정합니다. 예를 들어, 'NULL'로 지정할 수 있습니다.
• float_format: 실수 데이터를 저장할 때 사용할 형식을 지정합니다. 예를 들어, '%.2f'는 소수점 둘째 자리까지 포맷팅합니다.
• columns: 저장할 열의 리스트를 지정합니다. 이 리스트에 포함된 열만 파일에 저장됩니다.
• header: 열 이름을 첫 번째 행에 포함할지 여부를 지정합니다. 기본값은 True입니다.
• index: DataFrame의 인덱스를 파일에 포함할지 여부를 지정합니다. 기본값은 True입니다.
• startrow: 데이터가 시작될 행 번호를 지정합니다. 기본값은 0입니다.
• startcol: 데이터가 시작될 열 번호를 지정합니다. 기본값은 0입니다.
• engine: Excel 파일을 작성할 때 사용할 엔진을 지정합니다. 일반적으로 openpyxl 또는 xlsxwriter를 사용합니다.
• freeze_panes: 첫 번째 행이나 열을 고정하여 스크롤할 때 항상 보이게 할 수 있습니다. 예를 들어 (1, 0)을 지정하면 첫 번째 행이 고정됩니다.

예제:

df.to_excel('output.xlsx', sheet_name='Sheet1', index=False)

 

위의 코드는 DataFrame을 output.xlsx라는 파일로 저장하며, 시트 이름은 Sheet1이고 인덱스는 포함되지 않습니다.

 

 

 

2. df.to_csv() 메서드

 

df.to_csv() 메서드는 pandas DataFrame을 CSV 파일로 저장하는 데 사용됩니다. 이 메서드는 CSV 파일의 구분자, 인코딩, 파일 압축 등 다양한 옵션을 제공합니다.

 

주요 매개변수:

• path_or_buf: CSV 파일의 이름이나 경로를 지정합니다. .csv 확장자를 사용해야 합니다.
• sep: CSV 파일에서 사용할 구분자를 지정합니다. 기본값은 쉼표(,)입니다. 다른 구분자를 사용하려면 sep='\t'와 같이 지정할 수 있습니다.
• na_rep: NaN 값을 CSV 파일에서 대체할 문자열을 지정합니다. 예를 들어, 'NULL'로 지정할 수 있습니다.
• float_format: 실수 데이터를 저장할 때 사용할 형식을 지정합니다. 예를 들어, '%.2f'는 소수점 둘째 자리까지 포맷팅합니다.
• columns: 저장할 열의 리스트를 지정합니다. 이 리스트에 포함된 열만 파일에 저장됩니다.
• header: 열 이름을 첫 번째 행에 포함할지 여부를 지정합니다. 기본값은 True입니다.
• index: DataFrame의 인덱스를 파일에 포함할지 여부를 지정합니다. 기본값은 True입니다.
• index_label: 인덱스 열의 레이블을 지정합니다. 인덱스를 CSV 파일에 포함하는 경우 사용할 이름을 지정할 수 있습니다.
• mode: 파일을 쓰는 모드를 지정합니다. 기본값은 'w'로, 파일을 새로 생성하여 씁니다. 'a'를 사용하면 기존 파일에 데이터를 추가할 수 있습니다.
• encoding: 파일 인코딩을 지정합니다. 기본값은 None이며, 일반적으로 'utf-8', 'utf-8-sig' 등이 사용됩니다.
• compression: CSV 파일을 압축할지 여부와 그 방식을 지정합니다. 'gzip', 'bz2', 'zip', 'xz' 등이 가능합니다.

 

예제:

df.to_csv('output.csv', index=False, encoding='utf-8-sig')

 

위의 코드는 DataFrame을 output.csv라는 파일로 저장하며, 인덱스는 포함되지 않고 파일 인코딩은 utf-8-sig입니다.

 

 

요약:

• df.to_excel(): Excel 파일로 DataFrame을 저장하며, 시트 이름, 열 제목, 인덱스 포함 여부 등을 설정할 수 있습니다.
• df.to_csv(): CSV 파일로 DataFrame을 저장하며, 구분자, 인코딩, 인덱스 포함 여부 등을 설정할 수 있습니다.

이 두 메서드는 데이터 분석 결과를 파일로 저장하거나, 다른 시스템으로 데이터를 전달할 때 매우 유용합니다. to_excel은 Excel 파일을 직접 생성할 수 있어 Excel을 사용하는 작업 환경에 유리하고, to_csv는 CSV 형식으로 데이터를 저장하여 대부분의 데이터 처리 시스템에 쉽게 통합할 수 있습니다.