Come eseguire query SQL in Python e R – Tutorial

Creare un database

In questa parte impareremo a caricare il dataset COVID-19's impact on airport traffic, con licenza CC BY-NC-SA 4.0, nel nostro database SQLite. Impareremo anche a creare tabelle da zero.

Il dataset sul traffico aeroportuale riporta la percentuale del volume di traffico rispetto al periodo di riferimento dal 1° febbraio 2020 al 15 marzo 2020. Caricheremo un file CSV usando la funzione Pandas read_csv e poi useremo la funzione to_sql per trasferire il dataframe nella nostra tabella SQLite. La funzione to_sql richiede un nome tabella (stringa) e la connessione al motore SQLite.

data = pd.read_csv("data/covid_impact_on_airport_traffic.csv")


data.to_sql(
            'airport',             # Name of the sql table
            conn,                 # sqlite.Connection or sqlalchemy.engine.Engine
            if_exists='replace'
           )

Ora verificheremo se abbiamo avuto successo eseguendo una rapida query SQL. Prima di eseguire una query, dobbiamo creare un cursore che ci aiuterà a eseguire le query, come mostrato nel blocco di codice qui sotto. Puoi avere più cursori sullo stesso database all’interno di una singola connessione.

Nel nostro caso, la query SQL ha restituito tre colonne e cinque righe dalla tabella airport. Per visualizzare la prima riga, useremo cursor.fetchone().

cursor = conn.cursor()
cursor.execute("""SELECT Date, AirportName, PercentOfBaseline  
                  FROM airport
                  LIMIT 5""")
cursor.fetchone()


>>> ('2020-04-03', 'Kingsford Smith', 64)

Per visualizzare il resto dei record, useremo cursor.fetchall(). Il dataset aeroportuale è stato caricato correttamente nel database con poche righe di codice.

cursor.fetchall()

>>> [('2020-04-13', 'Kingsford Smith', 29),
     ('2020-07-10', 'Kingsford Smith', 54),
     ('2020-09-02', 'Kingsford Smith', 18),
     ('2020-10-31', 'Kingsford Smith', 22)]

Ora impariamo a creare una tabella da zero e popolarla aggiungendo valori di esempio. Creeremo una tabella studentinfo con id (integer, primary key, auto-increment), name (text) e subject (text).

Nota: La sintassi di SQLite è un po’ diversa. Ti consigliamo di consultare la cheat sheet di SQLite per capire le query SQL menzionate in questo tutorial.

cursor.execute("""
                CREATE TABLE studentinfo
                (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT,
                subject TEXT
                )
                 """)

Vediamo quante tabelle abbiamo aggiunto al database eseguendo una semplice query SQLite.

cursor.execute("""
                SELECT name
                FROM sqlite_master
                WHERE type='table'
                 """)
cursor.fetchall()

>>> [('airport',), ('studentinfo',)]

Esecuzione delle query

In questa sezione aggiungeremo valori alla tabella studentinfo ed eseguiremo semplici query SQL. Usando INSERT INTO, possiamo aggiungere una singola riga alla tabella studentinfo.

Per inserire valori, dobbiamo fornire gli argomenti query e value alla funzione execute. La funzione compila gli input “?” con i valori che abbiamo fornito.

query = """
            INSERT INTO studentinfo
            (name, subject)
            VALUES (?, ?)
        """
value = ("Marry", "Math")
cursor.execute(query,value)

Ripeti la query precedente aggiungendo più record.

query = """
           INSERT INTO studentinfo
           (name, subject)
           VALUES (?, ?)
        """
values = [("Abid", "Stat"),
          ("Carry", "Math"),
          ("Ali","Data Science"),
          ("Nisha","Data Science"),
          ("Matthew","Math"),
          ("Henry","Data Science")]

cursor.executemany(query,values)

È il momento di verificare i record. Per farlo eseguiremo una semplice query SQL che restituirà le righe in cui il subject è Data Science.

cursor.execute("""
                SELECT *
                FROM studentinfo
                WHERE subject LIKE 'Data Science'
                 """)
cursor.fetchall()

>>> [(4, 'Ali', 'Data Science'),
     (5, 'Nisha', 'Data Science'),
     (7, 'Henry', 'Data Science')]

Il comando DISTINCT subject viene usato per mostrare i valori unici presenti nelle colonne subject. Nel nostro caso, sono Math, Stat e Data Science.

cursor.execute("SELECT DISTINCT subject from studentinfo")
cursor.fetchall()

>>> [('Math',), ('Stat',), ('Data Science',)]

Per salvare tutte le modifiche useremo la funzione commit(). Senza il commit, i dati andranno persi dopo il riavvio della macchina.

conn.commit()

SQL con Pandas

In questa parte impareremo come estrarre i dati dal database SQLite e convertirli in un dataframe Pandas con una sola riga di codice. read_sql offre più del semplice eseguire query SQL. Possiamo usarlo per impostare colonne indice, analizzare data e ora, aggiungere valori e filtrare i nomi delle colonne. Scopri di più sull’importazione dei dati in Python seguendo un breve corso su DataCamp.

read_sql richiede due argomenti: una query SQL e la connessione al motore SQLite. L’output contiene le prime cinque righe della tabella airport in cui PercentOfBaseline è maggiore di 20.

data_sql_1 = pd.read_sql("""
                        SELECT Date,City,PercentOfBaseline
                        FROM airport
                        WHERE PercentOfBaseline > 20
                        ORDER BY Date DESC
                        """,
                        conn)
print(data_sql_1.head())

         Date          City  PercentOfBaseline
0  2020-12-02        Sydney                 27
1  2020-12-02      Santiago                 48
2  2020-12-02       Calgary                 99
3  2020-12-02  Leduc County                100
4  2020-12-02      Richmond                 86

Eseguire analisi dei dati su database relazionali è diventato più semplice con l’integrazione di Pandas. Possiamo anche usare questi dati per fare previsioni e svolgere analisi statistiche complesse.

La funzione plot viene utilizzata per visualizzare l’istogramma della colonna PercentOfBaseline.

data_sql_1.plot(y="PercentOfBaseline",kind="hist");

Allo stesso modo, possiamo limitare i valori ai primi 20 e visualizzare un grafico a linee di serie temporale.

data_sql_2 = pd.read_sql("""
                        SELECT Date,City,PercentOfBaseline
                        FROM airport
                        WHERE PercentOfBaseline > 20
                        ORDER BY Date DESC
                        LIMIT 20
                        """,
                        conn)


data_sql_2.plot(x="Date",y="PercentOfBaseline",kind="line");

Infine, chiuderemo la connessione per liberare risorse. La maggior parte dei pacchetti lo fa automaticamente, ma è preferibile chiudere le connessioni dopo aver finalizzato le modifiche.

conn.close()

Tutorial R

Replicheremo tutte le attività del tutorial Python usando R. Il tutorial include la creazione di connessioni, la scrittura di tabelle, l’aggiunta di righe, l’esecuzione di query e l’analisi dei dati con dplyr.

Configurazione

Il pacchetto DBI viene usato per connettersi ai database più popolari come MariaDB, Postgres, Duckdb e SQLite. Ad esempio, installa il pacchetto RMySQL e crea un database fornendo username, password, nome del database e indirizzo host.

install.packages("RMySQL")
library(RMySQL)
conn = dbConnect(
                MySQL(),
                user = 'abid',
                password = '1234',
                dbname = 'datacamp_R',
                host = 'localhost'
                )

In questo tutorial creeremo un database SQLite fornendo un nome e la funzione SQLite.

library(RSQLite)
library(DBI)
library(tidyverse)

conn = dbConnect(SQLite(), dbname = 'datacamp_R.db')

Creazione del database

Importando la libreria tidyverse, avremo accesso a dplyr, ggplot e ai dataset di default.

La funzione dbWriteTable accetta un data.frame e lo inserisce nella tabella SQL. Richiede tre argomenti: connessione a SQLite, nome della tabella e data frame. Con dbReadTable possiamo visualizzare l’intera tabella. Per vedere le prime 6 righe, abbiamo usato head.

dbWriteTable(conn, "cars", mtcars)
head(dbReadTable(conn, "cars"))

    mpg    cyl    disp    hp    drat    wt    qsec    vs    am    gear    carb
1    21.0    6    160    110    3.90    2.620    16.46    0    1    4    4
2    21.0    6    160    110    3.90    2.875    17.02    0    1    4    4
3    22.8    4    108    93    3.85    2.320    18.61    1    1    4    1
4    21.4    6    258    110    3.08    3.215    19.44    1    0    3    1
5    18.7    8    360    175    3.15    3.440    17.02    0    0    3    2
6    18.1    6    225    105    2.76    3.460    20.22    1    0    3    1

dbExecute ci permette di eseguire qualsiasi query SQLite, quindi lo useremo per creare una tabella chiamata idcard.

Per visualizzare i nomi delle tabelle nel database useremo dbListTables.

dbExecute(conn, 'CREATE TABLE idcard (id int, name text)')
dbListTables(conn)

>>> 'cars''idcard'

Aggiungiamo una singola riga alla tabella idcard e usiamo dbGetQuery per mostrare i risultati.

Nota: dbGetQuery esegue una query e restituisce i record, mentre dbExecute esegue una query SQL ma non restituisce alcun record.

dbExecute(conn, "INSERT INTO idcard (id,name)\
                 VALUES(1,'love')")
dbGetQuery(conn,"SELECT * FROM idcard")


id    name
1    love

Ora aggiungeremo altre due righe e mostreremo i risultati usando dbReadTable.

dbExecute(conn,"INSERT INTO idcard (id,name)\
                VALUES(2,'Kill'),(3,'Game')
               ")
dbReadTable(conn,'idcard')


id    name
1    love
2    Kill
3    Game

dbCreateTable ci consente di creare una tabella in modo semplice. Richiede tre argomenti: connessione, nome della tabella e un vettore di caratteri o un data.frame. Il vettore di caratteri è composto da nomi (nomi delle colonne) e valori (tipi). Nel nostro caso, forniremo un data.frame population predefinito per creare la struttura iniziale.

dbCreateTable(conn,'population',population)
dbReadTable(conn,'population')

country    year    population

Quindi useremo dbAppendTable per aggiungere valori nella tabella population.

dbAppendTable(conn,'population',head(population))
dbReadTable(conn,'population')

country    year    population
Afghanistan    1995    17586073
Afghanistan    1996    18415307
Afghanistan    1997    19021226
Afghanistan    1998    19496836
Afghanistan    1999    19987071
Afghanistan    2000    20595360

Esecuzione delle query

Useremo dbGetQuery per svolgere tutte le nostre attività di data analytics. Proviamo a eseguire una semplice query e poi a conoscere meglio le altre funzioni.

dbGetQuery(conn,"SELECT * FROM idcard")

id    name
1    love
2    Kill
3    Game

Puoi anche eseguire una query SQL complessa per filtrare la potenza del motore e mostrare un numero limitato di righe e colonne.

dbGetQuery(conn, "SELECT mpg,hp,gear\
                  FROM cars\
                  WHERE hp > 50\
                  LIMIT 5")


mpg    hp    gear
21.0    110    4
21.0    110    4
22.8    93    4
21.4    110    3
18.7    175    3

Per rimuovere tabelle, usa dbRemoveTable. Come possiamo vedere, abbiamo rimosso con successo la tabella idcard.

dbRemoveTable(conn,'idcard')
dbListTables(conn)
>>> 'cars''population'

Per capire meglio le tabelle useremo dbListFields, che mostrerà i nomi delle colonne in una determinata tabella.

dbListFields(conn, "cars")


>>> 'mpg''cyl''disp''hp''drat''wt''qsec''vs''am''gear''carb'

SQL con dplyr

In questa sezione useremo dplyr per leggere le tabelle ed eseguire poi query usando filter, select e collect. Se non vuoi imparare la sintassi SQL e preferisci svolgere tutto con puro R, questo metodo fa per te. Abbiamo caricato la tabella cars, l’abbiamo filtrata per gears e mpg e poi selezionato tre colonne come mostrato sotto.

cars_results <-
  tbl(conn, "cars") %>%
  filter(gear %in% c(4, 3),
         mpg >= 14,
         mpg <= 21) %>%
  select(mpg, hp, gear) %>%
  collect()
cars_results

mpg    hp    gear
21.0    110    4
21.0    110    4
18.7    175    3
18.1    105    3
14.3    245    3
...   ...   ...

Possiamo usare il dataframe filtrato per mostrare un boxplot con ggplot.

ggplot(cars_results,aes(fill=as.character(gear), y=mpg)) +
  geom_boxplot()

Oppure possiamo visualizzare un grafico a punti con facet diviso per numero di gears.

ggplot(cars_results,
  aes(mpg, ..count.. ) ) +
  geom_point(stat = "count", size = 4) +
  coord_flip()+
  facet_grid( as.character(gear) ~ . )

Conclusione

In questo tutorial abbiamo visto l’importanza di eseguire query SQL con Python e R, creare database, aggiungere tabelle ed effettuare analisi dei dati usando query SQL. Abbiamo anche imparato come Pandas e dplyr ci aiutino a eseguire query con una sola riga di codice.

SQL è una competenza imprescindibile per tutti i lavori legati alla tecnologia. Se stai iniziando la tua carriera come data analyst, ti consigliamo di completare in due mesi il career track Data Analyst with SQL Server. Questo percorso ti insegnerà tutto su query SQL, server e gestione delle risorse.

Puoi eseguire gratuitamente tutti gli script usati in questo tutorial:

• Il progetto SQL con Python è disponibile in questa cartella di lavoro DataLab.
• Il progetto SQL con R è disponibile in questa cartella di lavoro DataLab.