Utiliser PostgreSQL en Python

Les données sont au cœur de presque toutes les applications numériques auxquelles vous pouvez penser. Des applications mobiles aux jeux informatiques, en passant par les outils bancaires et les véhicules autonomes, tout repose sur des données. Où sont stockées toutes ces données ? La réponse se trouve dans les bases de données. 

Une base de données est une collection organisée de données structurées, généralement stockées électroniquement dans un système informatique. Malgré le développement récent de nouveaux types de bases de données (conçues pour répondre à l'augmentation du volume et de la variabilité des données), une quantité considérable de données dans le monde est encore stockée dans ce que l'on appelle des bases de données relationnelles. Les bases de données relationnelles stockent les données sous forme de tableaux prédéfinis avec des lignes et des colonnes reliées par une ou plusieurs relations. 

Être capable de manipuler et d'extraire des données de ces bases est une compétence essentielle dans l'industrie des données et une compétence de plus en plus demandée. La méthode standard pour créer et gérer des bases de données est le langage SQL (Structured Query Language). SQL est le fondement de certaines des bases de données relationnelles les plus populaires du marché, telles que PostgreSQL, Microsoft SQL Server, MySQL et SQLite.

Dans ce tutoriel, nous nous concentrerons sur les bases de données basées sur PostgreSQL et sur la manière dont vous pouvez les créer, vous y connecter et les gérer à l'aide de Python. Le tandem SQL-Python est l'une des compétences incontournables que vous devez maîtriser tout au long de votre parcours en science des données. Pour connecter Python à PostgreSQL, nous utiliserons le paquet psycopg2, l'adaptateur de base de données PostgreSQL le plus populaire pour Python. Prêt à relever le défi ? Commençons !

Comprendre PostgreSQL

PostgreSQL est une base de données relationnelle légère, libre et gratuite. En raison de son architecture éprouvée, de sa fiabilité, de l'intégrité de ses données et de son intégration harmonieuse avec d'autres langages de programmation populaires, tels que Python et R, PostgreSQL est extrêmement bien accepté par l'industrie, des entreprises de toutes tailles et de toutes régions l'utilisant. 

PostgreSQL utilise et étend le langage SQL, combiné à de nombreuses fonctionnalités qui permettent de stocker et de faire évoluer en toute sécurité les charges de travail les plus complexes. 

Pour commencer à utiliser PostgreSQL, vous devez d'abord l'installer sur votre ordinateur. Sur le site officiel de PostgreSQL, vous trouverez les nombreux paquets et installateurs prêts à l'emploi de PostgreSQL ainsi qu'une archive du code source disponible pour différentes plates-formes et différents cas d'utilisation.

Pour ce tutoriel, nous utiliserons Postgres App, une application macOS native simple qui inclut toutes les fonctionnalités nécessaires pour démarrer avec PostgreSQL, mais gardez à l'esprit que tous les autres paquets disponibles sont également valides, et que la syntaxe PostgreSQL pour exécuter des requêtes est la même pour tous les paquets.

Si vous souhaitez en savoir plus sur PostgreSQL, consultez notre Guide du débutant pour PostgreSQL et le grand nombre de cours sur SQL.

Comprendre psycopg2

Pour vous connecter à une base de données déjà créée dans votre système ou sur Internet, vous devrez indiquer à Python comment la détecter. En d'autres termes, vous devrez indiquer à Python que la base de données qui vous intéresse est une base PostgreSQL. 

En Python, vous avez le choix entre plusieurs options. Dans ce cas, nous utiliserons psycopg2, probablement l'adaptateur de base de données PostgreSQL le plus populaire pour Python. Psycopg2 nécessite quelques prérequis pour fonctionner correctement sur votre ordinateur. Une fois que vous les avez installés (lisez la documentation pour plus d'informations), vous pouvez installer psycopg2 comme n'importe quel autre paquet Python :

pip install psycopg2

Cependant, si vous souhaitez utiliser psycopg2 directement, vous pouvez également installer psycopg2-binary, une version autonome du paquet, ne nécessitant pas de compilateur ou de bibliothèques externes. C'est l'installation préférée des nouveaux utilisateurs.

pip install psycopg2-binary

Enfin, si vous utilisez Python dans un environnement Conda, vous devez installer psycopg2 à l'aide de la commande Anaconda installation:

conda install -c anaconda psycopg2

Maintenant que vous êtes prêts, créons votre première connexion à votre session PostgreSQL avec psycopg2 !

Connecter Python à PostgreSQL

Afin d'utiliser Python pour interagir avec une base de données PostgreSQL, nous devons établir une connexion. Cela se fait avec la fonction psycopg2 connect(), qui crée une nouvelle session de base de données et renvoie une nouvelle instance de connexion.

Pour ce tutoriel, nous nous connecterons à une base de données appelée "datacamp_courses" qui est hébergée localement. 

conn = psycopg2.connect(database = "datacamp_courses", 
                        user = "datacamp", 
                        host= 'localhost',
                        password = "postgresql_tutorial",
                        port = 5432)

Les paramètres de connexion de base requis sont les suivants :

• base de données. Le nom de la base de données.
• user. Nom d'utilisateur requis pour l'authentification.
• password. Mot de passe utilisé pour l'authentification.
• host. Adresse du serveur de la base de données (dans notre cas, la base de données est hébergée localement, mais il peut s'agir d'une adresse IP).
• port. Numéro du port de connexion (5432 par défaut s'il n'est pas fourni).

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 (notez que seule cette clé est en gras), 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 fonction 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 sur la façon de créer des 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 !

INSÉRER 

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 dans la science des données. C'est ce qu'on appelle généralement une requête SELECT. Pour l'instant, voyons comment se porte le tableau datacamp_courses.

Nous appellerons l'instruction classique SELECT * FROM nom_de_la_base_de_données 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')

UPDATE 

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. Mais 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 meilleurs 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)

Il ne s'agit là que d'un exemple, mais le potentiel des JOINS SQL est infini, ce qui en fait un sujet à connaître absolument. Notre cours, tutoratet antisèche sur les JOINTS 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 permettant de se connecter à nos tableaux datacamp_courses, et de 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 formidable 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 entre Jupyter Notebook et 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 à des sources de données de manière sécurisée, tout en poursuivant 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. 

L'image ci-dessous montre comment créer une intégration PostgreSQL. 

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 disponibles !

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