PostgreSQL

Installation

L'installation de PostgreSQL sort du cadre de ce document. On va supposer qu'une instance de PostgreSQL est déjà installée et accessible sur le port 5432 (port par défaut de PostgreSQL).

Pour cette formation, pg.solidev.net est disponible, avec des utilisateurs userXX associés à une base de données dbuserXX (où XX est le numéro de l'utilisateur). https://pg.solidev.net est une instance de PgAdmin, un outil d'administration de PostgreSQL. Des comptes userXX@nomail.com ont été créés pour permettre d'administrer les bases de données en mode "graphique".

Nous allons utiliser sqlalchemy pour interagir avec PostgreSQL, ainsi que le driver psycopg pour PostgreSQL. Pour installer ces deux paquets, il suffit d'utiliser pip ou uv:

pip install sqlalchemy psycopg[binary,pool]

ou

uv add sqlalchemy psycopg[binary,pool]

Référence : https://docs.sqlalchemy.org/en/21/dialects/postgresql.html

Connexion à PostgreSQL - utilisation en mode "direct"

La connexion à PostgreSQL se fait en créant un "Moteur" SQLAlchemy. Le moteur permet de se connecter à la base de données et d'exécuter des requêtes SQL. La chaîne de connexion permet de spécifier le type de base de données, le driver utilisé, le nom d'utilisateur, mot de passe, nom de la base de données, etc..

Référence : https://docs.sqlalchemy.org/en/20/core/engines.html#engine-creation-api

from sqlalchemy import create_engine
engine = create_engine(
    'postgresql+psycopg://userXX:password@pg.solidev.net/dbuserXX',
    echo=True
)

Le paramètre echo=True permet d'afficher les requêtes SQL exécutées dans la console. Il est très utile pour le débogage, mais à éviter en production.

On peut tester la connexion en exécutant une requête SQL simple. Par exemple, pour vérifier que la connexion fonctionne, on peut exécuter une requête qui renvoie 1 :

from sqlalchemy import text
with engine.connect() as sqa_conn:
    result = sqa_conn.execute(text("SELECT 1"))
    for row in result:
        print(row[0])

Il est alors possible d'exécuter des requêtes SQL directement sur le moteur en utilisant la méthode execute() : (reference : https://docs.sqlalchemy.org/en/20/tutorial/dbapi_transactions.html#getting-a-connection)

from sqlalchemy import text, exc as sa_exc
with engine.connect() as sqa_conn:
    sqa_conn.execute(text("""
    CREATE TABLE IF NOT EXISTS ingredients (
        id BIGSERIAL PRIMARY KEY,
        name VARCHAR NOT NULL UNIQUE
    );
    """))
    sqa_conn.commit()

    try:
        sqa_conn.execute(text("""
        INSERT INTO ingredients (name)
        VALUES ('Mozzarella'),('Tomate'),('Basilic'),
                ('Jambon'),('Olive'),
                    ('Oignon'),('Oeuf');
        """))
        sqa_conn.commit()
    except sa_exc.IntegrityError:
        # Si la table a déjà été remplie, on ne fait rien
        pass

La récupération de données se fait en utilisant aussi la méthode execute(). Le résultat renvoyé est un objet de type Result qui permet de récupérer les lignes de la requète, et d'accèder aux colonnes par leur nom ou leur index. Par exemple, pour récupérer les ingrédients de la table ingredients :

with engine.connect() as sqa_conn:
    result = sqa_conn.execute(text("SELECT id, name FROM ingredients"))
    for row in result:
        print(row['id'], row['name'])

ou

with engine.connect() as sqa_conn:
    result = sqa_conn.execute(text("SELECT id, name FROM ingredients"))
    for row in result:
        print(row[0], row[1])

Le paramètrage des requètes SQL se fait en utilisant des paramètres nommés :

q = request.query.get('q', '')
with engine.connect() as sqa_conn:
    result = sqa_conn.execute(text("SELECT id, name FROM ingredients WHERE name ILIKE :name"), {'name': f"%{q}%"})
    for row in result:
        print(row['id'], row['name'])

Référence : SQLAlchemy - PostgreSQL dialect

Connexion à PostgreSQL - utilisation en mode "ORM"

Hors du cadre de la formation, voir le reste du tutoriel SQLAlchemy : https://docs.sqlalchemy.org/en/20/tutorial/index.html

Programme

  • NSI terminale : Système de gestion de bases de données relationnelles