W dzisiejszym świecie opartym na danych wydajne przetwarzanie i obsługa danych są kluczowymi zadaniami dla każdej organizacji i osoby. Python, z jego bogatym ekosystemem bibliotek, oferuje potężne narzędzia do manipulacji i zarządzania danymi, takie jak biblioteka pandas. Częstym scenariuszem jest potrzeba wyodrębnienia lub zaimportowania danych z arkuszy kalkulacyjnych Excel oraz zapisania lub wstawienia danych do bazy danych w celu dalszej analizy lub integracji z innymi systemami. W tym samouczku omówimy, jak stworzyć skrypt w języku Python, który zautomatyzuje ten proces, umożliwiając płynne odczytywanie danych z arkuszy Excel i wstawianie ich do bazy danych. Po zakończeniu tego samouczka będziesz gotowy do sprawnego wykonywania zadań związanych z migracją danych. Zaczynamy!

Jak zaimportować plik Excel do tabeli bazy danych za pomocą języka Python

1. Zacznij od zainstalowania biblioteki IronXL.
2. Załaduj plik Excel do pamięci za pomocą IronXL.
3. Załaduj konkretny arkusz kalkulacyjny, nad którym chcesz pracować.
4. Wybierz dokładny zakres danych, który chcesz zaimportować.
5. Nawiąż połączenie z dowolną bazą danych, taką jak SQLite lub MySQL, za pomocą języka Python.
6. Utwórz nową tabelę w bazie danych SQLite, aby pomieścić zaimportowane dane.
7. Wstaw wybrane wiersze z pliku Excel do nowo utworzonej tabeli SQLite.
8. Pobierz i wybierz dane z utworzonej tabeli SQLite w celu dalszej analizy lub przetwarzania.

W tym samouczku będziemy korzystać z biblioteki IronXL, języka Python znanej ze swojej wydajności w obsłudze plików Excel. Dzięki integracji IronXL z naszym skryptem zapewniamy płynne pozyskiwanie danych z arkuszy kalkulacyjnych Excel, umożliwiając ich sprawne wprowadzanie do baz danych w celu dalszej analizy i przetwarzania.

Czym jest IronXL?

IronXL to biblioteka języka Python opracowana przez firmę Iron Software, oferująca rozbudowaną funkcjonalność do odczytu, generowania i edycji plików Excel bezpośrednio w aplikacjach Python. Warto podkreślić, że IronXL wyróżnia się niezależnością od instalacji programu Microsoft Excel, co ułatwia wdrażanie w różnych środowiskach. Dzięki IronXL programiści zyskują:

Obsługa wielu platform: Ciesz się płynnym działaniem na platformach Windows, macOS, Linux, Docker, Azure i AWS, co zapewnia dostosowanie do różnych środowisk programistycznych.

Import i eksport danych: Łatwa obsługa importu danych z plików XLS, XLSX, CSV i TSV, z możliwością eksportowania arkuszy do tych formatów, a nawet do JSON w celu zwiększenia interoperacyjności.

Funkcje szyfrowania: Zapewnij bezpieczeństwo danych, wykorzystując możliwości szyfrowania IronXL, które pozwalają na ochronę plików XLSX, XLSM i XLTX za pomocą haseł.

Formuły i ponowne obliczanie: Pracuj bez wysiłku z formułami programu Excel, korzystając z dodatkowej funkcji automatycznego ponownego obliczania przy każdej edycji arkusza, co zapewnia dokładność i niezawodność podczas przetwarzania danych.

Stylizacja komórek: Dostosuj wygląd poszczególnych komórek, zmieniając style czcionek, rozmiary, wzory tła, obramowania i wyrównanie, poprawiając wizualną prezentację dokumentów Excel.

Szeroki zakres formatów dokumentów: Dzięki obsłudze różnych formatów, w tym XLS, XLSX, XLST, XLSM, CSV i TSV, IronXL umożliwia programistom łatwą i wydajną obsługę danych w wielu różnych scenariuszach.

Zacznijmy od zainstalowania IronXL.

Krok 1: Zainstaluj bibliotekę IronXL

Pierwszym krokiem jest zainstalowanie biblioteki IronXL. Uruchom następujące polecenie, aby zainstalować IronXL w wierszu poleceń.

pip install IronXL

pip install IronXL

Krok 2: Załaduj skoroszyt Excel

Kolejnym krokiem jest załadowanie pliku Excel. W tym samouczku będziemy korzystać z poniższego pliku Excel.

Poniższy kod załaduje istniejący plik Excel do pamięci.

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")

Powyższy fragment kodu w języku Python pokazuje ładowanie skoroszytu Excel o nazwie "sample_excel.xlsx" przy użyciu biblioteki IronXL. Najpierw importowany jest niezbędny moduł Python z IronXL. Następnie przypisywany jest klucz licencyjny w celu weryfikacji korzystania z biblioteki. Bezpłatny klucz licencyjny można uzyskać na stronie internetowej IronXL. Na koniec metoda Load jest używana do otwarcia i załadowania określonego skoroszytu Excel do pamięci. Umożliwia to późniejszą manipulację zawartością za pomocą kodu, taką jak odczytywanie danych, modyfikowanie wartości komórek lub stosowanie formatowania.

Krok 3: Wybór arkusza roboczego

Aby wybrać arkusz w skoroszycie programu Excel za pomocą IronXL, można podać indeks lub nazwę arkusza.

# 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]

Ta linia wybiera pierwszy arkusz w załadowanym skoroszycie Excel i przypisuje go do zmiennej worksheet, umożliwiając przeprowadzanie dalszych operacji na tym konkretnym arkuszu w skoroszycie. Spowoduje to załadowanie danych z arkusza Excel do zmiennej arkusza.

Krok 4: Otwórz połączenie z bazą danych

W tym samouczku wykorzystujemy bazę danych SQLite zamiast serwera baz danych MySQL. Aby zainicjować operacje na bazie danych, zaczynamy od nawiązania połączenia z bazą danych.

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')

Powyższa linia nawiązuje połączenie z bazą danych SQLite o nazwie "data.db". Jeśli podana baza danych nie istnieje, zostanie utworzona automatycznie. To połączenie umożliwia dalszą interakcję z bazą danych SQLite, taką jak wykonywanie zapytań i operacji manipulacji danymi.

Krok 5: Utwórz tabelę

Kolejnym krokiem jest utworzenie tabeli w bazie danych, do której zaimportujemy dane z pliku Excel. Aby utworzyć tabelę w bazie danych SQLite, można wykonać instrukcję SQL przy użyciu obiektu połączenia.

# 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
)
''')

Powyższy fragment kodu inicjuje obiekt kursora w celu wykonywania poleceń SQL w ramach połączenia z bazą danych SQLite. Tworzy tabelę o nazwie "customer" z kolumnami "id", "FirstName", "LastName", "Gender", "Country" i "Age". Tabela zostanie utworzona, jeśli jeszcze nie istnieje, zgodnie z określonymi typami danych kolumn.

Krok 6: Importowanie danych do bazy danych przy użyciu języka Python

Teraz wstawimy dane do naszej nowo utworzonej tabeli. Zaimportujemy plik Excel i wstawimy jego dane do bazy danych SQLite.

# 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()

Powyższy kod iteruje po wierszach od 2 do 10 w arkuszu Excel, wyodrębniając wartości z kolumn od A do F dla każdego wiersza. Wartości te są przechowywane w krotce, reprezentującej dane, które mają zostać wstawione do tabeli "customer". Następnie kursor wykonuje polecenie SQL INSERT, wprowadzając krotkę wartości do tabeli. Proces ten powtarza się dla każdego wiersza, skutecznie importując dane z pliku Excel do bazy danych SQLite. Na koniec conn.commit() zatwierdza transakcję, zapewniając zapisanie i trwałość zmian w bazie danych.

Krok 7: Odczytywanie danych z bazy danych

Aby sprawdzić, czy dane zostały poprawnie wstawione, można odczytać dane z tabeli "customer" w bazie danych SQLite za pomocą zapytania SELECT. Na przykład:

# 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()

Powyższy kod wykonuje zapytanie SELECT na tabeli "customer" w bazie danych SQLite, pobierając wszystkie wiersze. Pobrane wiersze są przechowywane w zmiennej 'rows' za pomocą metody fetchall(). Następnie każdy wiersz jest drukowany iteracyjnie, wyświetlając dane wstawione do tabeli "customer". Na koniec połączenie z bazą danych jest zamykane przy użyciu metody close(), aby zwolnić zasoby.

Pełny kod wygląda następująco:

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()

Wnioski

Podsumowując, niniejszy samouczek przedstawił zautomatyzowane podejście do manipulacji danymi, a konkretnie do pobierania i wstawiania danych z Excela do bazy danych. Proces ten nie tylko zwiększa wydajność zarządzania danymi, ale także uwalnia ich pełny potencjał w zakresie przetwarzania danych. Wykorzystaj potęgę Pythona i IronXL, aby zoptymalizować przepływ danych i z pewnością siebie realizować swoje projekty.