Présentation de PostgreSQL
PostgreSQL est un SGBDR qui fonctionne en mode client-serveur. Une machine (ou cluster) serveur abrite les données et répond aux requêtes, et des clients interagissent avec le serveur.
Un SGBD est géré par un administrateur de base de données (SGBDA dans les annonces de postes). C’est lui qui administre le serveur, crée les différentes bases, gère les utilisateurs et gère les questions de performance et de sécurité des données. Vous pouvez, si vous le souhaitez, installer PostgreSQL sur votre ordinateur (https://www.postgresql.org/download , ou le système de packages de votre système). Cependant, ce n’est pas une compétence attendue dans ce master, et nous mettons à votre disposition un serveur PostgreSQL qui suffit entièrement pour nos enseignements et projets.
Comprendre l’organisation du serveur.
Les données sur le serveur sont réparties dans différentes bases de données (database). Sur le serveur, il y a différents rôles (role) qui ont des droits strictement déterminés sur certaines parties des données. Dans une même base de données, il y a plusieurs schémas (schema) qui sont des espaces de nommage des objets de la base ; le BDDA définit des droits d’un rôle sur un schéma. Voici par exemple quelques commandes SQL entrées par l’administrateur de notre serveur
CREATE ROLE ds1 ;
CREATE ROLE toto WITH LOGIN PASSWORD 'motdepassedetoto' ;
CREATE DATABASE toto OWNER toto;
GRANT ds1 TO toto;
CREATE DATABASE employees;
GRANT SELECT ON ALL TABLES IN SCHEMA employees.public TO ds1;Ainsi, le rôle "toto" peut se connecter en fournissant un mot de passe, il possède sa base de données de même nom. Par ailleurs, toto est membre d’un groupe plus grand "ds1". Celui-ci peut effectuer des interrogations sur les tables du schéma "public" de la base "employees".
Par ailleurs, le SGBDA a défini soigneusement les droits d’accès depuis l’extérieur sur le SGBD.
Utilisation du client en ligne de commande psql
psql est un client en ligne de commande fourni avec PostgreSQL. Vous en trouverez une description exhaustive ici
On lance une session interactive par la commande
psql -h serveur -U role -d nom_baseoù
-
serveur désigne le nom de la machine ( data si vous vous vous connectez d’une des machines de la salle informatique ou de scylla, savennieres.math.univ-angers.fr si vous vous connectez depuis l’extérieur). Si vous travaillez sur la machine qui abrite le serveur (data), il n’y a rien à renseigner.
-
role désigne l’identité sous laquelle vous êtes connu sur le serveur. Si elle n’est pas indiquée, psql cherchera à se connecter sous votre identité sur la machine.
-
nom_base indique sur quelle base vous vous voulez vous connecter (par défaut la base qui porte votre nom)
Une fois dans le client interactif, vous pouvez lancer des commandes SQL :
toto=> SELECT nom FROM factures;ou lancer des commandes spécifiques à psql, toutes préfixées par \ :
ducrot=> \d
N a trouvé aucune relation.
ducrot=> \c employees
Vous êtes maintenant connecté à la base de données « employees » en tant qu utilisateur « ducrot ».
employees=> \d
Liste des relations
Schéma | Nom | Type | Propriétaire
--------+--------------+-------+--------------
public | departments | table | postgres
public | dept_emp | table | postgres
public | dept_manager | table | postgres
public | employees | table | postgres
public | salaries | table | postgres
public | titles | table | postgres
(6 lignes)
employees=> \i monfichier.sql
dept_no | dept_name
---------+--------------------
d001 | Marketing
d002 | Finance
d003 | Human Resources
d004 | Production
d005 | Development
d006 | Quality Management
d007 | Sales
d008 | Research
d009 | Customer Service
(9 lignes)
employees=> \qOn peut aussi utiliser psql pour
-
exécuter un fichier sql
psql -h data -d nom_base -U toto -f nom_fichier.sql-
ou exécuter une commande
psql -h data -d nom_base -U toto - c "SELECT nom FROM factures;"Copier des données vers un fichier, ou à partir d’un fichier par \copy
La commande \copy de psql permet de copier des données d’une table vers un fichier csv
\copy matable TO 'monfichier.csv' WITH DELIMITER ',' CSV HEADER;Ici, la clause optionnelle CSV HEADER indique que les noms des colonnes sont recopiées comme entête du fichier csv.
On peut aussi exporter le résultat d’une requête SELECT vers un fichier :
\copy (SELECT ... FROM matable WHERE ...) TO 'monfichier.csv' WITH DELIMITER ',' CSV HEADER;Inversement, si on a une table déjà créée et un fichier csv respectant exactement la structure de la table, on peut directement importer les données du fichier dans la table :
\copy matable FROM 'monfichier.csv' WITH DELIMITER ',' CSV HEADER;Accès à la base de données depuis l’extérieur
Si vous souhaitez accéder aux bases de l’extérieur, il faut d’abord établir une session ssh :
ssh monlogin@savennieres.math.univ-angers.frpuis lancer psql (ici, inutile de spécifier -h data, puisque vous êtes déjà dessus.)
Utilisation du module psycopg2 de python
Pour aller plus loin dans l’utilisation de PostgreSQL, vous allez avoir besoin d’un procédé automatisé. C’est le moment d’utiliser le module psycopg2 de python, qui fournit une interface de connexion à PostgreSQL.
Une description des fonctionnalités du module se trouve sur le site de psycopg2
Ce module implémente les recommendations de python pour le schéma général d’une API d’interaction avec une base de donnée décrite dans la PEP 249. Les autres modules qui établissent des connexions avec MySQL, Mariadb, sqlite, etc fonctionnent tous avec le même schéma, qui est très bien décrit dans le document.
Voici un exemple de session utilisant psycopg2 :
import psycopg2
conn = psycopg2.connect(host="data",database="toto",user="toto",password="taratata")
cur = conn.cursor()
cur.execute("SELECT * from matable")
# on récupère dans un objet python :
tableau = cur.fetchall()
# on fait ensuite ce qu'on veut de cet objet python
for ligne in tableau:
print(ligne)
conn.commit()
cur.close()
conn.close()Pour interroger avec python le résultat d’une requête, on dispose de plusieurs méthodes : fetchone(), fetchmany(), fetchall().
Pour insérer des données dans une base de manière automatique, il est recommandé d’utiliser des étiquettes (placeholders). par exemple :
cur.execute("""
INSERT INTO some_table (numero, date_edition, editeur)
VALUES (%s, %s, %s);
""",
(10, datetime.date(2005, 11, 18), "O'Reilly"))On notera que c’est le module qui se charge de transformer les objets python en objets compréhensibles par PostgreSQL. Ceci simplifie beaucoup la programmation, et évite également les problèmes d’attaque par "injection SQL".
On peut aussi utiliser des arguments nommés, autrement dit, faire appel à un dictionnaire.
cur.execute("""
INSERT INTO some_table (numero, date_edition, editeur)
VALUES (%(num)s, %(dateEd)s, %(ed)s);
""",
{'num': 10, 'ed': "O'Reilly", 'dateEd': datetime.date(2005, 11, 18)})Les étiquettes doivent absolument être écrites sous la forme %(nom)s.