Zapisywanie obiektów DataFrame w tabelach bazy danych
Zapisz oczyszczony DataFrame w nowej lub istniejącej tabeli za pomocą DataFrame.to_sql(), ustawiając parametry if_exists i chunksize.
Zapisywanie obiektów DataFrame w tabelach bazy danych to bezpłatna lekcja Pandas & NumPy Academy na CoddyKit. To lekcja 3 z 4. Możesz przeczytać całą lekcję poniżej za darmo — a potem ćwiczyć ją interaktywnie w przeglądarce z wbudowanym edytorem kodu i tutorem AI dostępnym 24/7. To część ścieżki edukacyjnej Pandas & NumPy Academy, a Twój postęp synchronizuje się między webem a aplikacją CoddyKit. Kurs Pandas & NumPy Academy zawiera 4 lekcji w sumie.
Dlaczego zapisywać struktury DataFrame w bazach danych?
Po oczyszczeniu i przekształceniu danych w Pandas często trzeba utrwalić wyniki z powrotem w bazie danych: aby udostępnić je innym aplikacjom, pulpitom nawigacyjnym lub członkom zespołu, przechowywać przyrostowe wyniki analiz albo zbudować składnicę danych na podstawie surowego jeziora danych. DataFrame.to_sql() to standardowa metoda Pandas służąca do zapisywania danych w dowolnej bazie obsługiwanej przez SQLAlchemy za pomocą jednego wywołania.
Podstawowe użycie to_sql()
df.to_sql('table_name', con=engine, if_exists='replace', index=False) zapisuje strukturę DataFrame w tabeli bazy danych. Parametr if_exists określa zachowanie w przypadku, gdy tabela już istnieje: 'fail' zgłasza błąd, 'replace' usuwa tabelę i tworzy ją ponownie, a 'append' dodaje nowe wiersze bez modyfikowania istniejących. Zawsze należy ustawiać index=False, chyba że indeks struktury DataFrame ma być jawnie zapisany jako kolumna w bazie danych.
import pandas as pd
import sqlalchemy as sa
engine = sa.create_engine('sqlite:///results.db')
df = pd.DataFrame({
'date': pd.date_range('2024-01-01', periods=5),
'revenue': [1200.0, 980.5, 1450.0, 760.3, 1100.0],
'region': ['North', 'South', 'East', 'West', 'North']
})
df.to_sql('daily_revenue', con=engine,
if_exists='replace', index=False)
print('Table written successfully')Objaśnienie parametru if_exists
Trzy wartości parametru if_exists służą różnym celom. 'replace' jest przeznaczone do programowania: usuwa starą tabelę i tworzy nową — zmiany schematu są wprowadzane automatycznie, ale wszystkie stare dane zostają utracone. 'append' służy do ładowania przyrostowego: dodaje nowe wiersze do istniejącej tabeli bez zmiany jej struktury — jest przydatne w codziennych zadaniach wsadowych. 'fail' pełni funkcję zabezpieczenia: należy go używać do ochrony ważnych tabel przed przypadkowym nadpisaniem przez potok zawierający błąd.
import pandas as pd
import sqlalchemy as sa
engine = sa.create_engine('sqlite:///results.db')
new_batch = pd.DataFrame({
'date': ['2024-06-01', '2024-06-02'],
'revenue': [1500.0, 1300.0],
'region': ['North', 'East']
})
# Append new rows without losing existing data
new_batch.to_sql('daily_revenue', con=engine,
if_exists='append', index=False)
print('Appended new rows')Kontrolowanie typów danych kolumn
Domyślnie to_sql() automatycznie mapuje typy danych Pandas na typy SQLAlchemy. Czasami wartości domyślne są nieprawidłowe — na przykład kolumna datetime64 może zostać zapisana jako TEXT w SQLite. Należy użyć parametru dtype, aby określić dokładne typy SQL za pomocą obiektów typów SQLAlchemy. Zapewnia to prawidłowe przechowywanie, poprawne indeksowanie i właściwą obsługę typów podczas ponownego odczytu danych. Po zapisie należy zawsze zweryfikować schemat za pomocą szybkiego wywołania PRAGMA table_info() lub inspector.get_columns().
import pandas as pd
import sqlalchemy as sa
from sqlalchemy import types
engine = sa.create_engine('sqlite:///results.db')
df = pd.DataFrame({
'id': [1, 2, 3],
'name': ['Alice', 'Bob', 'Carol'],
'score': [0.95, 0.87, 0.91],
'created_at': pd.to_datetime(['2024-01-01', '2024-01-02', '2024-01-03'])
})
df.to_sql('users', con=engine, if_exists='replace', index=False,
dtype={'id': types.Integer(),
'score': types.Float(),
'created_at': types.DateTime()})Zapisywanie partiami za pomocą chunksize
W przypadku dużych struktur DataFrame wywołanie to_sql() bez parametru chunksize próbuje wstawić wszystkie wiersze w jednej instrukcji, co może zakończyć się przekroczeniem limitu czasu bazy danych lub błędem pamięci. Należy określić chunksize=N, aby wstawiać N wierszy w ramach jednej transakcji. Daje to bazie danych możliwość stopniowego zatwierdzania zmian i zmniejsza szczytowe zużycie pamięci. Wartość chunksize wynosząca od 10 000 do 50 000 wierszy zazwyczaj zapewnia równowagę między szybkością wstawiania a zużyciem pamięci, ale optymalna wartość zależy od bazy danych i opóźnienia sieci.
import pandas as pd
import sqlalchemy as sa
import numpy as np
engine = sa.create_engine('sqlite:///results.db')
# Large DataFrame
df = pd.DataFrame({
'id': range(500000),
'value': np.random.randn(500000)
})
# Insert in chunks of 10,000 rows at a time
df.to_sql('large_table', con=engine,
if_exists='replace',
index=False,
chunksize=10000)
print('Written 500,000 rows')Upsert: wstawianie lub aktualizowanie
Metoda Pandas to_sql() nie obsługuje natywnie operacji upsert (wstawienia, jeśli rekord jest nowy, lub aktualizacji, jeśli już istnieje). Aby zaimplementować upsert, należy użyć SQLAlchemy Core wraz z instrukcją INSERT OR REPLACE (SQLite) lub ON CONFLICT DO UPDATE (PostgreSQL). Typowe obejście w Pandas polega na zapisaniu danych w tymczasowej tabeli pośredniej za pomocą if_exists='replace', następnie wykonaniu surowego SQL w celu połączenia tabeli pośredniej z tabelą produkcyjną, a na końcu usunięciu tabeli pośredniej.
import pandas as pd
import sqlalchemy as sa
engine = sa.create_engine('sqlite:///results.db')
new_data = pd.DataFrame({
'id': [1, 2, 5],
'value': [99.9, 88.8, 77.7]
})
# Write to staging table
new_data.to_sql('staging', con=engine,
if_exists='replace', index=False)
# Merge into production (SQLite syntax)
with engine.connect() as conn:
conn.execute(sa.text(
'INSERT OR REPLACE INTO production SELECT * FROM staging'
))
conn.commit()
print('Upsert complete')Weryfikowanie zapisu
Po zapisie należy zawsze zweryfikować wynik, ponownie odczytując podsumowanie oraz liczbę wierszy. Należy porównać je z wartościami z wejściowej struktury DataFrame. Pozwala to wykryć ciche błędy spowodowane niezgodnością typów danych (na przykład wartościami NaN w kolumnie całkowitoliczbowej prowadzącymi do częściowego wstawienia) lub ograniczeniami bazy danych (na przykład naruszeniem unikatowego klucza, które w niektórych konfiguracjach może po cichu pomijać wiersze). Szybkie wykonanie SELECT COUNT(*) FROM table po każdym wywołaniu to_sql wiąże się z minimalnym narzutem i zapobiega cichej utracie danych.
import pandas as pd
import sqlalchemy as sa
engine = sa.create_engine('sqlite:///results.db')
df = pd.DataFrame({'id': range(1000), 'value': range(1000)})
df.to_sql('my_table', con=engine, if_exists='replace', index=False)
# Verify
with engine.connect() as conn:
count = conn.execute(sa.text('SELECT COUNT(*) FROM my_table')).scalar()
print(f'Source rows: {len(df)}, DB rows: {count}')
assert count == len(df), 'Row count mismatch!'Dodawanie klucza głównego po zapisie
to_sql() zapisuje dane, ale nie dodaje kluczy głównych ani ograniczeń bazy danych — tworzy zwykłą tabelę. W przypadku tabeli produkcyjnej ograniczenie klucza głównego należy dodać osobno za pomocą surowego SQL wykonanego przez SQLAlchemy. SQLite wymaga ponownego utworzenia tabeli, aby dodać ograniczenia po jej utworzeniu, natomiast PostgreSQL obsługuje ALTER TABLE ADD PRIMARY KEY. Alternatywnie można zdefiniować pełny schemat z wyprzedzeniem i użyć if_exists='append', aby wstawiać dane do istniejącej tabeli o prawidłowo zdefiniowanej strukturze.
import pandas as pd
import sqlalchemy as sa
from sqlalchemy import Table, Column, Integer, Float, MetaData
engine = sa.create_engine('sqlite:///results.db')
meta = MetaData()
# Define table with primary key
my_table = Table('defined_table', meta,
Column('id', Integer, primary_key=True),
Column('value', Float)
)
meta.create_all(engine) # Create table with constraints
# Then insert data using append
df = pd.DataFrame({'id': range(5), 'value': [1.1, 2.2, 3.3, 4.4, 5.5]})
df.to_sql('defined_table', con=engine,
if_exists='append', index=False)Zapisy transakcyjne
Aby zapewnić spójność danych, należy umieścić to_sql() w jawnej transakcji. Jeśli dowolny etap zapisu do wielu tabel zakończy się niepowodzeniem, można wycofać wszystkie zmiany. Bez transakcji częściowe zapisy mogą pozostawić bazę danych w niespójnym stanie. Menedżer kontekstu połączenia SQLAlchemy wraz z conn.begin() umożliwia ręczne sterowanie transakcją. Alternatywnie można użyć engine.begin() do utworzenia bloku z automatycznym zatwierdzaniem, który wycofuje zmiany w przypadku wyjątku.
import pandas as pd
import sqlalchemy as sa
engine = sa.create_engine('sqlite:///results.db')
df_orders = pd.DataFrame({'id': [1, 2], 'amount': [100.0, 200.0]})
df_summary = pd.DataFrame({'total': [300.0], 'count': [2]})
try:
with engine.begin() as conn: # Auto-rollback on exception
df_orders.to_sql('orders_v2', con=conn,
if_exists='replace', index=False)
df_summary.to_sql('summary_v2', con=conn,
if_exists='replace', index=False)
print('Both tables written atomically')
except Exception as e:
print(f'Write failed, rolled back: {e}')Wydajność: metody masowego wstawiania
Domyślna metoda to_sql() wstawia jeden wiersz na instrukcję SQL, co jest bardzo wolne w przypadku dużych struktur DataFrame. Należy przekazać method='multi', aby użyć jednej instrukcji INSERT z wieloma krotkami wartości — zwykle jest to od 10 do 100 razy szybsze. W przypadku PostgreSQL można przekazać niestandardową funkcję method, która używa protokołu COPY (za pośrednictwem copy_expert biblioteki psycopg2), aby uzyskać maksymalną szybkość masowego ładowania. Optymalna metoda zależy od wersji bazy danych i konfiguracji sieci.
import pandas as pd
import sqlalchemy as sa
import numpy as np
import time
engine = sa.create_engine('sqlite:///perf.db')
df = pd.DataFrame({'a': range(100000), 'b': np.random.randn(100000)})
# Default (one row per INSERT) — slow
start = time.time()
df.to_sql('test_default', con=engine, if_exists='replace', index=False)
print(f'Default: {time.time()-start:.2f}s')
# multi-row INSERT — faster
start = time.time()
df.to_sql('test_multi', con=engine, if_exists='replace',
index=False, method='multi', chunksize=1000)
print(f'Multi: {time.time()-start:.2f}s')Rejestrowanie i audytowanie zapisów
W potokach produkcyjnych należy śledzić, co i kiedy zostało zapisane, prowadząc tabelę dziennika audytowego. Po każdym pomyślnym wywołaniu to_sql() należy wstawić do dziennika audytowego wiersz zawierający nazwę tabeli, liczbę wierszy, sygnaturę czasową i identyfikator uruchomienia potoku. Ułatwia to wykrywanie brakujących uruchomień, podwójnych zapisów lub zmian schematu zachodzących w czasie. Sam dziennik audytowy jest strukturą DataFrame zapisywaną za pomocą to_sql — jest to ta sama technika zastosowana rekurencyjnie do monitorowania operacyjnego.
import pandas as pd
import sqlalchemy as sa
from datetime import datetime
engine = sa.create_engine('sqlite:///results.db')
def write_with_audit(df, table_name, engine, run_id):
df.to_sql(table_name, con=engine, if_exists='append', index=False)
audit = pd.DataFrame([{
'run_id': run_id,
'table_name': table_name,
'rows_written': len(df),
'written_at': datetime.utcnow().isoformat()
}])
audit.to_sql('audit_log', con=engine, if_exists='append', index=False)
print(f'Wrote {len(df)} rows to {table_name}')
df = pd.DataFrame({'id': [1, 2], 'val': [10, 20]})
write_with_audit(df, 'my_table', engine, run_id='run_001')Szybki test
Sprawdź swoją znajomość zagadnień analizy danych omówionych w tej lekcji.
Podsumowanie lekcji
W tej lekcji dowiedziałeś się, że: df.to_sql() zapisuje strukturę DataFrame w dowolnej tabeli bazy danych połączonej z SQLAlchemy, a parametr if_exists steruje zachowaniem podczas tworzenia, dopisywania i zastępowania danych; chunksize i method='multi' poprawiają wydajność w przypadku dużych struktur DataFrame; natomiast zapisy transakcyjne z użyciem engine.begin() zapewniają atomowe aktualizacje wielu tabel, które są wycofywane w przypadku niepowodzenia. Następnie porównamy Pandas i SQL, aby zrozumieć, kiedy lepiej użyć każdego z tych narzędzi.
Często zadawane pytania
Czy lekcja „Zapisywanie obiektów DataFrame w tabelach bazy danych” jest bezpłatna?
Tak — pełny tekst „Zapisywanie obiektów DataFrame w tabelach bazy danych” jest dostępny za darmo tutaj w sieci. Aby ćwiczyć ją interaktywnie (wbudowany edytor kodu i tutor AI dostępny 24/7) i odblokować resztę kursu Pandas & NumPy Academy, przejdź na CoddyKit PRO. Kurs Pandas & NumPy Academy zawiera 4 lekcji w sumie.
Co nauczysz się w „Zapisywanie obiektów DataFrame w tabelach bazy danych”?
Zapisz oczyszczony DataFrame w nowej lub istniejącej tabeli za pomocą DataFrame.to_sql(), ustawiając parametry if_exists i chunksize. Ćwiczysz Pandas & NumPy Academy z praktycznym kodem, który uruchamiasz bezpośrednio w przeglądarce, a tutor AI dostępny 24/7 odpowiada na Twoje pytania podczas pracy nad lekcją.
Czy potrzebuję doświadczenia, aby zacząć Pandas & NumPy Academy?
Nie wymagamy żadnego doświadczenia. Pandas & NumPy Academy w CoddyKit jest strukturyzowany dla początkujących i zaawansowanych użytkowników, więc możesz zacząć tutaj lub od początku i uczyć się w swoim tempie. To lekcja 3 z 4.
Ile czasu zajmuje lekcja „Zapisywanie obiektów DataFrame w tabelach bazy danych”?
Większość lekcji CoddyKit trwa około 5–10 minut. Każda lekcja to mały, interaktywny krok, dzięki czemu robisz systematyczne postępy i zawsze wracasz dokładnie do tego samego miejsca — na webie i w aplikacji.
Czy mogę pisać i uruchamiać kod w tej lekcji Pandas & NumPy Academy?
Tak. Każda lekcja Pandas & NumPy Academy zawiera wbudowany edytor kodu, więc piszesz i uruchamiasz prawdziwy kod bezpośrednio w przeglądarce i od razu otrzymujesz sprzężenie zwrotne od AI — bez konfiguracji na komputerze.
Wszystkie lekcje w tym kursie
- Łączenie z bazą danych za pomocą SQLAlchemy
- Wykonywanie zapytań SQL z Pandas
- Zapisywanie obiektów DataFrame w tabelach bazy danych
- Pandas a SQL: wybór odpowiedniego narzędzia