Lettura e scrittura di file SQL in Panda

Ripubblicato da Platone

Seguaci: 0

Quando ho iniziato a imparare l'analisi dei dati alcuni anni fa, la prima cosa che ho imparato è stata SQL e Panda. In qualità di analista di dati, è fondamentale disporre di solide basi per lavorare con SQL e Panda. Entrambi sono potenti strumenti che aiutano gli analisti di dati ad analizzare e manipolare in modo efficiente i dati archiviati nei database.

Panoramica di SQL e Panda

SQL (Structured Query Language) è un linguaggio di programmazione utilizzato per gestire e manipolare database relazionali. D'altra parte, Pandas è una libreria Python utilizzata per la manipolazione e l'analisi dei dati.

L'analisi dei dati comporta l'utilizzo di grandi quantità di dati e i database vengono spesso utilizzati per archiviare questi dati. SQL e Panda forniscono potenti strumenti per lavorare con i database, consentendo agli analisti di dati di estrarre, manipolare e analizzare i dati in modo efficiente. Sfruttando questi strumenti, gli analisti di dati possono ottenere informazioni preziose da dati che altrimenti sarebbero difficili da ottenere.

In questo articolo, esploreremo come utilizzare SQL e Panda per leggere e scrivere in un database.

Collegamento al DB

Installazione delle librerie

Dobbiamo prima installare le librerie necessarie prima di poterci connettere al database SQL con Pandas. Le due librerie principali richieste sono Pandas e SQLAlchemy. Pandas è una popolare libreria di manipolazione dei dati che consente l'archiviazione di grandi strutture di dati, come menzionato nell'introduzione. Al contrario, SQLAlchemy fornisce un'API per la connessione e l'interazione con il database SQL.

Possiamo installare entrambe le librerie utilizzando il gestore di pacchetti Python, pip, eseguendo i seguenti comandi al prompt dei comandi.

$ pip install pandas
$ pip install sqlalchemy

Effettuare la connessione

Con le librerie installate, ora possiamo usare Panda per connetterci al database SQL.

Per iniziare, creeremo un oggetto motore SQLAlchemy con create_engine(). create_engine() La funzione collega il codice Python al database. Accetta come argomento una stringa di connessione che specifica il tipo di database e i dettagli della connessione. In questo esempio, utilizzeremo il tipo di database SQLite e il percorso del file del database.

Crea un oggetto motore per un database SQLite utilizzando l'esempio seguente:

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

Se il file del database SQLite, student.db nel nostro caso, si trova nella stessa directory dello script Python, possiamo utilizzare direttamente il nome del file, come mostrato di seguito.

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

Lettura di file SQL con Panda

Leggiamo i dati ora che abbiamo stabilito una connessione. In questa sezione, esamineremo il read_sql, read_sql_tablee read_sql_query funzioni e come usarle per lavorare con un database.

Esecuzione di query SQL utilizzando Panda leggi_sql() Funzione

Il read_sql() è una funzione della libreria Pandas che ci consente di eseguire una query SQL e recuperare i risultati in un dataframe Pandas. IL read_sql() La funzione collega SQL e Python, permettendoci di sfruttare la potenza di entrambi i linguaggi. La funzione avvolge read_sql_table() ed read_sql_query(). read_sql() la funzione viene instradata internamente in base all'input fornito, il che significa che se l'input deve eseguire una query SQL, verrà instradato a read_sql_query()e se si tratta di una tabella di database, verrà instradata a read_sql_table().

Il read_sql() la sintassi è la seguente:

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

I parametri SQL e con sono obbligatori; il resto è facoltativo. Tuttavia, possiamo manipolare il risultato utilizzando questi parametri opzionali. Diamo un'occhiata più da vicino a ciascun parametro.

sql: query SQL o nome della tabella del database
con: oggetto di connessione o URL di connessione
index_col: Questo parametro ci consente di utilizzare una o più colonne dal risultato della query SQL come indice del frame di dati. Può richiedere una singola colonna o un elenco di colonne.
coerce_float: Questo parametro specifica se i valori non numerici devono essere convertiti in numeri in virgola mobile o lasciati come stringhe. È impostato su true per impostazione predefinita. Se possibile, converte i valori non numerici in tipi float.
params: i parametri forniscono un metodo sicuro per passare valori dinamici alla query SQL. Possiamo usare il parametro params per passare un dizionario, una tupla o un elenco. A seconda del database, la sintassi di params varia.
parse_dates: Questo ci consente di specificare quale colonna nel dataframe risultante verrà interpretata come una data. Accetta una singola colonna, un elenco di colonne o un dizionario con la chiave come nome della colonna e il valore come formato della colonna.
columns: Questo ci consente di recuperare solo le colonne selezionate dall'elenco.
chunksize: quando si lavora con un set di dati di grandi dimensioni, chunksize è importante. Recupera il risultato della query in blocchi più piccoli, migliorando le prestazioni.

Ecco un esempio di come utilizzare read_sql():

Codice:

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

Produzione:

 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]

Dopo esserci connessi al database, eseguiamo una query che restituisce tutti i record dal file Student table e li memorizza nel DataFrame df. La colonna "Numero di matricola" viene convertita in un indice utilizzando il index_col parametro e il tipo di dati "dateOfBirth" è "datetime64[ns]" a causa di parse_dates. Possiamo usare read_sql() non solo per recuperare i dati, ma anche per eseguire altre operazioni come l'inserimento, l'eliminazione e l'aggiornamento. read_sql() è una funzione generica.

Caricamento di tabelle o viste specifiche dal database

Caricamento di una tabella o vista specifica con Pandas read_sql_table() è un'altra tecnica per leggere i dati dal database in un dataframe Pandas.

Che cosa è l' leggi_sql_tabella?

La libreria Pandas fornisce il read_sql_table funzione, progettata specificamente per leggere un'intera tabella SQL senza eseguire alcuna query e restituire il risultato come dataframe Pandas.

La sintassi di read_sql_table() è come sotto:

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

Eccetto per table_name e schema, i parametri sono spiegati allo stesso modo di read_sql().

table_name: Il parametro table_name è il nome della tabella SQL nel database.
schema: questo parametro facoltativo è il nome dello schema contenente il nome della tabella.

Dopo aver creato una connessione al database, utilizzeremo il file read_sql_table funzione per caricare il file Student table in un DataFrame Pandas.

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

Produzione:

 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

Supponiamo che si tratti di una tabella di grandi dimensioni che può richiedere un uso intensivo della memoria. Esploriamo come possiamo usare il chunksize parametro per risolvere questo problema.

Dai un'occhiata alla nostra guida pratica e pratica per l'apprendimento di Git, con le migliori pratiche, gli standard accettati dal settore e il cheat sheet incluso. Smetti di cercare su Google i comandi Git e in realtà imparare esso!

Codice:

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

Produzione:

 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

Si prega di tenere presente che il chunksize Sto usando qui è 1 perché ho solo 3 record nella mia tabella.

Interrogazione diretta del DB con la sintassi SQL di Pandas

L'estrazione di informazioni dal database è una parte importante per gli analisti di dati e gli scienziati. Per fare ciò, sfrutteremo il read_sql_query() funzione.

Cos'è read_sql_query()?

Usando i panda read_sql_query() funzione, possiamo eseguire query SQL e ottenere i risultati direttamente in un DataFrame. IL read_sql_query() funzione è creata appositamente per SELECT dichiarazioni. Non può essere utilizzato per altre operazioni, ad esempio DELETE or UPDATE.

Sintassi:

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)

Tutte le descrizioni dei parametri sono le stesse del read_sql() funzione. Ecco un esempio di read_sql_query():

Codice:

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

Produzione:

 firstName lastName
0 Mark Simson

Scrivere file SQL con Panda

Durante l'analisi dei dati, supponiamo di aver scoperto che alcune voci devono essere modificate o che è necessaria una nuova tabella o vista con i dati. Per aggiornare o inserire un nuovo record, un metodo è utilizzare read_sql() e scrivi una domanda. Tuttavia, tale metodo può essere lungo. I panda forniscono un ottimo metodo chiamato to_sql() per situazioni come questa.

In questa sezione, creeremo prima una nuova tabella nel database e poi ne modificheremo una esistente.

Creazione di una nuova tabella nel database SQL

Prima di creare una nuova tabella, discutiamone prima to_sql() in dettaglio.

Che cosa è l' to_sql()?

Il to_sql() funzione della libreria Pandas ci permette di scrivere o aggiornare il database. IL to_sql() La funzione può salvare i dati DataFrame in un database SQL.

Sintassi per to_sql():

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

Solo name ed con i parametri sono obbligatori per l'esecuzione to_sql(); tuttavia, altri parametri forniscono ulteriore flessibilità e opzioni di personalizzazione. Discutiamo ogni parametro in dettaglio:

name: il nome della tabella SQL da creare o modificare.
con: L'oggetto connessione del database.
schema: lo schema della tabella (facoltativo).
if_exists: Il valore predefinito di questo parametro è "fail". Questo parametro ci permette di decidere l'azione da intraprendere se la tabella esiste già. Le opzioni includono "fallire", "sostituire" e "aggiungere".
index: Il parametro index accetta un valore booleano. Per impostazione predefinita, è impostato su True, il che significa che l'indice del DataFrame verrà scritto nella tabella SQL.
index_label: Questo parametro facoltativo ci consente di specificare un'etichetta di colonna per le colonne dell'indice. Per impostazione predefinita, l'indice viene scritto nella tabella, ma è possibile assegnare un nome specifico utilizzando questo parametro.
chunksize: il numero di righe da scrivere alla volta nel database SQL.
dtype: questo parametro accetta un dizionario con chiavi come nomi di colonna e valori come tipi di dati.
method: Il parametro metodo consente di specificare il metodo utilizzato per l'inserimento dei dati nell'SQL. Per impostazione predefinita, è impostato su Nessuno, il che significa che i panda troveranno il modo più efficiente in base al database. Esistono due opzioni principali per i parametri del metodo:
- multi: Permette di inserire più righe in una singola query SQL. Tuttavia, non tutti i database supportano l'inserimento su più righe.
- Funzione richiamabile: Qui possiamo scrivere una funzione personalizzata per insert e chiamarla usando i parametri del metodo.

Ecco un esempio usando 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()

Nel database viene creata una nuova tabella denominata Cliente, con due campi denominati "Nome" ed "Età".

Istantanea del database:

Uscita di to_sql()

Aggiornamento delle tabelle esistenti con Pandas Dataframes

L'aggiornamento dei dati in un database è un'attività complessa, in particolare quando si tratta di dati di grandi dimensioni. Tuttavia, utilizzando il to_sql() funzione in Pandas può rendere questo compito molto più semplice. Per aggiornare la tabella esistente nel database, il file to_sql() funzione può essere utilizzata con il if_exists parametro impostato su “replace”. Questo sovrascriverà la tabella esistente con i nuovi dati.

Ecco un esempio di to_sql() che aggiorna il file precedentemente creato Customer tavolo. Supponiamo, nel Customer tabella vogliamo aggiornare l'età di un cliente di nome Paul da 9 a 10. Per fare ciò, prima, possiamo modificare la riga corrispondente nel DataFrame, quindi utilizzare la to_sql() funzione per aggiornare il database.

Codice:

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

Nel database, l'età di Paul viene aggiornata:

Uscita di to_sql()

Conclusione

In conclusione, Panda e SQL sono entrambi potenti strumenti per attività di analisi dei dati come la lettura e la scrittura di dati nel database SQL. Pandas fornisce un modo semplice per connettersi al database SQL, leggere i dati dal database in un dataframe Pandas e riscrivere i dati del dataframe nel database.

La libreria Pandas semplifica la manipolazione dei dati in un dataframe, mentre SQL fornisce un potente linguaggio per interrogare i dati in un database. L'utilizzo sia di Panda che di SQL per leggere e scrivere i dati può far risparmiare tempo e fatica nelle attività di analisi dei dati, soprattutto quando i dati sono molto grandi. Nel complesso, l'utilizzo combinato di SQL e Panda può aiutare gli analisti di dati e gli scienziati a semplificare il proprio flusso di lavoro.