Python을 사용하여 Excel 파일을 데이터베이스 테이블로 가져오는 방법

단계 1: IronXL 라이브러리 설치

첫 번째 단계는 IronXL 라이브러리를 설치하는 것입니다. 명령 프롬프트에서 다음 명령을 실행하여 IronXL을 설치합니다.

pip install IronXL

pip install IronXL

단계 2: Excel 워크북 로드

다음 단계는 Excel 파일을 로드하는 것입니다. 이 튜토리얼에서는 다음 Excel 파일을 사용할 것입니다.

다음 코드가 메모리에 기존 Excel 파일을 로드합니다.

from ironxl import *  # Supported for XLSX, XLS, XLSM, XLTX, CSV, and TSV

# Assign a license key (retrieved from IronXL website)
License.LicenseKey = "IRONSUITE.ABC.XYZ.COM.15796-DEPLOYMENT.TRIAL-5X63V4.TRIAL.EXPIRES.27.MAY.2024"

# Load the Excel workbook into memory
workbook = WorkBook.Load("sample_excel.xlsx")

from ironxl import *  # Supported for XLSX, XLS, XLSM, XLTX, CSV, and TSV

# Assign a license key (retrieved from IronXL website)
License.LicenseKey = "IRONSUITE.ABC.XYZ.COM.15796-DEPLOYMENT.TRIAL-5X63V4.TRIAL.EXPIRES.27.MAY.2024"

# Load the Excel workbook into memory
workbook = WorkBook.Load("sample_excel.xlsx")

위 Python 코드 스니펫은 IronXL 라이브러리를 사용하여 'sample_excel.xlsx'라는 이름의 Excel 워크북을 로드하는 것을 보여줍니다. 먼저 IronXL에서 필요한 Python 모듈을 가져옵니다. 그런 다음 라이브러리 사용의 유효성을 검증하기 위해 라이선스 키가 할당됩니다. 무료 라이선스 키는 IronXL 웹사이트에서 받을 수 있습니다. 마지막으로 Load 메서드를 사용하여 지정된 Excel 워크북을 열고 메모리에 로드합니다. 이로 인해 그 내용의 프로그램적 조작이 가능하게 되어, 데이터를 읽거나 셀 값을 수정하거나 형식을 적용하는 등의 작업을 수행할 수 있습니다.

단계 3: 워크시트 선택

IronXL을 사용하여 Excel 워크북에서 워크시트를 선택하려면 워크시트 인덱스나 이름을 지정할 수 있습니다.

# Select the first worksheet in the loaded Excel workbook
worksheet = workbook.WorkSheets[0]

# Select the first worksheet in the loaded Excel workbook
worksheet = workbook.WorkSheets[0]

이 줄은 로드된 Excel 워크북의 첫 번째 워크시트를 선택하고 이를 변수 worksheet에 할당하여, 워크북 내의 특정 워크시트에 대한 후속 작업을 수행할 수 있게 합니다. 이것은 Excel 시트에서 엑셀 데이터가 워크시트 변수로 로드될 것입니다.

단계 4: 데이터베이스 연결 열기

이 튜토리얼에서는 MySQL 데이터베이스 서버 대신 SQLite 데이터베이스를 사용하고 있습니다. 데이터베이스 작업을 시작하기 위해 데이터베이스에 연결을 설정하는 것으로 시작합니다.

import sqlite3

# Connect to SQLite database (or create it if it doesn't exist)
conn = sqlite3.connect('data.db')

import sqlite3

# Connect to SQLite database (or create it if it doesn't exist)
conn = sqlite3.connect('data.db')

위 라인은 'data.db'라는 이름의 SQLite 데이터베이스에 연결을 설정합니다. 지정된 데이터베이스가 존재하지 않으면 자동으로 생성됩니다. 이 연결을 통해 SQLite 데이터베이스와의 후속 상호작용, 쿼리 실행 및 데이터 조작 작업이 가능해집니다.

단계 5: 테이블 생성

다음 단계는 Excel 파일에서 데이터를 가져올 데이터베이스에 테이블을 만드는 것입니다. SQLite 데이터베이스에서 테이블을 생성하려면 연결 객체를 사용하여 SQL 문을 실행할 수 있습니다.

# Create a cursor object for database operations
cursor = conn.cursor()

# Define and execute SQL to create a table if it doesn't exist
cursor.execute('''
CREATE TABLE IF NOT EXISTS customer (
    id INTEGER,
    FirstName TEXT,
    LastName TEXT,
    Gender TEXT,
    Country TEXT,
    Age INTEGER
)
''')

# Create a cursor object for database operations
cursor = conn.cursor()

# Define and execute SQL to create a table if it doesn't exist
cursor.execute('''
CREATE TABLE IF NOT EXISTS customer (
    id INTEGER,
    FirstName TEXT,
    LastName TEXT,
    Gender TEXT,
    Country TEXT,
    Age INTEGER
)
''')

위 코드 스니펫은 SQLite 데이터베이스 연결 내에서 SQL 명령을 실행하기 위한 커서 객체를 초기화합니다. 'customer'라는 이름의 테이블을 'id', 'FirstName', 'LastName', 'Gender', 'Country', 'Age' 열과 함께 생성합니다. 테이블이 이미 존재하지 않는 경우 지정된 열 데이터 유형에 따라 생성됩니다.

단계 6: Python을 사용하여 데이터베이스로 데이터 가져오기

이제, 새로 생성된 테이블에 데이터를 삽입하겠습니다. Excel 파일을 가져와 데이터베이스에 데이터를 삽입합니다.

# Iteratively insert data from Excel worksheet into SQLite database
for i in range(2, 11):
    # Extracting values from columns A to F in Excel worksheet
    values_tuple = (
        worksheet[f"A{i}"].StringValue,
        worksheet[f"B{i}"].StringValue,
        worksheet[f"C{i}"].StringValue,
        worksheet[f"D{i}"].StringValue,
        worksheet[f"E{i}"].StringValue,
        worksheet[f"F{i}"].StringValue
    )
    # Executing SQL INSERT command
    cursor.execute("INSERT INTO customer VALUES (?, ?, ?, ?, ?, ?)", values_tuple)

# Commit data insertion to the database
conn.commit()

# Iteratively insert data from Excel worksheet into SQLite database
for i in range(2, 11):
    # Extracting values from columns A to F in Excel worksheet
    values_tuple = (
        worksheet[f"A{i}"].StringValue,
        worksheet[f"B{i}"].StringValue,
        worksheet[f"C{i}"].StringValue,
        worksheet[f"D{i}"].StringValue,
        worksheet[f"E{i}"].StringValue,
        worksheet[f"F{i}"].StringValue
    )
    # Executing SQL INSERT command
    cursor.execute("INSERT INTO customer VALUES (?, ?, ?, ?, ?, ?)", values_tuple)

# Commit data insertion to the database
conn.commit()

위 코드는 Excel 워크시트의 2행부터 10행까지 반복하여 각 행에서 A열부터 F열까지의 값을 추출합니다. 이 값들은 'customer' 테이블에 삽입될 데이터로 표현되는 튜플에 저장됩니다. 커서가 SQL INSERT 명령을 실행한 후 값 튜플을 테이블에 추가합니다. 이 과정은 각 행에 대해 반복되며, 효과적으로 Excel 파일에서 SQLite 데이터베이스로 데이터를 가져옵니다. 마지막으로, conn.commit()은 트랜잭션을 커밋하여 데이터베이스에 변경 사항이 저장되고 유지되도록 합니다.

7단계: 데이터베이스에서 데이터 읽기

'customer' 테이블의 데이터가 올바르게 삽입되었는지 확인하려면 SQLite 데이터베이스에서 SELECT 쿼리를 사용하여 데이터를 읽을 수 있습니다. 예를 들어:

# Execute a SELECT query to retrieve all data from the 'customer' table
cursor.execute("SELECT * FROM customer")

# Fetch all rows from the result set
rows = cursor.fetchall()

# Print each row to verify inserted data
for row in rows:
    print(row)

# Close the database connection to release resources
conn.close()

# Execute a SELECT query to retrieve all data from the 'customer' table
cursor.execute("SELECT * FROM customer")

# Fetch all rows from the result set
rows = cursor.fetchall()

# Print each row to verify inserted data
for row in rows:
    print(row)

# Close the database connection to release resources
conn.close()

위의 코드는 SQLite 데이터베이스의 'customer' 테이블에서 SELECT 쿼리를 실행하여 모든 행을 검색합니다. 가져온 행들은 fetchall() 메서드를 사용하여 'rows' 변수에 저장됩니다. 그런 다음 각 행을 반복적으로 출력하여 'customer' 테이블에 삽입된 데이터를 표시합니다. 마지막으로, close() 메서드를 사용하여 데이터베이스 연결을 닫아 자원을 해제합니다.

전체 코드는 다음과 같습니다:

import sqlite3
from ironxl import *  # Supported for XLSX, XLS, XLSM, XLTX, CSV, and TSV

# Assign a license key (retrieved from IronXL website)
License.LicenseKey = "IRONSUITE.ABC.XYZ.COM.15796-DEPLOYMENT.TRIAL-5X63V4.TRIAL.EXPIRES.27.MAY.2024"

# Load the Excel workbook into memory
workbook = WorkBook.Load("sample_excel.xlsx")

# Select worksheet at index 0
worksheet = workbook.WorkSheets[0]

# Connect to SQLite database (or create it if it doesn't exist)
conn = sqlite3.connect('data.db')

# Create a cursor object for database operations
cursor = conn.cursor()

# Define and execute SQL to create a table if it doesn't exist
cursor.execute('''
CREATE TABLE IF NOT EXISTS customer (
    id INTEGER,
    FirstName TEXT,
    LastName TEXT,
    Gender TEXT,
    Country TEXT,
    Age INTEGER
)
''')

# Clear any existing data from the table
cursor.execute("DELETE FROM customer")

# Iteratively insert data from Excel worksheet into SQLite database
for i in range(2, 11):
    # Extracting values from columns A to F in Excel worksheet
    values_tuple = (
        worksheet[f"A{i}"].StringValue,
        worksheet[f"B{i}"].StringValue,
        worksheet[f"C{i}"].StringValue,
        worksheet[f"D{i}"].StringValue,
        worksheet[f"E{i}"].StringValue,
        worksheet[f"F{i}"].StringValue
    )
    # Executing SQL INSERT command
    cursor.execute("INSERT INTO customer VALUES (?, ?, ?, ?, ?, ?)", values_tuple)

# Commit data insertion to the database
conn.commit()

# Execute a SELECT query to retrieve all data from the 'customer' table
cursor.execute("SELECT * FROM customer")

# Fetch all rows from the result set
rows = cursor.fetchall()

# Print each row to verify inserted data
for row in rows:
    print(row)

# Close the database connection to release resources
conn.close()

import sqlite3
from ironxl import *  # Supported for XLSX, XLS, XLSM, XLTX, CSV, and TSV

# Assign a license key (retrieved from IronXL website)
License.LicenseKey = "IRONSUITE.ABC.XYZ.COM.15796-DEPLOYMENT.TRIAL-5X63V4.TRIAL.EXPIRES.27.MAY.2024"

# Load the Excel workbook into memory
workbook = WorkBook.Load("sample_excel.xlsx")

# Select worksheet at index 0
worksheet = workbook.WorkSheets[0]

# Connect to SQLite database (or create it if it doesn't exist)
conn = sqlite3.connect('data.db')

# Create a cursor object for database operations
cursor = conn.cursor()

# Define and execute SQL to create a table if it doesn't exist
cursor.execute('''
CREATE TABLE IF NOT EXISTS customer (
    id INTEGER,
    FirstName TEXT,
    LastName TEXT,
    Gender TEXT,
    Country TEXT,
    Age INTEGER
)
''')

# Clear any existing data from the table
cursor.execute("DELETE FROM customer")

# Iteratively insert data from Excel worksheet into SQLite database
for i in range(2, 11):
    # Extracting values from columns A to F in Excel worksheet
    values_tuple = (
        worksheet[f"A{i}"].StringValue,
        worksheet[f"B{i}"].StringValue,
        worksheet[f"C{i}"].StringValue,
        worksheet[f"D{i}"].StringValue,
        worksheet[f"E{i}"].StringValue,
        worksheet[f"F{i}"].StringValue
    )
    # Executing SQL INSERT command
    cursor.execute("INSERT INTO customer VALUES (?, ?, ?, ?, ?, ?)", values_tuple)

# Commit data insertion to the database
conn.commit()

# Execute a SELECT query to retrieve all data from the 'customer' table
cursor.execute("SELECT * FROM customer")

# Fetch all rows from the result set
rows = cursor.fetchall()

# Print each row to verify inserted data
for row in rows:
    print(row)

# Close the database connection to release resources
conn.close()