Comment exporter des données de PostgreSQL vers un fichier CSV

Vous avez besoin d'envoyer rapidement des données à un client ou de partager un rapport pour une analyse plus approfondie ? Un fichier CSV est une excellente option de partage ! Voyons comment utiliser ce format pour exporter des données depuis une base PostgreSQL.

Dans cet article, nous verrons d'abord ce qu'est le format de fichier CSV et pourquoi il est pratique pour exporter votre base de données PostgreSQL. Ensuite, nous exporterons quelques exemples de données à partir d'une base de données réelle en utilisant psql en ligne de commande et pgAdmin, une interface libre et gratuite pour les bases de données PostgreSQL.

Avant de commencer, assurez-vous d'avoir configuré votre base de données PostgreSQL. Si vous n'avez pas encore travaillé avec PostgreSQL, consultez notre cours SQL pour les débutants in PostgreSQL. Il comprend 130 défis de codage interactifs conçus pour vous aider à démarrer votre voyage avec PostgreSQL. Mais si vous n'avez besoin que d'une remise à niveau rapide, voici un moyen de mettre en pratique vos compétences en matière de PostgreSQL.

Que sont les fichiers CSV ?

CSV est l'abréviation de Comma-Separated Values (valeurs séparées par des virgules). Ce format de fichier vous permet de stocker des données en texte brut, ce qui le rend idéal pour le partage de données entre applications.

Un fichier CSV contient des lignes de données correspondant à des enregistrements individuels ; la première ligne correspond généralement aux noms des colonnes. Les valeurs de chaque ligne sont séparées par une virgule. Bien que les virgules soient le séparateur le plus couramment utilisé (comme le suggère le nom du format), d'autres caractères (points-virgules, tabulations ou espaces, par exemple) peuvent également séparer les valeurs.

Voici un exemple de fichier CSV. Nous en exporterons un semblable dans les sections suivantes :

store_id,revenue,day
1,100.42,2023-05-01
1,148.89,2023-05-02
2,238.98,2023-05-03

Comme vous pouvez le voir, la première colonne contient l'identifiant du magasin, la deuxième le chiffre d'affaires et la troisième la date. Chaque ligne correspond à un enregistrement. L'organisation ressemble beaucoup à celle des tableurs et des bases de données, mais tout est en texte !

Pourquoi exporter une base de données sous forme de fichier CSV ?

Les fichiers CSV peuvent être ouverts par presque tous les logiciels orientés données. Exporter vos données PostgreSQL vers un fichier CSV signifie que vous pouvez facilement partager des informations avec vos collègues, même s'ils utilisent des outils différents.

Le format CSV est également natif de nombreux outils d'analyse de données et de feuilles de calcul. L'exportation via ce format vous permet de transférer rapidement et facilement des données en vue d'une analyse approfondie, d'une visualisation et d'un rapport.

Exporter des données de PostgreSQL vers CSV

Les données

Il s'agit de notre table d'exemple appelée sales. Chaque ligne représente le montant gagné par chaque magasin un jour donné.

sales

Exportation de données avec des requêtes SQL

Nous allons utiliser deux requêtes SQL différentes pour tester différentes méthodes d'exportation. (Il est utile d'avoir notre aide-mémoire PostgreSQL à portée de main pour augmenter votre productivité lorsque vous écrivez vos propres requêtes). Voici la première :

SELECT * FROM sales

Cette première requête est simple et sélectionne tout ce qui se trouve dans la table. L'idée est de s'assurer que nous ne perdons pas de données lors de l'exportation.

Voici la deuxième requête (beaucoup plus complexe) :

WITH max_revs (store_id, day, revenue, max_rev) AS (
	SELECT
		store_id,
		day,
		revenue, 
		MAX(revenue) OVER(PARTITION BY day) 
	FROM sales 
)
SELECT
	store_id,
	day,
	max_rev
FROM max_revs
WHERE revenue = max_rev;

La deuxième requête simule un rapport simple ; pour chaque jour, elle affiche le magasin le plus important en termes de chiffre d'affaires et le chiffre d'affaires le plus élevé. C'est ce que vous pourriez écrire dans le cadre d'un travail d'analyse de données. Nous utilisons une expression de table commune pour sélectionner d'abord le chiffre d'affaires le plus élevé de la journée ; la requête externe (deuxième) sélectionne le(s) magasin(s) qui correspond(ent) au chiffre d'affaires renvoyé par l'expression de table commune.

Si vous n'êtes pas encore familiarisé avec les CTE et les fonctions de fenêtre, consultez SQL Reporting track et notre cours sur les fonctions de fenêtre en SQL. Mais pour l'instant, revenons à l'exportation des données de PostgreSQL vers un fichier CSV. Une fois que nous avons les données que nous voulons, nous devons commencer le processus d'exportation.

Exporter la base de données à l'aide de la ligne de commande

Tout d'abord, explorons l'exportation d'une base de données PostgreSQL via la ligne de commande. Deux options s'offrent à vous : utiliser la commande \copy ou la déclaration COPY; nous expliquerons les différences entre les deux dans un instant.

Bien que l'utilisation de l'une ou l'autre de ces méthodes soit moins simple que l'utilisation de pgAdmin, vous avez probablement déjà tout ce qu'il vous faut pour cela. Commençons donc.

Connexion à la base de données avec psql

Pour les deux options de ligne de commande, nous utiliserons psql. Il s'agit d'un outil de ligne de commande pour les bases de données PostgreSQL.

Pour vous connecter à la base de données, entrez la commande suivante :

psql -h <hostname> -p <port> -d <database name> -U <username>

Remplacez hostname par le nom d'hôte (ou l'adresse) de la base de données. Le port est spécifié sur le serveur distant avec le nom de la base de données et le nom d'utilisateur. Ensuite, un mot de passe vous sera demandé.

Une fois connecté à la base de données, vous pouvez utiliser la commande \copy ou la méthode COPY pour exporter vos données. Nous commencerons par la démonstration de \copy.

1) Exportation de données à l'aide de \copy

La commande \copy copiera directement le résultat de votre requête locale dans un fichier local sur votre machine. Elle fonctionne du côté client, c'est donc un excellent choix si vous n'avez que des permissions de lecture de la base de données.

Voici la syntaxe :

\copy (query) to ‘filename’ with cvs [header]

Vous pouvez inclure l'argument optionnel header lorsque vous souhaitez que le tableau généré ait une ligne supplémentaire en haut contenant tous les noms de colonnes. Pour garantir la clarté des résultats, j'utiliserai cet argument dans toutes les commandes à venir.

Voyons quelques exemples.

Exemple 1 : Copier le tableau entier

Code :

\copy (SELECT * FROM sales) to ‘/dbExport/sales_full_table.csv’ with csv header

Explication : Après avoir exécuté la requête SELECT * FROM sales, votre ordinateur copie les résultats dans le fichier CSV spécifié. Aucune autre requête n'est envoyée à la base de données.

Exemple 2 : Copier les résultats de la requête

Code :

\copy (WITH max_revs(store_id, day, revenue, max_rev) AS (SELECT store_id, day, revenue, MAX(revenue) OVER(PARTITION BY day) FROM sales) SELECT store_id, day, revenue FROM max_revs WHERE revenue = max_rev) to ‘/dbExport/sales_report_query.csv’ with csv header

Explication : Le même principe est à l'œuvre ici. La base de données exécute cette requête de rapport et votre ordinateur local copie les résultats dans un fichier.

La commande \copy est facile à utiliser et, dans la plupart des cas, elle est aussi rapide que la requête moyenne. En effet, le fichier résultant est généré localement au lieu d'être envoyé depuis la base de données.

2) Utilisation de l'instruction COPY

Contrairement à la commande \copy, l'instruction COPY s'exécute du côté de la base de données. Nous l'appelons une instruction car COPY fait en fait partie d'une commande exécutée par la base de données. Cela signifie que le fichier résultant sera enregistré sur le serveur distant, ce qui doit être pris en compte lors du choix du chemin d'accès au fichier. Enregistrer sur un serveur distant signifie également que vous devez avoir les privilèges de superutilisateur (root) de PostgreSQL.

La syntaxe de l'instruction COPY ressemble à ceci :

COPY { table | (query) } 'filename' [ DELIMITER 'delimiter' ] [HEADER]

J'ai omis certains arguments pour plus de simplicité ; si vous voulez voir la syntaxe complète, visitez la documentation PostgreSQL.

Exemple 1 : Copier la table entière

Code :

COPY sales TO ‘/dbExport/sales_full_table.csv’ DELIMITER ‘,’ CVS HEADER

Explication : Comme on peut s'y attendre, cet exemple copie l'intégralité de la table des ventes dans le fichier sales_full_table.csv. Notez que le délimiteur est une virgule et que nous avons inclus la ligne d'en-tête.

Exemple 2 : Copier les résultats de la requête

Code :

COPY (WITH max_revs(store_id, day, revenue, max_rev) AS (SELECT store_id, day, revenue, MAX(revenue) OVER(PARTITION BY day) FROM sales) SELECT store_id, day, revenue FROM max_revs WHERE revenue = max_rev) TO ‘/dbExport/sales_full_table.csv’ DELIMITER ‘,’ CVS HEADER

Explication : Comme le suggère le sous-titre, cet exemple exporte les résultats de la requête vers le fichier sales_full_table.csv. Voici un exemple de données sous forme de tableau :

Comme vous pouvez le voir, le résultat est le même que celui de la commande \copy; la seule différence est l'endroit où le fichier résultant est stocké.

Exporter des données à l'aide de pgAdmin

Examinons maintenant la deuxième option, qui utilise une interface utilisateur graphique (GUI). Vous connaissez peut-être déjà pgAdmin; il s'agit d'un programme de gestion de base de données PostgreSQL gratuit et open-source. Il s'agit d'un moyen simple et direct d'exporter des données à partir d'une base de données Postgres.

Connexion à la base de données

Ouvrez pgAdmin et cliquez sur Ajouter un nouveau serveur.

Sur la page General, choisissez un nom pour la connexion.

Sur la page Connexion , entrez le nom d'hôte ("localhost" si vous exécutez la base de données sur votre propre ordinateur), le nom de la base de données, le nom d'utilisateur et le mot de passe.

Enfin, cliquez sur Enregistrer. Si la connexion a été établie correctement, votre page de tableau de bord affichera maintenant des graphiques de l'activité de votre base de données.

Exporter les résultats d'une requête

Nous allons d'abord ouvrir l'outil d'interrogation en cliquant sur l'icône de la base de données (trois disques) dans la barre d'outils supérieure ou en utilisant Alt+Shift+Q. Vous pouvez maintenant interroger la base de données en écrivant des requêtes dans la zone de texte et en les exécutant à l'aide du bouton de lecture situé en haut de la page ou de la touche F5.

Une fois la requête exécutée, le résultat s'affiche dans la fenêtre du bas. Pour enregistrer le résultat dans un fichier CSV, cliquez sur le bouton de téléchargement situé juste au-dessus des données obtenues.

Comme vous pouvez le constater, les résultats sont les mêmes qu'avec les outils de ligne de commande. La principale différence est que pgAdmin facilite le passage de la conception des requêtes à l'enregistrement des résultats.

En savoir plus sur l'exportation de données avec des fichiers CSV

Exporter des bases de données vers des fichiers CSV est un plaisir ! Maintenant que vous savez comment exporter toutes les données dont vous avez besoin, vous avez peut-être une autre question : Comment importer des données dans une base de données Postgres ? Lisez ce court article sur l'importation de données dans PostgreSQL à l'aide de pgAdmin pour le découvrir.

Et si vous souhaitez approfondir vos connaissances sur PostgreSQL, consultez notre parcours complet de A à Z avec PostgreSQL, qui comprend plus de 1 000 exercices interactifs. Bon apprentissage !