Gérer les bases de données PostgreSQL en Python avec psycopg2

Création d'un tableau dans PostgreSQL

Il est temps de créer votre premier tableau dans la base de données datacamp_courses. Nous souhaitons créer un tableau contenant des informations sur certains cours du catalogue de cours de DataCamp. Le tableau a le schéma suivant : 

La spécification nous donne pas mal d'informations sur les colonnes du tableau. La clé primaire du tableau doit être course_id et son type de données doit être un nombre entier. Une clé primaire est une contrainte qui impose que les valeurs des colonnes soient non nulles et uniques. Il vous permet d'identifier de manière unique une instance spécifique ou un ensemble d'instances présentes dans le tableau. 

Les autres colonnes fournissent des informations sur le nom du cours, le nom de l'enseignement et le sujet du cours.

Avant de créer le tableau, il est important d'expliquer le fonctionnement de l'instance de connexion que vous venez de créer. Par essence, la connexion encapsule une session de base de données et vous permet d'exécuter des commandes et des requêtes SQL, telles que SELECT, INSERT, CREATE, UPDATE, ou DELETE, à l'aide de la méthode cursor(), et de rendre les modifications persistantes à l'aide de la méthode commit() .

Une fois l'instance de curseur créée, vous pouvez envoyer des commandes à la base de données à l'aide de la méthode execute() et récupérer des données d'un tableau à l'aide de la méthode fetchone(), fetchmany(), ou fetchall().

Enfin, il est important de fermer le curseur et la connexion à la base de données lorsque vous avez terminé vos opérations. Dans le cas contraire, ils continueront à détenir des ressources côté serveur. Pour ce faire, vous pouvez utiliser laméthode close() .

Vous trouverez ci-dessous le code permettant de créer le tableau datacamp_courses:

# Open a cursor to perform database operations
cur = conn.cursor()
# Execute a command: create datacamp_courses table
cur.execute("""CREATE TABLE datacamp_courses(
            course_id SERIAL PRIMARY KEY,
            course_name VARCHAR (50) UNIQUE NOT NULL,
            course_instructor VARCHAR (100) NOT NULL,
            topic VARCHAR (20) NOT NULL);
            """)
# Make the changes to the database persistent
conn.commit()
# Close cursor and communication with the database
cur.close()
conn.close()

Il s'agit d'un exemple très basique de création de tableaux sur PostgreSQL, mais les choses peuvent devenir beaucoup plus complexes. Si vous souhaitez en savoir plus sur la création d'une base de données PostgreSQL et explorer la structure, les types de données et la normalisation des bases de données, consultez notre rubrique Création d'une base de données PostgreSQL cours sur la création d'une base de données PostgreSQL.

Exécuter des requêtes PostgreSQL de base en Python

Le tableau datacamp_courses est prêt ; il est maintenant temps d'utiliser SQL pour effectuer quelques requêtes de base !

INSERT 

Vous avez peut-être remarqué que le tableau ne contient aucune valeur jusqu'à présent. Pour créer des enregistrements dans le tableau datacamp_courses, nous avons besoin de la commande INSERT.

cur = conn.cursor()

cur.execute("INSERT INTO datacamp_courses(course_name, course_instructor, topic) VALUES('Introduction to SQL','Izzy Weber','Julia')");

cur.execute("INSERT INTO datacamp_courses(course_name, course_instructor, topic) VALUES('Analyzing Survey Data in Python','EbunOluwa Andrew','Python')");

cur.execute("INSERT INTO datacamp_courses(course_name, course_instructor, topic) VALUES('Introduction to ChatGPT','James Chapman','Theory')");

cur.execute("INSERT INTO datacamp_courses(course_name, course_instructor, topic) VALUES('Introduction to Statistics in R','Maggie Matsui','R')");

cur.execute("INSERT INTO datacamp_courses(course_name, course_instructor, topic) VALUES('Hypothesis Testing in Python','James Chapman','Python')");

conn.commit()
cur.close()
conn.close()

Le tableau qui en résulte se présente comme suit :

SELECTIONNER 

La lecture de données dans les bases de données SQL est probablement quelque chose que vous ferez souvent au cours de votre parcours en science des données. C'est ce qu'on appelle généralement une requête SELECT. Pour l'instant, voyons comment le tableau datacamp_courses tient le coup.

Nous appellerons l'instruction classique SELECT * FROM database_name pour lire toutes les données disponibles dans le tableau. Ensuite, nous utiliserons la méthode fetchall() pour récupérer toutes les lignes disponibles. Notez que PostgreSQL crée automatiquement un index numérique pour la colonne course_id .

cur = conn.cursor()
cur.execute('SELECT * FROM datacamp_courses;')
rows = cur.fetchall()
conn.commit()
conn.close()
for row in rows:
    print(row)

(1, 'Introduction to SQL', 'Izzy Weber', 'Julia')
(2, 'Analyzing Survey Data in Python', 'EbunOluwa Andrew', 'Python')
(3, 'Introduction to ChatGPT', 'James Chapman', 'Theory')
(4, 'Introduction to Statistics in R', 'Maggie Matsui', 'R')
(5, 'Hypothesis Testing in Python', 'James Chapman', 'Python')

MISE À JOUR 

Les données sont souvent entachées d'erreurs. Vous avez peut-être remarqué dans la section précédente que le sujet associé au cours "Introduction à SQL"est Julia. Après avoir vérifié les informations sur le cours, nous avons découvert l'erreur. Nous devons le modifier et écrire "SQL" à la place. Pour ce faire, vous pouvez utiliser l'instruction UPDATE, comme suit :

cur = conn.cursor()
cur.execute("UPDATE datacamp_courses SET topic = 'SQL' WHERE course_name = 'Introduction to SQL';")
conn.commit()
conn.close()

DELETE 

Enfin, il se peut que vous souhaitiez supprimer l'un des enregistrements de votre tableau. Par exemple, supprimons le cours Introduction aux statistiques en R :

cur = conn.cursor()
cur.execute("""DELETE from datacamp_courses WHERE course_name = 'Introduction to Statistics in R'""");
conn.commit()
cur.close()

Requêtes avancées PostgreSQL en Python

Dans la section précédente, nous avons examiné les requêtes SQL les plus élémentaires. Mais il y a beaucoup à apprendre. Voyons quelques requêtes plus avancées.

ORDER BY

Supposons que vous souhaitiez trier votre base de données en fonction du nom de l'enseignant. Vous pouvez utiliser la déclaration ORDER BY:

cur = conn.cursor()

cur.execute('SELECT * FROM datacamp_courses ORDER BY course_instructor')
rows = cur.fetchall()
for row in rows:
    print(row)

(2, 'Analyzing Survey Data in Python', 'EbunOluwa Andrew', 'Python')
(1, 'Introduction to SQL', 'Izzy Weber', 'SQL')
(3, 'Introduction to ChatGPT', 'James Chapman', 'Theory')
(4, 'Hypothesis Testing in Python', 'James Chapman', 'Python')

GROUPE PAR

Il se peut que vous souhaitiez effectuer des fonctions d'agrégation au sein de différents groupes de données. Par exemple, vous pouvez être intéressé par le calcul du nombre de cours dispensés par les différents formateurs. Vous pouvez effectuer ce type d'opération à l'aide de la fonction GROUP BY.

cur = conn.cursor()
cur.execute('SELECT course_instructor, COUNT(*) FROM datacamp_courses GROUP BY course_instructor')
rows = cur.fetchall()
for row in rows:
    print(row)

('James Chapman', 2)
('Izzy Weber', 1)
('EbunOluwa Andrew', 1)

JOIN

Jusqu'à présent, nous n'avons travaillé qu'avec le tableau datacamp_course. Cependant, vous ne commencez à exploiter tout le potentiel des bases de données relationnelles, comme PostgreSQL, que lorsque vous travaillez avec plusieurs tableaux à la fois. 

L'outil magique pour combiner plusieurs tableaux est l'opération JOIN. Imaginez que nous ayons un deuxième tableau dans notre base de données, appelé programming_languages, qui contient des informations de base sur les principaux langages de programmation pour la science des données, notamment le nom, la position dans l'indice TIOBE et le nombre de cours sur le langage de programmation dans DataCamp. Le tableau se présente comme suit :

Imaginez que vous souhaitiez fusionner les deux tableaux pour obtenir les informations relatives aux cours, ainsi que la position de la langue dans l'index TIOBE. Nous utiliserons un INNER JOIN pour obtenir uniquement les informations sur les langages de programmation qui figurent dans le tableau datacamp_course. 

cur = conn.cursor()
cur.execute("""SELECT course_name, course_instructor, topic, tiobe_ranking
FROM datacamp_courses
INNER JOIN programming_languages
ON datacamp_courses.topic = programming_languages.language_name""")
rows = cur.fetchall()
for row in rows:
    print(row)

('Introduction to SQL', 'Izzy Weber', 'SQL', 8)
('Analyzing Survey Data in Python', 'EbunOluwa Andrew', 'Python', 1)
('Hypothesis Testing in Python', 'James Chapman', 'Python', 1)

Ce n'est qu'un exemple, mais le potentiel des jointures SQL est infini, ce qui en fait un sujet à connaître absolument. Notre cours, tutoratet antisèche vous aideront à démarrer.

Démarrer avec SQLAlchemy

Psycopg2 est un excellent outil pour se connecter aux bases de données PostgreSQL. Mais qu'en est-il si vous devez travailler avec des bases de données hébergées dans différentes bases de données SQL, telles qu'Oracle et MySQL ? Dans ce cas, il est préférable d'utiliser un outil capable de se connecter à différents types de bases de données SQL. Pour ce faire, SQLAlchemy est un excellent candidat. 

En termes simples, SQLAlchemy permet aux utilisateurs de connecter des bases de données à l'aide du langage Python, d'exécuter des requêtes SQL à l'aide de la programmation basée sur les objets et de rationaliser le flux de travail. 

Bien que la syntaxe varie par rapport à psycopg2, vous pouvez faire à peu près la même chose. Vous trouverez ci-dessous un exemple de création d'un moteur pour se connecter à nos ensembles de données datacamp_courses, et lire toutes les données disponibles dans le tableau datacap_course.

import sqlalchemy as db


engine = db.create_engine("postgresql://datacamp@localhost:5432/datacamp_courses")
conn = engine.connect() 
output = conn.execute("SELECT * FROM datacamp_courses")
print(output.fetchall())
conn.close()

[(3, 'Introduction to ChatGPT', 'James Chapman', 'Theory'),
 (5, 'Hypothesis Testing in Python', 'James Chapman', 'Python'),
 (2, 'Analyzing Survey Data in Python', 'EbunOluwa Andrew', 'Python'),
 (1, 'Introduction to SQL', 'Izzy Weber', 'SQL')]

SQLAlchemy est un autre outil qui mérite d'être appris. Pour avoir un aperçu du fonctionnement de l'outil, consultez notre tutoriel SQLALchemy.  

Utiliser PostgreSQL dans DataCamp DataLab

Chez DataCamp, nous travaillons dur pour rendre la science des données plus facile et accessible à tous. C'est ce qui a motivé la création de DataCamp DataLab, un carnet de notes basé sur le cloud et spécialement conçu pour la science des données. Vous pouvez considérer DataCamp DataLab comme un mélange de Jupyter Notebook et de Google Docs, qui permet aux utilisateurs d'écrire du code, d'analyser des données individuellement ou collectivement, et de partager des aperçus de données.

La connexion de DataLab aux bases de données PostgreSQL, ainsi qu'à d'autres bases de données SQL, est extrêmement facile. DataLab vous permet de vous connecter aux sources de données en toute sécurité. Parallèlement, vous pouvez poursuivre de manière transparente votre analyse de données avec Python ou R. Il vous suffit de créer une intégration, en suivant un processus simple et sans code, où vous devrez saisir vos identifiants de connexion.

Enfin, si vous souhaitez essayer l'intégration mais que vous n'avez pas de base de données PostgreSQL à laquelle vous connecter, vous pouvez utiliser l'une de nos bases de données PostgreSQL d'exemple !

Vous êtes curieux de connaître DataCamp DataLab ? Consultez cet article pour en savoir plus sur cet ordinateur portable de nouvelle génération pour la science des données.

Conclusion

Beau travail ! Dans ce tutoriel, vous vous êtes familiarisé avec PostgreSQL et avez appris à gérer des bases de données sans quitter Python, en utilisant le package psycopg2. Mais ce n'est que le début du voyage. SQL est l'un des langages de programmation les plus utilisés en science des données. Il est également assez facile à apprendre, vous n'avez donc aucune excuse.  

Heureusement, DataCamp vous couvre. Voici quelques ressources qui pourraient vous aider à maîtriser PostgreSQL et SQL en général :

• Tutoriel d'introduction à PostgreSQL pour les débutants
• Cours sur la création de bases de données PostgreSQL
• Améliorer les performances des requêtes dans PostgreSQL Cours
• Cours sur la gestion des bases de données en SQL
• Fonctions pour manipuler les données dans le cours PostgreSQL