Cours
Créer des bases de données PostgreSQL
4 h
22.5K
Savoir interroger des systèmes de bases de données relationnelles est une compétence incontournable pour tout data scientist. SQL, pour Structured Query Language, permet de le faire de manière très efficace. SQL vous aide non seulement à poser des questions pertinentes aux données, mais aussi à les manipuler de multiples façons. Sans bases de données, pratiquement aucune application réelle n’est possible. La maîtrise des bases de données et leur manipulation font donc partie intégrante de la boîte à outils d’un data scientist.
Astuce : SQL se prononce aussi SE-QU-EL. Historiquement, le nom initial de SQL était Simple English Query Language.
En général, les bases de données relationnelles ressemblent à ceci :
Les relations sont aussi appelées tables. Il existe plusieurs manières de représenter les bases de données ; celle-ci est la plus courante.
Ce tutoriel présente les quatre opérations les plus utilisées en SQL : Create, Read, Update et Delete. Ensemble, elles sont souvent désignées par l’acronyme CRUD. Dans toute application impliquant une interaction utilisateur, ces quatre opérations sont toujours présentes.
Vous utiliserez PostgreSQL comme système de gestion de bases de données relationnelles. PostgreSQL est léger et gratuit. Dans ce tutoriel, vous allez :
- Installer et démarrer avec PostgreSQL
- Vous connecter à une base PostgreSQL
- Créer, lire, mettre à jour et supprimer des tables dans cette base
- Exécuter du SQL dans Jupyter Notebook
- Exécuter du SQL en Python
Commençons.
Prise en main de PostgreSQL
PostgreSQL est un SGBDR open source et léger, largement adopté par l’industrie. Pour en savoir plus, consultez le site officiel.
Pour écrire et exécuter des requêtes dans PostgreSQL, vous devez l’installer sur votre machine. L’installation est très simple. Ces deux courtes vidéos montrent comment télécharger et installer PostgreSQL sur un Windows 7 32 bits :
Remarque : pendant l’installation, notez bien le mot de passe et le numéro de port renseignés.
Une fois PostgreSQL installé, ouvrez pgAdmin. pgAdmin, fourni avec l’installation, est un utilitaire pratique qui permet d’effectuer les tâches courantes sur la base via une interface graphique conviviale. L’interface de pgAdmin ressemble à ceci :
À l’ouverture, vous verrez un serveur nommé « PostgreSQL 9.4 (localhost:5432) » listé dans l’interface :
Remarque : votre version (et donc le port 5432) peut différer de l’exemple ci-dessus.
Connectez-vous au serveur en saisissant le mot de passe défini lors de l’installation. Pour référence : https://www.loom.com/share/71708f6a99ee483a977991e9e8a25808.
Une fois connecté avec succès au serveur local, vous obtiendrez une interface similaire à la suivante :
Votre première tâche sera de créer une base de données ; pour cela, faites un clic droit sur l’onglet Databases puis sélectionnez New Database dans le menu déroulant. Créons une base nommée DataCamp_Courses. Une fois créée, vous pouvez poursuivre les sections suivantes.
Opérations CRUD dans PostgreSQL
Créer une table selon un cahier des charges donné :
Pour travailler dans une base, il vous faut une table. Créons donc une table simple (aussi appelée relation) nommée datacamp_courses avec la spécification (schéma) suivante :
La spécification fournit plusieurs informations sur les colonnes de la table :
- La clé primaire de la table doit être course_id (remarquez que seule celle-ci est en gras) et son type doit être un entier. Une clé primaire est une contrainte qui impose des valeurs non nulles et uniques. Elle permet d’identifier de manière unique une occurrence (ou un ensemble d’occurrences) de la table.
- Le reste des informations du schéma se comprend désormais facilement.
Pour créer une table, faites un clic droit sur la base nouvellement créée DataCamp_Courses et sélectionnez CREATE Script. Vous devriez obtenir quelque chose comme ceci :
Exécutez maintenant la requête suivante :
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
);
Pour exécuter la requête, sélectionnez-la puis cliquez sur le bouton d’exécution dans la barre de menu :
Le résultat attendu :
La structure générale d’une requête de création de table dans PostgreSQL ressemble à ceci :
CREATE TABLE table_name (
column_name TYPE column_constraint,
table_constraint table_constraint
)
Nous n’avons pas défini de table_constraint lors de la création. Vous pouvez l’ignorer pour l’instant. Tout est assez lisible, à l’exception du mot-clé SERIAL. SERIAL dans PostgreSQL permet de créer une colonne auto-incrémentée. Par défaut, elle génère des entiers. SERIAL vous évite d’avoir à mémoriser la dernière clé primaire insérée/mise à jour ; c’est une bonne pratique d’utiliser des auto-incréments pour les clés primaires. Pour en savoir plus sur SERIAL, consultez cette page.
Insérer quelques enregistrements dans la table nouvellement créée
Dans cette étape, vous allez insérer quelques lignes dans la table. Vos enregistrements doivent contenir :
- Un nom de cours
- Le nom de l’instructeur du cours
- Le sujet du cours
Les valeurs de la colonne course_id seront gérées automatiquement par PostgreSQL. La structure générale d’une requête d’insertion dans PostgreSQL ressemble à :
INSERT INTO table(column1, column2, …)
VALUES
(value1, value2, …);
Insérons quelques lignes :
INSERT INTO datacamp_courses(course_name, course_instructor, topic)
VALUES('Deep Learning in Python','Dan Becker','Python');
INSERT INTO datacamp_courses(course_name, course_instructor, topic)
VALUES('Joining Data in PostgreSQL','Chester Ismay','SQL');
Remarquez que vous n’avez pas explicitement renseigné les clés primaires. Vous allez en voir l’effet dans un instant.
À l’exécution de ces deux requêtes, vous devriez obtenir le message suivant si l’insertion a réussi :
Query returned successfully: one row affected, 11 ms execution time.
Lire/afficher les données de la table
Vous ferez probablement cela très souvent dans votre parcours en data science. Pour l’instant, voyons l’état de la table datacamp_courses.
On parle généralement de requête SELECT. Sa structure générique ressemble à :
SELECT
column_1,
column_2,
...
FROM
table_name;
Sélectionnons toutes les colonnes de la table datacamp_courses
SELECT * FROM datacamp_courses;
Et vous obtenez :
Notez maintenant les clés primaires. Si vous voulez uniquement voir les noms des cours, vous pouvez faire :
SELECT course_name from datacamp_courses;
Et vous obtenez :
Vous pouvez indiquer autant de colonnes que vous souhaitez afficher, à condition qu’elles existent dans la table. Si vous exécutez select course_name, number_particpants from datacamp_courses;, vous obtiendrez une erreur car la colonne number_particpants n’existe pas dans la table. Voyons maintenant comment mettre à jour un enregistrement précis.
Mettre à jour un enregistrement dans la table
La structure générale d’une requête de mise à jour en SQL est la suivante :
UPDATE table
SET column1 = value1,
column2 = value2 ,...
WHERE
condition;
Vous allez mettre à jour l’enregistrement où course_instructor = "Chester Ismay" et définir course_name sur "Joining Data in SQL". Vous vérifierez ensuite la mise à jour. La requête correspondante est :
UPDATE datacamp_courses SET course_name = 'Joining Data in SQL'
WHERE course_instructor = 'Chester Ismay';
Vérifions l’effet de votre requête UPDATE en exécutant un SELECT :
Votre requête a produit exactement l’effet attendu. Passons à la suppression d’un enregistrement.
Supprimer un enregistrement dans la table
La structure générale d’une requête de suppression en SQL ressemble à :
DELETE FROM table
WHERE condition;
Vous allez supprimer l’enregistrement où course_name = "Deep Learning in Python" puis vérifier la suppression. En suivant la structure, la requête suivante convient :
DELETE from datacamp_courses
WHERE course_name = 'Deep Learning in Python';
N’oubliez pas que les mots-clés ne sont pas sensibles à la casse en SQL, mais les données, elles, le sont. C’est pourquoi vous voyez un mélange de majuscules et de minuscules dans les requêtes.
Vérifions si l’enregistrement visé a bien été supprimé :
Oui, l’enregistrement a bien été supprimé.
Les structures génériques de requêtes mentionnées dans ce tutoriel sont tirées de PostgreSQL Tutorial.
Vous savez désormais exécuter les requêtes CRUD de base en SQL. Si vous utilisez souvent Jupyter Notebooks et souhaitez exécuter ces requêtes directement depuis Notebook, la section suivante vous montre comment faire.
SQL + Jupyter Notebooks
Pour exécuter des requêtes SQL depuis Jupyter Notebooks, commencez par installer le paquet ipython-sql.
S’il n’est pas installé, utilisez :
pip install ipython-sql
Une fois fait, chargez l’extension sql dans votre Notebook en exécutant :
%load_ext sql
Étape suivante : se connecter à une base PostgreSQL. Connectez-vous à la base que vous avez créée : DataCamp_Courses.
Pour vous connecter à une base déjà présente sur votre système, vous devez indiquer à Python son dialecte, autrement dit préciser qu’il s’agit d’une base PostgreSQL. Pour cela, vous aurez besoin de psycopg2, installable avec :
pip install psycopg2
Une fois psycopg installé, connectez-vous à la base avec :
%sql postgresql://postgres:postgres@localhost:5432/DataCamp_Courses
'Connected: postgres@DataCamp_Courses'Notez l’usage de %sql. C’est une magic command. Elle permet d’exécuter des instructions SQL depuis Jupyter Notebook. Ce qui suit %sql est une URL de connexion à la base où vous précisez :
- Le dialecte (postgres)
- Le nom d’utilisateur (postgres)
- Le mot de passe (postgres)
- L’adresse du serveur (localhost)
- Le numéro de port (5432)
- Le nom de la base (DaaCamp_Courses)
Vous pouvez maintenant reproduire depuis votre Notebook tout ce que vous avez fait dans pgAdmin. Commençons par créer la table _datacampcourses avec le même schéma.
Mais avant cela, il faut supprimer la table existante : SQL n’autorise pas deux tables portant le même nom. Supprimez une table avec :
%sql DROP table datacamp_courses;
* postgresql://postgres:***@localhost:5432/DataCamp_Courses
Done.
[] La table _datacampcourses est maintenant supprimée de PostgreSQL ; vous pouvez donc en créer une nouvelle portant ce nom.
%%sql
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
);
* postgresql://postgres:***@localhost:5432/DataCamp_Courses
Done.
[] Notez l’usage de
%sqlet%%sql. Pour exécuter une seule ligne de requête, utilisez%sql. Pour exécuter plusieurs requêtes d’un coup, utilisez%%sql.
Insérons quelques enregistrements :
%%sql
INSERT INTO datacamp_courses(course_name, course_instructor, topic)
VALUES('Deep Learning in Python','Dan Becker','Python');
INSERT INTO datacamp_courses(course_name, course_instructor, topic)
VALUES('Joining Data in PostgreSQL','Chester Ismay','SQL');
* postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected.
1 rows affected.
[] Affichez la table pour vérifier que les insertions se sont bien déroulées :
%%sql
select * from datacamp_courses;
* postgresql://postgres:***@localhost:5432/DataCamp_Courses
2 rows affected. | course_id | course_name | course_instructor | topic |
|---|---|---|---|
| 1 | Deep Learning in Python | Dan Becker | Python |
| 2 | Joining Data in PostgreSQL | Chester Ismay | SQL |
Poursuivons. À l’étape suivante, vous allez mettre à jour un enregistrement de la table :
%sql update datacamp_courses set course_name = 'Joining Data in SQL' where course_instructor = 'Chester Ismay';
* postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected.
[] Soyez attentif lorsque vous manipulez des chaînes en SQL. Contrairement aux langages de programmation classiques, les valeurs de type chaîne doivent être entourées de guillemets simples.
Vérifions maintenant l’effet de votre requête UPDATE :
%%sql
select * from datacamp_courses;
* postgresql://postgres:***@localhost:5432/DataCamp_Courses
2 rows affected. | course_id | course_name | course_instructor | topic |
|---|---|---|---|
| 1 | Deep Learning in Python | Dan Becker | Python |
| 2 | Joining Data in SQL | Chester Ismay | SQL |
Supprimons maintenant un enregistrement et vérifions :
%%sql
delete from datacamp_courses where course_name = 'Deep Learning in Python';
* postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected.
[] %%sql
select * from datacamp_courses;
* postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected. | course_id | course_name | course_instructor | topic |
|---|---|---|---|
| 2 | Joining Data in SQL | Chester Ismay | SQL |
À ce stade, vous avez une vision claire de l’exécution des opérations CRUD dans PostgreSQL et de leur réalisation via Jupyter Notebook. Si vous maîtrisez Python et souhaitez accéder à votre base depuis votre code Python, c’est également possible. La prochaine section y est consacrée.
Premiers pas avec SQLAlchemy et combinaison avec les magic commands SQL
Pour cette section, vous aurez besoin du paquet SQLAlchemy. Il est généralement inclus dans la distribution Anaconda. Vous pouvez aussi l’installer via pip. Une fois installé, importez-le :
import sqlalchemy
Pour interagir avec vos bases via SQLAlchemy, vous devez créer un engine pour le SGBDR où elles sont stockées. Dans votre cas, PostgreSQL. SQLAlchemy permet de créer l’engine en un seul appel à create_engine(), qui prend une URL de connexion à la base (comme précédemment).
from sqlalchemy import create_engine
engine = create_engine('postgresql://postgres:postgres@localhost:5432/DataCamp_Courses')
print(engine.table_names()) # Affiche les noms des tables présentes dans la base
['datacamp_courses']Vous voyez la table _datacampcourses, ce qui confirme la création réussie de l’engine. Exécutons une requête SELECT simple pour afficher les enregistrements de _datacampcourses et stockons le résultat dans un objet DataFrame de pandas.
Vous utiliserez la méthode read_sql() (fournie par pandas), qui prend une requête SQL en chaîne et un engine.
import pandas as pd
df = pd.read_sql('select * from datacamp_courses', engine)
df.head()
| course_id | course_name | course_instructor | topic | |
|---|---|---|---|---|
| 0 | 2 | Joining Data in SQL | Chester Ismay | SQL |
Vous pouvez aussi combiner la magic command %sql dans votre code Python.
df_new = %sql select * from datacamp_courses
df_new.DataFrame().head()
* postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected. | course_id | course_name | course_instructor | topic | |
|---|---|---|---|---|
| 0 | 2 | Joining Data in SQL | Chester Ismay | SQL |
Quel chemin parcouru !
Bravo, vous pouvez être fier de vous ! Dans ce tutoriel, vous avez non seulement démarré avec PostgreSQL, mais vous avez aussi vu plusieurs façons d’exécuter des requêtes SQL. Pour aller plus loin, regardez ce webinar de David Robinson (Chief Data Scientist chez DataCamp), où il montre comment il utilise SQL au quotidien. Consultez également notre tutoriel How to Install PostgreSQL on Windows and Mac OS X. Pour améliorer vos compétences SQL, voici d’excellentes ressources pour débuter :
Sujets