Skrivning af DataFrames til databasetabeller

Efter at have renset og transformeret data i Pandas har du ofte brug for at gemme resultaterne permanent i en database igen: for at gøre dem tilgængelige for andre programmer, dashboards eller teammedlemmer, for at gemme trinvise analyseresultater eller for at opbygge et datamart fra en rå datasø. DataFrame.to_sql() er Pandas' standardmetode til at skrive data til enhver database, der understøttes af SQLAlchemy, i ét enkelt kald.

df.to_sql('table_name', con=engine, if_exists='replace', index=False) skriver DataFramen til en databasetabel. Parameteren if_exists bestemmer, hvad der sker, hvis tabellen allerede findes: 'fail' udløser en fejl, 'replace' sletter og opretter tabellen igen, og 'append' tilføjer nye rækker uden at ændre de eksisterende. Angiv altid index=False, medmindre du specifikt vil gemme DataFramens indeks som en kolonne i databasen.

De tre værdier for if_exists bruges i forskellige situationer. 'replace' er beregnet til udvikling: Slet den gamle tabel, og opret en ny — ændringer i skemaet sker automatisk, men alle gamle data går tabt. 'append' bruges til trinvise indlæsninger: Tilføj nye rækker til den eksisterende tabel uden at ændre dens struktur — det er nyttigt til daglige batchjob. 'fail' fungerer som en sikkerhedsforanstaltning: Brug den til at beskytte vigtige tabeller mod utilsigtet overskrivning fra en datapipeline med en fejl.

Som standard knytter to_sql() automatisk Pandas-datatyper til SQLAlchemy-typer. Nogle gange er standarderne forkerte — en datetime64-kolonne kan for eksempel blive gemt som TEXT i SQLite. Brug parameteren dtype til at angive de præcise SQL-typer ved hjælp af SQLAlchemy-typeobjekter. Det sikrer korrekt lagring, korrekt indeksering og præcis typehåndtering, når dataene læses tilbage. Kontrollér altid skemaet efter skrivning med en hurtig PRAGMA table_info() eller inspector.get_columns().

For store DataFrames forsøger to_sql() uden chunksize at indsætte alle rækker i én enkelt sætning, hvilket kan føre til en databasetimeout eller en hukommelsesfejl. Angiv chunksize=N for at indsætte N rækker pr. transaktion. Det giver databasen mulighed for at gennemføre ændringer trinvist og reducerer det maksimale hukommelsesforbrug. En chunksize på 10.000–50.000 rækker giver typisk en god balance mellem indsættelseshastighed og hukommelsesforbrug, men den optimale værdi afhænger af din database og netværksforsinkelse.

Pandas' to_sql() understøtter ikke upsert indbygget (indsæt, hvis ny, og opdatér, hvis den findes). Brug SQLAlchemy Core med en INSERT OR REPLACE- (SQLite) eller ON CONFLICT DO UPDATE-sætning (PostgreSQL) for at implementere upsert. Den almindelige løsning i Pandas er at skrive til en midlertidig stagingtabel med if_exists='replace', derefter køre rå SQL for at flette stagingtabellen ind i produktionstabellen og til sidst slette stagingtabellen.

Efter skrivning skal du altid kontrollere resultatet ved at læse en opsummerende optælling og et rækketal tilbage. Sammenlign dem med kilde-DataFramen. Det afslører fejl, der ellers ikke giver sig til kende, forårsaget af uoverensstemmelser mellem datatyper (f.eks. NaN i en heltalskolonne, som medfører delvise indsættelser) eller databasebegrænsninger (f.eks. overtrædelser af entydige nøgler, som i nogle konfigurationer stille springer rækker over). En hurtig SELECT COUNT(*) FROM table efter hvert kald til to_sql medfører kun en minimal ekstra belastning og forhindrer ubemærket datatab.

to_sql() skriver data, men tilføjer ikke primærnøgler eller databasebegrænsninger — den opretter en almindelig tabel. For en produktionstabel skal du tilføje primærnøglebegrænsningen separat ved hjælp af rå SQL, der udføres gennem SQLAlchemy. SQLite kræver, at tabellen oprettes igen for at tilføje begrænsninger efter oprettelsen, mens PostgreSQL understøtter ALTER TABLE ADD PRIMARY KEY. Alternativt kan du definere hele skemaet på forhånd og bruge if_exists='append' til at indsætte data i en eksisterende tabel med en korrekt definition.

For at sikre datakonsistens skal du pakke to_sql() ind i en eksplicit transaktion. Hvis et trin i en skrivning til flere tabeller mislykkes, kan du rulle alle ændringer tilbage. Uden en transaktion kan delvise skrivninger efterlade databasen i en inkonsistent tilstand. SQLAlchemys forbindelseskontekststyring med conn.begin() muliggør manuel transaktionsstyring. Alternativt kan du bruge engine.begin() til en blok med automatisk gennemførelse, som ruller tilbage ved en undtagelse.

Som standard indsætter to_sql() én række pr. SQL-sætning, hvilket er meget langsomt for store DataFrames. Angiv method='multi' for at bruge én INSERT med flere værdisæt — typisk 10-100 gange hurtigere. I PostgreSQL kan du angive en tilpasset method-funktion, der bruger COPY-protokollen (via psycopg2's copy_expert) til den absolut hurtigste masseindlæsning. Den optimale metode afhænger af din databaseversion og netværksopsætning.

I produktionsdatapipelines skal du registrere, hvad der blev skrevet, og hvornår det skete, ved at vedligeholde en revisionslogtabel. Efter hver vellykkede to_sql() skal du indsætte en række i revisionsloggen med tabelnavn, rækketælling, tidsstempel og datapipelinekørslens ID. Det gør det nemt at opdage manglende kørsler, dobbeltskrivninger eller ændringer i skemaet over tid. Selve revisionsloggen er en Pandas-DataFrame, der skrives via to_sql — den samme teknik anvendt rekursivt til driftsovervågning.

I denne lektion har du lært, at df.to_sql() skriver en DataFrame til enhver SQLAlchemy-forbundet databasetabel, hvor parameteren if_exists styrer adfærden ved oprettelse, tilføjelse og erstatning, at chunksize og method='multi' forbedrer ydeevnen for store DataFrames, og at transaktionelle skrivninger med engine.begin() sikrer atomiske opdateringer af flere tabeller, som rulles tilbage ved fejl. Nu sammenligner vi Pandas og SQL for at forstå, hvornår hvert værktøj er det bedste valg.