Déposez votre base de données pour PostgreSQL

Les bases de données sont des outils pour stocker des informations de manière organisée mais flexible. Un tableur est essentiellement une base de données, mais les contraintes d’une application graphique rendent la plupart des applications de tableur inutiles pour les programmeurs. Avec Bord et que les appareils IoT deviennent des plates-formes cibles importantes, les développeurs ont besoin de solutions puissantes mais légères pour stocker, traiter et interroger de grandes quantités de données. Une de mes combinaisons préférées est la base de données PostgreSQL et Liaisons Lua, mais les possibilités sont infinies. Quel que soit le langage que vous utilisez, Postgres est un excellent choix pour une base de données, mais vous devez connaître quelques bases avant de l’adopter.

Installer Postgres

Pour installer PostgreSQL sur Linux, utilisez votre référentiel de logiciels. Sur Fedora, CentOS, Mageia et similaires :

$ sudo dnf install postgresql postgresql-server

Sur Debian, Linux Mint, Elementary et similaires :

$ sudo apt install postgresql postgresql-contrib

Sur macOS et Windows, téléchargez un programme d’installation à partir de postgresql.org.

Configurer Postgres

La plupart des distributions installent la base de données Postgres sans départ mais vous fournir un script ou un service systemd pour l’aider à démarrer de manière fiable. Cependant, avant de démarrer PostgreSQL, vous devez créer un cluster de bases de données.

Feutre

Sur Fedora, CentOS ou similaire, un script de configuration Postgres est fourni dans le package Postgres. Exécutez ce script pour une configuration facile :

$ sudo /usr/bin/postgresql-setup --initdb
[sudo] password:
 * Initializing database in '/var/lib/pgsql/data'
 * Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log

DebianName

Sur les distributions basées sur Debian, l’installation est effectuée automatiquement par apt pendant l’installation.

Tout le reste

Enfin, si vous exécutez autre chose, vous pouvez simplement utiliser la chaîne d’outils fournie par Postgres lui-même. La initdb La commande crée un cluster de bases de données, mais vous devez l’exécuter en tant que postgres utilisateur, une identité que vous pouvez temporairement assumer en utilisant sudo:

$ sudo -u postgres \
"initdb -D /var/lib/pgsql/data \
--locale en_US.UTF-8 --auth md5 --pwprompt"

Démarrer Postgres

Maintenant qu’un cluster existe, démarrez le serveur Postgres en utilisant soit la commande qui vous est fournie dans la sortie de initdb ou avec systemd :

$ sudo systemctl start postgresql

Création d’un utilisateur de base de données

Pour créer un utilisateur Postgres, utilisez le createuser commande. La postgres user est le super-utilisateur de l’installation de Postgres,

$ sudo -u postgres createuser --interactive --password bogus
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n
Password:

Créer une base de données

Pour créer une nouvelle base de données, utilisez le createdb commande. Dans cet exemple, je crée la base de données exampledb et en attribuer la propriété à l’utilisateur bogus:

$ createdb exampledb --owner bogus

Interagir avec PostgreSQL

Vous pouvez interagir avec une base de données PostgreSQL en utilisant le psql commande. Cette commande fournit un shell interactif vous permettant d’afficher et de mettre à jour vos bases de données. Pour vous connecter à une base de données, spécifiez l’utilisateur et la base de données que vous souhaitez utiliser :

$ psql --user bogus exampledb

psql (XX.Y)

Type "help" for help.
exampledb=>

Créer un tableau

Les bases de données contiennent des tableaux, qui peuvent être visualisés sous forme de tableur. Il y a une série de lignes (appelées enregistrements dans une base de données) et des colonnes. L’intersection d’une ligne et d’une colonne s’appelle un champ.

Le langage de requête structuré (SQL) est nommé d’après ce qu’il fournit : une méthode pour se renseigner sur le contenu d’une base de données dans une syntaxe prévisible et cohérente afin de recevoir des résultats utiles.

Actuellement, votre base de données est vide, dépourvue de tables. Vous pouvez créer un tableau avec le CREATE requête. Il est utile de combiner cela avec le IF NOT EXISTS qui empêche PostgreSQL d’écraser une table existante.

Avant de créer une table, réfléchissez au type de données (le “type de données” dans la terminologie SQL) que vous prévoyez que la table contiendra. Dans cet exemple, je crée une table avec une colonne pour un identifiant unique et une colonne pour du texte arbitraire jusqu’à neuf caractères.

exampledb=> CREATE TABLE IF NOT EXISTS my_sample_table(
exampledb(> id SERIAL,
exampledb(> wordlist VARCHAR(9) NOT NULL
);

La SERIAL mot-clé n’est pas réellement un type de données. C’est notation spéciale dans PostgreSQL qui crée un champ entier auto-incrémenté. La VARCHAR mot-clé est un type de données indiquant un nombre variable de caractères dans une limite. Dans ce code, j’ai spécifié un maximum de 9 caractères. Il existe de nombreux types de données dans PostgreSQL, consultez donc la documentation du projet pour obtenir une liste des options.

Insérer des données

Vous pouvez remplir votre nouvelle table avec des exemples de données en utilisant le INSERT Mot clé SQL :

exampledb=> INSERT INTO my_sample_table (wordlist) VALUES ('Alice');
INSERT 0 1

Votre saisie de données échoue, si vous essayez de mettre plus de 9 caractères dans le wordlist champ:

exampledb=> INSERT INTO my_sample_table (WORDLIST) VALUES ('Alexandria');
ERROR:  VALUE too long FOR TYPE CHARACTER VARYING(9)

Modifier un tableau ou une colonne

Lorsque vous devez modifier une définition de champ, vous utilisez la ALTER Mot clé SQL. Par exemple, si vous décidez qu’une limite de neuf caractères pour wordlistvous pouvez augmenter son allocation en définissant son type de données :

exampledb=> ALTER TABLE my_sample_table
ALTER COLUMN wordlist SET DATA TYPE VARCHAR(10);
ALTER TABLE
exampledb=> INSERT INTO my_sample_table (WORDLIST) VALUES ('Alexandria');
INSERT 0 1

Afficher les données dans un tableau

SQL est un langage de requête, vous visualisez donc le contenu d’une base de données via des requêtes. Les requêtes peuvent être simples ou impliquer de joindre des relations complexes entre plusieurs tables différentes. Pour tout voir dans un tableau, utilisez la SELECT mot clé sur * (un astérisque est un joker):

exampledb=> SELECT * FROM my_sample_table;
 id |  wordlist
\----+------------
  1 | Alice
  2 | Bob
  3 | Alexandria
(3 ROWS)

Plus de données

PostgreSQL peut gérer beaucoup de données, mais comme pour toute base de données, la clé du succès réside dans la façon dont vous concevez votre base de données pour le stockage et ce que vous faites avec les données une fois que vous les avez stockées. Un ensemble de données publiques relativement important peut être trouvé sur OCDE.orget en utilisant cela, vous pouvez essayer des techniques de base de données avancées.

Tout d’abord, téléchargez les données sous forme de valeurs séparées par des virgules (CSV) et enregistrez le fichier sous land-cover.csv dans ton Downloads dossier.

Parcourez les données dans un éditeur de texte ou un tableur pour avoir une idée des colonnes qui existent et du type de données que chaque colonne contient. Examinez attentivement les données et surveillez les exceptions à une règle apparente. Par exemple, le COU colonne, contenant un code de pays tel que AUS pour l’Australie et GRC pour la Grèce, a tendance à être de 3 caractères jusqu’à la bizarrerie BRIICS.

Une fois que vous avez compris les données avec lesquelles vous travaillez, vous pouvez préparer une base de données Postgres :

$ createdb landcoverdb --owner bogus
$ psql --user bogus landcoverdb
landcoverdb=> create table land_cover(
country_code varchar(6),
country_name varchar(76),
small_subnational_region_code varchar(5),
small_subnational_region_name varchar(14),
large_subnational_region_code varchar(17),
large_subnational_region_name varchar(44),
measure_code varchar(13),
measure_name varchar(29),
land_cover_class_code varchar(17),
land_cover_class_name varchar(19),
year_code integer,
year_value integer,
unit_code varchar(3),
unit_name varchar(17),
power_code integer,
power_name varchar(9),
reference_period_code varchar(1),
reference_period_name varchar(1),
value float(8),
flag_codes varchar(1),
flag_names varchar(1));

Importation de données

Postgres peut importer des données CSV directement à l’aide de la métacommande spéciale \copy:

landcoverdb=> \copy land_cover from '~/land-cover.csv' with csv header delimiter ','
COPY 22113

Cela représente 22 113 enregistrements importés. Cela semble être un bon début !

Interroger des données

À l’étranger SELECT pour voir toutes les colonnes des 22 113 enregistrements est possible, et Postgres dirige très bien la sortie vers un téléavertisseur d’écran afin que vous puissiez faire défiler la sortie à un rythme tranquille. Cependant, en utilisant SQL avancé, vous pouvez obtenir des vues utiles de ce qui est autrement des données plutôt brutes.

landcoverdb=> SELECT
    lcm.country_name,
    lcm.year_value,
    SUM(lcm.value) sum_value
FROM land_cover lcm
JOIN (
    SELECT
        country_name,
        large_subnational_region_name,
        small_subnational_region_name,
        MAX(year_value) max_year_value
    FROM land_cover
    GROUP BY country_name,
        large_subnational_region_name,
        small_subnational_region_name
) AS lcmyv
ON
    lcm.country_name = lcmyv.country_name AND
    lcm.large_subnational_region_name = lcmyv.large_subnational_region_name AND
    lcm.small_subnational_region_name = lcmyv.small_subnational_region_name AND
    lcm.year_value = lcmyv.max_year_value
GROUP BY lcm.country_name,
    lcm.large_subnational_region_name,
    lcm.small_subnational_region_name,
    lcm.year_value
ORDER BY country_name,
    year_value;

Voici un exemple de sortie :

\---------------+------------+------------
 Afghanistan    |       2019 |  743.48425
 Albania        |       2019 |  128.82532
 Algeria        |       2019 |  2417.3281
 American Samoa |       2019 |   100.2007
 Andorra        |       2019 |  100.45613
 Angola         |       2019 |  1354.2192
 Anguilla       |       2019 | 100.078514
 Antarctica     |       2019 |  12561.907
[...]

SQL est un langage riche et dépasse donc le cadre de cet article. Lisez le code SQL et voyez si vous pouvez le modifier pour fournir un ensemble de données différent.

Ouvrir la base de données

PostgreSQL est l’une des grandes bases de données open source. Avec lui, vous pouvez concevoir des référentiels pour les données structurées, puis utiliser SQL pour les afficher de différentes manières afin d’avoir de nouvelles perspectives sur ces données. Postgres s’intègre à de nombreux langages, notamment Python, Lua, Groovy, Java, etc. Ainsi, quel que soit votre ensemble d’outils, vous pouvez probablement utiliser cette excellente base de données.