Mapper des relations avec SQLAlchemy

SQLAlchemy est un ORM (Object-Relational Mapping) permettant de synchroniser vos classes avec des tables en base de données relationnelle (basée sur SQL).

L'étape la plus importante, quand on utilise un ORM, c'est de réaliser le « Mapping », c'est à dire l'association entre les éléments de vos classes et ceux de vos tables en base de données. Mais attention, certaines tables peuvent être liées les unes aux autres via des relations : d'ou le terme de base de données relationnelle.

Présentation des tables SQL

Imaginons que nous souhaitions mettre en oeuvre un prototype de gallerie marchande sur Internet : nous l'appelerons WebStore. Voici un script SQL, pour MySql (ou MariaDB), permettant de créer la base de données initiale. Notez bien que cela reste un prototype très simpliste.

-- ------------------------------------------------------------------------------
-- - Reconstruction de la base de données                                     ---
-- ------------------------------------------------------------------------------
DROP DATABASE IF EXISTS WebStore;
CREATE DATABASE WebStore;
USE WebStore;


-- -----------------------------------------------------------------------------
-- - Construction de la table des utilisateurs                               ---
-- -----------------------------------------------------------------------------
CREATE TABLE T_Users (
    IdUser              int(4)      PRIMARY KEY AUTO_INCREMENT,
    Login               text        NOT NULL,
    Password            text        NOT NULL,
    ConnectionNumber    int(4)      NOT NULL DEFAULT 0
) ENGINE = InnoDB;

INSERT INTO T_Users (IdUser, Login, Password) VALUES ( 1, 'Anderson',   'Neo' );
INSERT INTO T_Users (IdUser, Login, Password) VALUES ( 2, 'Skywalker',  'Luke' );
INSERT INTO T_Users (IdUser, Login, Password) VALUES ( 3, 'Plissken',   'Snake' );
INSERT INTO T_Users (IdUser, Login, Password) VALUES ( 4, 'Ripley',     'Ellen' );
INSERT INTO T_Users (IdUser, Login, Password) VALUES ( 5, 'Bond',       'James' );

SELECT * FROM T_Users;

-- -----------------------------------------------------------------------------
-- - Construction de la tables des articles en vente                         ---
-- -----------------------------------------------------------------------------
CREATE TABLE T_Articles (
    IdArticle           int(4)      PRIMARY KEY AUTO_INCREMENT,
    Description         varchar(30) NOT NULL,
    Brand               varchar(30) NOT NULL,
    UnitaryPrice        float(8)    NOT NULL
) ENGINE = InnoDB;

INSERT INTO T_Articles ( Description, Brand, UnitaryPrice ) VALUES ( 'Souris',                  'Logitoch',             65 );
INSERT INTO T_Articles ( Description, Brand, UnitaryPrice ) VALUES ( 'Clavier',                 'Microhard',            49.5 );
INSERT INTO T_Articles ( Description, Brand, UnitaryPrice ) VALUES ( 'Systeme d''exploitation', 'Fenetres Vistouille',  150 );
INSERT INTO T_Articles ( Description, Brand, UnitaryPrice ) VALUES ( 'Tapis souris',            'KooR.fr',         5 );
INSERT INTO T_Articles ( Description, Brand, UnitaryPrice ) VALUES ( 'Cle USB 8 To',            'Syno',                 8 );
INSERT INTO T_Articles ( Description, Brand, UnitaryPrice ) VALUES ( 'Laptop',                  'PH',                   1199 );
INSERT INTO T_Articles ( Description, Brand, UnitaryPrice ) VALUES ( 'CD x 500',                'CETME',                250 );
INSERT INTO T_Articles ( Description, Brand, UnitaryPrice ) VALUES ( 'DVD-R x 100',             'CETME',                99 );
INSERT INTO T_Articles ( Description, Brand, UnitaryPrice ) VALUES ( 'DVD+R x 100',             'CETME',                105 );
INSERT INTO T_Articles ( Description, Brand, UnitaryPrice ) VALUES ( 'Batterie Laptop',         'PH',                   80 );
INSERT INTO T_Articles ( Description, Brand, UnitaryPrice ) VALUES ( 'Casque Audio',            'Syno',                 105 );
INSERT INTO T_Articles ( Description, Brand, UnitaryPrice ) VALUES ( 'WebCam',                  'Logitoch',             755 );

SELECT * FROM T_Articles;

-- -----------------------------------------------------------------------------
-- - Construction de la table des commandes                                  ---
-- -----------------------------------------------------------------------------
CREATE TABLE T_Commands (
    IdCommand       int(4)      PRIMARY KEY AUTO_INCREMENT,
    IdUser          int(4)      NOT NULL REFERENCES T_Users(IdUser),
    CommandDate     datetime    NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE = InnoDB;

-- -----------------------------------------------------------------------------
-- - Construction de la table des lignes de commandes                        ---
-- -----------------------------------------------------------------------------
CREATE TABLE T_CommandLines (
    IdCommandLine   int(4)      PRIMARY KEY AUTO_INCREMENT,
    IdCommand       int(4)      NOT NULL REFERENCES T_Commands(IdCommand),
    IdArticle       int(4)      NOT NULL REFERENCES T_Articles(IdCommand),
    Quantity        int(4)      NOT NULL
) ENGINE = InnoDB;

-- Une première commande
INSERT INTO T_Commands (IdUser, CommandDate) VALUES ( 2, now() ); 
INSERT INTO T_CommandLines (IdCommand, IdArticle, Quantity) VALUES (1, 1, 5); 
INSERT INTO T_CommandLines (IdCommand, IdArticle, Quantity) VALUES (1, 3, 3);

-- Une seconde commande
INSERT INTO T_Commands (IdUser, CommandDate) VALUES ( 1, now() ); 
INSERT INTO T_CommandLines (IdCommand, IdArticle, Quantity) VALUES (2, 2, 4); 
INSERT INTO T_CommandLines (IdCommand, IdArticle, Quantity) VALUES (2, 3, 1);
INSERT INTO T_CommandLines (IdCommand, IdArticle, Quantity) VALUES (2, 4, 1);

Pour mapper une classe avec SQLAlchemy, vous devez respecter un certain nombre de règles :

• Toutes les classes « mappées » de votre programme doivent toutes dériver d'une même classe de base (pourquoi par appelée Base) et qui est produite via un appel à la fonction declarative_base du framework SQLAlchemy.

• Vous devez nommée la table associée à votre classe via un attribut statique obligatoirement nommé __tablename__.

• Chaque colonne de la table en base de données doit être associée à un attribut statique de votre classe : cet attribut statique est une instance de la classe Column fournie par le framework SQLAlchemy.

• En cas de relation, il faudra mapper les clés de références et ajouter des attributs de type relationship.

Voici un exemple de mapping associé à la base de données précédente.

 1 
 2 
 3 
 4 
 5 
 6 
 7 
 8 
 9 
 10 
 11 
 12 
 13 
 14 
 15 
 16 
 17 
 18 
 19 
 20 
 21 
 22 
 23 
 24 
 25 
 26 
 27 
 28 
 29 
 30 
 31 
 32 
 33 
 34 
 35 
 36 
 37 
 38 
 39 
 40 
 41 
 42 
 43 
 44 
 45 
 46 
 47 
 48 
 49 
 50 
 51 
 52 
 53 
 54 
 55 
 56 
 57 
 58 
 59 
 60 
 61 
 62 
 63 
 64 
 65 
 66 
 67 
 68 
 69 
 70 
 71 
 72 
 73 
 74 
 75 
 76 
 77 
 78 
 79 
 80 
 81 
 82 
 83 
 84 
 85 
 86 
 87 
 88 
 89 
 90 
 91 
 92 
 93 

#!/usr/bin/python3

import sys
from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, Text, Date, Float
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql.schema import ForeignKey

Base = declarative_base()    # Required

class User( Base ):
    __tablename__ = "T_Users"

    idUser = Column(Integer, primary_key=True)
    login = Column(Text)
    password = Column(Text)
    connectionNumber = Column(Integer)

    def __init__(self, idUser=0, login="John", password="Doe", connectionNumber=0):
        self.idUser = idUser
        self.login = login
        self.password = password
        self.connectionNumber = connectionNumber

    def __str__(self):
        return "%d: %s %s - %d" % (self.idUser, self.login, self.password, self.connectionNumber)


class Article( Base ):
    __tablename__ = "T_Articles"
    
    idArticle = Column(Integer, primary_key=True)
    description = Column(Text)
    brand = Column(Text)
    unitaryPrice = Column(Float)

    def __str__(self):
        return "%d: %s de marque %s = %.2f euros" % (self.idArticle, self.description, self.brand, self.unitaryPrice)


class CommandLine( Base ):
    __tablename__ = "T_CommandLines"
    
    idCommandLine = Column(Integer, primary_key=True)
    quantity = Column(Integer)
    
    idArticle = Column(Integer, ForeignKey("T_Articles.idArticle"))
    article = relationship("Article")

    idCommand = Column(Integer, ForeignKey("T_Commands.idCommand"))
    command = relationship("Command")

    def __str__(self):
        return "%s x %d" % (str(self.article), self.quantity)


class Command( Base ):
    __tablename__ = "T_Commands"
    
    idCommand = Column(Integer, primary_key=True)
    commandDate = Column(Date)
    
    idUser = Column(Integer, ForeignKey("T_Users.idUser"))
    user = relationship("User")

    commandLines = relationship("CommandLine", backref="CommandLine.idCommand")

    def __str__(self):
        result = "%s - Command %d for %s\n" % (str(self.commandDate), self.idCommand, str(self.user))
        for line in self.commandLines:
            result += "\t%s\n" % (str(line))
        return result



if __name__ == '__main__':
    
    try:
        #engine = create_engine('sqlite:///demo.db', echo=True)
        engine = create_engine('mysql+mysqldb://<user>:<pwd>@localhost/WebStore', echo=False)
    
        #Session = sessionmaker(bind=engine)
        #session = Session()
        session = sessionmaker(bind=engine)()    
        
        commands = session.query(Command).all()
        for command in commands:
            print( command )
    except:
        print("Cannot use database", file=sys.stderr)
    finally:
        session.close()

Et voici les résultats produit par cet exemple de code : notez avec quelle facilité nous avons chargé les données des commandes (ces données étant réparties sur nos quatre tables).

$> python3 webstore.py
2017-11-25 17:09:29 - Command 1 for 2: Skywalker Luke - 0
    1: Souris de marque Logitoch = 65.00 euros x 5
    3: Systeme d'exploitation de marque Fenetres Vistouille = 150.00 euros x 3

2017-11-25 17:09:29 - Command 2 for 1: Anderson Neo - 0
    2: Clavier de marque Microhard = 49.50 euros x 4
    3: Systeme d'exploitation de marque Fenetres Vistouille = 150.00 euros x 1
    4: Tapis souris de marque KooR.fr = 5.00 euros x 1
$>