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 :
- Que fait la requête
DEMO_QUERY
? - Quelle est la différence entre les fonctions
demo_sync()
etdemo_async()
? Quand utiliser l’une ou l’autre ? - Que se passe-t’il si on remplace
fetchone()
parfetchall()
dans les fonctions précédentes ? Justifier. - 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
- À quoi sert la clause
RETURNING
de requêten de la fonctioninsert_student()
? - Exécuter
insert_student(353, "O_Reilly", 1.0, 100)
,insert_student(353, "O_Reilly", 1.0)
etinsert_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 ? - Il y a un problème de sécurité majeur dans la fonction
insert_student()
. Comment s’appelle-t’il ? - 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. - 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
- Décrire en une phrase simple (pas de laïus) ce que calcule la requête précédente.
- Faire ce qui est demandé avec la ligne de commande
psql
en utilisant la commandeCOPY
, voir la doc. - 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 :
- https://pandas.pydata.org/docs/reference/api/pandas.read_sql.html
- https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_csv.html
Le code doit devenir beaucoup plus concis car Pandas sait faire la majeure partie du travail.