Introduction à l’analyse de données

Séance 9 - introduction à SQL

Emmanuel Coquery

https://perso.liris.cnrs.fr/emmanuel.coquery/home/enseignement/ue8/

Vu précédement

• Données tabulaires
• Pandas, Dataframes, Series, indexes
• Filtrer les données (colonnes, lignes)
• Combiner des données, (concat, jointure, aggrégation)
• Figures (2D, 3D)
• Indexation à plusieurs niveaux, cubes

Bases de données

• s’abstraire des fichiers
• permettre des accès simultanés et distants
• permettre une bonne structuration des données
• bases de données relationnelles: données tabulaires

Connexion et utilisation dans Jupyter

• Utiliser le serveur L3 qui intègre JupySQL
• Créer un fichier connections.ini (à adapter):

[bd-pedago]
drivername = postgresql
username = p1234567
password = à recopier
host = bd-pedago.univ-lyon1.fr
port = 5432
database = p1234567

password à retrouver sur
https://tomuss.univ-lyon1.fr

Connexion et utilisation dans Jupyter (2)

Première cellule:

%load_ext sql
%config SqlMagic.dsn_filename = "connections.ini"
%sql --section bd-pedago
%sql --connections
import pandas

Tables

• Analogues à des DataFrames
• Sont systématiquement persistées (sauvegardées sur disque)
• Pas d’index à la pandas
  • mais certains attributs peuvent constituer une clé (~ index)
  • les indexes sont une notion différente en base de données

Tables et DataFrames dans Jupyter

Créer une table à partir d’une dataframe df:

%sql --persist df

Exercice: créer une table avec le contenu du fichier Donnees_M2_RD.xlsx

Remarque: cette méthode créer une colonne index

Premières requêtes

Récupérer le contenu de la table df complète:

SELECT *
FROM df

ou seulement les colonnes indiquées:

SELECT "Subject", "RT"
FROM df

à faire dans une cellule commençant par %%sql

Filtrer les lignes

On exprime des conditions de filtrage avec le mot-clé WHERE:

SELECT "Subject", "RT"
FROM df
WHERE "Space" = 'E'
  AND "Name_A" <> 0
  AND "Name_B" = 1

Attention aux guillemets:

• ‘chaîne de caractères’
• “nom_de_colonne”

Nouvelles colonnes en résultat

• On peut exprimer des calculs dans le SELECT
• Le mot clé AS permet de donner un nom à la colonne résultat

SELECT "index", "Dist_A" - "Dist_B" as "DiffDist"
FROM df

Group By

• On peut faire des group by similaires à Pandas via les mots clés GROUP BY
• On indique quelle aggrégation faire dans le SELECT
• Il faut gérer à la main des colonnes du GROUP BY dans le SELECT

SELECT "Subject", "Space", avg("RT") as "moy_RT"
FROM df
WHERE "Mode" = 'Dic'
GROUP BY "Subject", "Space"

Jointures

• Mettre plusieurs tables dans le FROM
  • optionnellement en leur donnant un nom alternatif dans la requête
• Indiquer les conditions de combinaison dans le WHERE
• Similaire à merge en pandas

SELECT "Dist_A"
FROM df, dist_i_m dm
WHERE df."Dist_A" = dm.index
  AND df."Space" = 'E'

Importer le résultat d’une requête dans une df

Affecter la df à une requête commençant par %sql (sur une ligne)

df2 = %sql SELECT "Subject", "Space" FROM df WHERE "Name_A" = 0

Exercices

Reprendre les exercices pandas en SQL