Zephyrnet-logotyp

Läsa och skriva SQL-filer i Pandas

Datum:

När jag började lära mig Dataanalys för några år sedan var det första jag lärde mig SQL och Pandas. Som dataanalytiker är det avgörande att ha en stark grund i att arbeta med SQL och Pandas. Båda är kraftfulla verktyg som hjälper dataanalytiker att effektivt analysera och manipulera lagrad data i databaser.

Översikt över SQL och Pandas

SQL (Structured Query Language) är ett programmeringsspråk som används för att hantera och manipulera relationsdatabaser. Å andra sidan är Pandas ett Python-bibliotek som används för datamanipulation och analys.

Dataanalys innebär att man arbetar med stora mängder data, och databaser används ofta för att lagra denna data. SQL och Pandas tillhandahåller kraftfulla verktyg för att arbeta med databaser, vilket gör det möjligt för dataanalytiker att effektivt extrahera, manipulera och analysera data. Genom att utnyttja dessa verktyg kan dataanalytiker få värdefulla insikter från data som annars skulle vara svåra att få tag på.

I den här artikeln kommer vi att utforska hur man använder SQL och Pandas för att läsa och skriva till en databas.

Ansluter till DB

Installation av biblioteken

Vi måste först installera de nödvändiga biblioteken innan vi kan ansluta till SQL-databasen med Pandas. De två huvudbibliotek som krävs är Pandas och SQLAlchemy. Pandas är ett populärt datamanipuleringsbibliotek som tillåter lagring av stora datastrukturer, som nämnts i inledningen. Däremot tillhandahåller SQLAlchemy ett API för att ansluta till och interagera med SQL-databasen.

Vi kan installera båda biblioteken med Python-pakethanteraren, pip, genom att köra följande kommandon vid kommandotolken.

$ pip install pandas
$ pip install sqlalchemy

Gör anslutningen

Med biblioteken installerade kan vi nu använda Pandas för att ansluta till SQL-databasen.

Till att börja med kommer vi att skapa ett SQLAlchemy-motorobjekt med create_engine(). De create_engine() funktionen kopplar Python-koden till databasen. Den tar som argument en anslutningssträng som anger databastyp och anslutningsdetaljer. I det här exemplet kommer vi att använda SQLite-databastypen och databasfilens sökväg.

Skapa ett motorobjekt för en SQLite-databas med exemplet nedan:

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

Om SQLite-databasfilen, student.db i vårt fall, finns i samma katalog som Python-skriptet kan vi använda filnamnet direkt, som visas nedan.

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

Läser SQL-filer med pandor

Låt oss läsa data nu när vi har upprättat en anslutning. I det här avsnittet kommer vi att titta på read_sql, read_sql_tableoch read_sql_query funktioner och hur man använder dem för att arbeta med en databas.

Exekvera SQL-frågor med Pandas read_sql() Funktion

Smakämnen read_sql() är en Pandas biblioteksfunktion som låter oss köra en SQL-fråga och hämta resultaten till en Pandas dataram. De read_sql() funktion kopplar ihop SQL och Python, vilket gör att vi kan dra nytta av kraften i båda språken. Funktionen avslutas read_sql_table() och read_sql_query(). De read_sql() funktionen dirigeras internt baserat på den inmatning som tillhandahålls, vilket innebär att om ingången ska köra en SQL-fråga kommer den att dirigeras till read_sql_query(), och om det är en databastabell kommer den att dirigeras till read_sql_table().

Smakämnen read_sql() syntaxen är följande:

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

SQL- och con-parametrar krävs; resten är valfria. Vi kan dock manipulera resultatet med dessa valfria parametrar. Låt oss ta en närmare titt på varje parameter.

  • sql: SQL-fråga eller databastabellnamn
  • con: Anslutningsobjekt eller anslutnings-URL
  • index_col: Denna parameter tillåter oss att använda en eller flera kolumner från SQL-frågeresultatet som ett dataramindex. Det kan ta antingen en enda kolumn eller en lista med kolumner.
  • coerce_float: Den här parametern anger om icke-numeriska värden ska konverteras till flytande tal eller lämnas som strängar. Den är inställd på sant som standard. Om möjligt konverterar den icke-numeriska värden till flytande typer.
  • params: Parametrarna tillhandahåller en säker metod för att skicka dynamiska värden till SQL-frågan. Vi kan använda parametern params för att skicka en ordbok, tupel eller lista. Beroende på databasen varierar syntaxen för parametrar.
  • parse_dates: Detta låter oss ange vilken kolumn i den resulterande dataramen som ska tolkas som ett datum. Den accepterar en enstaka kolumn, en lista med kolumner eller en ordbok med nyckeln som kolumnnamn och värdet som kolumnformat.
  • columns: Detta tillåter oss att bara hämta valda kolumner från listan.
  • chunksize: När du arbetar med en stor datamängd är chunksize viktigt. Den hämtar frågeresultatet i mindre bitar, vilket förbättrar prestandan.

Här är ett exempel på hur du använder read_sql():

Koda:

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

Produktion:

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

Efter att ha anslutit till databasen kör vi en fråga som returnerar alla poster från Student tabellen och lagrar dem i DataFrame df. Kolumnen "Rullnummer" konverteras till ett index med hjälp av index_col parametern och datatypen "dateOfBirth" är "datetime64[ns]" på grund av parse_dates. Vi kan använda read_sql() inte bara för att hämta data utan också för att utföra andra operationer som att infoga, ta bort och uppdatera. read_sql() är en generisk funktion.

Laddar specifika tabeller eller vyer från DB

Laddar en specifik tabell eller vy med Pandas read_sql_table() är en annan teknik för att läsa data från databasen till en Pandas dataram.

Vad är read_sql_table?

Pandas bibliotek tillhandahåller read_sql_table funktion, som är speciellt utformad för att läsa en hel SQL-tabell utan att köra några frågor och returnera resultatet som en Pandas dataram.

Syntaxen för read_sql_table() är som nedan:

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

Förutom table_name och schema, parametrarna förklaras på samma sätt som read_sql().

  • table_name: Parametern table_name är namnet på SQL-tabellen i databasen.
  • schema: Denna valfria parameter är namnet på schemat som innehåller tabellnamnet.

Efter att ha skapat en anslutning till databasen kommer vi att använda read_sql_table funktion för att ladda Student tabell 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()

Produktion:

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

Vi antar att det är ett stort bord som kan vara minneskrävande. Låt oss utforska hur vi kan använda chunksize parameter för att lösa detta problem.

Kolla in vår praktiska, praktiska guide för att lära dig Git, med bästa praxis, branschaccepterade standarder och medföljande fuskblad. Sluta googla Git-kommandon och faktiskt lära Det!

Koda:

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

Produktion:

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

Vänligen kom ihåg att chunksize Jag använder här är 1 eftersom jag bara har 3 poster i min tabell.

Fråga i databasen direkt med Pandas SQL-syntax

Att extrahera insikter från databasen är en viktig del för dataanalytiker och forskare. För att göra det kommer vi att utnyttja read_sql_query() funktion.

Vad är read_sql_query()?

Använder Pandas read_sql_query() funktion kan vi köra SQL-frågor och få resultaten direkt i en DataFrame. De read_sql_query() funktionen är skapad speciellt för SELECT uttalanden. Den kan inte användas för några andra operationer, som t.ex DELETE or UPDATE.

Syntax:

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)

Alla parameterbeskrivningar är desamma som read_sql() fungera. Här är ett exempel på read_sql_query():

Koda:

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

Produktion:

 firstName lastName
0 Mark Simson

Skriva SQL-filer med pandor

När vi analyserade data, anta att vi upptäckte att några poster måste ändras eller att en ny tabell eller vy med data krävs. För att uppdatera eller infoga en ny post är en metod att använda read_sql() och skriv en fråga. Den metoden kan dock vara lång. Pandas ger en bra metod som kallas to_sql() för sådana här situationer.

I det här avsnittet kommer vi först att bygga en ny tabell i databasen och sedan redigera en befintlig.

Skapa en ny tabell i SQL-databasen

Innan vi skapar en ny tabell, låt oss först diskutera to_sql() i detalj.

Vad är to_sql()?

Smakämnen to_sql() funktion i Pandas-biblioteket tillåter oss att skriva eller uppdatera databasen. De to_sql() funktionen kan spara DataFrame-data till en SQL-databas.

Syntax för to_sql():

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

Endast name och con parametrar är obligatoriska för att köras to_sql(); andra parametrar ger dock ytterligare flexibilitet och anpassningsmöjligheter. Låt oss diskutera varje parameter i detalj:

  • name: Namnet på SQL-tabellen som ska skapas eller ändras.
  • con: Anslutningsobjektet för databasen.
  • schema: Tabellens schema (valfritt).
  • if_exists: Standardvärdet för denna parameter är "fail". Denna parameter låter oss bestämma vilken åtgärd som ska vidtas om tabellen redan finns. Alternativen inkluderar "misslyckas", "ersätt" och "lägg till".
  • index: Indexparametern accepterar ett booleskt värde. Som standard är det satt till True, vilket betyder att indexet för DataFrame kommer att skrivas till SQL-tabellen.
  • index_label: Denna valfria parameter låter oss ange en kolumnetikett för indexkolumnerna. Som standard skrivs indexet till tabellen, men ett specifikt namn kan ges med denna parameter.
  • chunksize: Antalet rader som ska skrivas åt gången i SQL-databasen.
  • dtype: Denna parameter accepterar en ordbok med nycklar som kolumnnamn och värden som deras datatyper.
  • method: Metodparametern gör det möjligt att specificera metoden som används för att infoga data i SQL. Som standard är den inställd på Ingen, vilket betyder att pandor kommer att hitta det mest effektiva sättet baserat på databasen. Det finns två huvudalternativ för metodparametrar:
    • multi: Det tillåter att infoga flera rader i en enda SQL-fråga. Men inte alla databaser stöder infogning av flera rader.
    • Anropsbar funktion: Här kan vi skriva en anpassad funktion för infogning och anropa den med hjälp av metodparametrar.

Här är ett exempel på att använda 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 som heter Kund skapas i databasen, med två fält som heter "Namn" och "Ålder".

Databas ögonblicksbild:

Utdata från to_sql()

Uppdatera befintliga tabeller med Pandas dataramar

Att uppdatera data i en databas är en komplex uppgift, särskilt när man hanterar stora data. Men att använda to_sql() funktion i Pandas kan göra denna uppgift mycket enklare. För att uppdatera den befintliga tabellen i databasen, to_sql() funktionen kan användas med if_exists parameter satt till "ersätt". Detta kommer att skriva över den befintliga tabellen med nya data.

Här är ett exempel på to_sql() som uppdaterar den tidigare skapade Customer tabell. Antag att i Customer tabell vill vi uppdatera åldern på en kund som heter Paul från 9 till 10. För att göra det kan vi först ändra motsvarande rad i DataFrame och sedan använda to_sql() funktion för att uppdatera databasen.

Koda:

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 uppdateras Pauls ålder:

Utdata från to_sql()

Slutsats

Sammanfattningsvis är Pandas och SQL båda kraftfulla verktyg för dataanalysuppgifter som att läsa och skriva data till SQL-databasen. Pandas tillhandahåller ett enkelt sätt att ansluta till SQL-databasen, läsa data från databasen till en Pandas-dataram och skriva tillbaka dataramdata till databasen.

Pandas-biblioteket gör det enkelt att manipulera data i en dataram, medan SQL tillhandahåller ett kraftfullt språk för att söka efter data i en databas. Att använda både Pandas och SQL för att läsa och skriva data kan spara tid och ansträngning i dataanalysuppgifter, särskilt när data är mycket stora. Sammantaget kan utnyttjande av SQL och Pandas tillsammans hjälpa dataanalytiker och forskare effektivisera sitt arbetsflöde.

plats_img

Senaste intelligens

plats_img