UPDATE e DELETE

Vimos nas sessões anteriores como aplicar os comandos do SQL, INSERT e SELECT, para a inserção de dados em um banco de dados e a recuperação das informações desejadas, em termos do SQLAlchemy. Claro que precisamos também de UPDATE E DELETE para atualizar as informações e linhas existentes. Essa seção cobre essas funções no CORE SQLAlchemy. As operações UPDATE e DELETE com ORM são normalmente chamadas internamente no objeto Session.

As construções com UPDATE e DELETE podem ser usadas diretamente com o ORM, usando o padrão conhecido denominado “atualização e exclusão habilitadas para ORM”. Por isso é necessário compreender essas construções feitas no CORE, antes de usá-las com o ORM.

UPDATE

No SQLAlchemy usamos a função update() para gerar uma instância do objeto Update que representa uma instrução UPDATE em SQL, responsável pela atualização de dados em uma tabela.

Assim como ocorre com insert(), existe uma forma padrão de emitir um update() que executa um UPDATE em uma única tabela por vez, sem retornar nenhuma linha. Alguns back-ends, por outro lado, dão suporte a UPDATEs que modificam várias tabelas de uma vez, além de suporte a RETURNING, de permite o retorno das colunas modificadas, como veremos.

from sqlalchemy import update
query = (
    update(aluno)
    .where(aluno.c.nome == "Marcos")
    .values(sobrenome="Abudab")
)
print(query)
↳ UPDATE aluno SET sobrenome=:sobrenome WHERE aluno.nome = :nome_1
# que, após a substituição dos parâmetros se torna
↳ UPDATE aluno SET sobrenome='Abudab' WHERE aluno.nome = 'Marcos'

O método Update.values() define qual será o conteúdo dos elementos SET em uma instrução UPDATE. Os parâmetros podem ser passados como pares chave = valor usando os nomes das colunas como chaves. Por ex., para atualizar o sobrenome de todos os alunos acrescentando “da Silva” fazemos:

query = update(aluno).values(sobrenome = aluno.c.sobrenome + " da Silva")
print(query)
↳ UPDATE aluno SET sobrenome=(aluno.sobrenome || :name_1) (' da Silva',)

Para fazer várias atualizações (no contexto “executemany”) usamos a construção bindparam() pode ser usada para configurar parâmetros vinculados; estes substituem os lugares onde os valores literais normalmente iriam:

from sqlalchemy import bindparam
query = (
    update(aluno)
    .where(aluno.c.nome == bindparam("nomeantigo"))
    .values(nome = bindparam("nomenovo"))
)
with engine.begin() as conn:
    conn.execute( query, 
        [
            {"nomeantigo": "Pedro", "nomenovo": "George"},
            {"nomeantigo": "Anita", "nomenovo": "Anitta"},
            {"nomeantigo": "Aluisio", "nomenovo": "Alonso"},
        ],
    )
[SQL]
UPDATE aluno SET nome=? WHERE aluno.name = ? 
[('Pedro', 'George'), ('Anita', 'Anitta'), ('Aluisio','Alonso')]

Observe que em .where() temos um operador de comparação == enquanto em values() temos uma atribuição, =. Fornecemos uma lista de dicionários com as chaves “nomeantigo” e “nomenovo” para serem substituídos como parâmetros nas três consultas a serem realizadas.

Outras possibilidades de uso de UPDATE

Updates Correlacionados: Uma instrução UPDATE pode usar linhas de outras tabelas obtidas em uma subconsulta. Subconsultas podem ser usada no lugar de qualquer expressão de coluna:

scalar_subq = (
    select(endereco.c.email)
    .where(endereco.c.aluno_id == aluno.c.id)
    .order_by(endereco.c.id)
    .limit(1)
    .scalar_subquery()
)
query = update(aluno).values(sobrenome=scalar_subq)
print(query)

↳ UPDATE aluno SET sobrenome=(SELECT endereco.email FROM endereco
     WHERE endereco.aluno_id = aluno.id ORDER BY endereco.id LIMIT 1)

LIMIT é uma cláusula SQL que especifica o número de linhas que devem ser retornadas no resultado de uma consulta. Nem todos os SGBDS dão suporte a esse recurso.

UPDATE FROM: Alguns bancos de dados, como PostgreSQL e MySQL, aceitam a sintaxe “UPDATE FROM” onde tabelas adicionais podem ser declaradas diretamente em uma cláusula FROM especial. No SQLAlchemy esse tipo de consulta é gerada quando existirem tabelas adicionais na cláusula WHERE da instrução:

query = (
    update(aluno)
    .where(aluno.c.id == endereco.c.aluno_id)
    .where(endereco.c.email == "asilva@gmail.com")
    .values(sobrenome="Silva")
)
print(query)

↳ UPDATE aluno SET sobrenome=:sobrenome FROM endereco
  WHERE aluno.id = endereco.aluno_id AND endereco.email = :email_1

Não se esqueça de que os parâmetros são substituídos na execução da query: :sobrenome -> 'Silva':email_1 -> 'asilva@gmail.com'.

UPDATE múltiplas tabelas: existe no MySQL uma forma específica para atualizar múltiplas tabelas simultaneamente. No SQLAlchemy nos referimos aos objetos Table adicionais dentro das cláusulas values.

query = (
    update(aluno)
    .where(aluno.c.id == endereco.c.aluno_id)
    .where(endereco.c.email == "patrick@aol.com")
    .values(
        {
            aluno.c.sobrenome: "Jones",
            endereco.c.email: "jones@aol.com",
        }
    )
)
from sqlalchemy.dialects import mysql
print(query.compile(dialect=mysql.dialect()))

↳ UPDATE aluno, endereco
  SET endereco.email=%s, aluno.sobrenome=%s
  WHERE aluno.id = endereco.aluno_id AND endereco.email = %s

Updates com parâmetros ordenados: Outra característica que existe apenas no MySQL é que a ordem dos parâmetros fornecidos na cláusula SET de um UPDATE modifica a ordem de execução de cada expressão. Para conseguir isso usamos o método Update.ordered_values() que aceita uma sequência de tuplas que são executadas na ordem em que aparecem na expressão.

query = update(tabela).ordered_values((tabela.c.y, 20), (tabela.c.x, tabela.c.y + 10))
print(query)
↳ UPDATE tabela SET y=:y, x=(tabela.y + :y_1)

O comando faz tabela.y=20 e depois tabela.x=tabela.y + 10 (ou seja, y=20, x =30).

Função delete()

A função delete() retorna uma instância do objeto Delete que contém as instruções de apagamento, traduzidas como um SQL DELETE, que apaga linhas de uma tabela, modificado pela clásula WHERE. Em geral a instrução não retorna linhas, embora seja possível o retorno de dados específicos em algumas variantes de SGBD.

from sqlalchemy import delete
query = delete(aluno).where(aluno.c.nome == "Mauro")
print(query)
↳ DELETE FROM aluno WHERE aluno.nome = 'Mauro'

DELETE em múltiplas tabelas: Assim como ocorre com UPDATE, é possível realizar apagamentos de linhas em várias tabelas, dependendo do dialeto usado. Por exemplo, no MySQL podemos usar:

query = (
    delete(aluno)
    .where(aluno.id == endereco.c.aluno_id)
    .where(endereco.c.email == "mauro@igmail.com")
)
from sqlalchemy.dialects import mysql
print(query.compile(dialect=mysql.dialect()))
↳ DELETE FROM aluno USING aluno, endereco
    WHERE aluno.id = endereco.aluno_id AND endereco.email = %s

Obtendo o número de linhas afetadas por UPDATE e DELETE: Tanto Update quanto Delete permitem o retorno de número de linhas afetadas pelo procedimento. Esse valor é extraído do atributo CursorResult.rowcount quando usamos Core Connection, acessado por meio de Connection.execute().

with engine.begin() as conn:
    result = conn.execute(
        update(aluno)
        .values(sobrenome="Aquino")
        .where(aluno.c.nome == "José")
    )
    print(result.rowcount)

[SQL]
UPDATE aluno SET sobrenome=? WHERE nome = ? ('Aquino', 'José')
# é retornado
↳ 1

CursorResult é uma subclasse de Result que contém outros atributos. Uma instância dessa subclasse é retornada sempre que uma instrução é passada para o método Connection.execute(). Quando se usa ORM o método Session.execute() sempre retorna um objeto CursorResult quando se executa INSERT, UPDATE, ou DELETE.

Sobre o atributo CursorResult.rowcount:

seu valor é o número de linhas que satisfazem a cláusula WHERE da instrução, independente do número de linhas efetivamente modificada.
esse valor pode não estar disponível para instruções UPDATE ou DELETE que usam RETURNING, ou para casos de execução executemany. Isso depende do módulo DBAPI em uso e das opções configuradas.
Existe o atributo CursorResult.supports_sane_multi_rowcount que indica se esse valor estará disponível para o backend em uso. Alguns drivers, especialmente dialetos de terceiros para bancos de dados não relacionais, podem não oferecer suporte a CursorResult.rowcount. A propriedade CursorResult.supports_sane_rowcount indicará isso.
rowcount é usado pelo processador do ORM para validar se uma instrução UPDATE ou DELETE correspondeu ao número esperado de linhas afetadas.

Usando RETURNING com UPDATE e DELETE: Assim como Insert, Update e Delete também dão suporte à cláusula RETURNING. Ele é inserido com os métodos Update.returning() e Delete.returning(). Quando o backend do BD aceita RETURNING as colunas selecionadas de todas as linhas que satisfazem o critério WHERE são retornadas no objeto Result como um objeto iterável. Isso significa que as linhas que podem ser percorridas por iteração.

query = (
    update(aluno)
    .where(aluno.c.nome == "Marcos")
    .values(sobrenome="Olímpio")
    .returning(aluno.c.id, aluno.c.nome)
)
print(query)
↳ UPDATE aluno SET sobrenome=:sobrenome
  WHERE aluno.nome = :nome_1
  RETURNING aluno.id, aluno.nome

query = (
    delete(table)
    .where(aluno.c.nome == "Jones")
    .returning(aluno.c.id, aluno.c.nome)
)
print(query)
↳ DELETE FROM aluno WHERE aluno.nome = :nome_1
  RETURNING aluno.id, aluno.nome

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.

Phylos.net

Pensar e Imaginar

DELETE

SQLAlchemy: UPDATE e DELETE