Miage-BDAV

Page principale de l'EC "Bases de données avancées" en M1 Miage UNC

TP driver PostgreSQL en Python

Prise en main de l’API psycopg3

On donne le code de départ dans le fichier demo.py qui utilise le driver psycopg3. On utilisera la base Stanford du TP précédent.

Télécharger le fichier de départ et adapter la chaîne CONNECTION_URL à votre instance Postgres, voir la doc. Ensuite installer les dépandances Python du fichier requirements.txt (lire les commentaires) et enfin exécuter demo.py.

Répondre ensuite aux questions suivantes :

  1. Que fait la requête DEMO_QUERY ?
  2. Quelle est la différence entre les fonctions demo_sync() et demo_async() ? Quand utiliser l’une ou l’autre ?
  3. Que se passe-t’il si on remplace fetchone() par fetchall() dans les fonctions précédentes ? Justifier.
  4. A quels moment sont créées les connections à la base de données ? Avec le programme de départ, combien sont créées ?

Insertion de données

  1. À quoi sert la clause RETURNING de requêten de la fonction insert_student() ?
  2. Exécuter insert_student(353, "O_Reilly", 1.0, 100), insert_student(353, "O_Reilly", 1.0) et insert_student(353, "O'Reilly", 1.0, 100). Vérifier quels tuples sont insérés en base. Quelles sont les erreurs et leurs causes pour ceux qui ont échoués ?
  3. Il y a un problème de sécurité majeur dans la fonction insert_student(). Comment s’appelle-t’il ?
  4. Corriger l’appel à conn.execute() en passant correctement les paramètres à la requête exécutée. Vérifier que les trois appels de la question 1 fonctionnent bien.
  5. Est-il normal qu’on puisse ajouter plusieurs fois un étudiant avec le même identifiant ? Que faut-il faire dans le code Python pour corriger cela ?

Lecture des données

La fonction all_students() renvoie une liste de tuples. Modifier la connection pour que le résultat soit retourné sous la forme d’une liste de dictionnaires où chaque tuple est de la forme {'GPA': ..., 'sID': ..., 'sName': ..., 'sizeHS': ...}. Utiliser pour cela la dict_row factory lors de l’établissement de la connection.

Refactorisation

On pourra supprimer la fonction demo_async() qu’on ne considèrera plus dans la suite.

Modifier les fonctions qui utilisent une connection à la base pour que la connection soit créée une fois pour toutes dans le programme principal et passée en paramètre des fonctions, on aura par exemple demo_sync(conn).

Ensuite, transformer les fonctions pour qu’elles ne fassent pas des affichages mais renvoient un résultat (ou une liste de résultats selon le cas).

Application

On souhaite extraire des données au format CSV. On donne la requête SQL suivante.

select sid, sname, major, count(cname) as nb
from student left join apply using (sid)
group by sid, sname, major
order by sid, major;

On souhaite faire un outil ligne de commande en Python qui se connecte à la base, exécute cette requête et entregistre le résultat dans un fichier au format CSV comme suit. Ce résultat est doit être utilisable avec un tableur.

sid,sname,major,nb
123,Amy,CS,2
123,Amy,EE,2
234,Bob,biology,1
345,Craig,bioengineering,2
345,Craig,CS,1
345,Craig,EE,1
456,Doris,,0
543,Craig,CS,1
567,Edward,,0
654,Amy,,0
678,Fay,history,1
765,Jay,history,2
765,Jay,psychology,1
789,Gary,,0
876,Irene,biology,1
876,Irene,CS,1
876,Irene,marine biology,1
987,Helen,CS,2
  1. Décrire en une phrase simple (pas de laïus) ce que calcule la requête précédente.
  2. Faire ce qui est demandé avec la ligne de commande psql en utilisant la commande COPY, voir la doc.
  3. Réaliser l’outil demandé en utilisant la lib csv standard de Python et la classe DictWriter.

Éviter de mettre la requête en dur dans le code Python, la mettre plutôt dans un fichier .sql lu depuis Python.

Deuxième version

Reprendre l’application de départ mais cette fois avec la bilbiothèque https://pandas.pydata.org/, en utilisant :

Le code doit devenir beaucoup plus concis car Pandas sait faire la majeure partie du travail.