Skip to content

19. Utilização do ORM SQLAlchemy

O capítulo anterior mostrou que, em alguns casos, podemos escrever código independente do SGBD utilizado com a seguinte arquitetura:

Image

Neste capítulo, utilizaremos o ORM (Object Relational Mapper) [SQLAlchemy] para aceder aos SGBDs de forma uniforme, independentemente do SGBD utilizado. Um ORM permite duas coisas:

  • permite que um script interaja com o SGBD sem emitir comandos SQL;
  • oculta as especificidades de cada SGBD do script;

A arquitetura fica da seguinte forma:

Image

O script está agora separado dos conectores pelo ORM. Ele comunica com o ORM utilizando classes e métodos. Não executa código SQL. O ORM faz isso utilizando os conectores aos quais está ligado. Oculta as especificidades desses conectores do script. Portanto, o código do script não é afetado por uma mudança no conector (e, consequentemente, no SGBD);

A estrutura de diretórios dos scripts em questão será a seguinte:

Image

19.1. Instalação do ORM [SQLAlchemy]

O ORM [SQLAlchemy] é fornecido como um pacote Python que deve ser instalado num terminal Python:


(venv) C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\databases\sqlalchemy>pip install sqlalchemy
Collecting sqlalchemy
  Downloading SQLAlchemy-1.3.18-cp38-cp38-win_amd64.whl (1.2 MB)
     || 1.2 MB 3.3 MB/s
Installing collected packages: sqlalchemy
Successfully installed sqlalchemy-1.3.18

19.2. Scripts 01: O Básico

Image

  • em [1], os scripts que serão estudados. Estes scripts utilizarão as classes de [2]: BaseEntity, MyException, Person, Utils;

19.2.1. Configuração

O ficheiro [config] configura a aplicação da seguinte forma:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
def configure():
    #  root_dir
    #  absolute path configuration relative path reference
    root_dir = "C:/Data/st-2020/dev/python/cours-2020/python3-flask-2020"
    #  absolute paths of dependencies
    absolute_dependencies = [
        #  BaseEntity, MyException, Person, Utilities
        f"{root_dir}/classes/02/entities",
    ]

    #  set the syspath
    from myutils import set_syspath
    set_syspath(absolute_dependencies)

    #  class configuration
    from Personne import Personne
    Personne.excluded_keys = ['_sa_instance_state']

    #  return the config
    return {}

Comentários

  • linha 8: adicionar a pasta que contém as classes [BaseEntity, MyException, Person, Utils] ao Python Path;
  • linhas 12-13: definimos o Python Path da aplicação;
  • linhas 16-17: deve lembrar-se de que a classe |BaseEntity| tem um atributo de classe chamado [excluded_keys]. Este atributo é uma lista na qual colocamos as propriedades da classe que não queremos que apareçam no dicionário da classe (função asdict). Aqui, excluímos a propriedade [_sa_instance_state] do estado da classe [Person]. Veremos o motivo em breve;

19.2.2. Script [demo]

O script [demo] mostra uma utilização inicial do ORM [sqlalchemy]:

 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
#  retrieve application configuration
import config

config = config.configure()

#  imports
from sqlalchemy import Table, Column, Integer, String, MetaData, UniqueConstraint
from sqlalchemy.orm import mapper

from Personne import Personne

#  metadata
metadata = MetaData()

#  the table
personnes_table = Table("personnes", metadata,
                        Column('id', Integer, primary_key=True),
                        Column('prenom', String(30), nullable=False),
                        Column("nom", String(30), nullable=False),
                        Column("age", Integer, nullable=False),
                        UniqueConstraint('nom', 'prenom', name='uix_1')
                        )
#  the Person class before mapping
personne1 = Personne().fromdict({"id": 67, "prénom": "x", "nom": "y", "âge": 10})
print(f"personne1={personne1.__dict__}")

#  mapping
mapper(Personne, personnes_table, properties={
    'id': personnes_table.c.id,
    'prénom': personnes_table.c.prenom,
    'nom': personnes_table.c.nom,
    'âge': personnes_table.c.age
})

#  person1 has not been modified
print(f"personne1={personne1.__dict__}")
#  the Person class has been modified - it has been "enriched"
personne2 = Personne().fromdict({"id": 68, "prénom": "x1", "nom": "y1", "âge": 11})
print(f"personne2={personne2.__dict__}")

Comentários

  • linhas 1-4: configuramos a aplicação;
  • linhas 6-10: importamos os módulos necessários para o script;
  • linha 13: [MetaData] é uma classe em [sqlalchemy];
  • linhas 15-22: [Table] é uma classe em [sqlalchemy]. É utilizada para descrever uma tabela de base de dados. Aqui, iremos descrever a tabela [people] na base de dados MySQL [dbpeople] abordada no capítulo |MySQL|;
    • linha 16: o primeiro parâmetro [people] é o nome da tabela que está a ser descrita;
    • linha 16: o segundo parâmetro [metadata] é a instância [MetaData] criada na linha 13;
    • linhas 17–22: cada um dos parâmetros seguintes descreve uma coluna da tabela utilizando sintaxe específica do [SQLAlchemy], mas semelhante à sintaxe SQL;
    • cada coluna é descrita utilizando uma instância da classe [Column] do [sqlalchemy];
      • o primeiro parâmetro é o nome da coluna;
      • o segundo parâmetro é o seu tipo;
      • os parâmetros seguintes são parâmetros nomeados:
        • Linha 17: [primary_key=True] para indicar que a coluna [id] é a chave primária da tabela [people];
        • linha 18: [nullable=False] para indicar que uma coluna deve ter um valor quando uma linha é inserida na tabela;
    • linha 21: finalmente, a classe [UniqueConstraint] permite definir uma restrição de exclusividade. Aqui, especificamos que as colunas (last_name, first_name) devem ser únicas dentro da tabela. A propriedade denominada [name] permite atribuir um nome a esta restrição. Aqui, há dois casos a considerar:
      • estamos a descrever uma tabela existente. Nesse caso, temos de procurar o nome da restrição nas propriedades da tabela (phpMyAdmin ou pgAdmin);
      • estamos a descrever uma tabela que estamos prestes a criar. Nesse caso, introduzimos o nome que pretendemos;
  • linhas 23–25: criamos uma pessoa [person1] e exibimos o seu dicionário [__dict__]. Aqui teremos: 
personne1={'_BaseEntity__id': 67, '_Personne__prénom': 'x', '_Personne__nom': 'y', '_Personne__âge': 10}
  • linhas 27–33: realizamos um mapeamento, ou seja, criamos uma correspondência entre a classe [Person] e a tabela [people]. Trata-se essencialmente de um mapeamento [propriedades da classe  colunas da tabela]. A função [mapper] recebe aqui três parâmetros:
    • linha 28: o primeiro parâmetro é o nome da classe para a qual o mapeamento está a ser realizado;
    • linha 28: o segundo parâmetro é a tabela à qual será associada. Trata-se do objeto [Table] criado na linha 16;
    • linha 28: o terceiro parâmetro aqui é um parâmetro denominado [properties]. Trata-se de um dicionário em que as chaves são as propriedades da classe mapeada e os valores são as colunas da tabela mapeada. Para referir-nos à coluna X da tabela [personnes_table], escrevemos [personnes_table.c.X];
  • linhas 35–36: exibimos novamente a pessoa [person1] assim que o mapeamento estiver concluído. Vemos que ela não sofreu alterações: 
personne1={'_BaseEntity__id': 67, '_Personne__prénom': 'x', '_Personne__nom': 'y', '_Personne__âge': 10}
  • linhas 37-39: criamos uma nova pessoa [person2] e exibimo-la. Vemos então o seguinte resultado: 
personne2={'_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x00000259A6747FA0>, 'id': 68, 'prénom': 'x1', 'nom': 'y1', 'âge': 11}

Podemos ver que o dicionário [__dict__] foi significativamente modificado:

  • (continuação)
    • aparece uma nova propriedade [_sa_instance_state]. Podemos ver que se trata de um objeto ORM [sqlalchemy];
    • as outras propriedades tiveram os seus prefixos removidos, que anteriormente indicavam a que classe pertenciam;

Podemos, portanto, concluir que a operação de mapeamento nas linhas 27–33 modificou a classe [Person].

Quando queremos exibir o estado de um objeto [Person], geralmente não queremos a propriedade [_sa_instance_state]. Ela existe exclusivamente para o funcionamento interno do [SQLAlchemy] e, em geral, não nos interessa. É por isso que escrevemos no script [config]:

1
2
3
    #  class configuration
    from Personne import Personne
    Personne.excluded_keys = ['_sa_instance_state']

19.2.3. O script [main]

O script [main] irá manipular a tabela [people] na base de dados MySQL [dbpeople] através da interface com [sqlalchemy]. Para compreender o que se segue, precisamos de recordar a arquitetura aqui utilizada:

Image

Se [Database1] for a base de dados [dbpersonnes], podemos ver que a ligação entre o script e esta base de dados envolve dois componentes:

  • o conector Python para o SGBD MySQL;
  • o SGBD MySQL;

O script [main] irá comunicar com o ORM, que, por sua vez, irá comunicar com o conector Python. O ORM comunica com este conector utilizando as ferramentas descritas nas secções |MySQL| e |PostgreSQL|, nomeadamente através da emissão de comandos SQL. O script [main] não utilizará comandos SQL. Irá basear-se na API (Interface de Programação de Aplicações) do ORM, que consiste em classes e interfaces.

O script [main] é o seguinte:

 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
#  configure the application
import config

config = config.configure()

#  imports
from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, UniqueConstraint
from sqlalchemy.exc import IntegrityError, InterfaceError
from sqlalchemy.orm import mapper, sessionmaker

from Personne import Personne

#  database connection string MySQL
engine = create_engine("mysql+mysqlconnector://admpersonnes:nobody@localhost/dbpersonnes")

#  metadata
metadata = MetaData()

#  the table
personnes_table = Table("personnes", metadata,
                        Column('id', Integer, primary_key=True),
                        Column('prenom', String(30), nullable=False),
                        Column("nom", String(30), nullable=False),
                        Column("age", Integer, nullable=False),
                        UniqueConstraint('nom', 'prenom', name='uix_1')
                        )

#  mapping
mapper(Personne, personnes_table, properties={
    'id': personnes_table.c.id,
    'prénom': personnes_table.c.prenom,
    'nom': personnes_table.c.nom,
    'âge': personnes_table.c.age
})

#  the factory session
Session = sessionmaker()
Session.configure(bind=engine)

session = None
try:
    #  a session
    session = Session()

    #  delete [people] table
    session.execute("drop table if exists personnes")

    #  table recreation from mapping
    metadata.create_all(engine)

    #  insertion
    session.add(Personne().fromdict({"id": 67, "prénom": "x", "nom": "y", "âge": 10}))
    #  session.commit()

    #  a request
    personnes = session.query(Personne).all()

    #  display
    print("Liste des personnes ---------")
    for personne in personnes:
        print(personne)

    #  two other insertions, the second of which fails due to uniqueness (first name, last name)
    session.add(Personne().fromdict({"id": 68, "prénom": "x1", "nom": "y1", "âge": 10}))
    session.add(Personne().fromdict({"id": 69, "prénom": "x1", "nom": "y1", "âge": 10}))

    #  a request
    personnes = session.query(Personne).all()

    #  display
    print("Liste des personnes ---------")
    for personne in personnes:
        print(personne)

    #  session validation
    session.commit()

except (InterfaceError, IntegrityError) as erreur:
    #  display
    print(f"L'erreur suivante s'est produite : {erreur}")
    #  cancellation of last session
    if session:
        print("rollback...")
        session.rollback()
finally:
    #  release session resources
    if session:
        session.close()

Comentários

  • linhas 1–4: a aplicação é configurada;
  • linhas 7–9: importamos toda uma série de classes e interfaces da biblioteca [sqlalchemy];
  • Linha 11: a classe [Person] é importada;
  • Linha 14: a cadeia de ligação à base de dados. Especifica:
    • o SGBD utilizado (mysql);
    • o conector Python utilizado (mysql.connector sem o .);
    • o utilizador que inicia sessão (admpersonnes);
    • a sua palavra-passe (nobody);
    • a máquina na qual o SGBD está localizado (localhost = a máquina na qual o script está a ser executado);
    • o nome da base de dados (dbpersonnes);

Com estas informações, o [sqlalchemy] pode ligar-se à base de dados. Note-se que o conector Python utilizado já deve estar instalado. O [sqlalchemy] não o instala.

  • linhas 19–26: descrição da tabela [people];
  • linhas 28–34: mapeamento entre a classe [Person] e a tabela [people];
  • linhas 36–38: a maioria das operações [sqlalchemy] é realizada no âmbito de uma sessão. O conceito de uma sessão [sqlalchemy] é semelhante ao de uma transação SQL. As sessões são criadas a partir da classe [Session] devolvida pela função [sessionmaker] na linha 37;
  • linha 38: a classe [Session] está associada à base de dados [dbpeople] através da cadeia de ligação na linha 14;
  • linha 43: é criada uma sessão. Como mencionado, uma sessão pode ser comparada a uma transação;
  • linhas 45–46: o método [Session.execute] permite que uma instrução SQL seja executada. Esta não é uma prática comum, uma vez que mencionámos que o ORM permite evitar o uso de SQL;
  • linhas 48–49: o método [metadata.create_all] cria todas as tabelas utilizando a instância [MetaData] da linha 17. Temos apenas uma: a tabela [people] definida nas linhas 20–26. O [SQLAlchemy] utilizará as informações destas linhas para criar a tabela. Aqui vemos uma vantagem fundamental do ORM: ele oculta os detalhes do SGBD. De facto, a instrução SQL [create] pode variar significativamente de um SGBD para outro devido aos tipos de dados atribuídos às colunas. Não existe uma padronização dos tipos de dados em SQL. Assim, a instrução [create] varia de um SGBD para outro. Aqui, graças ao [SQLAlchemy]:
    • descrevemos a tabela que queremos de uma forma única e consistente;
    • o [SQLAlchemy] consegue gerar a instrução [create] apropriada para o SGBD com o qual está a trabalhar;
  • linha 52: adicionamos um objeto [Person] à sessão. Isto não o adiciona automaticamente à base de dados. Na verdade, um ORM segue as suas próprias regras para sincronizar com a base de dados. Procurará sempre otimizar o número de consultas que efetua. Vejamos um exemplo. O script adiciona (add) duas pessoas (person1, person2) à sessão e, em seguida, faz uma consulta: pretende ver todas as pessoas na tabela. O [SQLAlchemy] pode proceder da seguinte forma:
    • a adição de [pessoa1] pode ser feita na memória. Não há necessidade de a colocar na base de dados por enquanto;
    • o mesmo se aplica a [pessoa2];
    • Segue-se a consulta [select]. Temos então de recuperar todas as linhas da tabela [people]. O [SQLAlchemy] irá então inserir [person1, person2] na base de dados e executar a consulta;

O [SQLAlchemy] irá, assim, realizar otimizações que são transparentes para o programador.

  • Linha 56: Para executar uma consulta [select] (quero ver…), usamos o método [Session.query]. O parâmetro para o método [query] é a classe mapeada para a tabela que está a ser consultada. Este método devolve um objeto [Query]. O método [Query.all] recupera todos os objetos [Person] da sessão. Ele retorna todas as linhas da tabela [people], cada uma na forma de um objeto [Person]. Para fazer isso, o [SQLAlchemy] usa o mapeamento estabelecido entre a classe [Person] e a tabela [people]. O resultado da linha 56 é uma lista de objetos [Person];
  • linhas 58–61: apresentamos os elementos da lista [people]. Como a classe [Person] deriva da classe [BaseEntity], o método [Person.__str__] utilizado aqui implicitamente na linha 61 é, na verdade, o método [BaseEntity.__str__], que devolve a cadeia JSON do objeto que o invoca. Esta string é a string JSON do dicionário [Person.asdict] (ver |BaseEntity|). Mencionámos que, após o mapeamento, encontraríamos a propriedade [_sa_instance_state] em cada objeto [Person]. No entanto, o valor desta propriedade não é do tipo [BaseEntity]. Deve, portanto, ser excluído do dicionário da classe [Person]; caso contrário, a exibição irá falhar. Foi isto que foi feito no script [config];
  • linhas 63–65: adicionamos mais duas pessoas que têm o mesmo nome e apelido. No entanto, existe uma restrição de unicidade na união destas duas colunas. Deverá, portanto, ocorrer um erro. É isto que estamos a tentar verificar;
  • linhas 67–68: solicitamos novamente a lista de todas as pessoas na base de dados;
  • linhas 70–73: e exibimo-las;
  • linhas 75-76: a sessão é confirmada. Como o nome indica, a transação subjacente será confirmada;
  • veremos durante a execução que as linhas 67–76 não serão executadas devido à exceção levantada pela linha 65. Passaremos então às linhas 78–84 para tratar da exceção;
  • Linha 78: A exceção [InterfaceError] ocorre se o [SQLAlchemy] não conseguir ligar-se à base de dados [dbpersonnes]. A exceção [IntegrityError] ocorre na linha 65;
  • linha 80: o erro é exibido;
  • linhas 82–84: se a sessão existir, revertemos a transação. Isto equivale a reverter a transação subjacente;
  • linhas 85–88: em todos os casos, quer ocorra um erro ou não, a sessão é encerrada para libertar recursos;

Os resultados da execução são os seguintes:


C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\venv\Scripts\python.exe C:/Data/st-2020/dev/python/cours-2020/python3-flask-2020/databases/sqlalchemy/01/main.py
Liste des personnes ---------
{"nom": "y", "prénom": "x", "id": 67, "âge": 10}
L'erreur suivante s'est produite : (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely)
(mysql.connector.errors.IntegrityError) 1062 (23000): Duplicate entry 'y1-x1' for key 'uix_1'
[SQL: INSERT INTO personnes (id, prenom, nom, age) VALUES (%(id)s, %(prenom)s, %(nom)s, %(age)s)]
[parameters: ({'id': 68, 'prenom': 'x1', 'nom': 'y1', 'age': 10}, {'id': 69, 'prenom': 'x1', 'nom': 'y1', 'age': 10})]
(Background on this error at: http://sqlalche.me/e/13/gkpj)
rollback...
 
Process finished with exit code 0
  • linhas 2-3: a lista de pessoas após a primeira inserção;
  • linha 5: a exceção [IntegrityError] que ocorreu quando duas pessoas com o mesmo nome e apelido foram adicionadas;
  • linhas 6-7: repare na instrução SQL que falhou. Trata-se de uma instrução INSERT parametrizada: o [SQLAlchemy] inseriu ambas as pessoas com um único INSERT. Aqui podemos ver que tentou otimizar as instruções SQL emitidas;

Agora vamos usar o phpMyAdmin para visualizar o conteúdo da tabela [people]:

Image

Podemos ver em [6] que a tabela está vazia. Nem mesmo a primeira pessoa que o script adicionou à sessão está lá. Isto deve-se ao facto de a sessão fazer parte de uma transação, e essa transação ter sido revertida na cláusula [except] do script [main].

Vamos agora fazer a seguinte alteração em [main]:

1
2
3
    #  insertion
    session.add(Personne().fromdict({"id": 67, "prénom": "x", "nom": "y", "âge": 10}))
    #  session.commit()

Depois de adicionar uma pessoa na linha 2, removemos o comentário da linha 3. A operação [session.commit] irá confirmar a transação subjacente e uma nova transação terá início. Após a execução, o conteúdo da tabela [people] é o seguinte:

Image

Podemos ver em [6] que a primeira inserção foi mantida. Isto deve-se ao facto de ter sido realizada na transação 1, enquanto o erro subsequente ocorreu na transação 2.

19.3. Scripts 02: mapeamentos [sqlalchemy]

Image

Os Scripts 02 são uma variação dos Scripts 01. Tentamos configurar o máximo possível em [config.py]. Configuramos agora o ambiente [sqlalchemy] da aplicação nesse ficheiro:

 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
def configure():
    #  absolute path configuration relative path reference
    root_dir = "C:/Data/st-2020/dev/python/cours-2020/python3-flask-2020"
    #  absolute paths of dependencies
    absolute_dependencies = [
        #  BaseEntity, MyException, Person, Utilities
        f"{root_dir}/classes/02/entities",
    ]

    #  set the syspath
    from myutils import set_syspath
    set_syspath(absolute_dependencies)

    #  imports
    from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, UniqueConstraint
    from sqlalchemy.orm import mapper, sessionmaker

    #  link to a database MySQL
    engine = create_engine("mysql+mysqlconnector://admpersonnes:nobody@localhost/dbpersonnes")

    #  metadata
    metadata = MetaData()

    #  the table
    personnes_table = Table("personnes", metadata,
                            Column('id', Integer, primary_key=True),
                            Column('prenom', String(30), nullable=False),
                            Column("nom", String(30), nullable=False),
                            Column("age", Integer, nullable=False),
                            UniqueConstraint('nom', 'prenom', name='uix_1')
                            )

    #  mapping
    from Personne import Personne

    mapper(Personne, personnes_table, properties={
        'id': personnes_table.c.id,
        'prénom': personnes_table.c.prenom,
        'nom': personnes_table.c.nom,
        'âge': personnes_table.c.age
    })

    #  the factory session
    Session = sessionmaker()
    Session.configure(bind=engine)

    #  we put this information in the config
    config = {}
    config["Session"] = Session
    config["metadata"] = metadata
    config["engine"] = engine
    config["personnes_table"] = personnes_table

    #  class configuration
    from Personne import Personne
    Personne.excluded_keys = ['_sa_instance_state']

    #  return the config
    return config

Comentários

  • linhas 2–12: configuração do caminho do Python;
  • linhas 14–45: configuração do ambiente [sqlalchemy];
  • linhas 47–52: o ambiente [sqlalchemy] é adicionado ao dicionário de configuração;
  • linhas 54–56: configuração da classe [Person];

Com esta configuração, o script [main] fica da seguinte forma:

 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
#  configure the application
import config

config = config.configure()

#  syspath is configured - imports are made
from sqlalchemy.exc import IntegrityError, DatabaseError, InterfaceError
from sqlalchemy.orm.exc import FlushError

from Personne import Personne

session = None
try:
    #  a session
    session = config["Session"]()

    #  delete [people] table
    session.execute("drop table if exists personnes")

    #  table recreation from mapping
    config["metadata"].create_all(config["engine"])

    #  two inserts
    session.add(Personne().fromdict({"prénom": "x", "nom": "y", "âge": 10}))
    personne = Personne().fromdict({"prénom": "x1", "nom": "y1", "âge": 7})
    session.add(personne)

    #  validation of the two inserts
    session.commit()

    #  a request
    personnes = session.query(Personne).all()

    #  display
    print("Liste des personnes-----------")
    for personne in personnes:
        print(personne)

    #  two other insertions, the second of which fails
    session.add(Personne().fromdict({"prénom": "x2", "nom": "y2", "âge": 10}))
    session.add(Personne().fromdict({"prénom": "x2", "nom": "y2", "âge": 10}))

    #  a request
    personnes = session.query(Personne).all()

    #  display
    print("Liste des personnes-----------")
    for personne in personnes:
        print(personne)

    #  session validation
    session.commit()

except (FlushError, DatabaseError, InterfaceError, IntegrityError) as erreur:
    #  display
    print(f"L'erreur suivante s'est produite : {erreur}")
    #  cancellation of last session
    if session:
        print("rollback...")
        session.rollback()
finally:
    #  display
    print("Travail terminé...")
    #  release session resources
    if session:
        session.close()

Os resultados da execução são os seguintes:


C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\venv\Scripts\python.exe C:/Data/st-2020/dev/python/cours-2020/python3-flask-2020/databases/sqlalchemy/02/main.py
Liste des personnes-----------
{"âge": 10, "nom": "y", "prénom": "x", "id": 1}
{"âge": 7, "nom": "y1", "prénom": "x1", "id": 2}
L'erreur suivante s'est produite : (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely)
(mysql.connector.errors.IntegrityError) 1062 (23000): Duplicate entry 'y2-x2' for key 'uix_1'
[SQL: INSERT INTO personnes (prenom, nom, age) VALUES (%(prenom)s, %(nom)s, %(age)s)]
[parameters: {'prenom': 'x2', 'nom': 'y2', 'age': 10}]
(Background on this error at: http://sqlalche.me/e/13/gkpj)
rollback...
Travail terminé...
 
Process finished with exit code 0

No phpMyAdmin, a tabela [people] tem agora o seguinte aspeto:

Image

Agora, vamos ver a tabela [people] gerada pelo [SQLAlchemy]:

Image

  • Em [6], os tipos utilizados para as diferentes colunas;
  • em [7], vemos que a coluna [id] possui o atributo [AUTO_INCREMENT]. Isto significa que, ao inserir uma linha na tabela, se essa linha não tiver um valor para a coluna [id], o MySQL irá gerá-lo de forma incremental: 1, 2, 3, … Esta propriedade evita que tenhamos de nos preocupar com o valor da chave primária ao inserir dados na tabela: deixamos que o MySQL o gere;
  • em [8], vemos que a coluna [id] é a chave primária;
  • Em [9], vemos a restrição de unicidade nos campos [last_name, first_name];

19.4. Scripts 03: manipulação de entidades de sessão [sqlalchemy]

Image

O ficheiro de configuração [config] é o mesmo do exemplo anterior. No script [main], realizamos operações padrão [INSERT, UPDATE, DELETE, SELECT] na tabela [people] utilizando métodos [SQLAlchemy]:

  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
 94
 95
 96
 97
 98
 99
100
#  configure the application
import config

config = config.configure()

#  imports
from sqlalchemy import func
from sqlalchemy.exc import IntegrityError, DatabaseError, InterfaceError
from sqlalchemy.orm.session import Session
from Personne import Personne

#  displays the contents of table [people]
def affiche_table(session: Session):
    print("----------------")
    #  a request
    personnes = session.query(Personne).all()
    #  display
    affiche_personnes(personnes)

#  displays a list of people
def affiche_personnes(personnes: list):
    print("----------------")
    #  display
    for personne in personnes:
        print(personne)


#  main ---------------------------
session = None
try:
    #  a session
    session = config["Session"]()

    #  delete [people] table
    #  checkfirst=True: first checks that the table exists
    config["personnes_table"].drop(config["engine"], checkfirst=True)

    #  table recreation from mapping
    config["metadata"].create_all(config["engine"])

    #  inserts
    session.add(Personne().fromdict({"prénom": "Pierre", "nom": "Nicazou", "âge": 35}))
    session.add(Personne().fromdict({"prénom": "Géraldine", "nom": "Colou", "âge": 26}))
    session.add(Personne().fromdict({"prénom": "Paulette", "nom": "Girondé", "âge": 56}))

    #  displays the session content
    affiche_table(session)

    #  list of persons in alphabetical order of surnames and, for equal surnames, in alphabetical order of first names
    personnes = session.query(Personne).order_by(Personne.nom.desc(), Personne.prénom.desc())

    #  display
    affiche_personnes(personnes)

    #  list of people with an age in the range [20,40] in descending order of age
    #  then for equal ages in alphabetical order of surnames and for equal surnames in alphabetical order of first names
    personnes = session.query(Personne). \
        filter(Personne.âge >= 20, Personne.âge <= 40). \
        order_by(Personne.âge.desc(), Personne.nom.asc(), Personne.prénom.asc())

    #  display
    affiche_personnes(personnes)

    #  insertion of mrs Bruneau
    bruneau = Personne().fromdict({"prénom": "Josette", "nom": "Bruneau", "âge": 46})
    session.add(bruneau)
    #  change of age
    bruneau.âge = 47

    #  list of people named Bruneau
    personne = session.query(Personne).filter(func.lower(Personne.nom) == "bruneau").first()

    #  display
    affiche_personnes([personne])

    #  deletion of Mme Bruneau
    session.delete(personne)

    #  list of people named Bruneau
    personnes = session.query(Personne).filter(func.lower(Personne.nom) == "bruneau")

    #  display
    affiche_personnes(personnes)

    #  session validation
    session.commit()

except (DatabaseError, InterfaceError, IntegrityError) as erreur:
    #  display
    print(f"L'erreur suivante s'est produite : {erreur}")
    #  cancellation of last session
    if session:
        session.rollback()

finally:
    #  display
    print("Travail terminé...")
    #  release session resources
    if session:
        session.close()

Comentários

  • linhas 20–25: a função [display_people] apresenta os itens de uma lista de pessoas;
  • linhas 12–18: a função [display_people] exibe o conteúdo da tabela [people];
  • Linhas 34–36: Eliminamos a tabela [people]. Ao contrário das versões anteriores, não utilizamos uma consulta SQL, mas sim um método [SQLAlchemy]:
    • config["people_table"] é o objeto [Table] que descreve a tabela [people];
    • config["engine"] é a cadeia de ligação à base de dados [dbpersonnes];
    • o parâmetro denominado [checkfirst=True] garante que a operação só é executada se a tabela [people] existir;
  • linhas 38–39: a tabela [people] é recriada;
  • linhas 41–44: são adicionadas três pessoas à sessão. Note-se que estas não são necessariamente inseridas imediatamente na tabela [people]. Isto depende da estratégia orientada para o desempenho do [SQLAlchemy];
  • linhas 46–47: o conteúdo da tabela [people] é exibido. Se as três pessoas ainda não tivessem sido inseridas, elas são agora inseridas devido a esta solicitação;
  • Linhas 49-50: Um exemplo de utilização do método [order_by], que permite que os resultados da consulta sejam apresentados numa ordem específica. A sintaxe [order_by(criterion1, criterion2)] apresenta os resultados primeiro de acordo com o critério [criterion1] e, quando as linhas têm o mesmo valor para [criterion1], são então ordenadas de acordo com o critério [criterion2]. É possível especificar vários critérios desta forma;
  • linhas 55–59: introduzem o conceito de filtragem utilizando o método [filter]. A notação [filter(criterion1, criterion2)] realiza uma operação lógica AND entre os critérios utilizados;
  • linhas 64–67: um novo utilizador inicia sessão;
  • linhas 70–71: outro exemplo de uma consulta filtrada. A função [func.lower(param)] converte [param] para minúsculas. Existem outras funções disponíveis, indicadas como [func.xx]. Na expressão da linha 71:
    • [session.query.filter] devolve uma lista de objetos [Person];
    • [session.query.filter.first] devolve o primeiro elemento desta lista;
  • linha 77: um elemento é removido da sessão;
  • linha 86: a sessão é validada;

Os resultados da execução são os seguintes:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\venv\Scripts\python.exe C:/Data/st-2020/dev/python/cours-2020/python3-flask-2020/databases/sqlalchemy/03/main.py
----------------
----------------
{"âge": 35, "nom": "Nicazou", "prénom": "Pierre", "id": 1}
{"âge": 26, "nom": "Colou", "prénom": "Géraldine", "id": 2}
{"âge": 56, "nom": "Girondé", "prénom": "Paulette", "id": 3}
----------------
{"âge": 35, "nom": "Nicazou", "prénom": "Pierre", "id": 1}
{"âge": 56, "nom": "Girondé", "prénom": "Paulette", "id": 3}
{"âge": 26, "nom": "Colou", "prénom": "Géraldine", "id": 2}
----------------
{"âge": 35, "nom": "Nicazou", "prénom": "Pierre", "id": 1}
{"âge": 26, "nom": "Colou", "prénom": "Géraldine", "id": 2}
----------------
{"prénom": "Josette", "nom": "Bruneau", "âge": 47, "id": 4}
----------------
Travail terminé...

Process finished with exit code 0
  • linhas 4-6: o conteúdo da sessão;
  • linhas 8-10: o conteúdo da sessão por ordem decrescente de nomes;
  • linhas 12–13: o conteúdo da sessão para pessoas cuja idade está no intervalo [20, 40];
  • linha 15: a pessoa chamada «bruneau»;

No phpMyAdmin, o conteúdo da tabela [people] no final da execução é o seguinte:

Image

19.5. Scripts 04: Utilização de uma base de dados [PostgreSQL]

Image

A pasta [04] é uma cópia da pasta [03]. Alteramos apenas uma coisa: a cadeia de ligação no ficheiro [config]:


    # lien vers une base de données PostgreSQL
    engine = create_engine("postgresql+psycopg2://admpersonnes:nobody@localhost/dbpersonnes")

Esta cadeia de ligação refere-se agora à base de dados [dbpersonnes] num SGBD [PostgreSQL]. Repare na utilização do conector [psycopg2]. Este deve estar instalado.

A execução do script [main] produz os seguintes resultados:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\venv\Scripts\python.exe C:/Data/st-2020/dev/python/cours-2020/python3-flask-2020/databases/sqlalchemy/04/main.py
----------------
----------------
{"nom": "Nicazou", "prénom": "Pierre", "id": 1, "âge": 35}
{"nom": "Colou", "prénom": "Géraldine", "id": 2, "âge": 26}
{"nom": "Girondé", "prénom": "Paulette", "id": 3, "âge": 56}
----------------
{"nom": "Nicazou", "prénom": "Pierre", "id": 1, "âge": 35}
{"nom": "Girondé", "prénom": "Paulette", "id": 3, "âge": 56}
{"nom": "Colou", "prénom": "Géraldine", "id": 2, "âge": 26}
----------------
{"nom": "Nicazou", "prénom": "Pierre", "id": 1, "âge": 35}
{"nom": "Colou", "prénom": "Géraldine", "id": 2, "âge": 26}
----------------
{"prénom": "Josette", "nom": "Bruneau", "âge": 47, "id": 4}
----------------
Travail terminé...

Process finished with exit code 0

Ao utilizar a ferramenta [pgAdmin] (consulte a secção |pgAdmin|), a tabela [people] encontra-se no seguinte estado:

Image

A tabela [people] foi gerada com o seguinte código SQL:

Image

  • Em [4-5], vemos que a coluna [id] é a chave primária. Vemos também que tem um valor por defeito [palavra-chave DEFAULT], o que significa que, se for inserida uma linha sem uma chave primária, o SGBD irá gerar uma. Esta é uma prática comum: deixamos que o SGBD gere as chaves primárias;

Esta versão 05 dos scripts [sqlalchemy] demonstra claramente como é fácil mudar de um SGBD para outro: bastou alterar a string de ligação num script de configuração. Nada mais mudou. Se compararmos os tipos de coluna [id, last_name, first_name, age] acima com os da tabela MySQL do exemplo |02|, vemos que são diferentes. O [sqlalchemy] adapta-os ao SGBD que está a ser utilizado. Esta capacidade de adaptação a um novo SGBD é motivo suficiente para adotar o [sqlalchemy] ou outro ORM.

19.6. Scripts 05: Exemplo Completo

Image

O exemplo que estamos a analisar é uma reformulação do abordado na secção |troiscouches-v01|. Esse exemplo apresentava uma arquitetura de três camadas [UI, lógica de negócio, DAO] que manipulava entidades [Class, Student, Subject, Grade]. As entidades estavam codificadas de forma rígida numa camada [DAO]. Agora, estamos a colocá-las numa base de dados. Iremos utilizar dois SGBDs: MySQL e PostgreSQL.

19.6.1. A arquitetura da aplicação

A arquitetura da aplicação será a seguinte:

Image

  • Em [1-3], encontramos as camadas [UI, Negócio, DAO] já presentes no exemplo |troiscouches-v01|. A camada [DAO] comunica agora com a camada [ORM];
  • as camadas [1-5] são implementadas utilizando código Python;

19.6.2. As bases de dados

Estamos a criar uma base de dados MySQL denominada [dbecole], pertencente ao utilizador [admecole] com a palavra-passe [mdpecole]. Para tal, seguimos o procedimento descrito na secção |Criação de uma Base de Dados|:

Image

Image

  • em [1], a base de dados [dbecole] não tem tabelas [3];
  • em [7], o utilizador [admecole] tem privilégios totais nesta base de dados;

Fazemos o mesmo com o SGBD PostgreSQL. Criamos uma base de dados chamada [dbecole], pertencente ao utilizador [admecole] e com a palavra-passe [mdpecole]. Para tal, seguimos o procedimento descrito na secção |criar uma base de dados|:

Image

  • em [1], a base de dados [dbecole];
  • em [2], o utilizador [admecole];
  • em [3-4], a base de dados [dbecole] pertence ao utilizador [admecole];

19.6.3. Entidades tratadas pela aplicação

Na aplicação |troiscouches v01|, as entidades tratadas eram as seguintes (ver |entities|). Estas são as entidades que serão armazenadas nas bases de dados anteriores. Não iremos duplicar estas entidades na nova aplicação. Iremos recuperá-las de onde já estão definidas.

A classe [Class]:

 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
#  imports
from BaseEntity import BaseEntity
from MyException import MyException
from Utils import Utils


class Classe(BaseEntity):
    #  attributes excluded from class state
    excluded_keys = []

    #  class properties
    @staticmethod
    def get_allowed_keys() -> list:
        #  id: class identifier
        #  name: class name
        return BaseEntity.get_allowed_keys() + ["nom"]

    #  getter
    @property
    def nom(self: object) -> str:
        return self.__nom

    #   setters
    @nom.setter
    def nom(self: object, nom: str):
        #  name must be a non-empty string
        if Utils.is_string_ok(nom):
            self.__nom = nom
        else:
            raise MyException(11, f"Le nom de la classe {self.id} doit être une chaîne de caractères non vide")

A classe [Student]:

 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
#  imports
from BaseEntity import BaseEntity
from Classe import Classe
from MyException import MyException

from Utils import Utils


class Elève(BaseEntity):
    #  attributes excluded from class state
    excluded_keys = []

    #  class properties
    @staticmethod
    def get_allowed_keys() -> list:
        #  id: student identifier
        #  name: student's name
        #  first name: student's first name
        #  class: student's class
        return BaseEntity.get_allowed_keys() + ["nom", "prénom", "classe"]

    #  getters
    @property
    def nom(self: object) -> str:
        return self.__nom

    @property
    def prénom(self: object) -> str:
        return self.__prénom

    @property
    def classe(self: object) -> Classe:
        return self.__classe

    #   setters
    @nom.setter
    def nom(self: object, nom: str) -> str:
        #  name must be a non-empty string
        if Utils.is_string_ok(nom):
            self.__nom = nom
        else:
            raise MyException(41, f"Le nom de l'élève {self.id} doit être une chaîne de caractères non vide")

    @prénom.setter
    def prénom(self: object, prénom: str) -> str:
        #  first name must be a non-empty string
        if Utils.is_string_ok(prénom):
            self.__prénom = prénom
        else:
            raise MyException(42, f"Le prénom de l'élève {self.id} doit être une chaîne de caractères non vide")

    @classe.setter
    def classe(self: object, value):
        try:
            #  we expect a Class type
            if isinstance(value, Classe):
                self.__classe = value
            #  or a type dict
            elif isinstance(value,dict):
                self.__classe=Classe().fromdict(value)
            #  or a json type
            elif isinstance(value,str):
                self.__classe = Classe().fromjson(value)
        except BaseException as erreur:
            raise MyException(43, f"L'attribut [{value}] de l'élève {self.id} doit être de type Classe ou dict ou json. Erreur : {erreur}")

A classe [Subject]:

 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
#  imports
from BaseEntity import BaseEntity
from MyException import MyException
from Utils import Utils


class Matière(BaseEntity):
    #  attributes excluded from class state
    excluded_keys = []

    #  class properties
    @staticmethod
    def get_allowed_keys() -> list:
        #  id: material identifier
        #  name: material name
        #  coefficient: subject coefficient
        return BaseEntity.get_allowed_keys() + ["nom", "coefficient"]

    #  getter
    @property
    def nom(self: object) -> str:
        return self.__nom

    @property
    def coefficient(self: object) -> float:
        return self.__coefficient

    #   setters
    @nom.setter
    def nom(self: object, nom: str):
        #  name must be a non-empty string
        if Utils.is_string_ok(nom):
            self.__nom = nom
        else:
            raise MyException(21, f"Le nom de la matière {self.id} doit être une chaîne de caractères non vide")

    @coefficient.setter
    def coefficient(self, coefficient: float):
        #  the coefficient must be a real number >=0
        erreur = False
        if isinstance(coefficient, (int, float)):
            if coefficient >= 0:
                self.__coefficient = coefficient
            else:
                erreur = True
        else:
            erreur = True
        #  mistake?
        if erreur:
            raise MyException(22, f"Le coefficient de la matière {self.nom} doit être un réel >=0")

A classe [Grade]:

 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
#  imports
from BaseEntity import BaseEntity
from Elève import Elève
from Matière import Matière
from MyException import MyException


class Note(BaseEntity):
    #  attributes excluded from class state
    excluded_keys = []

    #  class properties
    @staticmethod
    def get_allowed_keys() -> list:
        #  id: note identifier
        #  value: the note itself
        #  student: student (of type Student) concerned by the note
        #  subject: subject (of type Subject) concerned by the grade
        #  the Note object is therefore a student's grade in a subject
        return BaseEntity.get_allowed_keys() + ["valeur", "élève", "matière"]

    #  getters
    @property
    def valeur(self: object) -> float:
        return self.__valeur

    @property
    def élève(self: object) -> Elève:
        return self.__élève

    @property
    def matière(self: object) -> Matière:
        return self.__matière

    #  getters
    @valeur.setter
    def valeur(self: object, valeur: float):
        #  the score must be a real number between 0 and 20
        if isinstance(valeur, (int, float)) and 0 <= valeur <= 20:
            self.__valeur = valeur
        else:
            raise MyException(31,
                f"L'attribut {valeur} de la note {self.id} doit être un nombre dans l'intervalle [0,20]")

    @élève.setter
    def élève(self: object, value):
        try:
            #  we expect a Student type
            if isinstance(value, Elève):
                self.__élève = value
            #  or a type dict
            elif isinstance(value, dict):
                self.__élève = Elève().fromdict(value)
            #  or a json type
            elif isinstance(value, str):
                self.__élève = Elève().fromjson(value)
        except BaseException as erreur:
            raise MyException(32,
                f"L'attribut [{value}] de la note {self.id} doit être de type Elève ou dict ou json. Erreur : {erreur}")

    @matière.setter
    def matière(self: object, value):
        try:
            #  we expect a Material type
            if isinstance(value, Matière):
                self.__matière = value
            #  or a type dict
            elif isinstance(value, dict):
                self.__matière = Matière().fromdict(value)
            #  or a json type
            elif isinstance(value, str):
                self.__matière = Matière().fromjson(value)
        except BaseException as erreur:
            raise MyException(33,
                f"L'attribut [{value}] de la note {self.id} doit être de type Matière ou dict ou json. Erreur : {erreur}")

19.6.4. Configuração

Image

A configuração foi dividida em vários ficheiros:

  • configuração geral em [config.py]: define o Python Path da aplicação e instancia as camadas de arquitetura;
  • configuração [SQLAlchemy] em [config_database]: trata dos mapeamentos Classe/Tabela;
  • As camadas da aplicação são configuradas em [config_layers];

O ficheiro [config] é o seguinte:

 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
def configure(config: dict) -> dict:
    import os

    #  step 1 ---
    #  establish the application's Python Path
    #  absolute path of this script's folder
    script_dir = os.path.dirname(os.path.abspath(__file__))

    #  absolute path configuration relative path reference
    root_dir = "C:/Data/st-2020/dev/python/cours-2020/python3-flask-2020"

    #  absolute paths of dependencies
    absolute_dependencies = [
        #  BaseEntity, MyException
        f"{root_dir}/classes/02/entities",
        #  projet troiscouches v01
        f"{root_dir}/troiscouches/v01/interfaces",
        f"{root_dir}/troiscouches/v01/services",
        f"{root_dir}/troiscouches/v01/entities",
        #  project files
        script_dir,
        f"{script_dir}/../services",
    ]

    #  update syspath
    from myutils import set_syspath
    set_syspath(absolute_dependencies)

    #  step 2 ------
    #  database configuration
    import config_database
    config = config_database.configure(config)

    #  step 3 ------
    #  instantiation of application layers
    import config_layers
    config = config_layers.configure(config)

    #  return the config
    return config
  • linhas 4–27: construção do Python Path da aplicação;
  • linhas 29–32: configuração do [SQLAlchemy];
  • linhas 34–37: configuração das camadas da aplicação;

O ficheiro [config_database] é o seguinte:

  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
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
def configure(config: dict) -> dict:
    #  config['sgbd'] is the name of the SGBD used
    #  mysql : MySQL
    #  pgres : PostgreSQL

    #  sqlalchemy configuration
    from sqlalchemy import Table, Column, Integer, MetaData, String, Float, ForeignKey, create_engine

    from sqlalchemy.orm import mapper, relationship, sessionmaker

    #  connection chains to the databases used
    engines = {
        'mysql': "mysql+mysqlconnector://admecole:mdpecole@localhost/dbecole",
        'pgres': "postgresql+psycopg2://admecole:mdpecole@localhost/dbecole"
    }
    #  connection chain to the database used
    engine = create_engine(engines[config['sgbd']])

    #  metadata
    metadata = MetaData()

    #  database tables
    tables = {}
    #  mapped classes
    from Classe import Classe
    from Elève import Elève
    from Note import Note
    from Matière import Matière

    #  the class table
    tables['classes'] = classes_table = \
        Table("classes", metadata,
              Column('id', Integer, primary_key=True),
              Column('nom', String(30), nullable=False),
              )

    mapper(Classe, tables['classes'], properties={
        'id': classes_table.c.id,
        'nom': classes_table.c.nom
    })

    #  the student table
    tables['élèves'] = élèves_table = \
        Table("élèves", metadata,
              Column('id', Integer, primary_key=True),
              Column('nom', String(30), nullable=False),
              Column('prénom', String(30), nullable=False),
              #  a student belongs to a class
              Column('classe_id', Integer, ForeignKey('classes.id')),
              )
    #  mapping
    mapper(Elève, tables['élèves'], properties={
        'id': élèves_table.c.id,
        'nom': élèves_table.c.nom,
        'prénom': élèves_table.c.prénom,
        'classe': relationship(Classe, backref="élèves", lazy="select")
    })

    #  table of contents
    tables['matières'] = matières_table = \
        Table("matières", metadata,
              Column('id', Integer, primary_key=True),
              Column('nom', String(30), nullable=False),
              Column('coefficient', Float, nullable=False)
              )
    #  mapping
    mapper(Matière, tables['matières'], properties={
        'id': matières_table.c.id,
        'nom': matières_table.c.nom,
        "coefficient": matières_table.c.coefficient
    })

    #  notes table
    tables['notes'] = notes_table = \
        Table("notes", metadata,
              Column('id', Integer, primary_key=True),
              Column('valeur', Float, nullable=False),
              #  a grade is a student's grade
              Column('élève_id', Integer, ForeignKey('élèves.id')),
              #  a grade is a subject grade
              Column('matière_id', Integer, ForeignKey('matières.id')),
              )

    #  mapping
    mapper(Note, tables['notes'], properties={
        'id': notes_table.c.id,
        'valeur': notes_table.c.valeur,
        'élève': relationship(Elève, backref="notes", lazy="select"),
        'matière': relationship(Matière, backref="notes", lazy="select")
    })

    #  entity configuration [BaseEntity]
    Elève.excluded_keys = ['_sa_instance_state', 'notes', 'classe']
    Classe.excluded_keys = ['_sa_instance_state', 'élèves']
    Matière.excluded_keys = ['_sa_instance_state', 'notes']
    Note.excluded_keys = ['_sa_instance_state', 'matière', 'élève']

    #  the factory session
    Session = sessionmaker()
    Session.configure(bind=engine)

    #  a session
    session = Session()

    #  certain information is stored in the configuration dictionary
    config['database'] = {"engine": engine, "metadata": metadata, "tables": tables, "session": session}

    #  return the config
    return config

Comentários

  • linhas 1-4: a função [configure] recebe um dicionário como parâmetro. Apenas a chave [db] é utilizada. É definida como [mysql] se a base de dados for uma base de dados MySQL, [pgres] se a base de dados for uma base de dados PostgreSQL;
  • linhas 6-9: importações de elementos de [sqlalchemy]. O script [config_database] realiza os mapeamentos entre as tabelas da base de dados [dbecole] e as entidades [Classes, Student, Subject, Grade]. Na tabela, os dados da entidade estão encapsulados numa linha. No código Python, estão encapsulados num objeto. Daí o nome ORM (Object Relational Mapper): o ORM estabelece um mapeamento (uma ligação) entre as linhas de uma base de dados relacional e os objetos. Nesta aplicação, temos quatro entidades [Class, Student, Subject, Grade] que serão ligadas a quatro tabelas [classes, students, subjects, grades]. Note-se que os nomes das tabelas podem conter caracteres acentuados;
  • linhas 11–17: a cadeia de ligação à base de dados que está a ser utilizada. Isto depende de config['db'].
  • linhas 24–28: as entidades da aplicação que serão mapeadas [SQLAlchemy]. Quando estas linhas forem executadas, o Python Path já terá sido estabelecido pelo script [config];
  • linhas 30–40: o mapeamento entre a entidade [Class] e a tabela [classes];
  • linhas 30–35: a tabela [classes] é definida utilizando a classe [Table] do [sqlalchemy]. Especificamos que esta tabela tem duas colunas:
    • a coluna [id], que é a chave primária e representa o número da classe, linha 33;
    • a coluna [nome], que contém o nome da classe, linha 34;
  • linhas 31–32: note que a sintaxe x=y=z é válida em Python: o valor de z é atribuído a y, depois o valor de y a x;
  • linhas 37–40: são listados os mapeamentos entre as colunas da tabela [classes] e as propriedades da entidade [Class];
  • linhas 42–57: o mapeamento entre a entidade [Student] e a tabela [students];
  • linhas 51–57: a tabela [students] é definida utilizando a classe [Table] do [SQLAlchemy]. Especificamos que esta tabela tem quatro colunas:
    • a coluna [id], que é a chave primária e representa o ID do aluno, linha 45;
    • a coluna [name], que contém o apelido do aluno, linha 46;
    • a coluna [first_name], que contém o nome próprio do aluno, linha 47. Note-se que um nome de coluna pode conter caracteres acentuados;
    • linha 49, a coluna [class_id], que conterá o ID da turma à qual o aluno pertence. Isto é chamado de chave estrangeira. [students.class_id] é uma chave estrangeira (ForeignKey) na coluna [classes.id]. Isto significa que o valor de [students.class_id] deve existir na coluna [classes.id];
  • Linhas 51–57: Listamos os mapeamentos entre as colunas da tabela [students] e as propriedades da entidade [Student]:
    • As linhas 53–55 são fáceis de compreender;
    • a linha 56 é mais difícil: define o valor da propriedade [Student.class] como sendo calculado pela relação de chave estrangeira que liga as tabelas [students] e [classes]. Os parâmetros da função [relationship] são os seguintes:
      • [Class]: este é o nome da entidade com a qual a entidade [Student] tem uma relação de chave estrangeira. Isto deve estar refletido na tabela [students] através da presença de uma chave estrangeira que faz referência à tabela [classes]. Sabemos que isto existe;
      • [backref="students"]: o nome de uma propriedade que será adicionada à entidade [Class]. [Class.students] será a lista de todos os alunos da turma. Esta propriedade não deve já existir. Se já existir, basta escolher aqui um nome diferente para [backref]. O programador não precisa de gerir esta propriedade. O [SQLAlchemy] tratará disso. O programador precisa apenas de saber que ela existe, adicionada pelo [SQLAlchemy], e que pode utilizá-la no seu código;
      • [lazy='select']: isto significa que o ORM não deve tentar atribuir imediatamente um valor à propriedade [Student.class]. Só deve recuperar o seu valor quando o código o solicitar explicitamente. Assim:
      • se o código solicitar uma lista de todos os alunos, estes serão devolvidos, mas a sua propriedade [class] não será calculada;
      • um pouco mais tarde, o código foca-se num aluno específico [e] e faz referência à sua turma [e.class]. Esta referência irá então forçar o [SQLAlchemy] a fazer uma consulta à base de dados para recuperar a turma do aluno, tudo de forma transparente para o programador;
      • definir [lazy='select'] também tem como objetivo evitar consultas desnecessárias à base de dados;
  • Linha 56: Quando o ORM recupera uma linha da tabela [students], recupera os campos [id, last_name, first_name, class_id]. A partir daí, deve construir um objeto Student (id, last_name, first_name, class). Para as propriedades [id, last_name, first_name], isto não apresenta qualquer dificuldade. Para a propriedade [class], é mais complicado. O seu valor é uma referência a um objeto do tipo [Class]. No entanto, o ORM dispõe apenas de uma informação: [students.class_id]. Uma vez que [students.class_id] é uma chave estrangeira na coluna [classes.id], instruímos aqui o ORM a utilizar esta relação para recuperar a linha com id=[students.class_id] da tabela [classes] (que deve existir) e a criar o objeto [Class] esperado pela propriedade [Student.class] a partir dessa linha;
  • linhas 59–71: o mapeamento entre a entidade [Subject] e a tabela [subjects];
  • linhas 59–65: definição da tabela [SQLAlchemy] denominada [subjects];
  • linhas 66–71: listamos os mapeamentos entre as colunas da tabela [subjects] e as propriedades da entidade [Subject]. Não há dificuldades aqui;
  • linhas 73–90: o mapeamento entre a entidade [Note] e a tabela [notes];
  • linhas 73-82: definição da tabela [sqlalchemy] denominada [notes]. Possui duas chaves estrangeiras:
    • linha 79, a coluna [notes.student_id] obtém os seus valores da coluna [students.id]. Esta chave estrangeira reflete o facto de que uma nota pertence a um aluno específico;
    • linha 81: a coluna [notes.subject_id] obtém os seus valores da coluna [subjects.id]. Esta chave estrangeira representa o facto de uma nota ser uma nota numa disciplina específica;
  • Linhas 84–90: o mapeamento entre a entidade [Note] e a tabela [notes]:
    • linha 88: a propriedade [Note.student] deve ter um valor de uma instância do tipo [Student]. O ORM tem apenas a coluna [notes.student_id] na linha da tabela [notes], que faz referência à coluna [students.id]. Aqui, especificamos que se utilize esta relação de chave estrangeira para recuperar a instância [Student] para a qual temos a nota. Além disso, [relationship(Student, backref="grades", …)] criará a nova propriedade [Student.grades], que será a lista das notas do aluno. Esta propriedade não deve já existir na classe [Student];
    • Linha 89: A propriedade [Grade.subject] deve ter como valor uma instância do tipo [Subject]. O ORM dispõe apenas da coluna [notes.subject_id] na linha da tabela [grades], que faz referência à coluna [subjects.id]. Aqui, estamos a especificar a utilização desta relação de chave estrangeira para recuperar a instância [Subject] para a qual temos a nota. Além disso, [relationship(Subject, backref="grades", …)] criará a nova propriedade [Subject.grades], que será a lista de notas para a disciplina. Esta propriedade não deve já existir na classe [Subject];
  • Linhas 92–96: Para cada entidade derivada de [BaseEntity], definimos a lista de propriedades a excluir do dicionário de propriedades da entidade (BaseEntity.asdict). Vimos que o [sqlalchemy] adiciona a propriedade [_sa_instance_state] a todas as entidades mapeadas. Não queremos isso no dicionário de propriedades. Além disso, vimos que os mapeamentos anteriores adicionaram novas propriedades às entidades:
    • [Student.grades]: todas as notas do aluno;
    • [Class.students]: todos os alunos da turma;
    • [Subject.grades]: todas as notas da disciplina;

Geralmente, não queremos que estas propriedades sejam adicionadas ao estado da entidade. De facto, calcular o seu valor implica um custo de SQL, e este valor é frequentemente desnecessário. Portanto, se recuperarmos o aluno chamado «X»:

  • (continuação)
    • o ORM irá devolver uma entidade [Student(id, last_name, first_name, class, grades)]. Devido a [lazy='select'], as propriedades [class, grades] ligadas a chaves estrangeiras na base de dados não terão sido calculadas;
    • agora, se eu exibir a cadeia JSON para este aluno, sabemos que será a cadeia JSON do dicionário [asdict] da entidade. Se as propriedades [class] e [grades] estiverem incluídas, o [SQLAlchemy] será forçado a executar consultas SQL para calcular os seus valores. Isto é dispendioso. Se pudermos evitar estas consultas, é preferível;
    • aqui, excluímos todas as propriedades ligadas a uma chave estrangeira;
  • linhas 98–100: instanciação e configuração de uma [Session factory] (factory = production factory). O objeto [Session] é usado para criar sessões [SQLAlchemy] apoiadas por transações;
  • linhas 102–103: criação de uma sessão [SQLAlchemy];
  • linha 106: certos elementos da configuração do [SQLAlchemy] são colocados no dicionário de configuração global da aplicação;
  • linha 109: este dicionário é devolvido;

O ficheiro [config_layers] configura as camadas da aplicação:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
def configure(config: dict) -> dict:
    #  layer instantiation [dao]
    from DatabaseDao import DatabaseDao
    dao = DatabaseDao(config)

    #  instantiation of the [business] layer
    from Métier import Métier
    métier = Métier(dao)

    #  instantiation of the [ui] layer
    from Console import Console
    ui = Console(métier)

    #  we put the layers in the config
    config['dao'] = dao
    config['métier'] = métier
    config['ui'] = ui

    #  return the config
    return config
  • Linha 1: A função [configure] recebe o dicionário que contém a configuração global da aplicação;
  • linhas 2–12: as camadas da aplicação são instanciadas;
  • linhas 15–17: as referências das camadas são adicionadas à configuração global;
  • linha 20: a nova configuração é devolvida;

19.6.5. A camada [dao] - 1

Image

É importante compreender aqui que a camada [dao] [3] comunica com o ORM [sqlalchemy] [4] configurado conforme descrito no parágrafo anterior. Das três camadas [ui, business, dao] da aplicação |troiscouches v01|, apenas a camada [dao] precisa de ser reescrita. As camadas [ui, business] são mantidas.

A implementação da camada [dao] foi colocada na pasta [services]:

Image

[InterfaceDatabaseDao] é a interface para a camada [DAO]:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
from abc import ABC, abstractmethod

from InterfaceDao import InterfaceDao


class InterfaceDatabaseDao(InterfaceDao, ABC):

    #  database initialization
    @abstractmethod
    def init_database(self, data: dict):
        pass
  • linha 6: a interface [InterfaceDatabaseDao] deriva tanto da classe [ABC], para ser uma classe abstrata, como da interface [InterfaceDao] do projeto |troiscouches v01|;
  • linhas 8–11: adicionamos o método [init_database] aos métodos herdados de [InterfaceDao]. A sua função será inicializar a base de dados com os dados do dicionário [data] que lhe são passados como parâmetro na linha 10;

Recorde-se que a interface [InterfaceDao] era a seguinte:

 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
#  imports
from abc import ABC, abstractmethod

#  dao interface
from Elève import Elève


class InterfaceDao(ABC):
    #  class list
    @abstractmethod
    def get_classes(self: object) -> list:
        pass

    #  list of students
    @abstractmethod
    def get_élèves(self: object) -> list:
        pass

    #  list of materials
    @abstractmethod
    def get_matières(self: object) -> list:
        pass

    #  lIST OF NOTES
    @abstractmethod
    def get_notes(self: object) -> list:
        pass

    #  list of student grades
    @abstractmethod
    def get_notes_for_élève_by_id(self: object, élève_id: int) -> list:
        pass

    #  search for a student by id
    @abstractmethod
    def get_élève_by_id(self: object, élève_id: int) -> Elève:
        pass

A implementação da camada [DAO] é a seguinte:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
from sqlalchemy.exc import DatabaseError, IntegrityError, InterfaceError

from Classe import Classe
from Elève import Elève
from InterfaceDatabaseDao import InterfaceDatabaseDao
from Matière import Matière
from MyException import MyException
from Note import Note


class DatabaseDao(InterfaceDatabaseDao):

    def __init__(self, config: dict):
        #  database = {"engine": engine, "metadata": metadata, "tables": tables, "session": session}
        self.database = config['database']
        self.session = self.database['session']

    def init_database(self, data: dict):
        …
…
  • linha 11: a classe [DatabaseDao] implementa a interface [InterfaceDatabaseDao];
  • linhas 13–16: o construtor da classe. Recebe o dicionário de configuração da aplicação como parâmetro;
  • linha 15: a configuração [sqlalchemy] é armazenada;
  • linha 16: a sessão [sqlalchemy] através da qual a base de dados será manipulada é armazenada;
  • linha 18: o método [init_database] inicializa a base de dados com o dicionário [data];

O dicionário [data] é implementado pelo seguinte script [data.py]:

 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
def configure():
    from Classe import Classe
    from Elève import Elève
    from Matière import Matière
    from Note import Note

    #  classes are instantiated
    classe1 = Classe().fromdict({"id": 1, "nom": "classe1"})
    classe2 = Classe().fromdict({"id": 2, "nom": "classe2"})
    classes = [classe1, classe2]
    #  materials
    matière1 = Matière().fromdict({"id": 1, "nom": "matière1", "coefficient": 1})
    matière2 = Matière().fromdict({"id": 2, "nom": "matière2", "coefficient": 2})
    matières = [matière1, matière2]
    #  students
    élève11 = Elève().fromdict({"id": 11, "nom": "nom1", "prénom": "prénom1", "classe": classe1})
    élève21 = Elève().fromdict({"id": 21, "nom": "nom2", "prénom": "prénom2", "classe": classe1})
    élève32 = Elève().fromdict({"id": 32, "nom": "nom3", "prénom": "prénom3", "classe": classe2})
    élève42 = Elève().fromdict({"id": 42, "nom": "nom4", "prénom": "prénom4", "classe": classe2})
    élèves = [élève11, élève21, élève32, élève42]
    #  student grades in various subjects
    note1 = Note().fromdict({"id": 1, "valeur": 10, "élève": élève11, "matière": matière1})
    note2 = Note().fromdict({"id": 2, "valeur": 12, "élève": élève21, "matière": matière1})
    note3 = Note().fromdict({"id": 3, "valeur": 14, "élève": élève32, "matière": matière1})
    note4 = Note().fromdict({"id": 4, "valeur": 16, "élève": élève42, "matière": matière1})
    note5 = Note().fromdict({"id": 5, "valeur": 6, "élève": élève11, "matière": matière2})
    note6 = Note().fromdict({"id": 6, "valeur": 8, "élève": élève21, "matière": matière2})
    note7 = Note().fromdict({"id": 7, "valeur": 10, "élève": élève32, "matière": matière2})
    note8 = Note().fromdict({"id": 8, "valeur": 12, "élève": élève42, "matière": matière2})
    notes = [note1, note2, note3, note4, note5, note6, note7, note8]
    #  we group all
    data = {"élèves": élèves, "classes": classes, "matières": matières, "notes": notes}
    #  we return the data
    return data
  • linha 34: o dicionário que será passado para o método [init_database]. Este dicionário é composto pelas seguintes chaves (linha 32):
    • [students]: a lista de alunos;
    • [classes]: a lista de turmas;
    • [disciplinas]: a lista de disciplinas;
    • [notes]: a lista de notas de todos os alunos em todas as disciplinas;

Voltemos ao método [init_database]:

 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
def init_database(self, data: dict):
        #  comic book config
        database = self.database
        engine = database['engine']
        metadata = database['metadata']
        tables = database['tables']

        try:
            #  delete existing tables
            #  checkfirst=True: first checks that the table exists
            tables["notes"].drop(engine, checkfirst=True)
            tables["matières"].drop(engine, checkfirst=True)
            tables["élèves"].drop(engine, checkfirst=True)
            tables["classes"].drop(engine, checkfirst=True)

            #  recreate tables from mapping
            metadata.create_all(engine)

            #  table filling
            session = self.session

            #  classes
            classes = data["classes"]
            for classe in classes:
                session.add(classe)

            #  materials
            matières = data["matières"]
            for matière in matières:
                session.add(matière)

            #  students
            élèves = data["élèves"]
            for élève in élèves:
                session.add(élève)

            #  notes
            notes = data["notes"]
            for note in notes:
                session.add(note)

            #  commit
            session.commit()
        except (DatabaseError, InterfaceError, IntegrityError) as erreur:
            #  session cancellation
            if session:
                session.rollback()
            #  up the exception
            raise MyException(23, f"{erreur}")
  • linhas 3–6: recuperar informações da configuração da base de dados;
  • linhas 9-14: vimos que a configuração [sqlalchemy] tinha mapeado quatro entidades para quatro tabelas [students, subjects, classes, grades]. Começamos por eliminar estas tabelas, caso existam;
  • linhas 16-17: recriamos as quatro tabelas que acabámos de eliminar;
  • linhas 22–25: adicionamos todas as turmas à sessão;
  • linhas 27–30: adicionamos todas as disciplinas à sessão;
  • linhas 32–35: adicionamos todos os alunos à sessão;
  • linhas 37–40: adicionamos todas as notas à sessão;
  • Para efetuar estas adições, seguimos uma ordem específica. Começámos pelas entidades que não têm relações com outras entidades e terminámos pelas que têm. Assim, quando adicionamos os alunos à sessão, as turmas a que eles se referem já se encontram na sessão;
  • linha 43: a sessão [sqlalchemy] é confirmada. Após esta operação, podemos ter a certeza de que todos os dados na sessão foram sincronizados com a base de dados. Em suma, os dados foram inseridos nas tabelas. Isto foi possível graças aos mapeamentos definidos na configuração do [sqlalchemy]. O [sqlalchemy] sabe como cada entidade deve ser armazenada nas tabelas. O [sqlalchemy] também gerou quaisquer chaves estrangeiras que as tabelas possam ter;
  • linhas 44–49: se for encontrado um problema, a sessão [sqlalchemy] é revertida e, na linha 49, é lançada uma exceção;

19.6.6. Inicialização da base de dados

Image

O script [main_init_database] inicializa a base de dados com o conteúdo do script [data.py]. O seu código é o seguinte:

 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
#  a mysql or pgres parameter is expected
import sys

syntaxe = f"{sys.argv[0]} mysql / pgres"
erreur = len(sys.argv) != 2
if not erreur:
    sgbd = sys.argv[1].lower()
    erreur = sgbd != "mysql" and sgbd != "pgres"
if erreur:
    print(f"syntaxe : {syntaxe}")
    sys.exit()

#  configure the application
import config
config = config.configure({'sgbd': sgbd})

#  syspath is configured - imports can be made
from MyException import MyException

#  retrieve the data to be stored in the database
import data
data = data.configure()

#  we recover the [dao] layer
dao = config["dao"]

#  ----------- hand
try:
    #  database table creation and initialization
    dao.init_database(data)
except MyException as ex:
    #  error is displayed
    print(f"L'erreur suivante s'est produite : {ex}")
finally:
    #  release of resources mobilized by the application
    import shutdown
    shutdown.execute(config)
#  end
print("Travail terminé...")
  • linhas 1-11: o script espera um parâmetro [mysql] ou [pgres], dependendo se pretende inicializar uma base de dados MySQL ou PostgreSQL;
  • linhas 13-15: a aplicação é configurada para o SGBD passado como parâmetro;
  • linhas 20-22: os dados a inserir na base de dados são recuperados;
  • linha 25: a camada [dao] já foi instanciada e está acessível na configuração da aplicação;
  • linha 30: a base de dados é inicializada;
  • linhas 34–37: independentemente de ter ocorrido um erro, os recursos da aplicação são libertados utilizando o módulo [shutdown];

O módulo [shutdown.py] é o seguinte:

1
2
3
4
5
def execute(config: dict):
    #  release the resources mobilized by the application
    sqlalchemy_session = config['database']['session']
    if sqlalchemy_session:
        sqlalchemy_session.close()

A função [shutdown.execute] fecha a sessão [sqlalchemy] utilizada para inicializar a base de dados.

Criamos uma configuração de execução inicial (ver |configuração de execução|) para executar [main_init_database] com o sistema de gestão de bases de dados MySQL:

Image

Os resultados da execução desta configuração são os seguintes no phpMyAdmin:

Image

Image

Image

Para o SGBD [PostgreSQL], utilizamos a seguinte configuração de execução:

Image

Após a execução, os resultados no [pgAdmin] são os seguintes:

Image

Image

Image

Repare como foi fácil mudar de SGBD.

19.6.7. A camada [dao] – 2

Voltamos à classe [DatabaseDao], que implementa a camada [DAO]. Até agora, mostrámos apenas a implementação do método [init_database]. Vamos agora mostrar a implementação dos outros métodos:

  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
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
from sqlalchemy.exc import DatabaseError, IntegrityError, InterfaceError

from Classe import Classe
from Elève import Elève
from InterfaceDatabaseDao import InterfaceDatabaseDao
from Matière import Matière
from MyException import MyException
from Note import Note


class DatabaseDao(InterfaceDatabaseDao):

    def __init__(self, config: dict):
        #  database = {"engine": engine, "metadata": metadata, "tables": tables, "session": session}
        self.database = config['database']
        self.session = self.database['session']

    def init_database(self, data: dict):
        …

    #  list of all classes
    def get_classes(self: object) -> list:
        #  request
        return self.session.query(Classe).all()

    #  list of all students
    def get_élèves(self: object) -> list:
        #  request
        return self.session.query(Elève).all()

    #  list of all materials
    def get_matières(self: object) -> list:
        #  request
        return self.session.query(Matière).all()

    #  a list of all students' grades
    def get_notes(self: object) -> list:
        #  request
        return self.session.query(Note).all()

    #  a list of grades for a particular student
    def get_notes_for_élève_by_id(self: object, élève_id: int) -> list:
        #  we look for the student - an exception is thrown if he doesn't exist
        #  we let it rise
        élève = self.get_élève_by_id(élève_id)
        #  lazy loading of notes
        notes = élève.notes
        #  a dictionary is returned
        return {"élève": élève, "notes": notes}

    #  a student identified by his number
    def get_élève_by_id(self, élève_id: int) -> Elève:
        #  we're looking for the student
        élèves = self.session.query(Elève).filter(Elève.id == élève_id).all()
        #  have we found?
        if élèves:
            return élèves[0]
        else:
            raise MyException(11, f"L'élève d'identifiant {élève_id} n'existe pas")

    #  a student identified by name
    def get_élève_by_name(self, élève_name: str) -> Elève:
        #  we're looking for the student
        élèves = self.session.query(Elève).filter(Elève.nom == élève_name).all()
        #  have we found?
        if élèves:
            return élèves[0]
        else:
            raise MyException(12, f"L'élève de nom {élève_name} n'existe pas")

    #  a class identified by its number
    def get_classe_by_id(self, classe_id: int) -> Classe:
        #  we are looking for the
        classes = self.session.query(Classe).filter(Classe.id == classe_id).all()
        #  have we found?
        if classes:
            return classes[0]
        else:
            raise MyException(13, f"La classe d'identifiant {classe_id} n'existe pas")

    #  a class identified by its name
    def get_classe_by_name(self, classe_name: str) -> Classe:
        #  we're looking for the class
        classes = self.session.query(Classe).filter(Classe.nom == classe_name).all()
        #  have we found?
        if classes:
            return classes[0]
        else:
            raise MyException(14, f"La classe de nom {classe_name} n'existe pas")

    #  a material identified by its number
    def get_matière_by_id(self, matière_id: int) -> Matière:
        #  we're looking for material
        matières = self.session.query(Matière).filter(Matière.id == matière_id).all()
        #  have we found?
        if matières:
            return matières[0]
        else:
            raise MyException(11, f"La matière d'identifiant {matière_id} n'existe pas")

    #  a material identified by its name
    def get_matière_by_name(self, matière_name: str) -> Matière:
        #  we're looking for the material
        matières = self.session.query(Matière).filter(Matière.nom == matière_name).all()
        #  have we found?
        if matières:
            return matières[0]
        else:
            raise MyException(15, f"La matière de nom {matière_name} n'existe pas")
  • linhas 21–24: o método [get_classes] deve devolver a lista de turmas da escola. Na linha 20, utilizamos uma consulta que já vimos anteriormente;
  • linhas 26–39: três outros métodos semelhantes para recuperar as listas de alunos, disciplinas e notas;
  • linhas 51–59: o método [get_student_by_id] deve devolver um aluno identificado pelo seu ID. Lança uma exceção se o aluno não existir;
    • linha 54: usamos uma consulta filtrada. Obtemos uma lista vazia ou uma lista com um elemento;
    • linha 57: se a lista recuperada não estiver vazia, retorne o primeiro elemento da lista;
    • caso contrário, linha 59, é lançada uma exceção;
  • linhas 41–49: o método [get_notes_for_student_by_id] deve devolver as notas de um aluno identificado pelo seu ID:
    • linha 45: usamos o método [get_student_by_id] para recuperar a entidade Student para o aluno;
    • linha 47: usamos a propriedade [Student.grades] criada pelo mapeamento entre a entidade [Grade] e a tabela [grades] (ver a secção |Configuração do SQLAlchemy|), que representa as notas do aluno;
    • linha 49: devolvemos um dicionário;
  • Linhas 61–109: uma série de métodos semelhantes que nos permitem:
    • encontrar um aluno pelo nome, linhas 61–69;
    • encontrar uma turma, linhas 71–89;
    • recuperar uma disciplina, linhas 91–109;

19.6.8. O script [main_joined_queries]

Image

O script [main_joined_queries] tem esse nome porque visa destacar as consultas implicitamente feitas pelo [sqlalchemy] para recuperar informações de várias tabelas. Estas consultas, ocultas do programador, são feitas sempre que uma propriedade de uma entidade é associada à função [relationship] no mapeamento da entidade. Por exemplo:


    # mapping
    mapper(Note, tables['notes'], properties={
        'id': notes_table.c.id,
        'valeur': notes_table.c.valeur,
        'élève': relationship(Elève, backref="notes", lazy="select"),
        'matière': relationship(Matière, backref="notes", lazy="select")
    })

Acima está o mapeamento entre a entidade [Note] e a tabela [notes]:

  • Linha 5: Quando a propriedade [student] de uma entidade [Grade] é solicitada pela primeira vez, ela é recuperada da tabela [students] através de uma consulta SQL. Até que essa propriedade seja solicitada, ela permanece indefinida (carregamento diferido). Uma vez recuperado, o seu valor permanece na memória do ORM. Quando for referenciado uma segunda vez, o ORM devolverá imediatamente o seu valor sem emitir uma nova consulta SQL. Tudo isto é transparente para o programador;
  • o mesmo se aplica à propriedade inversa [Student.grades] (backref), linha 5;
  • o mesmo se aplica à propriedade [Grade.subject] e à sua propriedade inversa [Subject.grades] (backref), linha 6;

O script [main_joined_queries] é o seguinte:

 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
#  a mysql or pgres parameter is expected
import sys

syntaxe = f"{sys.argv[0]} mysql / pgres"
erreur = len(sys.argv) != 2
if not erreur:
    sgbd = sys.argv[1].lower()
    erreur = sgbd != "mysql" and sgbd != "pgres"
if erreur:
    print(f"syntaxe : {syntaxe}")
    sys.exit()

#  configure the application
import config
config = config.configure({"sgbd": sgbd})

#  syspath is configured - imports can be made
from MyException import MyException

#  the [dao] layer
dao = config["dao"]
try:
    #  student by id
    print("élève id=11 -----------")
    élève = dao.get_élève_by_id(11)
    print(f"élève={élève}")
    #  student class (lazy loading)
    classe = élève.classe
    print(f"classe de l'élève : {classe}")
    #  students in the same class (lazy loading)
    print("élèves dans la même classe :")
    for élève in classe.élèves:
        print(f"élève={élève}")

    #  a student by name
    print("élève nom='nom2' -----------")
    print(f"élève={dao.get_élève_by_name('nom2')}")
    #  its class (lazy loading)
    print(f"classe de l'élève : {élève.classe}")

    #  student notes
    print("notes de l'élève id=11 -----------")
    #  first the student
    élève = dao.get_élève_by_id(11)
    #  then its notes (lazy loading)
    for note in élève.notes:
        #  the note
        print(f"note={note}, "
              #  note material (lazy loading)
              f"matière={note.matière}")

    #  students in a class
    print("élèves de la classe nom='classe1' -----------")
    #  first the class
    classe = dao.get_classe_by_name('classe1')
    #  then the students (lazy loading)
    for élève in classe.élèves:
        print(élève)

    #  same for [class2]
    print("élèves de la classe de nom 'classe2' -----------")
    classe = dao.get_classe_by_name('classe2')
    for élève in classe.élèves:
        print(élève)

    #  subject grades
    print("matière de nom='matière1' -----------")
    #  first the material
    matière = dao.get_matière_by_name('matière1')
    print(f"matière={matière}")
    #  then grades in this subject (lazy loading)
    print("Notes dans la matière : ")
    for note in matière.notes:
        print(note)

    #  same for matière2
    print("matière de nom='matière2' -----------")
    matière = dao.get_matière_by_name('matière2')
    print(f"matière={matière}")
    print("Notes dans la matière : ")
    for note in matière.notes:
        print(f"note={note}")
except MyException as ex1:
    #  error is displayed
    print(f"L'erreur 1 suivante s'est produite : {ex1}")
except BaseException as ex2:
    #  error is displayed
    print(f"L'erreur 2 suivante s'est produite : {ex2}")
finally:
    #  free up resources
    import shutdown
    shutdown.execute(config)

Os comentários são suficientes para compreender o código.

Criamos uma configuração de execução para o MySQL:

Image

Os resultados da execução são os seguintes:

 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
C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\venv\Scripts\python.exe C:/Data/st-2020/dev/python/cours-2020/python3-flask-2020/databases/sqlalchemy/05/main/main_joined_queries.py mysql
élève id=11 -----------
élève={"classe_id": 1, "nom": "nom1", "prénom": "prénom1", "id": 11}
classe de l'élève : {"nom": "classe1", "id": 1}
élèves dans la même classe :
élève={"classe_id": 1, "nom": "nom1", "prénom": "prénom1", "id": 11}
élève={"classe_id": 1, "nom": "nom2", "prénom": "prénom2", "id": 21}
élève nom='nom2' -----------
élève={"classe_id": 1, "nom": "nom2", "prénom": "prénom2", "id": 21}
classe de l'élève : {"nom": "classe1", "id": 1}
notes de l'élève id=11 -----------
note={"matière_id": 1, "valeur": 10.0, "élève_id": 11, "id": 1}, matière={"coefficient": 1.0, "nom": "matière1", "id": 1}
note={"matière_id": 2, "valeur": 6.0, "élève_id": 11, "id": 5}, matière={"coefficient": 2.0, "nom": "matière2", "id": 2}
élèves de la classe nom='classe1' -----------
{"classe_id": 1, "nom": "nom1", "prénom": "prénom1", "id": 11}
{"classe_id": 1, "nom": "nom2", "prénom": "prénom2", "id": 21}
élèves de la classe de nom 'classe2' -----------
{"classe_id": 2, "nom": "nom3", "prénom": "prénom3", "id": 32}
{"classe_id": 2, "nom": "nom4", "prénom": "prénom4", "id": 42}
matière de nom='matière1' -----------
matière={"coefficient": 1.0, "nom": "matière1", "id": 1}
Notes dans la matière : 
{"matière_id": 1, "valeur": 10.0, "élève_id": 11, "id": 1}
{"matière_id": 1, "valeur": 12.0, "élève_id": 21, "id": 2}
{"matière_id": 1, "valeur": 14.0, "élève_id": 32, "id": 3}
{"matière_id": 1, "valeur": 16.0, "élève_id": 42, "id": 4}
matière de nom='matière2' -----------
matière={"coefficient": 2.0, "nom": "matière2", "id": 2}
Notes dans la matière : 
note={"matière_id": 2, "valeur": 6.0, "élève_id": 11, "id": 5}
note={"matière_id": 2, "valeur": 8.0, "élève_id": 21, "id": 6}
note={"matière_id": 2, "valeur": 10.0, "élève_id": 32, "id": 7}
note={"matière_id": 2, "valeur": 12.0, "élève_id": 42, "id": 8}

Process finished with exit code 0

Para compreender estes resultados, lembre-se de que certas propriedades foram excluídas do dicionário de entidades (ver |configuração|):


    # configuration des entités [BaseEntity]
    Elève.excluded_keys = ['_sa_instance_state', 'notes', 'classe']
    Classe.excluded_keys = ['_sa_instance_state', 'élèves']
    Matière.excluded_keys = ['_sa_instance_state', 'notes']
    Note.excluded_keys = ['_sa_instance_state', 'matière', 'élève']

Assim, quando escrevemos [print(f"student={student}")] na linha 26 do código, a linha 1 acima indica-nos que as propriedades ['_sa_instance_state', 'grades', 'class'] não serão apresentadas. É isto que vemos na linha 3 dos resultados. Todas as outras propriedades são exibidas. Assim, ainda na linha 3, descobrimos uma nova propriedade [class_id] que inicialmente não existia na entidade [Student]. Esta propriedade corresponde diretamente à coluna [class_id] na tabela [students]. Assim, o [SQLAlchemy] adicionou as seguintes propriedades à entidade [Student]: [class_id, _sa_instance_state, grades]. É importante estar ciente disto, especialmente porque estas propriedades não devem já existir na entidade mapeada.

As propriedades excluídas do dicionário da entidade são importantes. Por exemplo, se não excluirmos as propriedades [grades, student] da entidade [Student], a operação [print(f"student={student}")] irá exibi-las e, consequentemente, tal como acabado de explicar, desencadeará consultas SQL implícitas (carregamento diferido) para recuperar os valores dessas propriedades. Se, como neste caso, estiver a ser apresentada uma lista de alunos, são realizadas operações SQL implícitas para cada aluno. Isto pode ser desnecessário e, certamente, dispendioso em termos de tempo de execução.

Para executar o script com uma base de dados PostgreSQL, crie a seguinte configuração de execução:

Image

A execução produz os mesmos resultados que com o MySQL.

19.6.9. O script [main_stats_for_student]

O script [main_stats_for_student] é o mesmo já utilizado na aplicação |troiscouches v01|. Anteriormente, chamava-se [main]. Trata-se de uma aplicação de consola que recupera determinadas métricas relativas às notas de um aluno: [média ponderada, mínimo, máximo, lista]. Enquadra-se na seguinte arquitetura:

Image

Nesta arquitetura em camadas, apenas a camada [dao] foi alterada entre a aplicação |troiscouches v01| e esta. Uma vez que a nova camada [dao] segue a interface [InterfaceDao] da antiga camada [dao], as camadas [ui, business] não precisam de ser alteradas. Podemos, portanto, continuar a utilizar as definidas na aplicação |troiscouches v01|.

O script [main_stats_for_élève] implementa a camada [main] do diagrama acima da seguinte forma:

 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
#  a mysql or pgres parameter is expected
import sys

syntaxe = f"{sys.argv[0]} mysql / pgres"
erreur = len(sys.argv) != 2
if not erreur:
    sgbd = sys.argv[1].lower()
    erreur = sgbd != "mysql" and sgbd != "pgres"
if erreur:
    print(f"syntaxe : {syntaxe}")
    sys.exit()

#  configure the application
import config
config = config.configure({'sgbd': sgbd})

#  syspath is configured - imports can be made
from MyException import MyException

#  the [ui] layer
ui = config["ui"]
try:
    #  execution layer [ui]
    ui.run()
except MyException as ex1:
    #  error is displayed
    print(f"L'erreur 1 suivante s'est produite : {ex1}")
except BaseException as ex2:
    #  error is displayed
    print(f"L'erreur 2 suivante s'est produite : {ex2}")
finally:
    #  free up resources
    import shutdown
    shutdown.execute(config)
  • linha 20: recuperar uma referência à camada [ui] a partir da configuração da aplicação;
  • linha 24: iniciamos a caixa de diálogo do utilizador utilizando o método único da camada [ui];

Uma configuração de execução para o PostgreSQL teria o seguinte aspeto:

Image

Aqui está um exemplo de execução com esta configuração:


C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\venv\Scripts\python.exe C:/Data/st-2020/dev/python/cours-2020/python3-flask-2020/databases/sqlalchemy/05/main/main_stats_for_élève.py pgres
Numéro de l'élève (>=1 et * pour arrêter) : 11
Elève={"prénom": "prénom1", "id": 11, "classe_id": 1, "nom": "nom1"}, notes=[10.0 6.0], max=10.0, min=6.0, moyenne pondérée=7.33
Numéro de l'élève (>=1 et * pour arrêter) : 1
L'erreur suivante s'est produite : MyException[11, L'élève d'identifiant 1 n'existe pas]
Numéro de l'élève (>=1 et * pour arrêter) : *
 
Process finished with exit code 0
https://stahe.github.io
Este tutorial, escrito por Serge Tahé, é disponibilizado ao público sob os termos da Creative Commons Atribuição – Não comercial – Partilha igual 3.0 Unported.