SQL-query's uitvoeren in Python en R: tutorial

Een database maken

In dit deel leren we hoe we de dataset COVID-19's impact on airport traffic, onder de licentie CC BY-NC-SA 4.0, in onze SQLite-database laden. We leren ook hoe we tabellen from scratch maken.

De dataset over luchthavenverkeer bestaat uit een percentage van het verkeersvolume tijdens de basisperiode van 1 februari 2020 tot en met 15 maart 2020. We laden een CSV-bestand met de Pandas-functie read_csv en gebruiken vervolgens de functie to_sql om de dataframe over te zetten naar onze SQLite-tabel. De functie to_sql vereist een tabelnaam (string) en een verbinding met de SQLite-engine.

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

We testen nu of het gelukt is door een snelle SQL-query uit te voeren. Voordat we een query draaien, moeten we een cursor maken die ons helpt bij het uitvoeren van query's, zoals in het onderstaande codeblok. Je kunt meerdere cursors op dezelfde database hebben binnen één verbinding.

In ons geval gaf de SQL-query drie kolommen en vijf rijen terug uit de tabel airport. Om de eerste rij weer te geven, gebruiken we cursor.fetchone().

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


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

Om de rest van de records weer te geven, gebruiken we cursor.fetchall(). De luchthaven-dataset is met een paar regels code succesvol in de database geladen.

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

Laten we nu leren een tabel from scratch te maken en te vullen met voorbeeldwaarden. We maken een tabel studentinfo met id (integer, primary key, auto-increment), name (text) en subject (text).

Opmerking: De SQLite-syntaxis is een beetje anders. Het is aan te raden de SQLite-cheatsheet te bekijken om de SQL-query's in deze tutorial te begrijpen.

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

Laten we controleren hoeveel tabellen we aan de database hebben toegevoegd door een eenvoudige SQLite-query uit te voeren.

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

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

Query's uitvoeren

In deze sectie voegen we waarden toe aan de tabel studentinfo en draaien we eenvoudige SQL-query's. Met INSERT INTO kunnen we één rij toevoegen aan de tabel studentinfo.

Om waarden in te voegen, moeten we query- en value-argumenten aan de functie execute meegeven. De functie vult de “?”-inputs met de waarden die we hebben opgegeven.

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

Herhaal de bovenstaande query door meerdere records toe te voegen.

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)

Het is tijd om het record te verifiëren. Hiervoor draaien we een eenvoudige SQL-query die rijen teruggeeft waar het subject Data Science is.

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

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

De opdracht DISTINCT subject wordt gebruikt om unieke waarden in de subject-kolom weer te geven. In ons geval zijn dat Math, Stat en Data Science.

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

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

Om alle wijzigingen op te slaan, gebruiken we de functie commit(). Zonder commit gaan de gegevens verloren na het herstarten van de machine.

conn.commit()

SQL met Pandas

In dit deel leren we hoe we de data uit de SQLite-database halen en met één regel code omzetten naar een Pandas-dataframe. read_sql biedt meer dan alleen het uitvoeren van SQL-query's. We kunnen het gebruiken om indexkolommen in te stellen, datum en tijd te parsen, waarden toe te voegen en kolomnamen uit te filteren. Leer meer over data importeren in Python met een korte cursus op DataCamp.

read_sql vereist twee argumenten: een SQL-query en een verbinding met de SQLite-engine. De output bevat de bovenste vijf rijen uit de tabel airport waar PercentOfBaseline groter is dan 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

Data-analyse op relationele databases is makkelijker geworden dankzij de integratie met Pandas. We kunnen deze data ook gebruiken om waarden te voorspellen en complexe statistische analyses uit te voeren.

De functie plot wordt gebruikt om de histogramvisualisatie van de kolom PercentOfBaseline te maken.

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

Op vergelijkbare wijze kunnen we de waarden beperken tot de top 20 en een lijndiagram van de tijdreeks tonen.

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");

Tot slot sluiten we de verbinding om resources vrij te geven. De meeste pakketten doen dit automatisch, maar het verdient de voorkeur om de verbindingen te sluiten nadat je de wijzigingen hebt afgerond.

conn.close()

R-tutorial

We gaan alle taken uit de Python-tutorial repliceren met R. De tutorial omvat het maken van verbindingen, tabellen schrijven, rijen toevoegen, query's uitvoeren en data-analyse met dplyr.

Aan de slag

Het pakket DBI wordt gebruikt om verbinding te maken met de populairste databases zoals MariaDB, Postgres, Duckdb en SQLite. Installeer bijvoorbeeld het pakket RMySQL en maak een database door een gebruikersnaam, wachtwoord, databasenaam en hostadres op te geven.

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

In deze tutorial gaan we een SQLite-database maken door een naam en de SQLite-functie op te geven.

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

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

Database maken

Door de bibliotheek tidyverse te importeren, krijgen we toegang tot dplyr, ggplot en de standaarddatasets.

De functie dbWriteTable neemt een data.frame en voegt die toe aan de SQL-tabel. De functie neemt drie argumenten: verbinding met SQLite, naam van de tabel en data frame. Met dbReadTable kunnen we de hele tabel bekijken. Om de bovenste 6 rijen te bekijken, hebben we head gebruikt.

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 laat ons elke SQLite-query uitvoeren, dus we gebruiken het om een tabel idcard te maken.

Om de namen van de tabellen in de database weer te geven, gebruiken we dbListTables.

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

>>> 'cars''idcard'

Laten we één rij toevoegen aan de tabel idcard en dbGetQuery gebruiken om resultaten weer te geven.

Opmerking: dbGetQuery voert een query uit en retourneert de records, terwijl dbExecute een SQL-query uitvoert maar geen records retourneert.

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


id    name
1    love

We voegen nu nog twee rijen toe en tonen de resultaten met 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 laat ons eenvoudig een tabel aanmaken. Het vereist drie argumenten: verbinding, naam van de tabel en ofwel een character vector of een data.frame. De character vector bestaat uit namen (kolomnamen) en waarden (types). In ons geval geven we een standaard data.frame population op om de initiële structuur te maken.

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

country    year    population

Vervolgens gebruiken we dbAppendTable om waarden toe te voegen aan de tabel 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

Query's uitvoeren

We gebruiken dbGetQuery voor al onze data-analysetaken. Laten we een eenvoudige query draaien en daarna meer leren over andere functies.

dbGetQuery(conn,"SELECT * FROM idcard")

id    name
1    love
2    Kill
3    Game

Je kunt ook een complexe SQL-query draaien om op horsepower te filteren en een beperkt aantal rijen en kolommen weer te geven.

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

Om tabellen te verwijderen, gebruik je dbRemoveTable. Zoals we nu zien, hebben we de tabel idcard succesvol verwijderd.

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

Om meer over tabellen te begrijpen, gebruiken we dbListFields, dat de kolomnamen in een bepaalde tabel weergeeft.

dbListFields(conn, "cars")


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

SQL met dplyr

In deze sectie gebruiken we dplyr om tabellen te lezen en vervolgens query's uit te voeren met filter, select en collect. Als je geen SQL-syntaxis wilt leren en alle taken in puur R wilt uitvoeren, dan is deze methode iets voor jou. We hebben de tabel cars opgehaald, gefilterd op gears en mpg en vervolgens drie kolommen geselecteerd, zoals hieronder weergegeven.

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
...   ...   ...

We kunnen het gefilterde dataframe gebruiken om met ggplot een boxplot te maken.

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

Of we kunnen een facet point-plot weergeven, opgesplitst naar het aantal gears.

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

Conclusie

In deze tutorial hebben we geleerd hoe belangrijk het is om SQL-query's uit te voeren met Python en R, databases te maken, tabellen toe te voegen en data-analyse uit te voeren met SQL-query's. We hebben ook geleerd hoe Pandas en dplyr ons helpen om met één regel code query's te draaien.

SQL is een onmisbare vaardigheid voor alle tech-gerelateerde banen. Als je je carrière als data-analist begint, raden we aan om binnen twee maanden de career track Data Analyst with SQL Server af te ronden. Deze career track leert je alles over SQL-query's, servers en het beheren van resources.

Je kunt alle scripts uit deze tutorial gratis draaien:

• Het project SQL met Python is beschikbaar in deze DataLab-werkmap.
• Het project SQL met R is beschikbaar in deze DataLab-werkmap.