Læsning og skrivning af SQL-filer i pandaer

Genudgivet af Platon

Abonnenter: 0

Da jeg begyndte at lære Dataanalyse for nogle år siden, var det første, jeg lærte, SQL og Pandas. Som dataanalytiker er det afgørende at have et stærkt fundament i arbejdet med SQL og Pandas. Begge er kraftfulde værktøjer, der hjælper dataanalytikere med effektivt at analysere og manipulere lagrede data i databaser.

Oversigt over SQL og Pandas

SQL (Structured Query Language) er et programmeringssprog, der bruges til at administrere og manipulere relationelle databaser. På den anden side er Pandas et Python-bibliotek, der bruges til datamanipulation og -analyse.

Dataanalyse involverer arbejde med store mængder data, og databaser bruges ofte til at gemme disse data. SQL og Pandas leverer kraftfulde værktøjer til at arbejde med databaser, hvilket giver dataanalytikere mulighed for effektivt at udtrække, manipulere og analysere data. Ved at udnytte disse værktøjer kan dataanalytikere få værdifuld indsigt fra data, som ellers ville være svære at opnå.

I denne artikel vil vi undersøge, hvordan du bruger SQL og Pandas til at læse og skrive til en database.

Opretter forbindelse til DB

Installation af bibliotekerne

Vi skal først installere de nødvendige biblioteker, før vi kan oprette forbindelse til SQL-databasen med Pandas. De to hovedbiblioteker, der kræves, er Pandas og SQLAlchemy. Pandas er et populært datamanipulationsbibliotek, der giver mulighed for lagring af store datastrukturer, som nævnt i indledningen. I modsætning hertil giver SQLAlchemy en API til at oprette forbindelse til og interagere med SQL-databasen.

Vi kan installere begge biblioteker ved hjælp af Python-pakkehåndteringen, pip, ved at køre følgende kommandoer ved kommandoprompten.

$ pip install pandas
$ pip install sqlalchemy

Opretter forbindelsen

Med bibliotekerne installeret kan vi nu bruge Pandas til at oprette forbindelse til SQL-databasen.

Til at begynde med vil vi oprette et SQLAlchemy-motorobjekt med create_engine(). Det create_engine() funktionen forbinder Python-koden til databasen. Det tager som argument en forbindelsesstreng, der specificerer databasetypen og forbindelsesdetaljerne. I dette eksempel bruger vi SQLite-databasetypen og databasefilens sti.

Opret et motorobjekt til en SQLite-database ved at bruge 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 vores tilfælde, er i samme mappe som Python-scriptet, kan vi bruge filnavnet direkte, som vist nedenfor.

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

Læsning af SQL-filer med pandaer

Lad os læse data nu, hvor vi har etableret en forbindelse. I dette afsnit vil vi se på read_sql, read_sql_tableog read_sql_query funktioner og hvordan man bruger dem til at arbejde med en database.

Udførelse af SQL-forespørgsler ved hjælp af Panda's læs_sql() Funktion

read_sql() er en Pandas-biblioteksfunktion, der giver os mulighed for at udføre en SQL-forespørgsel og hente resultaterne ind i en Pandas-dataramme. Det read_sql() funktion forbinder SQL og Python, så vi kan drage fordel af begge sprogs kraft. Funktionen ombrydes read_sql_table() , read_sql_query(). Det read_sql() funktion dirigeres internt på baggrund af det leverede input, hvilket betyder, at hvis inputtet skal udføre en SQL-forespørgsel, vil det blive dirigeret til read_sql_query(), og hvis det er en databasetabel, vil den blive dirigeret til read_sql_table().

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 parametre er påkrævet; resten er valgfrit. Vi kan dog manipulere resultatet ved hjælp af disse valgfri parametre. Lad os se nærmere på hver parameter.

sql: Navn på SQL-forespørgsel eller databasetabel
con: Forbindelsesobjekt eller forbindelses-URL
index_col: Denne parameter giver os mulighed for at bruge en eller flere kolonner fra SQL-forespørgselsresultatet som et datarammeindeks. Det kan tage enten en enkelt kolonne eller en liste over kolonner.
coerce_float: Denne parameter angiver, om ikke-numeriske værdier skal konverteres til flydende tal eller efterlades som strenge. Den er som standard sat til sand. Hvis det er muligt, konverterer den ikke-numeriske værdier til flydende typer.
params: Parametrene giver en sikker metode til at sende dynamiske værdier til SQL-forespørgslen. Vi kan bruge parameteren params til at sende en ordbog, tupel eller liste. Afhængigt af databasen varierer syntaksen af parametre.
parse_dates: Dette giver os mulighed for at angive, hvilken kolonne i den resulterende dataramme, der skal fortolkes som en dato. Den accepterer en enkelt kolonne, en liste over kolonner eller en ordbog med nøglen som kolonnenavn og værdien som kolonneformat.
columns: Dette giver os mulighed for kun at hente udvalgte kolonner fra listen.
chunksize: Når du arbejder med et stort datasæt, er chunksize vigtig. Det henter forespørgselsresultatet i mindre bidder, hvilket forbedrer ydeevnen.

Her er et eksempel på, hvordan du bruger 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()

Output:

 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]

Efter at have oprettet forbindelse til databasen, udfører vi en forespørgsel, der returnerer alle poster fra Student tabel og gemmer dem i DataFrame df. Kolonnen "Rulnummer" konverteres til et indeks ved hjælp af index_col parameter, og "dateOfBirth" datatypen er "datetime64[ns]" pga parse_dates. Vi kan bruge read_sql() ikke kun for at hente data, men også for at udføre andre handlinger såsom indsættelse, sletning og opdatering. read_sql() er en generisk funktion.

Indlæsning af specifikke tabeller eller visninger fra databasen

Indlæsning af et bestemt bord eller en visning med Pandas read_sql_table() er en anden teknik til at læse data fra databasen ind i en Pandas dataramme.

Hvad er read_sql_table?

Pandas bibliotek tilbyder read_sql_table funktion, som er specifikt designet til at læse en hel SQL-tabel uden at udføre nogen forespørgsler og returnere resultatet som en Pandas-dataramme.

Syntaksen for 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)

Med undtagelse af table_name og skema, er parametrene forklaret på samme måde som read_sql().

table_name: Parameteren table_name er navnet på SQL-tabellen i databasen.
schema: Denne valgfri parameter er navnet på det skema, der indeholder tabelnavnet.

Efter at have oprettet en forbindelse til databasen, vil vi bruge read_sql_table funktion til at indlæse Student tabel ind 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()

Output:

 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 antager, at det er et stort bord, der kan være hukommelseskrævende. Lad os undersøge, hvordan vi kan bruge chunksize parameter for at løse dette problem.

Tjek vores praktiske, praktiske guide til at lære Git, med bedste praksis, brancheaccepterede standarder og inkluderet snydeark. Stop med at google Git-kommandoer og faktisk lærer det!

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

Output:

 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

Husk venligst, at chunksize Jeg bruger her er 1, fordi jeg kun har 3 poster i min tabel.

Forespørgsel i databasen direkte med Pandas' SQL-syntaks

Uddrag af indsigt fra databasen er en vigtig del for dataanalytikere og videnskabsmænd. For at gøre det, vil vi udnytte read_sql_query() funktion.

Hvad er read_sql_query()?

Brug af pandaer read_sql_query() funktion, kan vi køre SQL-forespørgsler og få resultaterne direkte ind i en DataFrame. Det read_sql_query() funktion er skabt specielt til SELECT udsagn. Den kan ikke bruges til andre operationer, 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() fungere. 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()

Output:

 firstName lastName
0 Mark Simson

Skrivning af SQL-filer med pandaer

Mens vi analyserede data, antag, at vi opdagede, at nogle få poster skal ændres, eller at en ny tabel eller visning med dataene er påkrævet. For at opdatere eller indsætte en ny post er en metode at bruge read_sql() og skriv en forespørgsel. Den metode kan dog være lang. Pandaer giver en fantastisk metode kaldet to_sql() til situationer som denne.

I dette afsnit vil vi først bygge en ny tabel i databasen og derefter redigere en eksisterende.

Oprettelse af en ny tabel i SQL-databasen

Før vi opretter en ny tabel, lad os først diskutere to_sql() i detaljer.

Hvad er to_sql()?

to_sql() funktion af Pandas biblioteket giver os mulighed for at skrive eller opdatere databasen. Det to_sql() funktion kan gemme 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)

Kun name , con parametre er obligatoriske for at køre to_sql(); dog giver andre parametre yderligere fleksibilitet og tilpasningsmuligheder. Lad os diskutere hver parameter i detaljer:

name: Navnet på den SQL-tabel, der skal oprettes eller ændres.
con: Forbindelsesobjektet for databasen.
schema: Skemaet for tabellen (valgfrit).
if_exists: Standardværdien for denne parameter er "fail". Denne parameter giver os mulighed for at beslutte, hvilken handling der skal udføres, hvis tabellen allerede eksisterer. Indstillinger omfatter "mislykkes", "erstat" og "tilføj".
index: Indeksparameteren accepterer en boolesk værdi. Som standard er det sat til True, hvilket betyder, at indekset for DataFrame vil blive skrevet til SQL-tabellen.
index_label: Denne valgfri parameter giver os mulighed for at angive en kolonnelabel for indekskolonnerne. Som standard skrives indekset til tabellen, men et specifikt navn kan gives ved hjælp af denne parameter.
chunksize: Antallet af rækker, der skal skrives ad gangen i SQL-databasen.
dtype: Denne parameter accepterer en ordbog med nøgler som kolonnenavne og værdier som deres datatyper.
method: Metodeparameteren gør det muligt at specificere den metode, der bruges til at indsætte data i SQL. Som standard er den sat til Ingen, hvilket betyder, at pandaer vil finde den mest effektive måde baseret på databasen. Der er to hovedmuligheder for metodeparametre:
- multi: Det giver mulighed for at indsætte flere rækker i en enkelt SQL-forespørgsel. Det er dog ikke alle databaser, der understøtter multi-row insert.
- Kaldbar funktion: Her kan vi skrive en brugerdefineret funktion til indsættelse og kalde den ved hjælp af metodeparametre.

Her er et eksempel på at bruge 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 tabel kaldet Kunde oprettes i databasen med to felter kaldet "Navn" og "Alder".

Database øjebliksbillede:

Output af to_sql()

Opdatering af eksisterende tabeller med Pandas Dataframes

Opdatering af data i en database er en kompleks opgave, især når der er tale om store data. Men ved at bruge to_sql() funktion i Pandas kan gøre denne opgave meget lettere. For at opdatere den eksisterende tabel i databasen, to_sql() funktionen kan bruges med if_exists parameter sat til "erstat". Dette vil overskrive den eksisterende tabel med de nye data.

Her er et eksempel på to_sql() der opdaterer den tidligere oprettede Customer bord. Antag, i Customer tabel vil vi opdatere alderen på en kunde ved navn Paul fra 9 til 10. For at gøre det kan vi først ændre den tilsvarende række i DataFrame og derefter bruge to_sql() funktion til at opdatere 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 opdateret:

Output af to_sql()

Konklusion

Afslutningsvis er Pandas og SQL begge kraftfulde værktøjer til dataanalyseopgaver såsom at læse og skrive data til SQL-databasen. Pandas giver en nem måde at oprette forbindelse til SQL-databasen, læse data fra databasen til en Pandas-dataramme og skrive datarammedata tilbage til databasen.

Pandas-biblioteket gør det nemt at manipulere data i en dataramme, hvorimod SQL giver et kraftfuldt sprog til at forespørge data i en database. Brug af både Pandas og SQL til at læse og skrive dataene kan spare tid og kræfter i dataanalyseopgaver, især når dataene er meget store. Samlet set kan udnyttelse af SQL og Pandas sammen hjælpe dataanalytikere og videnskabsmænd med at strømline deres arbejdsgang.