ORM

Após termos visto do uso consultas de inserção, alteração e apagamento com o SQLAlchemy Core podemos considerar o mesmo conjunto de operações com o ORM.

Usando ORM

Na abordagem ORM (Object Relational Mapper) do SQLAlchemy o objeto Section é a base da interação entre o código Python e os bancos de dados. Ele é usado de forma muito semelhante ao objeto Connection, usado no CORE que, internamente, é acionado pelas sessões do ORM para produzir consultas SQL.

Para ilustrar o processo básico vamos usar a construção dos padrões semelhante à usada com Connection, usando um gerenciador de contexto, embora Section admita alguns padrões de criação diferentes.

from sqlalchemy.orm import Session
query = text("SELECT campo_1, campo_2 FROM tabela WHERE campo_1 > :c ORDER BY campo_1, campo_2")
with Session(engine) as session:
    result = session.execute(query, {"c": 23})
    for row in result:
        print(f"campo_1: {row.campo_1}  campo_2: {row.campo_2}")
[SQL]
SELECT campo_1, campo_2 FROM tabela WHERE campo_1 > ? ORDER BY campo_1, campo_2
[...] (23,)
# o resultado contém uma lista de listas com campo_1 e campo_2 (se campo_1 >23)

# um update
with Session(engine) as session:
    result = session.execute(
        text("UPDATE tabela SET campo_1=:c1 WHERE campo_2=:c2"),
        [{"c1": 9, "c2": 11}, {"c1": 13, "c2": 15}],
    )
    session.commit()
[SQL]
UPDATE tabela SET campo_1=? WHERE campo_2=?
[...] [(9, 11), (13, 15)]

Vemos no exemplo que simplesmente substituimos as intruções:

with engine.connect() as conn  por  with Session(engine) as session
Connection.execute()           por  Session.execute()
Connection.commit()            por  Session.commit()

Obs.: Todas as consultas SQL são precedidas por BEGIN e terminadas por COMMIT (omitidas aqui).

Uma sessão com ORM

Para ilustrar o uso do ORM continuaremos, por enquanto, usando a construção de consultas com a função text("query"), que passa a string de consulta diretamente para o banco de dados. O artigo Sqlalchemy ORM Resumido contém uma amostra das funções básicas do ORM.

Começaremos com a tabela coordenadas do BD meu_banco.db do SQLite, construído na seção anterior. Na última operação ele foi gravado com o estado mostrado na figura. Faremos uma atualização de valores com UPDATE coordenadas SET y=:y WHERE x=:x. Os valores de :x, :y são lidos na lista de dicionários. Cada dicionário gera uma operação de UPDATE. Para conferir o resultado da atualização fazemos uma consulta somente dos valores com y > 100.

from sqlalchemy import create_engine, text
from sqlalchemy.orm import Session

engine = create_engine("sqlite:///meu_banco.db")	

query = text("UPDATE coordenadas SET y=:y WHERE x=:x")
valores = [{"x": 11, "y": 110}, {"x": 15, "y": 150}]
with Session(engine) as session:
    result = session.execute(query, valores)
    session.commit()    

query = text("SELECT x, y FROM coordenadas WHERE y > :y ORDER BY x, y")
with Session(engine) as session:
    result = session.execute(query, {"y": 100})
    for row in result:
        print(f" x = {row.x}  y = {row.y}")
        
# a consulta com SELECT resulta em
↳  x = 11  y = 110
   x = 15  y = 150

As consultas geradas acima são, respectivamente:

[SQL]
UPDATE coordenadas SET y=110 WHERE x=11
UPDATE coordenadas SET y=150 WHERE x=15
SELECT x, y FROM coordenadas WHERE y > 100 ORDER BY x, y

A sessão não é tornada permamente (commited) automaticamente. Para isso é necessário emitir o comando session.commit(). Pelos exemplos mostrados vemos que simplesmente substituimos as intruções:

with engine.connect() as conn  por  with Session(engine) as session
Connection.execute()           por  Session.execute()
Connection.commit()            por  Session.commit()

Obs.: Todas as consultas SQL são precedidas por BEGIN e terminadas por COMMIT (omitidas aqui).

Definindo tabelas com ORM

Com o SQLAlchemy ORM temos uma sintaxe de criação de tabelas mais próxima do estilo do Python. Ele fornece uma interface chamada de Tabela Declarativa (Declarative Table) que usa tipos de variáveis do Python para representar e configurar as tabelas. Com esse procedimento temos classes mapeadas do Python que refletem as propriedades das tabelas do SQL. Em outras palavras, criamos classes do Python com atributos e propriedades que refletem tabelas, colunas, vínculos e relacionamentos que são mapeadas em tabelas do SQL. As operações CRUD usuais são feitas diretamente nos objetos que herdam dessas classes e que, depois, são transferidas para o BD.

A coleção MetaData é criada automaticamente (se uma não for explicitamente fornecida) e fica associada ao objeto chamado Base Declarativa (Declarative Base) que pode ser criado como instância da classe DeclarativeBase:

from sqlalchemy.orm import DeclarativeBase
class Base(DeclarativeBase):
    pass

# a coleção metadata é criada em Base
print(Base.metadata)
↳ MetaData()

Para definir nossas tabelas mapeadas herdamos de Base que, como vimos, herda de DeclarativeBase.

from typing import Optional, List
from sqlalchemy import create_engine, ForeignKey, String
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship

engine = create_engine("sqlite:///meu_banco.db")

class Base(DeclarativeBase):
    pass

class Aluno(Base):
    __tablename__ = "aluno"
    id: Mapped[int] = mapped_column(primary_key=True)
    matricula: Mapped[str] = mapped_column(String(50))
    nome: Mapped[str] = mapped_column(String(50))
    sobrenome: Mapped[Optional[str]]
    enderecos: Mapped[List["Endereco"]] = relationship(back_populates="aluno")
    
    def __repr__(self):
        return f"Aluno(id={self.id!r}, nome={self.nome!r}, sobrenome={self.sobrenome!r})"

class Endereco(Base):
    __tablename__ = "endereco"
    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str]
    aluno_id: Mapped[int] = mapped_column(ForeignKey("aluno.id"))    
    aluno: Mapped[Aluno] = relationship(back_populates="enderecos")

    def __repr__(self):
        return f"Aluno(id={self.id!r}, nome={self.nome!r}, sobrenome={self.sobrenome!r})"

Base.metadata.create_all(engine)

As duas classes, Alunos e Enderecos (e os objetos que herdam delas) ficam disponíveis para operações de persistência e consultas. Elas são denominadas classes mapeadas pelo ORM (ORM Mapped Classes). O nome de cada tabela fica atribuído em DeclarativeBase.__tablename__. Após a criação a tabela fica disponível por meio do atributo DeclarativeBase.__table__.

As colunas da tabela, por sua vez, são criadas por mapped_column() que usa anotações (a construção nome_campo: tipo_de_dado, que fica associado ao Mapped[tipo]. Se a coluna tem um tipo simples sem outras qualificações basta indicar apenas Mapped[tipo], onde os tipos do Python como int ou str significam as classes Integer ou String do SQLAlchemy, respectivamente. Essas definições podem ser bastante modificadas para representar objetos mais complexos.

Inserindo linhas

No ORM as instruções Insert são emitidas, e inseridas na transação^✠, pelo objeto Session. Para isso inserimos novos objetos à Session e os tornamos persistentes (gravando a transação no BD) com com um processo chamado de flush. Esse processo é conhecido como padrão de unidade de trabalho (UoW)^†.

Até agora inserimos dados usando INSERT com dicionários que contém as dados a serem incluídos. Com a abordagem ORM criamos novos objetos derivados das classes das classes customizadas que representam dados na tabela e os inserimos nos objetos table contidos em Session.

Vimos nos nossos exemplos como definir a estrutura de uma tabela criando classes de herdam de DeclarativeBase, criando uma classe para cada tabela SQL. Definimos as classes Aluno e Endereco e usamos Base.metadata.create_all(engine) para inserir no BD as tabelas representadas por elas. As mesmas classes são usadas na inserção de linhas.

Por exemplo, criamos abaixo dois objetos instâncias de Aluno, instanciando a classe e usando os nomes de colunas como keywords. Essa operação usa o construtor __init__() construído automaticamente pelo ORM.

jones = Aluno(matricula= '3456-1234', nome="Jones", sobrenome="Manoel", enderecos=[])
galileu = Aluno(matricula= '8888-9999',nome="Galileu", sobrenome="Galilei", enderecos=[])

# se exibirmos um objeto com print
print(jones)
↳ Aluno(id=None, matricula= '3456-1234', nome="Jones", sobrenome="Manoel", enderecos=[])

Observe que não incluimos um valor para id que é um campo de autoincremento inserido automaticamente. Se o objeto for exibido veremos que id=None, provisoriamente. Um valor é atribuído pelo mecanismo do banco de dados.

Objetos criados dessa forma são chamados transientes, pois não fazem parte ainda do BD, nem mesmo da representação no ORM. Essa inserção deve ser feita na Session, com o método .add(). Feito isso as linhas são pendentes, ainda não inseridas no BD. Esse estado pode ser verificado por meio do objeto Session.new.

# criamos uma sessão
session = Session(engine)

# inserimos os objetos (que representam linhas) na sessão
session.add(jones)
session.add(galileu)

# para verificar objetos pendentes
print(session.new)
↳ IdentitySet([Aluno(id=None, matricula= '3456-1234', nome="Jones", sobrenome="Manoel"),
               Aluno(id=None, matricula= '8888-9999',nome="Galileu", sobrenome="Galilei")]

# para inserir esses valores no modelo do BD
session.flush()
[SQL]
INSERT INTO aluno (matricula, nome, sobrenome) VALUES (?, ?), (?, ?) RETURNING id
('3456-1234', 'Jones', 'Manoel', '8888-9999', 'Galileu', 'Galilei')

A consulta realizada insere os dois objetos criados na Session e retorna os ids das linhas inseridas. Para isso é usado o padrão de unidade de trabalho (UoW), o que significa que as alterações não são comunicadas ao BD até que o método Session.flush() seja usado. A transação aberta no início com Session(engine) permanece aberta até que sejam emitidos um dos comandos, chamando métodos de Session:

Session.commit()
Session.rollback() # ou
Session.close()

A execução de .commit() também emite um .flush(). É possível configurar uma Session para que o comportamento autoflush (flush automático).

Recuperando pks: Quando um objeto é inserido o ORM gera automaticamente os atributos das chaves primárias (pk). Os objetos criados acima, jones e galileu passam a ter um id que pode ser lidos.

print(jones.id)
↳ 4
print(galileu.id)
↳ 5

Essa propriedade é lida internamente com CursorResult.inserted_primary_key e esse procedimento exige que operações de INSERT sejam feitas uma de cada vez. Por isso não foram feitas operações tipo executemany. Alguns gerenciadores, como o psycopg2 do PostgreSQL, são capazes de inserir várias linhas de uma vez e recuperar suas chaves primárias.

Podemos recuperar um dos objetos armazenados no mapa de identidade^‡ usando o método Session.get()

um_aluno = session.get(Aluno, 4)
print(um_aluno)
↳ Aluno(id=None, matricula= '3456-1234', nome="Jones", sobrenome="Manoel", enderecos=[])

# o objeto é o mesmo que o definido anteriormente
um_aluno is jones
↳ True

O objeto é retornado se existir no mapa ou, caso contrário, um SELECT é produzido. Observamos que get() retorna uma referência para o mesmo objeto já existente (desde que não tenha sido removido).

Committing: Comitar o estado do BD significa gravar as alterações feitas no BD. Após o commit, todos os objetos continuam ligados (attached) à seção até que ela seja encerrada. Se estamos usando um gerenciador de contexto, abrindo e usando a sessão dentro de um bloco with, a sessão é fechada ao abandonarmos o bloco. Caso contrário temos que fechar manualmente a sessão com session.close().

# para "comitar" uma sessão
session.commit()
[SQL]
COMMIT

# para fechar a sessão
session.close()

Update com UoW: Suponha que desejamos alterar a linha da tabela Aluno referente ao aluno de nome Galileu (que assumiremos tem id=4). Primeiro carregamos essa linha em um objeto (caso já não esteja carregada).

galileu = session.execute(select(Aluno).filter_by(name="Galileu")).scalar_one()
[SQL]
SELECT aluno.id, aluno.nome, aluno.sobrenome, aluno.enderecos
FROM aluno WHERE alunou.nome = ? ('Galileu',)

# o objeto é criado com os dados da linha
print(galileu)
galileu = Aluno(id=4, matricula= '8888-9999',nome="Galileu", sobrenome="Galilei", enderecos='')

Podemos alterar propriedades desse objeto da forma usual. A alteração fica armazenada em session, inicialmente na coleção chamada session.dirty, que contém objetos alterados antes de flush. Após session.flush() um UPDATE é executado no BD e o objeto alterado sai da coleção session.dirty (lembrando que podemos ajustar um autoflush).

galileu.sobrenome = "Osbourne"
galileu in session.dirty
↳ True

session.flush()
galileu in session.dirty
↳ False

# após a operação o novo dado pode ser verificado

galileu_sobrenome = session.execute(select(Aluno.sobrenome)
                           .where(Aluno.id == 4)).scalar_one()
print(galileu_sobrenome)
↳ Osbourne

# as linhas correspondem a
[SQL]
UPDATE aluno SET sobrenome=? WHERE aluno.id = ? ('Osbourne', 4)
SELECT aluno.sobrenome FROM aluno WHERE aluno.id = ? (4,)

Um flush é executado quando SELECT é executado.

Deleting com UoW: Uma linha pode ser removida do BD com Session.delete(obj), onde obj é um objeto carregado representando essa linha. O objeto permanece na sessão até a emissão de um flush e, depois, é removido dela. Da mesma forma que ocorre com a consulta de UPDATE, as alterações do estado do BD só é permanente quando se realiza um commit.

jones = session.get(Aluno, 1)
session.delete(jones)

# após um flush
session.flush()   # ou uma consulta de SELECT emitida
jones in session
↳ False

# para a permanência no BD
session.commit()

Operações de INSERT, UPDATE e DELETE em várias linhas: Vimos que objetos são inseridos em uma sessão com Session.add() e o mecanismo interno da ORM cuida da emissão de consultas SQL relacionadas.

Além dessa funcionalidade, sessões ORM também podem processar instruções INSERT, UPDATE e DELETE diretamente sem passar pela criação de outros objetos da ORM, recebendo listas de valores a serem inseridas, atualizados ou apagados, incluindo critérios WHERE que aplicam a transformação em muitas linhas de uma vez. Isso é útil quando se quer aplicar a alteração a muitas linhas, evitando a construção de objetos mapeados.

As sessões ORM podem se utilizar de recursos com insert(), update() e delete() de forma similar à usada no CORE. Para isso essas funções recebem coleções em seus argumentos, em geral uma lista de dicionários. Por exemplo:

from sqlalchemy import insert
session.execute(
    insert(Aluno),
    [
        {"matricula":"9487634", "nome": "Rodrigo", "sobrenome": "Santos"},
        {"matricula":"0698734", "nome": "Paula", "sobrenome": "Silva"},
        {"matricula":"9998765", "nome": "Humberto", "sobrenome": "Loyola"},
        {"matricula":"1230984", "nome": "Mariane", "sobrenome": "Louise"},
        {"matricula":"2345670", "nome": "Afonso", "sobrenome": "Pena"},
    ],
)
[SQL]
INSERT INTO aluno (matricula, nome, sobrenome) VALUES (?, ?, ?)
[
  ('9487634', 'Rodrigo' 'Santos'),
  ('0698734', 'Paula',  'Silva'),
  ('9998765', 'Humberto', 'Loyola'),
  ('1230984', 'Mariane',  'Louise'),
  ('2345670', 'Afonso', 'Pena')
]

Outras informações no Guia do SQLAlchemy: ORM-Enabled INSERT, UPDATE, and DELETE statements.

Desfazendo transações com Roll Back: Uma sessão possui o método Session.rollback() que se destina a emitir um ROLLBACK na conexão SQL ativa. Esse método também afeta os objetos associados à Session, como é o caso do objeto galileu armazenada em nossos exemplos. Fizemos no exemplo a alteração da propriedade galileu.sobrenome de "Galilei" para "Osbourne". Se aplicarmos Session.rollback() toda a transação atual será cancelada e todos os objetos em associação com a sessão ficarão expirados.

Fechando uma sessão: Em vários dos exemplos usados abrimos e manipulamos as sessões fora de um gerenciador de contexto e, portanto, elas devem ser fechadas manualmente. Claro que a alternativa é usar o gerenciador, como mostrado abaixo.

from sqlalchemy.orm import Session
engine = create_engine("url/do/banco_de_dados")	

session = Session(engine)
#  conjunto de operações sobre o BD
session.commit()
session.close()


# usando o gerenciador de contexto
with Session(engine) as session:
    # conjunto de operações sobre o BD
    session.commit()

Quando fechamos uma sessão, manualmente ou por meio de um gerenciador de contexto, liberamos os recursos de máquina usados para a conexão. Se existirem transações não comitadas elas serão perdidas (emitindo um ROLLBACK). Portanto, se usarmos a sessão apenas para operações de leitura, como em SELECTs, basta fechá-la, sem preocupação com a emissão de Session.rollback(). Além disso todos os objetos ligados à sessão são desconectados, ficando sujeitos à execução de limpeza pelo gc, coletor de lixo do Python. Veja Python Manual: Garbage Collector interface.

Transações

Uma transação é a menor unidade de operações realizadas sobre um banco de dados. Ela é composta de um conjunto ordenado de instruções e pode ser executada manualmente ou automatizada no código. Os sistemas gerenciadores devem garantir que a transação seja executada por completo ou abandonada, sem nenhuma alteração ao banco.

†: Padrão de Unidade de Trabalho (Unit Of Work, UoW) Unidade de Trabalho é um padrão de projeto onde se mantém uma lista de objetos afetados por uma transação e coordena como essas alterações são efetivadas, cuidando de possíveis problemas de concorrência. O padrão Unit of Work pode ser visto como um contexto, sessão ou objeto que acompanha as alterações das entidades de negócio durante uma transação e está presente em muitas das ferramentas ORM modernas. O objetivo das UoW é o agrupamento de funções e alterações aplicados sobre um banco de dados que possa ser executado de uma vez, ou abandonado por completo. Veja artigo de Martin Fowler.

COMMIT e ROLLBACK: Um COMMIT é a instrução para efetivar, tornando permanentes, as operações sobre o BD desde que o último COMMIT ou ROLLBACK foi feito. Um ROLLBACK é a instrução SQL usado para reverter o estado do BD para o estado tornado efetivo pela última operação COMMIT ou ROLLBACK.

Se uma transação for concluída com sucesso o banco de dados será alterado permanentemente, com gravação em disco dos dados alterados, na operação de COMMIT. Porém, se houver falha em qualquer uma das operações da transação, o banco deve ser deixado em seu estado inicial, coom um ROLLBACK.

Transações devem possuir início e fim e podem ser salvas (permanência no banco de dados) ou desfeitas. Se houver falha nenhuma operação deve ser tornada permanente.

No SQL transações são iniciadas com BEGIN TRANSACTION, e finalizada com COMMIT ou ROLLBACK. Essas operações estão ilustradas abaixo.

-- criamos uma tabela provisória de testes
SELECT matricula, nome INTO temp_aluno FROM aluno;

-- transação com rollback
BEGIN TRANSACTION
  DELETE FROM temp_aluno        -- apaga todos registros da tabela
  SELECT * FROM temp_aluno      -- a tabela está vazia
ROLLBACK TRANSACTION;           -- desfaz a transação
SELECT * FROM temp_aluno;       -- a tabela está como no início
                               
-- transação com commit        
BEGIN TRANSACTION              
  DELETE FROM temp_aluno        -- apaga todos registros da tabela
  SELECT * FROM temp_aluno      -- a tabela está vazia
COMMIT TRANSACTION;             -- confirma a transação
SELECT * FROM temp_aluno;       -- a tabela está vazia (permanente)

Fonte: Boson Treinamentos: Transacões, commit e rollback.

Bibliografia

Esse texto é baseado primariamente na documentação do SQLAlchemy, disponível em SQLAlchemy 2, Documentation. Outras referências no artigo Python e SQL: SQLAlchemy.