Lese og skrive SQL-filer i pandaer

Publisert av Platon

Følgere: 0

Da jeg begynte å lære Dataanalyse for noen år siden, var det første jeg lærte SQL og Pandas. Som dataanalytiker er det avgjørende å ha et sterkt fundament i arbeidet med SQL og Pandas. Begge er kraftige verktøy som hjelper dataanalytikere med å effektivt analysere og manipulere lagrede data i databaser.

Oversikt over SQL og Pandas

SQL (Structured Query Language) er et programmeringsspråk som brukes til å administrere og manipulere relasjonsdatabaser. På den annen side er Pandas et Python-bibliotek som brukes til datamanipulering og analyse.

Dataanalyse innebærer å jobbe med store datamengder, og databaser brukes ofte til å lagre disse dataene. SQL og Pandas gir kraftige verktøy for å jobbe med databaser, slik at dataanalytikere effektivt kan trekke ut, manipulere og analysere data. Ved å utnytte disse verktøyene kan dataanalytikere få verdifull innsikt fra data som ellers ville vært vanskelig å få tak i.

I denne artikkelen vil vi utforske hvordan du bruker SQL og Pandas til å lese og skrive til en database.

Kobler til DB

Installere bibliotekene

Vi må først installere de nødvendige bibliotekene før vi kan koble til SQL-databasen med Pandas. De to hovedbibliotekene som kreves er Pandas og SQLAlchemy. Pandas er et populært datamanipulasjonsbibliotek som tillater lagring av store datastrukturer, som nevnt i innledningen. Derimot gir SQLAlchemy et API for å koble til og samhandle med SQL-databasen.

Vi kan installere begge bibliotekene ved å bruke Python-pakkebehandleren, pip, ved å kjøre følgende kommandoer ved ledeteksten.

$ pip install pandas
$ pip install sqlalchemy

Opprette tilkoblingen

Med bibliotekene installert kan vi nå bruke Pandas til å koble til SQL-databasen.

Til å begynne med vil vi lage et SQLAlchemy-motorobjekt med create_engine(). De create_engine() funksjon kobler Python-koden til databasen. Den tar som argument en tilkoblingsstreng som spesifiserer databasetypen og tilkoblingsdetaljer. I dette eksemplet bruker vi SQLite-databasetypen og databasefilens bane.

Lag et motorobjekt for en SQLite-database ved å bruke eksemplet nedenfor:

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db')

Hvis SQLite-databasefilen, student.db i vårt tilfelle, er i samme katalog som Python-skriptet, kan vi bruke filnavnet direkte, som vist nedenfor.

engine = create_engine('sqlite:///student.db')

Lese SQL-filer med pandaer

La oss lese data nå som vi har opprettet en forbindelse. I denne delen skal vi se på read_sql, read_sql_tableog read_sql_query funksjoner og hvordan du bruker dem til å jobbe med en database.

Utføre SQL-spørringer ved hjelp av Panda's read_sql() Funksjon

De read_sql() er en Pandas bibliotekfunksjon som lar oss utføre en SQL-spørring og hente resultatene inn i en Pandas dataramme. De read_sql() funksjon kobler sammen SQL og Python, slik at vi kan dra nytte av kraften til begge språkene. Funksjonen omsluttes read_sql_table() og read_sql_query(). De read_sql() funksjonen rutes internt basert på inndataene som er gitt, noe som betyr at hvis inngangen skal utføre en SQL-spørring, vil den bli rutet til read_sql_query(), og hvis det er en databasetabell, vil den bli rutet til read_sql_table().

De read_sql() syntaks er som følger:

pandas.read_sql(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, columns=None, chunksize=None)

SQL og con parametere er påkrevd; resten er valgfritt. Vi kan imidlertid manipulere resultatet ved å bruke disse valgfrie parameterne. La oss se nærmere på hver parameter.

sql: Navn på SQL-spørring eller databasetabell
con: Tilkoblingsobjekt eller tilkoblings-URL
index_col: Denne parameteren lar oss bruke én eller flere kolonner fra SQL-spørringsresultatet som en datarammeindeks. Det kan ta enten en enkelt kolonne eller en liste over kolonner.
coerce_float: Denne parameteren spesifiserer om ikke-numeriske verdier skal konverteres til flytende tall eller stå som strenger. Den er satt til sann som standard. Hvis mulig, konverterer den ikke-numeriske verdier til flytetyper.
params: Parametrene gir en sikker metode for å sende dynamiske verdier til SQL-spørringen. Vi kan bruke parameteren params til å sende en ordbok, tuppel eller liste. Avhengig av databasen varierer syntaksen til paramer.
parse_dates: Dette lar oss spesifisere hvilken kolonne i den resulterende datarammen som skal tolkes som en dato. Den godtar en enkelt kolonne, en liste over kolonner eller en ordbok med nøkkelen som kolonnenavn og verdien som kolonneformat.
columns: Dette lar oss hente kun utvalgte kolonner fra listen.
chunksize: Når du arbeider med et stort datasett, er chunksize viktig. Den henter søkeresultatet i mindre biter, og forbedrer ytelsen.

Her er et eksempel på hvordan du bruker read_sql():

Kode:

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') df = pd.read_sql("SELECT * FROM Student", engine, index_col='Roll Number', parse_dates='dateOfBirth')
print(df)
print("The Data type of dateOfBirth: ", df.dateOfBirth.dtype) engine.dispose()

Utgang:

 firstName lastName email dateOfBirth
rollNumber
1 Mark Simson  2000-02-23
2 Peter Griffen  2001-04-15
3 Meg Aniston  2001-09-20
Date type of dateOfBirth: datetime64[ns]

Etter å ha koblet til databasen, utfører vi en spørring som returnerer alle poster fra Student tabellen og lagrer dem i DataFrame df. Kolonnen "Rullnummer" konverteres til en indeks ved hjelp av index_col parameter, og datatypen "dateOfBirth" er "datetime64[ns]" på grunn av parse_dates. Vi kan bruke read_sql() ikke bare for å hente data, men også for å utføre andre operasjoner som å sette inn, slette og oppdatere. read_sql() er en generisk funksjon.

Laster spesifikke tabeller eller visninger fra DB

Laster et spesifikt bord eller visning med Pandas read_sql_table() er en annen teknikk for å lese data fra databasen til en Pandas dataramme.

Hva er read_sql_table?

Pandas-biblioteket tilbyr read_sql_table funksjon, som er spesielt utviklet for å lese en hel SQL-tabell uten å utføre noen spørringer og returnere resultatet som en Pandas-dataramme.

Syntaksen til read_sql_table() er som nedenfor:

pandas.read_sql_table(table_name, con, schema=None, index_col=None, coerce_float=True, parse_dates=None, columns=None, chunksize=None)

Utenom table_name og skjema, er parameterne forklart på samme måte som read_sql().

table_name: Parameteren table_name er navnet på SQL-tabellen i databasen.
schema: Denne valgfrie parameteren er navnet på skjemaet som inneholder tabellnavnet.

Etter å ha opprettet en tilkobling til databasen, vil vi bruke read_sql_table funksjon for å laste inn Student tabell inn i en Pandas DataFrame.

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') df = pd.read_sql_table('Student', engine)
print(df.head()) engine.dispose()

Utgang:

 rollNumber firstName lastName email dateOfBirth
0 1 Mark Simson  2000-02-23
1 2 Peter Griffen  2001-04-15
2 3 Meg Aniston  2001-09-20

Vi antar at det er et stort bord som kan være minnekrevende. La oss utforske hvordan vi kan bruke chunksize parameter for å løse dette problemet.

Sjekk ut vår praktiske, praktiske guide for å lære Git, med beste praksis, bransjeaksepterte standarder og inkludert jukseark. Slutt å google Git-kommandoer og faktisk lære den!

Kode:

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') df_iterator = pd.read_sql_table('Student', engine, chunksize = 1) for df in df_iterator: print(df.head()) engine.dispose()

Utgang:

 rollNumber firstName lastName email dateOfBirth
0 1 Mark Simson  2000-02-23
0 2 Peter Griffen  2001-04-15
0 3 Meg Aniston  2001-09-20

Vær oppmerksom på at chunksize Jeg bruker her er 1 fordi jeg bare har 3 poster i tabellen min.

Spørre DB direkte med Pandas' SQL-syntaks

Å hente ut innsikt fra databasen er en viktig del for dataanalytikere og forskere. For å gjøre det, vil vi utnytte read_sql_query() funksjon.

Hva er read_sql_query()?

Bruker Pandas read_sql_query() funksjon, kan vi kjøre SQL-spørringer og få resultatene direkte inn i en DataFrame. De read_sql_query() funksjonen er laget spesielt for SELECT uttalelser. Den kan ikke brukes til andre operasjoner, som f.eks DELETE or UPDATE.

Syntaks:

pandas.read_sql_query(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, chunksize=None, dtype=None, dtype_backend=_NoDefault.no_default)

Alle parameterbeskrivelser er de samme som read_sql() funksjon. Her er et eksempel på read_sql_query():

Kode:

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') df = pd.read_sql_query('Select firstName, lastName From Student Where rollNumber = 1', engine)
print(df) engine.dispose()

Utgang:

 firstName lastName
0 Mark Simson

Skrive SQL-filer med pandaer

Mens vi analyserte data, anta at vi oppdaget at noen få oppføringer må endres eller at det kreves en ny tabell eller visning med dataene. For å oppdatere eller sette inn en ny post, er en metode å bruke read_sql() og skriv en forespørsel. Den metoden kan imidlertid være lang. Pandaer gir en flott metode kalt to_sql() for situasjoner som dette.

I denne delen skal vi først bygge en ny tabell i databasen og deretter redigere en eksisterende.

Opprette en ny tabell i SQL-databasen

Før vi oppretter en ny tabell, la oss først diskutere to_sql() i detalj.

Hva er til_sql()?

De to_sql() funksjonen til Pandas-biblioteket lar oss skrive eller oppdatere databasen. De to_sql() funksjon kan lagre DataFrame-data til en SQL-database.

Syntaks for to_sql():

DataFrame.to_sql(name, con, schema=None, if_exists='fail', index=True, index_label=None, chunksize=None, dtype=None, method=None)

Bare name og con parametere er obligatoriske for å kjøre to_sql(); men andre parametere gir ekstra fleksibilitet og tilpasningsmuligheter. La oss diskutere hver parameter i detalj:

name: Navnet på SQL-tabellen som skal opprettes eller endres.
con: Tilkoblingsobjektet til databasen.
schema: Skjemaet for tabellen (valgfritt).
if_exists: Standardverdien for denne parameteren er "fail". Denne parameteren lar oss bestemme handlingen som skal utføres hvis tabellen allerede eksisterer. Alternativene inkluderer "mislykkes", "erstatt" og "legg til".
index: Indeksparameteren godtar en boolsk verdi. Som standard er den satt til True, noe som betyr at indeksen til DataFrame vil bli skrevet til SQL-tabellen.
index_label: Denne valgfrie parameteren lar oss spesifisere en kolonneetikett for indekskolonnene. Som standard skrives indeksen til tabellen, men et spesifikt navn kan gis ved å bruke denne parameteren.
chunksize: Antall rader som skal skrives om gangen i SQL-databasen.
dtype: Denne parameteren godtar en ordbok med nøkler som kolonnenavn og verdier som deres datatyper.
method: Metodeparameteren gjør det mulig å spesifisere metoden som brukes for å sette inn data i SQL. Som standard er den satt til Ingen, noe som betyr at pandaer vil finne den mest effektive måten basert på databasen. Det er to hovedalternativer for metodeparametere:
- multi: Den tillater å sette inn flere rader i en enkelt SQL-spørring. Imidlertid støtter ikke alle databaser innsetting av flere rader.
- Ringbar funksjon: Her kan vi skrive en egendefinert funksjon for innsetting og kalle den ved å bruke metodeparametere.

Her er et eksempel på bruk to_sql():

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') data = {'Name': ['Paul', 'Tom', 'Jerry'], 'Age': [9, 8, 7]}
df = pd.DataFrame(data) df.to_sql('Customer', con=engine, if_exists='fail') engine.dispose()

En ny tabell kalt Kunde opprettes i databasen, med to felt kalt "Navn" og "Alder."

Database øyeblikksbilde:

Utdata av to_sql()

Oppdatering av eksisterende tabeller med Pandas datarammer

Oppdatering av data i en database er en kompleks oppgave, spesielt når du arbeider med store data. Men ved å bruke to_sql() funksjon i Pandas kan gjøre denne oppgaven mye enklere. For å oppdatere den eksisterende tabellen i databasen, to_sql() funksjonen kan brukes med if_exists parameter satt til "erstatt". Dette vil overskrive den eksisterende tabellen med de nye dataene.

Her er et eksempel på to_sql() som oppdaterer den tidligere opprettede Customer bord. Anta, i Customer tabellen ønsker vi å oppdatere alderen til en kunde som heter Paul fra 9 til 10. For å gjøre det kan vi først endre den tilsvarende raden i DataFrame, og deretter bruke to_sql() funksjon for å oppdatere databasen.

Kode:

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') df = pd.read_sql_table('Customer', engine) df.loc[df['Name'] == 'Paul', 'Age'] = 10 df.to_sql('Customer', con=engine, if_exists='replace') engine.dispose()

I databasen er Pauls alder oppdatert:

Utdata av to_sql()

konklusjonen

Som konklusjon er Pandas og SQL begge kraftige verktøy for dataanalyseoppgaver som å lese og skrive data til SQL-databasen. Pandas gir en enkel måte å koble til SQL-databasen, lese data fra databasen til en Pandas-dataramme og skrive datarammedata tilbake til databasen.

Pandas-biblioteket gjør det enkelt å manipulere data i en dataramme, mens SQL gir et kraftig språk for å spørre etter data i en database. Å bruke både Pandas og SQL til å lese og skrive dataene kan spare tid og krefter i dataanalyseoppgaver, spesielt når dataene er svært store. Samlet sett kan bruk av SQL og Pandas sammen hjelpe dataanalytikere og forskere å strømlinjeforme arbeidsflyten deres.