Introduction à l’analyse de données

Séance 9 - introduction à SQL

Emmanuel Coquery

http://emmanuel.coquery.pages.univ-lyon1.fr/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