Notebook de la séance 9

Connexion à la base de donnée et chargement de Pandas

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

Connecting to 'bd-pedago'

Chargement des données excel en base en passant par une dataframe df

df = pandas.read_excel("Donnees_M2_RD.xlsx")

%sql --persist-replace df

Running query in 'bd-pedago'

Success! Persisted df to the database.

Récupérer les données de toute la table

%%sql
SELECT *
FROM df

Running query in 'bd-pedago'

9594 rows affected.

Si on veut seulement 2 colonnes

%%sql
SELECT "Subject", "RT"
FROM df

Running query in 'bd-pedago'

9594 rows affected.

En filtrant des lignes

%%sql
SELECT *
FROM df
WHERE "Space" = 'E'
  AND "Name_A" <> 0
  AND "Name_B" = 1

Running query in 'bd-pedago'

720 rows affected.

En filtrant des lignes et des colonnes

%%sql
-- un commentaire
SELECT "Subject", "RT"
FROM df
WHERE "Space" = 'E'
  AND "Name_A" <> 0
  AND "Name_B" = 1

Running query in 'bd-pedago'

720 rows affected.

En calculant une nouvelle colonne dans le SELECT

%%sql
SELECT "Subject", "Dist_A" - "Dist_B" as "DiffDist"
FROM df

Running query in 'bd-pedago'

9594 rows affected.

On peut remarquer que la table df reste inchangée:

%%sql
SELECT *
FROM df

Running query in 'bd-pedago'

9594 rows affected.

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

Running query in 'bd-pedago'

48 rows affected.