SQLAlchemy: UPDATE e DELETE

Posted on 14/03/2023 by Guilherme Silva

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.

SQLAlchemy: INSERT e SELECT

Posted on 11/03/202317/03/2023 by Guilherme Silva

Todos os tópicos →

Inserindo e selecionando dados

Definimos uma tabela na seção sobre Metadados. Uma vez definidas as tabelas com seus tipos de dados, vínculos e relacionamentos, o próximo passo consiste em realizar operações de inserção, extração, modificação e apagamento de dados.

INSERT

No SQL dados são inseridos nas tabelas com a instrução INSERT. Tanto ao usar CORE ou ORM a instrução INSERT é gerada com função insert(). No CORE usamos insert(tabela).values(valores). O objeto query obtido tem a representação de string mostrada abaixo. Ele possui o método compile() com têm parâmetros como params que armazena os campos e valores associados na consulta.

from sqlalchemy import insert
query = insert(aluno).values(matricula="12345-67890", nome="Marcos", sobrenome="Sobral")
print(query)
↳ INSERT INTO aluno (matricula, nome, sobrenome) VALUES (:matricula, :nome, :sobrenome)

# a query compilada possui propriedades
query_compilada = query.compile()
print(query_compilada.params)
{matricula:"12345-67890", nome:"Marcos", sobrenome:"Sobral"}

# para efetivar a consulta
with engine.connect() as conn:
    result = conn.execute(query)
    conn.commit()

# que gera a consulta
[SQL]
INSERT INTO aluno (matricula, nome, sobrenome) VALUES (?, ?)
    ("12345-67890", "Marcos", "Sobral")

# a chave primária da linha inserida pode ser recuperada
result.inserted_primary_key
↳ (1,)

A chave primária pode ser obtida quando a consulta insere apenas 1 linha. O método inserted_primary_key retorna uma tupla contendo todas as colunas que são chaves primárias (pois podem existir várias pks). Isso significa que uma cláusula RETURNING é inserida automaticamente sempre que o banco de dados subjacente der suporte à essa característica. No entanto é possível retornar outros valores além da chave primária. Isso é feito com o método Insert.returning(). Nesse caso o objeto Result retornado contém linhas que podem ser percorridas e lidas.

insert_query = insert(aluno).returning(aluno.c.matricula, aluno.c.nome, aluno.c.sobrenome)
print(insert_query)

[SQL]
INSERT INTO aluno (matricula, nome, sobrenome)
    VALUES (:matricula, :nome, :sobrenome)
    RETURNING aluno.matricula, aluno.nome, aluno.sobrenome

Para instruções INSERT o recurso RETURNING pode ser usado para instruções de uma única linha ou para múltiplas linhas (desde que tenham suporte no dialeto usado). RETURNING pode também ser usado com as instruções UPDATE e DELETE.

INSERT inclue a cláusula VALUES automaticamente se insert().values() não for usado. Se a consulta for executada com uma lista de valores, uma consulta é feita para cada elemento da lista. Por exemplo:

# se nenhum valor for fornecido
print(insert(aluno))
[SQL]
INSERT INTO aluno (id, matricula, nome, sobrenome, enderecos)
       VALUES (:id, :matricula, :nome, :sobrenome, :enderecos)

# fornecendo os valores
valores = [{matricula:"12345-67890", nome:"Marcos", sobrenome:"Sobral"},{matricula:"54321-12345", nome:"Joana", sobrenome:"Rosa"}]
with engine.connect() as conn:
    result = conn.execute(insert(alunos), valores,)
    conn.commit()

[SQL]
INSERT INTO aluno (id, matricula, nome, sobrenome, enderecos) VALUES (?, ?, ?)
       [("12345-67890", "Marcos", "Sobral"),("54321-12345", "Joana", "Rosa")]

No código acima valores é uma lista de dicionários, cada dicionário com os pares campo: valor. A consulta gerada é exibida, sempre respeitando o dialeto usado para o banco de dados usado. Uma consulta “vazia”, que insere apenas os valores default, pode ser realizada, mostrada abaixo.

# para inserir todos os valores default
insert(alunos).values().compile(engine)
[SQL]
INSERT INTO aluno DEFAULT VALUES

SELECT

A função select() é usada tanto no Core (passado com Connection.execute()) quanto ORM (passado com Session.execute()), resultando no objeto Result que contém as linhas retornadas pela consulta. Para a abordagem com ORM existem muitas outras formas de aplicar SELECT.

Podemos passar argumentos posicionais para a função select() para representar qualquer quantidade de objetos Table ou Column (ou outros objetos compatíveis). A cláusula FROM é inferida a partir desses argumentos.

from sqlalchemy import select

print(select(aluno))
↳ SELECT id, matricula, nome, sobrenome, enderecos FROM aluno

# alternativamente, podemos escolher as colunas a serem retornadas
print(select(aluno.c["nome", "sobrenome"]))
↳ SELECT aluno.nome, aluno.sobrenome FROM aluno

O modificador WHERE é um método do objeto retornado por select().

from sqlalchemy import select
query = select(aluno).where(aluno.c.nome == "Marcos")
print(query)
↳  SELECT id, matricula, nome, sobrenome, enderecos FROM aluno
   WHERE aluno.nome = :nome_1

# a consulta pode ser efetivada com connection.execute(query)
# e o resultado percorrido em um loop
with engine.connect() as conn:
    for linha in conn.execute(query):
        print(linha)

[SQL]
SELECT id, matricula, nome, sobrenome, enderecos FROM aluno
       WHERE user_account.name = ? ('Marcos',)

# uma única linha é retornada
↳ (1, '12345-67890', 'Marco', 'Sobral', '')

O SQLAlchemy adota uma forma de métodos encadeados (method chaining), que é chamada de generativa (generative) na documentação. Essa é uma técnica de orientação a objetos onde um objeto pode ter suas propriedades configuradas através de chamadas sucessivas aos seus métodos. Para isso cada método retorna o objeto modificado ou um novo objeto construído com as novas propriedades. Os métodos podem ser novamente chamados nesse novo objeto.Esse é o caso de objetos Select e Query. Um objeto Select, por exemplo, pode receber chamadas sucessivas aos métodos where() e order_by(). Assumindo tabela possui os campos id, campo1 e campo2:

query = (
        select(tabela.c.campo1)
        .where(tabela.c.id > 5)
        .where(tabela.c.campo2.like("e%"))
        .order_by(tabela.c.campo2)
    )

O método order_by() fornece a campo para ordenamento do resultado da consulta.
ORDER BY: Na linguagem de consulta SQL podemos ordenar as linhas retornadas por meio da cláusula ORDER BY. No SQLAlchemy ORDER BY é inserido com o método Select.order_by() que aceita parâmetros posicionais. Ordenamento crescente ou decrescente é obtido com os modificadores ColumnElement.asc() e ColumnElement.desc(). Por exemplo, consultas básicas no CORE e ORM podem ser obtidas:

print(select(aluno).order_by(aluno.c.name))
↳ SELECT aluno.id, aluno.nome, aluno.sobrenome FROM aluno ORDER BY aluno.nome
  
# usando classes do ORM
print(select(Aluno).order_by(Aluno.sobrenome.desc()))
↳ SELECT aluno.id, aluno.name, aluno.sobrenome FROM aluno ORDER BY aluno.sobrenome DESC

SELECT com ORM

Na abordagem ORM usamos Session.execute() para efetivar a consulta. Agora o resultado é formado não apenas por linhas de tuplas mas pelas próprias instâncias da classe Aluno.

query = select(Aluno).where(Aluno.nome == "Marcos")
with Session(engine) as session:
    for linha in session.execute(query):
        print(linha)
        
[SQL]
SELECT id, matricula, nome, sobrenome, enderecos FROM aluno
       FROM aluno WHERE aluno.nome = ? ('Marcos',)

# um único objeto é retornado
↳ (Aluno(id=1, matricula='12345-67890', nome='Marcos', sobrenome='Sobral', enderecos=''),)

A forma como esse objeto é exibido (com print()) é definida no método __repr__ (ou __str__).

Objetos gerados pelo ORM, sejam as classes que criamos como Aluno ou as colunas Aluno.nome são inseridos nas consultas SELECT da mesma forma que as próprias tabelas no CORE, gerando consultas idênticas.

# Lembrando que Aluno é a classe associada à tabela aluno
print(select(Aluno))
↳ SELECT id, matricula, nome, sobrenome, enderecos FROM aluno

Os comandos são executados com Session.execute(). Diferente das consultas com CORE, agora cada linha do resultado é um objeto Row, que são instâncias do objeto Aluno.

row = session.execute(select(Aluno)).first()
[SQL]
SELECT aluno.id, aluno.nome, aluno.sobrenome, aluno.enderecos FROM aluno

print(row)
(Aluno(id=1, matricula="12345-67890", nome='Marcos', sobrenome='Sobral'),)

# a primeira linha pode ser obtida
row = session.execute(select(Aluno)).first()

# outro método fornecido por conveniência é Session.scalars(), com o mesmo resultado
aluno = session.scalars(select(Aluno)).first()

Na consulta acima row (instância de Row) tem apenas um objeto (que é row[0]).

Para selecionar colunas específicas os atributos coluna da classe table são passados como argumento em select().

print(select(Aluno.matricula, Aluno.nome))
↳ SELECT aluno.matricula, aluno.nome FROM aluno

row = session.execute(select(Aluno.matricula, Aluno.nome)).first()
print(row)
↳ ('12345-67890', 'Marcos')

Essa abordagem pode ser mixta, como mostrado abaixo. Fazemos uma consulta no campo Aluno.nome e na tabela inteira Endereco. No objeto resultado usamos o método where() que restringe quais os endereços serão selecionados por linha. O método all() retorna todos os resultados obtidos na consulta.

session.execute(select(Aluno.nome, Endereco)
       .where(Aluno.id == Endereco.aluno_id)
       .order_by(Aluno.nome)).all()
[SQL]
SELECT aluno.nome, endereco.id, endereco.email, endereco.aluno_id
       FROM aluno, endereco WHERE aluno.id = endereco.aluno_id ORDER BY aluno.nome

Aliases são úteis em consultas SQL, principalmente quando se deseja citar várias tabelas em uma única consulta ou para simpĺificar a exibição de colunas com nomes longos ou aquelas construídas programaticamente. No SQLAlchemy elas são denominadas labels, inseridas nas consultas com o método ColumnElement.label().

query = select(("Nome do aluno: " + aluno.c.nome).label("Nome"),).order_by(aluno.c.nome)
[SQL]
SELECT "Nome do aluno: " || aluno.nome AS Nome FROM aluno ORDER BY aluno.nome

# o resultado da consulta é
↳ Nome do aluno: Joana
  Nome do aluno: Marcos

Vale lembrar que || é o operador de concatenação no SQLite e AS é opcional no SQLite (e em vários outros BDs), tanto para tabelas quanto para nome das colunas. É comum se usar AS em nomes de campos e ignorá-lo para nomear tabelas.

# são equivalentes:
SELECT aluno.nome, endereco.email FROM aluno JOIN endereco ON aluno.id = endereco.aluno_id
SELECT a.nome, e.email FROM aluno a JOIN endereco e ON a.id = e.aluno_id

# alias em nomes de campos são úteis em resultados compostos
SELECT a.nome || " " || a.sobrenome AS "Nome do Aluno"  FROM aluno a

O exemplo abaixo ilustra o uso de DESC para a ordenação em ordem descendente e o uso do alias na ordenação.

from sqlalchemy import desc
query = select(Aluno.matricula, Aluno.nome + " " + Aluno.sobrenome.label("nomealuno"))
        .order_by("nomealuno", desc("matricula"))
print(query)
SELECT aluno.matricula, aluno.nome || " " || aluno.sobrenome AS nomealuno
FROM aluno ORDER BY matricula, nomealuno DESC

Cláusula WHERE

Os operadores padrões de comparação do Python são usados para gerar objetos de consulta, e não apenas retornarem valores booleanos. Esses objetos são passados para o método Select.where()

print(aluno.c.nome == "Roberto")
↳ aluno.nome = :nome_1

print(endereco.c.aluno_id > 10)
↳ endereco.c.aluno_id > :aluno_id_1

print(select(aluno).where(aluno.c.nome == "Roberto"))
↳ SELECT aluno.id, aluno.matricula, aluno.nome, aluno.sobrenome, aluno.enderecos
  FROM aluno WHERE aluno.nome = :nome_1

Condições encadeadas com AND são produzidas pelo uso múltiplo de Select.where() ou pelo uso de múltiplas expressões como argumento de where().


# múltiplos where()
print(
     select(endereco.c.email)
     .where(user_table.c.name == "Joana")
     .where(endereco.c.aluno_id == aluno.c.id)
)

# ou, múltiplos argumentos (o que é equivalente)
print(select(endereco.c.email).where(user_table.c.name == "Joana",
      endereco.c.aluno_id == aluno.c.id))

# em ambos os casos o resultado é
[SQL]
↳ SELECT endereco.email FROM endereco, aluno
   WHERE aluno.nome = :nome_1 AND enderco.aluno_id = aluno.id

As junções lógicas AND e OR são obtidas com o uso das funções and_() e or_().

from sqlalchemy import and_, or_
print(select(Endereco.email).where(
    and_(
        or_(Aluno.nome == "Marcos", Aluno.nome == "Joana"),
        Endereco.aluno_id == Aluno.id,)
    )
)
↳ SELECT endereco.email FROM endereco, aluno
  WHERE (aluno.name = :name_1 OR aluno.name = :name_2)
  AND endereco.aluno_id = aluno.id

Observe que, se A, B e C são testes booleanos então and_(or_(A, B),C) é o mesmo que (A OR B) AND C).

Para outras comparações podemos usar filtros Select.filter_by() que recebe argumentos nomeados para testar em valores nas colunas ou nomes de atributos no ORM. O filtro age sobre a última cláusula FROM ou última tabela em Join.

print(select(Aluno).filter_by(nome="Marcos", matricula="12345-67890"))
↳ SELECT aluno.id, aluno.matricula, aluno.nome,  aluno.sobrenome, aluno.enderecos
  FROM aluno WHERE aluno.nome = :nome_1 AND aluno.matricula = :matricula_1

Vimos que a tabela default de onde os campos serão pesquisados com SELECT é inferida pelo código da pesquisa. Para usar mais de uma tabela temos que listar cada uma como argumentos, separados por vírgula.

# consulta em uma única tabela	
print(select(aluno.c.name))
↳ SELECT aluno.nome FROM aluno

# consulta em mais de uma tabela	
print(select(aluno.c.nome, endereco.c.email))
↳ SELECT aluno.nome, endereco.email FROM aluno, endereco

Para juntar (com JOIN) as tabelas usamos um dos dois métodos: Select.join_from(), que permite indicar o lado esquerdo e direito da junção explicitamente, e Select.join() que apenas define o lado direito (sendo o esquerdo inferido). Com Select.select_from() podemos explictar a tabela que queremos na cláusula FROM.

# usando .join_from()
print(select(aluno.c.nome, endereco.c.email).join_from(aluno, endereco))
↳ SELECT aluno.nome, endereco.email FROM aluno JOIN endereco ON aluno.id = endereco.aluno_id

# usando .join()
print(select(aluno.c.nome, endereco.c.email).join(endereco))
↳ SELECT aluno.nome, enderco.email FROM aluno JOIN endereco ON aluno.id = endereco.aluno_id

# usando .select_from()
print(select(endereco.c.email).select_from(aluno).join(endereco))
↳ SELECT endereco.email FROM aluno JOIN endereco ON aluno.id = endereco.aluno_id

Gerando cláusula ON: Nos exemplos anteriores vimosque a clásula ON foi inserida automaticamente. Isso ocorreu porque existe um relacionamento entre as tabelas aluno e endereco por meio de uma foreignkey. Se não exitir um vínculo desse tipo, ou se existirem vínculos entre várias tabelas a cláusula ON pode ser especificada explicitamente em ambas as funções Select.join() e Select.join_from().

print(
     select(endereco.c.email)
     .select_from(aluno)
     .join(endereco, aluno.c.id == endereco.c.aluno_id)
)
↳ SELECT endereco.email FROM aluno JOIN endereco ON aluno.id = endereco.aluno_id

LEFT OUTER JOIN, FULL OUTER JOIN: os dois métodos também admitem a especificação que leva à construção de LEFT OUTER JOIN e FULL OUTER JOIN. Isso é feito com Select.join.isouter e Select.join.full.

print(select(aluno).join(endereco, isouter=True))
↳ SELECT aluno.id, aluno.nome, aluno.sobrenome 
  FROM aluno LEFT OUTER JOIN endereco ON aluno.id = endereco.aluno_id

print(select(user_table).join(address_table, full=True))
↳ SELECT aluno.id, aluno.nome, aluno.sobrenome 
  FROM aluno FULL OUTER JOIN endereco ON aluno.id = endereco.aluno_id

Obs.: Existe o método Select.outerjoin() equivalente ao uso de .join(..., isouter=True).
SQLAlchemy não dá suporte à RIGHT OUTER JOIN. Para conseguir o mesmo efeito inverta a ordem das tabelas e use LEFT OUTER JOIN.

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.

SQLAlchemy: Agrupamentos e Subqueries

Posted on 11/03/202311/03/2023 by Guilherme Silva

Todos os tópicos →

Agrupamentos e ordenações

GROUP BY e funções agregadas: A cláusula GROUP BY permite o agrupamento de linhas de forma a aplicar funções agregadas sobre os grupos gerados. Para estabelecer condições e filtros sobre linhas agrupadas não usamos WHERE e sim a cláusula HAVING. Funções de agregação são uma forma de inserir cálculos, tais como somas, contagem, médias ou localização de máximos e mínimos, sobre todos os elementos de um grupo.

Resultado de GROUP BY para uma tabela hipotética

No SQLAlchemy as funções de agregação estão em um namespace chamado de func, que é o construtor de instâncias da classe Function. Por exemplo, para contar quantas linhas tem a tabela aluno escolhemos uma coluna com valores únicos (como id) e contamos quantas linhas existem.

from sqlalchemy import func
print(func.count(aluno.c.id))
↳ count(user_account.id)

Nesse último caso, como nenhum agrupamento foi feito com GROUP BY, o grupo considerado consiste na tabela inteira e a contagem se refere a todas as linhas. Caso a tabela tenha sido particionada com GROUP BY as funções de agregação serão aplicadas a cada grupo individualmente. Para selecionar ou filtrar grupos usamos a cláusula HAVING sobre valores agregados. SQLAlchemy fornece os métodos Select.group_by() e Select.having().

with engine.connect() as conn:
    result = conn.execute(
        select(Aluno.nome, func.count(Endereco.id).label("count"))
        .join(Endereco)
        .group_by(Aluno.nome)
        .having(func.count(Endereco.id) > 1)
    )
    print(result.all())

[SQL]
SELECT aluno.nome, count(endereco.id) AS count
FROM aluno JOIN endereco ON aluno.id = endereco.aluno_id
GROUP BY aluno.nome HAVING count(address.id) > 1

A consulta acima faz um JOIN das tabelas aluno e endereco, o que pode retornar várias linhas para cada aluno se ele tiver mais de um endereço registrado. Em seguida ela faz um agrupamento pelo nome e conta quantos endereços existem, retornando apenas aqueles que tem mais de um endereço.

O uso de aliases ou labels podem tornar uma instrução SQL mais legível por evitar repetições de nomes. Aliases também podem ser usados para ordenação e agrupamento.

from sqlalchemy import func, desc
query = (
    select(Endereco.aluno_id, func.count(Endereco.id).label("n_enderecos"))
    .group_by("aluno_id")
    .order_by("aluno_id", desc("n_enderecos"))
)
print(query)
↳ SELECT endereco.aluno_id, count(endereco.id) AS n_enderecos
  FROM endereco GROUP BY endereco.aluno_id ORDER BY endereco.aluno_id, n_enderecos DESC

Essa consulta faz um agrupamento da tabela endereco no campo endereco.aluno_id (ou seja, agrupa todos os endereços de cada aluno), conta endereços armazenando a contagem como n_enderecos e exibe o resultado por ordem crescente de endereco.aluno_id, decrescente em n_enderecos. O nome provisório n_enderecos (um label ou alias) é retornado e serve como ordenador das linhas no resultado.

Aliases, no CORE: Também acontece de precisarmos usar o nome de várias tabelas quando fazemos consultas com JOIN e os nomes precisam ser usados várias vezes na mesma consulta. Na SQLAlchemy Expression Language esses aliases são construídos como o objeto Alias retornados pelo método FromClause.alias(). Um objeto construído como um Alias tem a mesma estrutura de uma Table, possuindo objetos Column em sua coleção Alias.c.

alias_1 = aluno.alias()
alias_2 = aluno.alias()
print(
    select(alias_1.c.name, alias_2.c.name)
    .join_from(alias_1, alias_2, alias_1.c.id > alias_2.c.id)
)
↳ SELECT alias_1.nome AS nome_1, alias_2.nome AS nome_2
  FROM aluno AS alias_1 JOIN aluno AS alias_2 ON alias_1.id > alias_2.id

Duas “tabelas” temporárias são construídas a partir da tabela aluno, com os nomes alias_1 e alias_2, que podem ser consultadas independentemente. Essa consulta retorna todos os pares de nomes da tabela aluno, sem repetições.

Subqueries e CTEs

Subqueries (subconsultas) são instruções SELECT realizadas entre parênteses, dentro de uma consulta que a envolve (geralmente outro SELECT). O resultado da subconsulta é usado para a consulta que a envolve. O SQLAlchemy usa o objeto Subquery (em Select.subquery()) para representar uma subconsulta. Qualquer objeto pode ser usado como um elemento FROM dentro de uma construção select() maior.

Como exemplo vamos construir uma subconsulta que selecionará uma contagem agregada de linhas da tabela de endereços (funções agregadas e GROUP BY foram introduzidas anteriormente em Funções Agregadas com GROUP BY / HAVING):

sub_query = (
    select(func.count(endereco.c.id).label("contagem"), endereco.c.aluno_id)
    .group_by(endereco.c.aluno_id).subquery()
)

# sub_query é uma consulta SELECT (sem os parênteses)
print(sub_query)
↳ SELECT count(endereco.id) AS countagem, endereco.aluno_id
  FROM endereco GROUP BY endereco.aluno_id

O objeto Subquery se comporta como qualquer outro objeto FROM (como uma Table), incluindo uma Subquery.c que contém as colunas selecionadas, de onde podemos fazer referência às colunas aluno_id e aquela rotulada “contagem” que contém o resultado da função agregada. Essa subconsulta pode ser usada como parte de FROM em uma nova consulta.

print(select(sub_query.c.user_id, sub_query.c.contagem))
↳ SELECT alias_1.aluno_id, alias_1.contagem FROM
  (SELECT count(endereco.id) AS contagem, endereco.aluno_id
  FROM endereco GROUP BY endereco.aluno_id) AS alias_1

A subconsulta retorna uma tabela com linhas agrupadas por aluno (aluno.id) e o número de endereços registrados para cada um deles no campo “contagem”. A consulta externa simplesmente lista esses linhas. Consultas mais gerais podem ser realizadas, usando a subquery como uma tabela qualquer.

query = select(aluno.c.nome, aluno.c.sobrenome, sub_query.c.contagem).join_from(aluno, sub_query)
print(query)
↳ SELECT aluno.nome, aluno.sobrenome, alias_1.contagem FROM aluno JOIN
     (SELECT count(endereco.id) AS contagem, endereco.aluno_id AS aluno_id FROM endereco
     GROUP BY endereco.aluno_id) AS alias_1 ON aluno.id = alias_1.aluno_id

A subconsulta gera a tabela com os campos alias_1.contagem e alias_1.aluno_id. A consulta externa faz um JOIN dessa tabela com aluno, retornando nome e sobrenome de cada aluno, junto com o número de endereços cada um tem registrado. A cláusula ON, nesse caso, foi inferida com base nos vínculo e na chave extrangeira (aluno.id = alias_1.aluno_id).

CTEs ou Common Table Expression (Expressões Comuns de Tabelas) são usadas de modo similar às subconsultas mas possuindo recursos adicionais. Um objeto CTE é construído com método Select.cte() e, da mesma forma que as subqueries, pode ser usado como tabela dentro da cláusula FROM. O uso é muito similar ao das subqueries mas o comando SQL gerado é bastante diferente.

ctable = (
    select(func.count(endereco.c.id).label("contagem"), endereco.c.aluno_id)
    .group_by(endereco.c.aluno_id)
    .cte()
)
query = select(aluno.c.nome, aluno.c.sobrenome, ctable.c.contagem).join_from(aluno, ctable)

print(query)
↳ WITH anon_1 AS
  (SELECT count(endereco.id) AS contagem, endereco.aluno_id AS aluno_id
    FROM endereco GROUP BY endereco.aluno_id)
  SELECT aluno.nome, aluno.sobrenome, anon_1.contagem
  FROM aluno JOIN anon_1 ON aluno.id = anon_1.aluno_id

A cláusula WITH gera uma tabela temporária (criada pela consulta entre parânteses) a atribui a ela o aliás anon_1. Essa tabela pode ser usada na consulta que se segue.

Subqueries e CTEs com ORM

Aliases, no ORM: O equivalente ORM para gerar aliases é a função aliased(). Quando aplicada a um objeto de classes mapeadas em tabelas, como Aluno e Endereco, essa função retorna um objeto que representa o objeto Table original, mantendo toda a sua funcionalidade ORM original.

from sqlalchemy.orm import aliased
Alias_1 = aliased(Address)
Alias_2 = aliased(Address)
print(
    select(Aluno)
    .join_from(Aluno, Alias_1)
    .where(Alias_1.email == "marcos@aol.com")
    .join_from(Aluno, Alias_2)
    .where(Alias_2.email == "soares@gmail.com")
)
↳ SELECT aluno.id, aluno.nome, aluno.sobrenome
  FROM aluno
  JOIN endereco AS alias_1 ON aluno.id = alias_1.aluno_id
  JOIN endereco AS alias_2 ON aluno.id = alias_2.aluno_id
  WHERE alias_1.email = :email_1 AND alias_2.email = :email_2

O SELECT acima seleciona id, nome e sobrenome da tabela aluno que têm dois endereços de e-mail especificados (no caso “marcos@aol.com” e “soares@gmail.com”).

Subqueries e CTEs: No caso das classes do ORM o método subquery() gera objeto semelhante à Table, com todas as suas propriedades. O mesmo ocorre com aliased() que pode armazenar uma Tabela inteira ou o resultado de uma consulta.

sub_query = select(Endereco).where(~Endereco.email.like("%@gmail.com")).subquery()
endereco_query = aliased(Endereco, sub_query)
query = (
    select(Aluno, endereco_query)
    .join_from(Aluno, endereco_query)
    .order_by(Aluno.id, endereco_query.id)
)
with Session(engine) as session:
    for aluno, endereco in session.execute(query):
        print(f"{aluno} {endereco}")

# a seguinte consulta é executada
[SQL]
SELECT aluno.id, aluno.nome, aluno.sobrenome,
  anon_1.id AS id_1, anon_1.email, anon_1.aluno_id
FROM aluno JOIN (
  SELECT endereco.id AS id, endereco.email AS email, endereco.aluno_id AS aluno_id
     FROM endereco WHERE endereco.email NOT LIKE ?
  ) AS anon_1 ON aluno.id = anon_1.user_id
  ORDER BY aluno.id, anon_1.id ('%@gmail.com',)

Nessa consulta anon_1 é a representação SQL de endereco_query que, por sua vez, é um aliás para a consulta em sub_query sobre a tabela endereco. A consulta retorna id, nome e sobrenome de alunos, id e email de endereços para aqueles que não possuem email do gmail. Uma consulta análoga com cte pode ser realizada substituindo subquery() por cte() na consulta acima. A consulta gerada usa WITH.

O operador de negação ~ foi usado acima. Por exemplo: ~Aluno.enderecos.any() seleciona as linhas
de Aluno que não possuem endereços cadastrados.

query = select(Aluno.nome).where(~Aluno.enderecos.any())
session.execute(query).all()
# a seguinte consulta é executada
↳ SELECT aluno.nome FROM aluno
  WHERE NOT (EXISTS (SELECT 1 FROM endereco WHERE aluno.id = endereco.aluno_id))

A consulta interna retorna 1 se a linha de aluno possui algum endereço associado.

Subqueries escalares e correlacionadas

Uma subquery escalar (scalar subquery) é uma subquery que retorna apenas 1, ou nenhuma, linha em uma única coluna. Diferente de uma consulta com mais de uma linha e uma coluna, ela pode ser usada em cláusulas WHERE de um SELECT subjacente (que envolve a subquery).
Esse tipo de objeto é obtido com o método .scalar_subquery() e é frequentemente usado com funções de agregamento.

sub_query = (
    select(func.count(endereco.c.id))
    .where(aluno.c.id == endereco.c.aluno_id)
    .scalar_subquery()
)
print(sub_query)
↳ (SELECT count(endero.id) AS count_1 FROM endereco, aluno
  WHERE aluno.id = endereco.aluno_id)

O comando SQL gerado é uma query usual e o resultado da consulta é uma única linha com a coluna count_1. A subquery pode ser usada como qualquer outra expressão de coluna.

print(sub_query == 5)
↳ (SELECT count(endereco.id) AS count_1 FROM endereco, aluno
  WHERE aluno.id = endereco.aluno_id) = :param_1 {5, }

No exemplo abaixo a subquery é usada em uma consulta ****

Uniões entre tabelas: UNION

Com consultas SQL usuais podemos unir o resultado de duas consultas obtidas com SELECT usando os operadores UNION e UNION ALL. Eles produzem o conjunto de todas as linhas resultantes em cada uma das consultas envolvidas. Também estão disponíveis consultas com INTERSECT [ALL] e EXCEPT [ALL]. No SQLAlchemy essas operações são obtidas com as funções union(), intersect() e except_(), além de union_all(), intersect_all() e except_all() para incluir o modificador ALL. Todas essas funções aceitam um número arbitrário de conjuntos selecionáveis.

O resultado dessas funções é um CompoundSelect que é usado da mesma forma que um objeto resultante de Select, embora tenha menos métodos. Por exemplo:

from sqlalchemy import union_all

query1 = select(aluno).where(aluno.c.nome == "Marcos")
query2 = select(aluno).where(aluno.c.nome == "Joana")
u = union_all(query1, query2)
with engine.connect() as conn:
    result = conn.execute(u)
    print(result.all())
[SQL]
SELECT aluno.id, aluno.nome, aluno.sobrenome, aluno.enderecos
FROM aluno WHERE aluno.nome = ?
UNION ALL
SELECT aluno.id, aluno.nome, aluno.sobrenome, aluno.enderecos
FROM aluno WHERE aluno.nome = ?

↳ (Marcos', 'Joana')

O resultado são os dados dos alunos “Marcos” e “Joana”.

Ainda usando a união construída acima podemos ilustrar como usar o resultado da união (que é um objeto CompoundSelect) como uma subquery. O próprio objeto possui um método Subquery.

u_subq = u.subquery()
query = (
    select(u_subq.c.name, aluno.c.email)
    .join_from(endereco, u_subq)
    .order_by(u_subq.c.name, endereco.c.email)
)
with engine.connect() as conn:
    result = conn.execute(query)
    print(result.all())

Uniões com ORM

Os exemplos acima mostram a união construída à partir de objetos Table retornando linhas do BD. Usando os objetos do ORM (as classes representando tabelas) construímos um objeto CompoundSelect que representa a união das entidades ORM e, portanto, das tabelas que elas mapeiam. O método Select.from_statement() pode ser usado para converter o objeto obtido de union_all() em um selecionável (que é uma coleção). Nesse caso UNION representa a consulta inteira, não necessitando de critérios adicionais. O exemplo tem o mesmo efeito que a consulta anterior feita com CORE.

query1 = select(Aluno).where(Aluno.nome == "Marcos")
query2 = select(Aluno).where(Aluno.nome == "Joana")
u = union_all(query1, query2)

orm_query = select(Aluno).from_statement(u)
with Session(engine) as session:
    for obj in session.execute(orm_query).scalars():
        print(obj)

Alternativamente, uma UNION pode ser usada como subquery e composta com outro objeto ORM por meio da função aliased(). No exemplo abaixo podemos adicionar critérios adicionais como ORDER BY fora do próprio UNION, inserindo filtros ou ordenamentos nas colunas geradas em uma subconsulta.

aluno_alias = aliased(Aluno, u.subquery())
orm_query = select(aluno_alias).order_by(aluno_alias.id)
with Session(engine) as session:
    for obj in session.execute(orm_query).scalars():
        print(obj)

↳ Aluno(id=1, matricula="12345-67890", nome='Marcos', sobrenome='Sobral', enderecos="")
  Aluno(id=2, matricula="54321-12345", nome='Joana', sobrenome='Rosa', enderecos="")

[SQL]
SELECT anon_1.id, anon_1.matricula, anon_1.nome, anon_1.sobrenome, anon_1.enderecos
FROM (SELECT aluno.id AS id, aluno.matricula AS matricula,
      aluno.nome AS nome, aluno.sobrenome AS sobrenome, aluno.enderecos AS enderecos
      FROM user_account WHERE user_account.name = ?
   UNION ALL
      SELECT aluno.id AS id, aluno.nome AS nome, aluno.sobrenome AS sobrenome,
      aluno.enderecos AS enderecos
FROM aluno WHERE aluno.nome = ?) AS anon_1 ORDER BY anon_1.id
('Marcos', 'Joana')

O resultado, visualizado com print(obj) são objetos ORM (como Aluno).

A subquery EXISTS

No SQL podemos usar o operador EXISTS junto com subconsultas escalares (aqueles que retornam apenas uma linha, ou nenhuma) para retornar um booleano informando se a instrução SELECT retorna uma linha (TRUE) ou nenhuma (FALSE). Esse funcionalidade é conseguida no SQLAlchemy com o uso de uma variante do objeto ScalarSelect chamado Exists.

subq = (
    select(func.count(endereco.c.id))
    .where(aluno.c.id == endereco.c.aluno_id)
    .group_by(endereco.c.aluno_id)
    .having(func.count(endereco.c.id) > 1)
).exists()
with engine.connect() as conn:
    result = conn.execute(select(aluno.c.nome).where(subq))
    print(result.all())
    
[SQL]
SELECT aluno.nome FROM aluno WHERE EXISTS
  (SELECT count(endereco.id) AS count_1 FROM endereco
   WHERE aluno.id = endereco.aluno_id GROUP BY endereco.aluno_id
   HAVING count(endereco.id) > ?) (1,)

A consulta SELECT em subq é uma consulta que faz uma JOIN de aluno com endereco, agrupa e conta quantos endereços existem para cada aluno, retornando apenas aqueles que possuem mais de um endereço. A função exists() retorna TRUE se existir alguma linha nessa consulta, FALSE se nenhuma linha existe. Esse conjunto de boolenos é usado na consulta seguinte para retornar os nomes dos alunos que satisfazem à condição descrita.

EXISTS é mais usada como uma negação, como em NOT EXISTS. Ela fornece uma forma de localizar linhas de uma tabela associada à outra tabela que não possui linhas no relacionamento. Por exemplo, para encontrar nomes de alunos que não possuem endereços de e-mail podemos fazer:

subq = (select(endereco.c.id).where(aluno.c.id == endereco.c.aluno_id)).exists()
with engine.connect() as conn:
    result = conn.execute(select(aluno.c.nome).where(~subq))
    print(result.all())

[SQL]
SELECT aluno.nome FROM aluno WHERE NOT (EXISTS 
   (SELECT endereco.id FROM endereco WHERE aluno.id = endereco.aluno_id)
)

Observe o uso de ~ para negar o resultado de subq dentro da segunda cláusula WHERE.

Funções SQL

Funções do SQL são utilizadas em conjunto com agrupagamentos e filtros (GROUP BY, HAVING), ou sobre linhas ou campos individuais. Elas foram introduzidas na seção sobre agrupamentos. No SQLAlchemy o objeto func funciona como uma fábrica de funções (objetos da classe Function) que podem ser usados em uma construção tipo select() para representar uma função SQL. Elas consistem em um nome, parênteses (na maioria das vezes) e possíveis argumentos. Seguem alguns exemplos de funções SQL.

# count(): função sobre linhas agregadas, conta quantas linhas foram retornadas
print(select(func.count()).select_from(aluno))
↳ SELECT count(*) AS count_1 FROM aluno

# lower(): converte um string em minúsculas:
print(select(func.lower("A String With Much UPPERCASE")))
↳ SELECT lower(:lower_2) AS lower_1
(a string with much uppercase)

# now(): fornece data e hora atual. In sqlite:
query = select(func.now())
with engine.connect() as conn:
    result = conn.execute(query)
    print(result.all())

SELECT CURRENT_TIMESTAMP AS now_1

O resultado, a consulta SQL, depende do dialeto e BD usado. Como os diversos dialetos incluem muitas funções (que podem variar entre eles) o método func aceita parâmetros de forma liberal, tentando contruir com eles uma função válida. Por outro lado existe um conjunto pequeno de funções comuns a diversas versões do SQL, como count, now, max, concat, que possuem versões pre-definidas.

# uso de nome genérico
print(select(func.uma_funcao_qualquer(tabela.c.campo, 17)))
↳ SELECT uma_funcao_qualquer(tabela, :uma_funcao_qualquer_2) AS uma_funcao_qualquer_1 FROM tabela

# uso de função comum no postgresql e no oracle
from sqlalchemy.dialects import postgresql
print(select(func.now()).compile(dialect=postgresql.dialect()))
↳ SELECT now() AS now_1

from sqlalchemy.dialects import oracle
print(select(func.now()).compile(dialect=oracle.dialect()))
↳ SELECT CURRENT_TIMESTAMP AS now_1 FROM DUAL

O segundo exemplo acima compara a geração da função SQL no PostgreSQL e no Oracle para a função now().

Tipos definidos de retorno: Algumas funções (mas não todas) retornam objetos com tipo de dados SQL definido. Eles serão chamados aqui de “tipos de retorno SQL” para diferenciá-los do “tipo de retorno” de uma função do Python. O tipo de retorno SQL de qualquer função SQL pode ser verificado com a leitura do atributo Function.type. Por exemplo func.now().type retorna DateTime(). Essa verificação é útil principalmente para debugging.

Considerar o tipo de retorno SQL pode ser importante dentro de uma declaração longa. Operadores matemáticos têm melhor desempenho quando atuam sobre expressões que retornam Integer ou Numeric, por exemplo. Existem operadores que esperam receber parâmetros JSON e funções que retornam colunas ao invés de linhas; as chamadas funções com valor de tabela. Portanto pode ser importante detectar o tipo de objeto que está em uso em algum ponto do código.

O tipo de retorno SQL da função também pode ser significativo quando o SQLAlchemy deve processar o resultado sobre um conjunto de resultados. Um bom exemplo são as funções de datas no SQLite, onde o SQLAlchemy deve converter strings em objetos datetime() do Python. Para garantir que um objeto de tipo específico seja aplicado a uma função passamos o parâmetro Function.type_ especificando esse tipo. No exemplo abaixo passamos a classe JSON para gerar a função PostgreSQL json_object(), lembrando que o tipo de retorno do SQL será do tipo JSON:

# passando a classe JSON para gerar um json_object() do PostgreSQL
from sqlalchemy import JSON
function_expr = func.json_object('{a, 1, b, "def", c, 3.5}', type_=JSON)	
query = select(function_expr["def"])
print(query)
↳ SELECT json_object(:json_object_1)[:json_object_2] AS anon_1

O retorno dessa consulta será do tipo JSON.

As funções built-in, como count, max, min, algumas funções de data como now e funções de string como concat, têm tipos de retorno SQL pré-estabelecidos. Em alguns casos esse tipo depende dos argumentos fornecidos.

m1 = func.max(Column("Coluna_de_inteiros", Integer))
m1.type
↳ Integer()

m2 = func.max(Column("Coluna_de_strings", String))
m2.type
↳ String()

Funções de data e hora do SQLAlchemy correspondem às expressões SQL DateTime, Date ou Time. Uma função de string, como concat() retorna strings, como esperado.

func.now().type
↳ DateTime()

func.current_date().type
↳ Date()

func.concat("x", "y").type
↳ String()

No entanto o SQLAlchemy não tem tipo definido de retorno para a maioria das funções SQL. Isso significa que ele não tem essas funções pre-definidas mas apenas converte as consultas em SQL válido. Por exemplo, func.lower() e func.upper() para converter strings em minúsculas e maiúsculas, têm tipo de retorno SQL “nulo”. Isso não significa nenhum problema para funções simples (como lower() e upper()) pois strings podem ser recebidas do banco de dados sem tratamento de tipo no lado do SQLAlchemy e as regras internas de coerção de tipo do SQLAlchemy podem interpretar corretamente a intenção: por ex., o operador Python + do python é interpretado como operador de concatenação de strings ou soma, dependendo dos argumentos usados.

# upper() e json_object() não tem tipo pre-definido	
func.upper("lowercase").type
↳ ()

func.json_object('{"a", "b"}').type
↳ NullType()


# inferência automática de tipo
print(select(func.upper("tudo minúscula") + " sufixo"))
↳ SELECT upper(:upper_1) || :upper_2 AS anon_1

Funções de janela SQL

Técnicas Avançadas de Função SQL: alguns gerenciadores de BD, como o PostgreSQL, dão suporte a um conjunto mais avançados de técnicas no uso de funções, em particular aquelas que retornam colunas ou tabelas, muito usadas quando se trabalha com dados em formato JSON.

Vimos que as funções SQL de agregação reúnem linhas sob a cláusula GROUP BY e realizam o cálculo sobre os grupos resultantes. A informação individual de cada linha fica perdida. As funções da janela SQL são diferentes: eles calculam seu resultado com base em um conjunto de linhas, retendo as informações individuais das linhas. Com elas podemos gerar um conjunto de resultados que incluem atributos de uma linha individual.

No SQLAlchemy, todas as funções SQL geradas pelo namespace func incluem um método FunctionElement.over() que insere o modificador OVER na consulta SQL para a determinação de janelas.

Uma função comum usada com funções de janela é a função row_number() que conta as linhas. Podemos particionar essa contagem de linhas em relação ao nome de usuário para numerar os endereços de e-mail de usuários individuais:

query = (
    select(
        func.row_number().over(partition_by=aluno.c.nome),
        aluno.c.nome,
        endereco.c.email,
    )
    .select_from(aluno)
    .join(endereco)
)
with engine.connect() as conn:  
    result = conn.execute(query)
    print(result.all())
[SQL]
SELECT row_number() OVER (PARTITION BY aluno.nome) AS anon_1,
aluno.nome, endereco.email
FROM aluno JOIN endereco ON aluno.id = endereco.aluno_id

A consulta acima faz a junção entre aluno e endereco (pelo id do aluno) e retorna quantas linhas existem para cada aluno, com seu respectivo email.

Coerção de tipos de dados

No SQL podemos obrigar um valor resultado de uma consulta a ter um tipo específico, como string ou inteiro. Isso é feito com o operador CAST. No SQLAlchemy temos a função cast() que recebe uma expressão de coluna e um tipo de dado como argumento.

from sqlalchemy import cast
query = select(cast(user_table.c.id, String))
with engine.connect() as conn:
    result = conn.execute(query)
    result.all()
[SQL]
SELECT CAST(aluno.id AS VARCHAR) AS id FROM aluno
# resultando em strings
[('1',), ('2',), ('3',)]

Ocorre às vezes a necessidade de informar ao SQLAlchemy o tipo de dado de uma expressão para uso no código python mas sem renderizar a expressão CAST do lado SQL. Para conseguir isso podemos usar a função type_coerce(). Ela é particularmente importante quando usamos o tipo de dados JSON que pode em si mesmo conter informação de tipos de dados. No ex. usamos type_coerce() para entregar uma estrutura Python como uma string JSON em uma das funções JSON do MySQL:

import json
from sqlalchemy import JSON, type_coerce
from sqlalchemy.dialects import mysql

s = select(type_coerce({"nome_campo": {"foo": "bar"}}, JSON)["nome_campo"])
print(s.compile(dialect=mysql.dialect()))
↳ SELECT JSON_EXTRACT(%s, %s) AS anon_1

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.

SQLAlchemy – ORM (Exemplo de Uso)

Posted on 07/02/2023 by Guilherme Silva

Todos os tópicos →

SQLAlchemy ORM

O SQLAlchemy Object Relational Mapper fornece métodos de associação de classes Python definidas pelo usuário com tabelas de banco de dados e instâncias dessas classes (objetos) com linhas em suas tabelas correspondentes, tipos de dados, vínculos e relacionamentos. Ele sincroniza de forma transparente todas as mudanças de estado entre objetos e suas linhas relacionadas, e inclui uma forma de expressação de consultas ao banco de dados como manipulações das classes do Python.

O ORM está construído sobre a SQLAlchemy Expression Language (CORE) mas enfatizando muito mais o modelo definido pelo usuário, garantindo a sincronia entre as duas camadas. Um aplicativo pode ser construído com o uso exclusivo do ORM, embora existam situções em que a Expression Language pode ser usada para fazer interações específicas com o banco de dados.

CREATE TABLE: Para ilustrar a criação de tabelas, inserção de dados, alteração e apagamento de valores listamos aqui o código do python. Outputs de código são precedidos pelo sinal ↳ e os comandos SQL emitidos internamente em quadros iniciados por [SQL].

A classe DeclarativeBase é a base de todas as classes que geram as classes do Python mapeadas em tabelas do banco de dados. Os tipos de cada coluna são informados com anotações com tipos tratados por Mapped[type], onde type é
int (INTEGER), str (VARCHAR), etc. Campos que podem ser nulos são declarados com o modificador Optional[type] (caso contrário o campo é NOT NULL).

A função mapped_column informa tipos e todos os demais atributos da coluna, como a informação de que ela é uma chave estrangeira. A função relationship() estabelece relacionamentos entre classes (portanto entre campos das tabelas). O método de classe __repr__() não é obrigatório mas pode ser útil para debugging. O parâmetro echo=True faz com que o comando SQL subjacente seja exibido no console.

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

engine = create_engine('sqlite:///contatos.db' , echo=True)

class Base(DeclarativeBase):
    pass

class Pessoa(Base):
    __tablename__ = "pessoas"

    id: Mapped[int] = mapped_column(primary_key=True)
    nome: Mapped[str] = mapped_column(String(30))
    sobrenome: Mapped[Optional[str]]
  
    enderecos: Mapped[list["Endereco"]] = relationship(back_populates="pessoa",
               cascade="all, delete-orphan")
    def __repr__(self) -> str:
        return f"Pessoa (id = {self.id!r}, nome={self.nome!r}, sobrenome={self.sobrenome!r})"

class Endereco(Base):
    __tablename__ = "enderecos"

    id: Mapped[int] = mapped_column(primary_key=True)
    pessoa_id: Mapped[int] = mapped_column(ForeignKey("pessoas.id"))

    email: Mapped[Optional[str]]
    endereco: Mapped[Optional[str]]
    pessoa: Mapped["Pessoa"] = relationship(back_populates="enderecos")

    def __repr__(self) -> str:
        return f"Endereco: (id = {self.id!r}, email = {self.email!r})"

Base.metadata.create_all(engine)

O comando Base.metadata.create_all(engine) cria um banco de dados e tabelas, se elas não existirem previamente. Os seguintes comandos são gerados.

[SQL]
CREATE TABLE pessoas (
	id INTEGER NOT NULL, 
	nome VARCHAR(30) NOT NULL, 
	sobrenome VARCHAR, 
	PRIMARY KEY (id)
)
CREATE TABLE enderecos (
	id INTEGER NOT NULL, 
	pessoa_id INTEGER NOT NULL, 
	email VARCHAR, 
	endereco VARCHAR, 
	PRIMARY KEY (id), 
	FOREIGN KEY(pessoa_id) REFERENCES pessoas (id)
)

Essa estrutura é denominada Mapeamento Declarativo (Declarative Mapping), responsável pela definição das classes Python e das tabelas, campos e relacionamentos que ficam armazenados em um objeto MetaData (embora esse não seja mencionado explicitamente no código). Temos, como resultado, a criação das tabelas e campos:

pessoas
id
nome
sobrenome

enderecos
id
pessoa_id
email
endereco

INSERT: Para inserirmos valores nas tabelas instanciamos objetos das classes Pessoa e Endereco (que são atribuidos ao campo Pessoa.enderecos). Criamos um objeto session = Session(engine) (dentro de um gerenciador de contexto width) e depois acrescentamos os objetos à sessão com session.add_all([lista_de_objetos]). Nenhuma alteração é gravada no banco de dados até a emissão de session.commit().

galileu = Pessoa(nome="Galileu", sobrenome="Galilei")
paulo = Pessoa(
    nome="Paul",
    sobrenome="Adrian Dirac",
    enderecos=[Endereco(email="pamdirac@hotmail.com")],
)
alberto = Pessoa(
    nome="Albert",
    sobrenome="Einstein",
    enderecos=[Endereco(email="albert@tre.org")],
)
ricardo = Pessoa(
    nome="Richerd",
    sobrenome="Feynman",
    enderecos=[
        Endereco(email="feynman@caltech.edu", endereco="R. Bahia, 2311"),
        Endereco(email="richar@google.com"),
    ],
)

width Session(engine) as session:
    session.add_all([paulo, alberto, ricardo, galileu])
    session.commit()

O nome Richerd foi digitado com erro propositalmente. As consultas são emitidas:

[SQL]
INSERT INTO pessoas (nome, sobrenome) VALUES (?, ?), (?, ?), (?, ?), (?, ?)
    ('Paul', 'Adrian Dirac', 'Albert', 'Einstein', 'Richerd', 'Feynman', 'Galileu', 'Galilei')

INSERT INTO enderecos (pessoa_id, email, endereco) VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?)
(5, 'pamdirac@hotmail.com', None, 6, 'albert@tre.org', None, 7, 'feynman@caltech.edu', 'R. Bahia, 2311', 7, 'richar@google.com', None)

Como resultado temos as tabelas com os seguintes valores:

id	nome	sobrenome
1	Paul	Adrian Dirac
2	Albert	Einstein
3	Richerd	Feynman
4	Galileu	Galilei

id	pessoa_id	email	endereco
1	1	pamdirac@hotmail.com	NULL
2	2	albert@tre.org	NULL
3	3	feynman@caltech.edu	R. Bahia, 2311
4	3	richar@google.com	NULL

SELECT: Consultas podem ser feitas com a classe select. Uma query tem a sintaxe básica query = select(Classe_tabela).where(condicao_na_classe). o resultado é um iterável:

from sqlalchemy import select

session = Session(engine)
query = select(Pessoa).where(Pessoa.nome.in_(["Galileu", "Paul"]))

for p in session.scalars(query):
    print(p)
↳   Pessoa (id = 1, nome='Paul', sobrenome='Adrian Dirac')
    Pessoa (id = 4, nome='Galileu', sobrenome='Galilei')

A consulta equivalente é:

[SQL]
SELECT pessoas.id, pessoas.nome, pessoas.sobrenome FROM pessoas
    WHERE pessoas.nome IN (?, ?) ('Galileu', 'Paul')

Uma consulta SELECT * pode ser feita diretamente por id:

print(session.get(Pessoa, 4))
↳ Pessoa (id = 4, nome='Galileu', sobrenome='Galilei')
print(session.get(Pessoa, 1).sobrenome)
↳ Adrian Dirac

JOIN: Para realizar consulta com relacionamentos usamos join.

query = (select(Endereco)
    .join(Endereco.pessoa)
    .where(Pessoa.nome == "Richard")
    .where(Endereco.email == "richar@google.com")
)
result = session.scalars(query).one()

print(result)
↳ Endereco: (id = 4, email = 'richar@google.com')

[SQL]
SELECT enderecos.id, enderecos.pessoa_id, enderecos.email, enderecos.endereco 
    FROM enderecos JOIN pessoas ON pessoas.id = enderecos.pessoa_id 
    WHERE pessoas.nome = ? AND enderecos.email = ? ('Richard', 'richar@google.com')

O resultado de print acima decorre da forma como definimos o método __repr__. Qualquer propriedade do objeto pode ser obtida, por exemplo com print(result.id). Em particular result.pessoa é o objeto pessoa associado a esse endereço e print(result.pessoa.nome) imprime o nome “Richard”.

UPDATE: Para alterar um campo de um registro recuperamos o objeto correpondente ao registro e alteramos a propriedade desejada. A alteração só é gravada no BD com session.commit(), quando é emitido e executado o UPDATE.

rick = session.execute(select(Pessoa).filter_by(nome="Richerd")).scalar_one()
print(rick)
↳ Pessoa (id = 3, nome='Richerd', sobrenome='Feynman')

rick.nome = "Richard"
print(rick in session.dirty)
↳ True

# para verificar a alteração (na classe)
rick_nome = session.execute(select(Pessoa.nome).where(Pessoa.id == 3)).scalar_one()
print(rick_nome)
↳ Richard

print(rick in session.dirty)
↳ False

session.commit()

O modificador scalar_one() só pode ser usado quando a consulta retorna apenas uma linha (um objeto). Caso contrário uma exceção é lançada. Após a alteração o objeto fica na coleção Session.dirty até que um commit seja emitido. No caso acima o commit foi implícito, ocorrido quando a query SELECT foi executada.
A consulta resulta em:

[SQL]
SELECT pessoas.id, pessoas.nome, pessoas.sobrenome FROM pessoas 
    WHERE pessoas.nome = ? ('Richerd',)
# depois
UPDATE pessoas SET nome=? WHERE pessoas.id = ? ('Richard', 3)

Uma alteração em um campo exige a recuperação desse objeto seguida da alteração propriamente dita depois a gravação no BD.

query = select(Pessoa).where(Pessoa.id == 3)
p = session.scalars(query).one()
p.sobrenome = "Dawkings"
print(p)
↳ Pessoa (id = 3, nome='Richard', sobrenome='Dawkings')

# para gravar no BD
session.commit()

As consultas são emitidas:

[SQL]
SELECT pessoas.id, pessoas.nome, pessoas.sobrenome  FROM pessoas 
    WHERE pessoas.id = ? (3,)

UPDATE pessoas SET sobrenome=? WHERE pessoas.id = ? ('Dawkings', 3)

DELETE: para uma operação de apagamento de uma linha de tabela recuperamos essa linha (em um objeto) e a apagamos com session.delete(objeto).

p = session.get(Pessoa, 1)
print(p)
↳ Pessoa (id = 1, nome='Paul', sobrenome='Adrian Dirac')

session.delete(p)
session.commit()

Os seguintes comandos SQL são gerados:

[SQL]
SELECT pessoas.id AS pessoas_id, pessoas.nome AS pessoas_nome, pessoas.sobrenome AS pessoas_sobrenome 
   FROM pessoas WHERE pessoas.id = ? (1,)

DELETE FROM enderecos WHERE enderecos.id = ? (1,)
DELETE FROM pessoas WHERE pessoas.id = ? (1,)

Devido aos vínculos estabelecidos na definição da tabela (e, portanto, também da classe) enderecos, relationship(back_populates="pessoa", cascade="all, delete-orphan") ao ser apagada a linha da pessoa de id = 1 as linhas vinculadas da tabela enderecos também são apagadas.

Python e SQL: SQLAlchemy

Posted on 07/02/202307/02/2023 by Guilherme Silva

Todos os tópicos →

SQL e SQLAlchemy

Nesse site: Linguagem de Consulta SQL,
Um projeto Python: SQLite.Essas notas e o código listado estão baseados na versão 2.0 do SQLAlchemy que é a versão lançada em 26 de janeiro de 2023. Um documento de migração, para quem está habituado com versões anteriores, está disponível em SQLAlchemy 2.0 – Major Migration Guide.

SQL é uma linguagem de consulta a bancos de dados relacionais universalmente usada para aplicativos em desktop ou na web. Existem muitas bibliotecas de integração desses bancos com o Python, inclusive o sqlite3 que vem instalado na biblioteca padrão, já descrito aqui em linhas básicas. Uma biblioteca Python poderosa e flexível muito usada é a SQLAlchemy, criada por Mike Bayer em 2005, de código aberto e disponibilizado sob licença MIT. Com ela se pode fazer consultas tradicionais, usando as queries padrões do SQL, mas também utilizar ferramentas que abstraem essas consultas associando as tabelas de banco de dados com classes. Ela pode ser usada para fazer a conexão com os bancos de dados mais comuns, como o Postgres, MySQL, SQLite, Oracle, entre outros.

Com o SQLAlchemy podemos abstrair do código específico do banco de dados subjacente. Com instruções comuns para todos os bancos ele facilita a migração de um banco para outro, sem maiores dificuldades. Além disso ele cuida de problemas de segurança comuns, tais como ataques de injeção de SQL. O SQLAlchemy é bastante flexível e permite duas formas principais de uso: o SQL Expression Language (referido como Core) e Object Relational Mapping (ORM), que podem ser usados separadamente ou juntos, dependendo das necessidades do aplicativo.

SQLAlchemy Core e ORM

SQL Expression Language (CORE): é uma forma de representar instruções e expressões SQL comuns de modo pitônico, uma abstração das consultas SQL sem se afastar muito delas. Ela é uma interface bem próxima das bancos de dados mas padronizado para ser consistente com muitos desses bancos. Além disso ela fundamenta o SQLAlchemy ORM.

SQLAlchemy ORM: é um mapeador relacional de objeto (ORM, Object Relational Mapper) que fornece uma abstração de alto nível sobre a SQL Expression Language. Ele utiliza um sistema declarativo semelhante aos utilizados em outros ORMs como, por exemplo, o do Ruby on Rails.

Diferente da maioria das outras ferramentas SQL/ORM, o SQLAlchemy não tenta ocultar os detalhes do mecanismo de SQL, deixando expostos e sob controle do programador todos os processos envolvidos. Ele estabelece uma associação entre o banco de dados e classes, geralmente atribuindo uma classe a cada tabela e cada instância dessa classe com linhas da tabela.

Instalando o SQLAlchemy

Um ambiente virtual é recomendado (embora não obrigatório).

# criamos um ambiente virtual com o comando
$ python3 -m venv ~/Projetos/.venv
# para ativar o ambiente virtual
$ cd ~/Projetos/.venv
$ source bin/activate

# instalamos o sqlalchemy (última versão publicada)
$ pip install sqlalchemy

# para a distribuição Anaconda do Python
$ conda install -c anaconda sqlalchemy

# criamos uma pasta para o sqlalchemy
$ mkdir ~/Projetos/.venv/sqlalchemy
$ cd ~/Projetos/.venv/sqlalchemy

# para verificar a versão instalada iniciamos o python e carregamos o sqlalchemy
$ python
>>> import sqlalchemy
>>> sqlalchemy.__version__
'2.0.0rc3'

O sqlalchemy consegue se conectar com banco de dados sqlite sem a necessidade de nenhum drive adicional. Para o PostgreSQL podemos usar o psycopg2, instalado com pip install psycopg2. Para o MySQl uma boa opção é o PyMySQL (pip install pymysql). Para nosso processo de aprendizado usaremos o SQLite.

A engine do SQLAlchemy

Para estabelecer contato com o banco de dados criamos uma instância do objeto da classe engine com create_engine que usa uma string de conexão (connection string), uma string com formato próprio para fornecer o tipo do banco, detalhes de autenticação (usuário e senha), localização do banco (servidor ou arquivo), e a DBAPI^† usada.

† A DBAPI (Python Database API Specification) do Python é um driver usado pelo SQLAlchemy para interagir com o banco de dados escolhido. Por exemplo, nos nossos exemplos estamos usando sqlite3, da biblioteca padrão.

A DBAPI é uma API de baixo nível usado pelo Python para conectar ao banco de dados. O sistema de dialetos do SQLAlchemy é construído pela DBAPI que fornece classes específicas para lidar com o mecanismo de BD usado, como POSTGRES, MYSQL, SQLite, etc.

Por exemplo, para uma conexão com um arquivo meu_banco.db do SQLite usamos:

from sqlalchemy import create_engine

# abaixo alguns exemplos de strings de conexão
engine1 = create_engine("sqlite:///meu_banco.db")
engine2 = create_engine("sqlite:////home/projeto/db/meu_banco.db")
engine3 = create_engine("sqlite:///:memory:")
# no windows
engine4 = create_engine("sqlite:///c:\\Users\\projeto\\db\\meu_banco.db")

# para efetivar a conexão
connection = engine1.connect()

# para ativar um serviço de log usamos echo=True
engine1 = create_engine("sqlite:///meu_banco.db", echo=True)

No caso 1 o arquivo está na pasta default, no 2 o caminho completo é informado. A conexão em engine3 cria um banco na memória (sem ser gravado em disco), o que é útil para aprendizado e experimentação. Em 4 se mostra a sintaxe de pastas para o Windows. A função create_engine retorna uma instância da engine mas não estabelece a conexão, o que é chamado de lazy connection. Essa conexão só é efetivada quando, pela primeira vez, alguma ação é executada no banco. Se o arquivo meu_banco.db não existe ele é criado com esse processo.

O ajuste do parâmetro opcional echo = True faz com que todas as operações feitas no banco sejam também exibidas no console com a sintaxe do SQL. Nessas notas exibiremos os comandos mostrados nesse log com a marcação [SQL].

Conexão

O SQLAlchemy Core usa uma linguagem de expressão (SQLAlchemy Expression Language) como forma de interagir com o código Python. Uma forma de enviar comandos SQL literais consiste no uso da função text(), útil no aprendizado e experimentação mas não muito usado na prática em projetos. Para efetivar a conexão usamos o método engine.connect(). No código abaixo o banco meu_banco.db será criado na pasta do projeto, se já não existir. O objeto engine é o elemento básico no relacionamento com o BD, basicamento feito através de sua função connect():

from sqlalchemy import create_engine, text
engine = create_engine("sqlite:///meu_banco.db")

with engine.connect() as conn:
    conn.execute(text("CREATE TABLE IF NOT EXISTS coordenadas (x int, y int)"))
    conn.execute(
        text("INSERT INTO coordenadas (x, y) VALUES (:x, :y)"),
        [{"x": 1, "y": 1}, {"x": 2, "y": 4}, {"x": 3, "y": 9}],
    )
    result = conn.execute(text("SELECT * FROM coordenadas"))
    print(result.all())

    # nenhuma alteração foi feito no banco de dados. Alterações são feitas com
    conn.commit()
    # agora o INSERT foi efetivado no BD

# o print acima exibe    
↳ [(1, 1), (2, 4), (3, 9)]

O objeto result é um iterador que fica esgotado após a operação print(result.all()). Se quisermos utilizar esse resultado posteriormente temos que refazer a consulta ou armazenar os valores. O gerenciador de contexto with garante que a conexão (atribuída à variável conn) é criada e fechada após a operação, o que garante que os recursos usados são liberados. Podemos percorrer result em um loop:

with engine.connect() as conn:
    result = conn.execute(text("SELECT x, y FROM coordenadas"))
    for linha in result:
        print(f"x = {linha.x}  y = {linha.y}")
        # ou  print(f"x = {linha[0]}  y = {linha[1]}")
        
# output
↳ x = 1  y = 1
  x = 2  y = 4
  x = 3  y = 9

Result possui vários métodos de busca e transformações de linhas. Um deles é result.all() visto acima, que retorna uma lista de todos os objetos Row. Ele age como um iterador do Python. Cada linha é um objeto row representado por uma tupla (e agindo como tuplas nomeadas). Para recuperar esses valores podemos usar fazer uma atribuição de tuplas, usar índices ou usar os nomes das tuplas nomeadas.

# feita a consulta
result = conn.execute(text("SELECT x, y FROM coordenadas"))

# qualquer um dos métodos pode ser usado:
# atribuição de tuplas
for x, y in result:
    print(x, y)

# uso de índices
for row in result:
    print(row[0], row[1])
    
# tuplas nomeadas
for row in result:
    print(row.x, row.y)

Também podemos usar as linhas recebidas mapeando o resultado em dicionários com o modificador Result.mappings():

result = conn.execute(text("SELECT x, y FROM coordenadas"))
for dict_row in result.mappings():
    x = dict_row["x"]
    y = dict_row["y"]
    ...

Passando parâmetros

O método Connection.execute() aceita parâmetros que modificam a consulta feita. Por exemplo, para fazer uma consulta SELECT, atendendo a alguns critérios, inserimos o modificador WHERE à instrução.

with engine.connect() as conn:
    query = text("SELECT x, y FROM coordenadas WHERE y > :y")
    result = conn.execute(query, {"y": 2})
    for row in result:
        print(f"x = {row.x}  y = {row.y}")
[SQL]
SELECT x, y FROM coordenadas WHERE y > 2        
# resulta em
↳ x = 2  y = 4
  x = 3  y = 9

O valor do parâmetro em :y é lido no dicionário, resultando em WHERE y > 2. Essa técnica é chamada de “estilo de parâmetro qmark” e deve sempre ser usada para evitar ataques de injeção SQL no aplicativo.

Múltiplos parâmetros podem ser passados. Podemos enviar vários parâmetros para o método Connection.execute() por meio de uma lista de dicionários (no estilo conhecido como executemany). Isso já foi feito na nossa primeira operação de inserção.

# vamos apagar todas as linhas da tabela
with engine.connect() as conn:
    result = conn.execute("DELETE FROM coordenadas")
    conn.commit()

# agora vamos inserir várias linhas de uma vez
with engine.connect() as conn:
    query = text("INSERT INTO coordenadas (x, y) VALUES (:x, :y)")
    values = [{"x": 11, "y": 12}, {"x": 13, "y": 14}, {"x": 15, "y": 16}]
    conn.execute(query, values,)
    conn.commit()
# o BD agora contém a tabela mostrada na figura.

No código acima, values é uma lista de dicionários e a operação de INSERT é feita uma vez para cada item da lista.

Metadata, Table e Column

Nos bancos de dados relacionais os objetos mais básicos são as tabelas que são, por sua vez, constituídas por colunas e linhas, cada uma delas com seu correspondente objeto do Python via SQLAchemy.

Classe MetaData: O SQLAlchemy mantém um objeto chamado MetaData que armazena toda a informação sobre as tabelas usadas, as colunas, vínculos e relacionamentos. A sintaxe de criação de um objeto MetaData é a seguinte:

from sqlalchemy import MetaData
metadata_objeto = MetaData()

É comum que um único objeto MetaData sirva para armazenar todas as tabelas de um aplicativo, geralmente como uma variável de nível de módulo. Pode ocorrer, embora seja menos comum, que existam vários objetos MetaData. Mesmo assim as tabelas continuam podendo se relacionar entre elas.

Table e Column: Objetos Table são inicializados em um objeto MetaData através do construtor de tabelas onde o nome é fornecido. Argumentos adicionais são considerados objetos de coluna. Objetos Column representam cada campo na tabela. A sintaxe de definição de uma tabela é variavel = Table("nome_tabela", metadata, Columns ...).

from sqlalchemy import Table, Column, Integer, Numeric, String

# tabela alunos
alunos = Table("alunos", metadata,
    Column("id", Integer(), primary_key=True), 
    Column("matricula", String(50), nullable=False, unique=True),
    Column("nome", String(50), index=True, nullable=False),
    Column("sobrenome", String(50)),
    Column("idade", Integer()),
    Column("curso", String(50)),
    Column("nota_final", Numeric(2, 2)),
    Column("nascimento", DateTime()), 
    Column("atualizado", DateTime(), default=datetime.now, onupdate=datetime.now)
)

# tabela notas
notas = Table("notas", metadata,
    Column("id", Integer(), primary_key=True), 
    Column("id_aluno", ForeignKey("aluno.id"), nullable=False),
    Column("nota", Numeric(2, 2)),
    Column("data_prova", DateTime())
)

# as chaves primárias podem ser visualizadas
print(alunos.primary_key)
# resulta em:
PrimaryKeyConstraint(Column('id', Integer(), table=, primary_key=True, nullable=False))

# as tabelas são criadas no BD com
engine = create_engine('sqlite:///meu_banco.db')
metadata.create_all(engine)

O campo id é uma chave primária, nome é um índice, usado para agilizar consultas. O construtor de table usa vários construtores de colunas, cada um com seu nome e definição. O campo matricula não pode ser nulo nem repetido (nullable=False, unique=True). O campo atualizado é um campo de datas com default (now), e é atualizado automaticamente toda vez que o registro é alterado. Os parênteses no import servem para quebrar a linha sem a necessidade de uso da barra invertida, \.

Quando uma coluna é definida como ForeignKey dentro da definição da tabela, como foi feito acima, o tipo de dado pode ser omitido pois é automaticamente ajustado de acordo com a coluna a que se refere. No caso acima id_aluno tem o mesmo tipo que aluno.id, que é um inteiro.

Chaves e vínculos: (Keys, Constraints) são formas de forçar algum critério sobre os dados e seus relacionamentos. Chaves primárias (primary keys ou “PK”) são identificadores únicos e nunca nulos usados em relacionamentos. Vimos que escolhemos um campo como chave primária usando primary_key=True. Vários campos podem ser usados em chaves compostas. Nesse caso a chave será usada como uma tupla contendo os vários campos. O vínculo UniqueConstraint (informado com unique=True) é a exigência de que um valor não pode ser duplicado no campo. Além desses temos o CheckConstraint que estabelece que os dados satisfaçam regras definidas pelo programador. Todos esses campos podem ser definidos em linhas próprias, depois das definições das colunas, como mostrado abaixo:

from sqlalchemy import PrimaryKeyConstraint, UniqueConstraint, CheckConstraint

PrimaryKeyConstraint("id", name="aluno_pk")
UniqueConstraint("matricula", name="aluno_matricula")
CheckConstraint("nota_final >= 0.00", name="aluno_nota")

Índices: são usados para agilizar buscas de valores em um campo e devem ser aplicados a campos que servem para buscas em uma tabela. Além da criação com index=True usado na tabela alunos podemos criar o índice explicitamente com

from sqlalchemy import Index
Index("ix_alunos_nome", "alunos_nome")

Mais de uma coluna podem ser usadas como índice.

Relacionamentos, chaves estrangeiras: O próximo passo é o estabelecimento de relacionamentos. Por ex., a tabela notas tem cada registro (linhas da tabela) vinculado à um aluno. Essa associação permite uma relação um-para-muitos, no nosso caso com a possibilidade de registrar várias notas para cada aluno. Isso é feito com a seguinte alteração na tabela notas para incluir uma chave estrangeira (forein key):

from sqlalchemy import ForeignKey
notas = Table("notas", metadata,
    Column("id", Integer(), primary_key=True),
    Column("id_aluno",  ForeignKey("alunos.id")),
    ...
)
# as outras colunas ficam inalteradas
# alternativamente podemos definir a chave em uma linha posterior à definição

from sqlalchemy import ForeignKeyConstraint
ForeignKeyConstraint(["id_aluno"], ["alunos.id"])

Claro que as tabelas podem ter várias chaves estrangeiras. Após todas as definições as alterações podem ser executadas e tornadas permanentes com create_all.

from sqlalchemy import MetaData
metadata_objeto = MetaData()

# ... definições de tabelas

metadata_objeto.create_all(engine)

Por default create_all() não recria tabelas que já existem. Podemos, portanto, executar o comando várias vezes.

Resumindo: O objeto MetaData armazena uma coleção de objetos Table que, por sua vez, armazena objetos Column e Constraint. Essa estrutura de objetos é a base da maioria das operações do SQLAlchemy, tanto Core quanto ORM.

Executando o código: Juntando as partes, colocamos todos os comando em um arquivo sqlal.py e o executamos com python sqlal.py.

from datetime import datetime
from sqlalchemy import (MetaData, Table, Column, Integer, Numeric, String,
                        DateTime, ForeignKey, create_engine)
metadata = MetaData()

# tabela alunos
alunos = Table("alunos", metadata,
    Column("id", Integer(), primary_key=True), 
    Column("matricula", String(50), nullable=False, unique=True),
    Column("nome", String(50), index=True, nullable=False),
    Column("sobrenome", String(50)),
    Column("idade", Integer()),
    Column("curso", String(50)),
    Column("nota_final", Numeric(2, 2)),
    Column("nascimento", DateTime()), 
    Column("atualizado", DateTime(), default=datetime.now, onupdate=datetime.now)
)

# tabela notas
notas = Table("notas", metadata,
    Column("id", Integer(), primary_key=True),
    Column("id_aluno",  ForeignKey("alunos.id")),
    Column("nota", Numeric(2, 2)),
    Column("data_prova", DateTime())
)

engine = create_engine("sqlite:///meu_banco.db", echo=True)
metadata.create_all(engine)

Podemos notar que a construção de um objeto Table tem semelhança com o processo de declarar um comando SQL CREATE TABLE. Foram usados os objetos: Table que representa uma tabela no banco de dados e fica armazenado em uma coleção MetaData; Column que representa uma coluna de uma tabela. A declaração de colunas incluem seu nome, e o tipo de objeto. A coleção de objetos coluna pode ser acessada por meio de um array associativo em Table.c.

alunos.c.nome
↳ Column('nome', String(length=50), table=)

alunos.c.keys()
↳ ['id', 'id_aluno', 'nome', 'data_prova']

Após a execução desse código temos as tabelas ilustradas na figura abaixo, inclusive o relacionamento de notas.id_alunos como foreign key ligado ao campo alunos.id.

Inserção de dados

Após a definição das tabelas, colunas e relacionamentos podemos inserir dados.

# inserção de dados
query = alunos.insert().values(
    matricula = "943.232-90",
    nome = "Arduino",
    sobrenome = "Bolivar",
    idade = "17",
    curso = "Eletrônica",
    nota_final = 17.20,
    nascimento = ""
)

print(str(query))

# o seguinte output é obtido:

↳ INSERT INTO alunos
      (matricula, nome, sobrenome, idade, curso, nota_final, nascimento, atualizado)
  VALUES
      (:matricula, :nome, :sobrenome, :idade, :curso, :nota_final, :nascimento, :atualizado)

print(query.compile().params)    
# o seguinte output é obtido:
↳     {"matricula": "943.232-90",
       "nome": "Arduino",
       "sobrenome": "Bolivar",
       "idade": "17",
       "curso": "Eletrônica",
       "nota_final": 17.2,
       "nascimento": "21/01/2023",
       "atualizado": None}

Note que :nome_campo é a forma usado pelo SQLAlchemy para a representação de string dos valores dos campos em str(query). Internamente os dados são tratados por questões de segurança, como um ataque de injeção SQL. Os valores a serem inseridos podem ser visualizados com query.compile().params. Note que, nas consultas de inserção, não fornecemos valores para os campos de inserção automática, id e atualizado.

De modo similar podemos usar os demais métodos como update(), delete() e select() para gerar consultas UPDATE, DELETE e SELECT respectivamente. Finalmente podemos garantir a persistência dos dados gravando no BD esses valores.

resultado = connection.execute(query)
print(resultado.inserted_primary_key)
↳ (1,0)

O útimo comando imprime o id da linha gravada.

Reflexão de tabelas

Além das consultas de criação de tabelas precisamos usar bancos de dados com tabelas já criadas, com suas colunas e relacionamentos estabelecidos. O SQLAlchemy consegue isso com as chamadas reflexões de tabelas (table reflections), o processo de gerar objetos Table (o seus componentes) lendo o estado de um banco de dados já construído.

Veremos uma breve apresentação dessa operação, para ser mais explorada em outra seção. Como exemplo desse processo vamos usar a tabela alunos definida anteriormente. A forma mais básica de se fazer isso é construindo um objeto Table fornecendo o nome da tabela e o objeto Metadata que a contém.

from sqlalchemy import (MetaData, Table, Column, Integer, Numeric, String,
                        DateTime, ForeignKey, create_engine)
metadata = MetaData()
engine = create_engine('sqlite:///meu_banco.db')

tbl_alunos = Table("alunos", metadata, autoload_with=engine)

print(tbl_alunos.c.keys())
↳ ['id', 'matricula', 'nome', 'sobrenome', 'idade', 'curso', 'nota_final', 'nascimento', 'atualizado']

Também podemos importar para as nossas classes mais de uma tabela de cada vez.

engine = create_engine('sqlite:///meu_banco.db')
metadata = MetaData()
metadata.reflect(bind=engine)
tbl_alunos = metadata.tables["alunos"]
tbl_notas = metadata.tables["notas"]

print("Alunos:", tbl_alunos.c.keys())
print("Notas:", tbl_notas.c.keys())

↳ Alunos: ['id', 'matricula', 'nome', 'sobrenome', 'idade', 'curso', 'nota_final', 'nascimento', 'atualizado']
↳ Notas: ['id', 'id_aluno', 'nota', 'data_prova']

Uma vez importada a tabela podemos extrair dela todas os dados, bem como realizar as alterações usuais de inserção e apagamento.

Tabelas e tipos de dados

O SQLAlchemy define diversos tipos de dados destinados a abstrair os tipos usados nos bancos SQL. Um exemplo disso é tipo genérico booleano que geralmente usa o tipo SQL BOOLEANO (True ou False no Python). No entanto ele possui também o SMALLINT para BDs que não suportam BOOLEANOs. Essa adaptação é automática e o desenvolvedor só tem que lidar com os campos bolleanos do Python: (True / False). A tabela mostra tipos genéricos e suas associações.

SQLAlchemy	Python	SQL
BigInteger	int	BIGINT
Boolean	bool	BOOLEAN ou SMALLINT
Date	datetime.date	DATE (SQLite: STRING)
DateTime	datetime.datetime	DATETIME (SQLite: STRING)
Enum	str	ENUM ou VARCHAR
Float	float ou Decimal	FLOAT ou REAL
Integer	int	INTEGER
Interval	datetime.timedelta	INTERVAL ou DATE from epoch
LargeBinary	byte	BLOB ou BYTEA
Numeric	decimal.Decimal	NUMERIC ou DECIMAL
Unicode	unicode	UNICODE ou VARCHAR
Text	str	CLOB ou TEXT
Time	datetime.time	DATETIME

Bibiografia

SqlAlchemy.org: SQLAlchemy 2.0 Documentation
RealPython: Working with Sqlalchemy and Python Objects
TutorialsPoint: SqlAlchemy Quick Guide
Datacamp: SqlAlchemy Tutorial with Examples

Michael Bayer: SQLAlchemy. In Amy Brown and Greg Wilson, editors, The Architecture of Open Source Applications Volume II: Structure, Scale, and a Few More Fearless Hacks 2012 aosabook.org

Um projeto SQLite

Posted on 16/01/202317/01/2023 by Guilherme Silva

Todos os tópicos →

SQLite

Nesse site: Linguagem de Consulta SQL

SQLite é um mecanismo de banco de dados relacional de código aberto, escrito em C por D. Richard Hipp, em 2000. Ele não necessita da instalação de um servidor para o seu funcionamento, como ocorre com a maioria dos demais bancos de dados, acessando diretamente um arquivo em disco. O estado completo de uma base de dados fica armazenado nesse arquivo, geralmente com extensão arquivo.db, .sqlite ou .db3. que pode ser utilizado diretamente em diversas plataformas. Ele não é um aplicativo independente e sim uma biblioteca que é incorporada junto com os aplicativos, sendo o mecanismo de banco de dados mais usado, servindo para armazenamento e manipulação de dados em navegadores da Web, sistemas operacionais, telefones celulares e outros sistemas integrados.

O SQLite usa uma sintaxe de SQL parecida com a do PostgreSQL, mas não impõe a verificação de tipo, tornando possível, por exemplo, se inserir uma string em uma coluna definida como inteiro. Como em outras versões de bancos de dados relacionais, o SQLite armazena dados em tabelas que podem conter campos de vários tipos de dados, como texto ou números inteiros. Essas tabelas podem ser acessadas (construídas, modificadas e consultadas) por meio de consultas SQL que realizam operações “CRUD” (criar, ler, atualizar, excluir). Um banco de dados SQLite pode ser criptografado usando o SQLite Encryption Extension (SEE) ou outra tecnologia, de forma a proteger dados de extrações não autorizadas.

Diversos Sistemas Gerenciadores de Bancos de Dados, SGBD, (em inglês RDBMS, Relational Database Management System) estão disponíveis para o SQLite, entre eles o SQLiteStudio, DB Browser, DBeaver. Também existem diversos plugins para navegadores e IDEs (como o VSCode).

SQLite no VSCode

O VSCode possui um plugin muito interessante para auxiliar no desenvolvimento com SQLite. Para usá-lo instale o plugin (extension) vscode-sqlite. Os seguintes recursos ficam disponíveis:

consulta a bancos de dados SQLite, visualização de tabelas, exportações de resultados para json, csv e html.
uso da barra lateral (explorer) para listar bancos de dados, tabelas, colunas e views.
preenchimento automático de palavras-chave, nomes de tabelas e colunas. Para isso deve-se vincular um banco de dados a um documento SQL, usando o comando: USE DATABASE.
emissão de mensagens de erros amigáveis apontando o local do erro.

Existem comandos para criar novos arquivos sqlite, novas consultas, editar e executar scripts de consulta, visualização rápida de BD, fechar e removar um BD, atualizar, exibir resultados de consultas.

Para iniciar o uso crie um documento, por exemplo meuBD.db. Clique nele (com botão direito) e gere um nova consulta. O arquivo é transformado automaticamente em um banco do SQLite. Também clicando com botão direito selecione “Open Database” para visualizar as tabelas e ter acesso a vários outros comandos. Consultas digitadas no editor podem ser executadas com as opções “Run Query” ou “Run Selected Query”.

Comandos SQLite

Existem diversas maneiras de inserir comandos SQL em um banco SQLite. Em um gerenciador de bancos de dados podemos abrir uma área de inserção de consultas e digitar, interativamente esses comandos. Alternativamente, se temos uma sequência de consultas válidas gravadas no arquivo consultas.sqljunto podemos executar no prompt de comando:

cat consultas.sql | sqlite3 test1.db
# ou
sqlite3 test2.db ".read consultas.sql"
# ou
sqlite3 test3.db < definicoes.sql

# no windows
sqlite3.exe test4.db ".read definicoes.sql"

Esses comandos produzem respectivamente os arquivos test_n.db após as execuções das consultas em definicoes.sql.

Projeto: vendas

Como uma ilustração de uso do SQLite vamos construir uma banco de dados para controle de uma loja, com tabelas para seus clientes, funcionários, fornecedores e itens vendidos. Todas as consultas SQL podem estar em um único arquivo .sql, mas nós as listaremos por partes para comentar os seus efeitos.

Consultas de definição das tabelas

O comando CREATE TABLE serve para inserir uma nova tabela no banco de dados. IF NOT EXISTS instrui a consulta a ser executada somente se a tabela ainda não foi criada. As definições de campo seguem dentro dos parênteses. O campo id INTEGER PRIMARY KEY AUTOINCREMENT é um índice inteiro, usado como chave primária e de autoincremento. Campos definidos como NOT NULL não podem ser deixados nulos.

Primeiro criamos uma tabela no prompt de comando:

>> sqlite3 vendas.db

Em seguida criamos as tabelas. Para usar esse arquivo podemos usar os comandos listados acima ou abrir um gerenciador de bancos de dados e inserir as consultas nele. O uso do VSCode ou diretamente no código com o sqlite3 do python são exemplos desse uso.

-- criando a tabela clientes
CREATE TABLE IF NOT EXISTS clientes (
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   nome VARCHAR(100) NOT NULL,
   sobrenome TEXT NOT NULL,
   email VARCHAR(100) NOT NULL,
   cidade TEXT NOT NULL,
   estado VARCHAR(2) NOT NULL,
   cep INTEGER NOT NULL
);

-- criando a tabela de funcionarios
CREATE TABLE IF NOT EXISTS funcionarios (
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   nome VARCHAR(100) NOT NULL,
   sobrenome TEXT NOT NULL,
   email TEXT NOT NULL,
   senha VARCHAR(20) NOT NULL,
   cargo VARCHAR(133),
   endereco TEXT NOT NULL,
   cidade VARCHAR(150) NOT NULL,
   estado VARCHAR(2) NOT NULL,
   cep INTEGER NOT NULL
);

-- Criando a tabela fornecedores
CREATE TABLE IF NOT EXISTS fornecedores (
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   nome VARCHAR(100) NOT NULL,
   cnpj VARCHAR(100) NOT NULL,
   email VARCHAR(100) NOT NULL,
   endereco TEXT NOT NULL,
   cidade VARCHAR(150) NOT NULL,
   estado VARCHAR(2) NOT NULL,
   cep INTEGER NOT NULL
);

Após a execução desses consultas temos as tabelas clientes, funcionarios e fornecedores. Digamos que queremos alterar uma tabela para acrescentar, renomear ou excluir algum campo. ALTER TABLE é a consulta usada.

-- para alterar uma tabela (alterando clientes)
ALTER TABLE clientes ADD data_aniversario DATE;
ALTER TABLE clientes ADD endereco TEXT NOT NULL;

-- para renomear um campo
ALTER TABLE clientes RENAME data_aniversario TO aniversario;
-- para excluir um campo
ALTER TABLE clientes DROP COLUMN aniversario;

Primeiro inserimos data_aniversario e endereco, depois renomeamos data_aniversario para aniversario, depois excluímos o campo aniversario. Da mesma forma tabelas inteiras podem ser excluídas com DROP TABLE.

-- criar uma tabela para ser apagada depois
CREATE TABLE IF NOT EXISTS usuarios (
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   nome VARCHAR(100) NOT NULL,
   codigo INTEGER NOT NULL
);
-- excluir a tabela recém criada
DROP TABLE usuarios;

Operações de apagamento devem ser executadas com cuidado pois os dados não podem ser recuperados!

Observe que no modelo adotado acima os cargos dos funcionários são inseridos como textos que devem ser repetidos para diversos funcionários com o mesmo cargo. Outra possibilidade consiste em ter uma tabela com os cargos em separado, referenciados na tabela funcionarios por meio de um id. Vamos fazer essas alterações.

-- criar a tabela cargos   
CREATE TABLE IF NOT EXISTS cargos (
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   descricao VARCHAR(200) NOT NULL
);   
-- exclui a coluna cargo de funcionarios   
ALTER TABLE funcionarios DROP COLUMN cargo;
-- insere cargo_id 
ALTER TABLE funcionarios ADD COLUMN cargo_id INTEGER REFERENCES cargos(id);

O último comando falaria se não existisse a tabela referenciada cargos. O campo cargo_id é uma chave estrangeira (foreign key), ligado à tabela cargos, pelo seu campo id. Vamos aplicar o mesmo conceito na criação de chaves estrangeiras para produtos vendidos e vendas.

-- tabela produtos
CREATE TABLE IF NOT EXISTS produtos (
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   fornecedor_id INTEGER,
   descricao VARCHAR(100),
   preco DECIMAL(10,2),

   FOREIGN KEY (fornecedor_id) REFERENCES fornecedores (id)
);

-- tabela vendas
CREATE TABLE IF NOT EXISTS vendas (
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   cliente_id INTEGER NOT NULL,
   funcionario_id INTEGER NOT NULL,
   data_venda DATETIME NOT NULL,
   total DECIMAL (10,2) NOT NULL,
   descricao TEXT,

   FOREIGN KEY (cliente_id) REFERENCES clientes (id),
   FOREIGN KEY (funcionario_id) REFERENCES funcionarios (id)
);

-- tabela itens_vendas
CREATE TABLE IF NOT EXISTS itens_vendas (
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   venda_id INTEGER NOT NULL,
   produto_id INTEGER NOT NULL,
   quantidade INTEGER NOT NULL,
   subtotal DECIMAL (10,2) NOT NULL,

   FOREIGN KEY (venda_id) REFERENCES vendas (id),
   FOREIGN KEY (produto_id) REFERENCES produto (id)
);

Os relacionamentos entre as tabelas expressam o fato de que um funcionário pode fazer várias vendas, cada venda pode conter vários ítens de produtos, um único fornecedor pode ser responsável por vários produtos e mais de um funcionário podem ter o mesmo cargo.

Vemos que as, após todos esses passos, as tabelas possuem os seguintes relacionamentos:

Consultas de inserção e leitura de dados

Uma vez definidas as tabelas passamos a inserir dados. Faremos também consultas para verificar os dados inseridos. Dados são inseridos com INSERT INTO. O nome da tabela e os campos são fornecidos e os valores a inserir. SELECT * significa “selecione todos os campos”.

INSERT INTO clientes (nome, sobrenome, email, cidade, estado, cep, endereco)
VALUES
('Caio', 'Zuretta', 'cz@hotmail.com', 'Seattle', 'WA', 123456789,'23rd Street AWE');
SELECT * FROM clientes;

id  nome  sobrenome   email            cidade   estado cep         endereco
1   Caio  Zuretta     cz@hotmail.com   Seattle  WA     123456789   23rd Street AWE

-- inserindo outro cliente
INSERT INTO clientes (nome, sobrenome, email, endereco, cidade, estado, cep )
    VALUES ('Polka', 'Brita', 'pbrita@gmail.com', 'Av. Contorno 432', 'Belo Horizonte', 'MG', 30876786);

SELECT id, nome, sobrenome FROM clientes;
id  nome   sobrenome
1   Caio   Zuretta
2   Polka  Brita

-- várias linhas podem ser inseridas na mesma consulta
INSERT INTO clientes (nome, sobrenome, email, endereco, cidade, estado, cep)
    VALUES
 ('Antonio', 'Tony', 'tmatador@gigamail.com.br', 'R. Pedro II, 34', 'Rio de Janeiro', 'RJ', 21654897),
 ('Martha', 'Maertis', 'marthis@onlymail.com', 'R. Joinha, 654', 'Goiania', 'GO', 41546546),
 ('Orlando', 'Orlandis', 'orlas@gmail.com', 'Av, Só que Não, 34', 'Itabira', 'MG', 35654654),
 ('Mirtes', 'Mello', 'mellom@gmail.com', 'SQL 123', 'Brasília', 'DF', 145428214);

-- uma operação sobre todos as linhas de um campo UPDATE clientes
SET email = UPPER(email);
SELECT email FROM clientes WHERE nome="Mirtes";
email
MELLOM@GMAIL.COM

-- vamos retornar as minúsculas no email
UPDATE clientes SET email = LOWER(email);
SELECT email FROM clientes WHERE nome="Mirtes";
email
mellom@gmail.com

-- inserindo dados na tabela funcionarios
INSERT INTO funcionarios (nome, sobrenome, email, senha, cargo_id, endereco, cidade, estado, cep)
VALUES
('Pedro', 'Altusser', 'pedroalt@email.com', '123456', 1, 'R. João Paulo, 534', 'Rio de Janeiro', 'RJ', 21654897),
('Levindo', 'Lopes', 'lopest@gmail.com', '234456', 1, 'R. Paulo II, 534', 'Ardósias', 'RJ', 21115114),
('Silvana', 'Gomes', 'silvana@gmail.com', '344456', 2, 'R. Paulo I, 4', 'Ardósias', 'RJ', 21651145),
('Lucas', 'Sêtte', 'lucas@gmail.com', '3er456', 3, 'R. Bahia, 1355', 'Belo Horizonte', 'MG', 31454232);

SELECT nome, email, cidade, estado, cep, cargo_id FROM funcionarios WHERE estado = "MG";
nome    email            cidade            estado   cep          cargo_id
Lucas   lucas@gmail.com  Belo Horizonte    MG       31454232     3

-- inserindo cargos
INSERT INTO cargos (descricao) VALUES ('Gerente'), ('Vendedor'), ('Desenvolvedor');

SELECT * FROM cargos;
id   descricao
1    Gerente
2    Vendedor
3    Desenvolvedor

-- alterar o valor de um campo já inserido
UPDATE funcionarios SET cargo_id=1 WHERE id=1;

-- agora a tabela funcionarios está no estado
SELECT * FROM funcionarios;
id nome    sobrenome  email               senha   endereco            cidade         estado cep       cargo_id
1  Pedro   Altusser   pedroalt@email.com  123456  R. João Paulo, 534  Rio de Janeiro RJ     21654897  3
2  Levindo Lopes      lopest@gmail.com    234456  R. Paulo II, 534    Ardósias       RJ     21115114  1
3  Silvana Gomes      silvana@gmail.com   344456  R. Paulo I, 4       Ardósias       RJ     21651145  2
4  Lucas   Sêtte      lucas@gmail.com     3er456  R. Bahia, 1355      Belo Horizonte MG     31454232  3

Uma vez preenchidas as tabelas podemos fazer consultas de todos os tipos. Para ler dados da tabela funcionarios com a descrição dos cargos em cargos usamos INNER JOIN. As duas consultas abaixo são equivalentes:

-- INNER JOIN
SELECT f.nome, c.descricao FROM funcionarios f
  INNER JOIN cargos c WHERE f.cargo_id = c.id;

SELECT f.nome, c.descricao FROM funcionarios f
  INNER JOIN cargos c ON (f.cargo_id = c.id);

nome    descricao
Pedro    Desenvolvedor
Levindo    Gerente
Silvana    Vendedor
Lucas    Desenvolvedor

-- aliases podem ser dados para qualquer campo. Strings são concatenados com ||
SELECT nome || " " || sobrenome  as 'Funcionário' FROM funcionarios;
Funcionário
Pedro Altusser
Levindo Lopes
Silvana Gomes
Lucas Sêtte

Aliases foram usados acima para atribuir nomes às tabelas (como em FROM funcionarios f INNER JOIN cargos c) ou a compos resultados da consultas (como em nome || " " || sobrenome as 'Funcionário').

Consultas podem ser modificadas pelas condições em WHERE, e partes dos campos podem ser encontrados com LIKE. % representa qualquer grupo de caracteres, _ (underline) significa um caracter.

-- Uma consulta simples com dupla condição
SELECT id, nome || " " || sobrenome  as 'Funcionário', estado
    FROM clientes  WHERE estado = "MG" and id=2;
id   Funcionário   estado
2   Polka Brita   MG

-- para apagar um ou mais registros (o registro listado acima)
DELETE FROM clientes  WHERE estado = "MG" and id=2;

-- nome iniciado com "Ma" e a letra "a" no sobrenome
SELECT id, nome, sobrenome FROM clientes WHERE nome LIKE 'Ma%' AND sobrenome LIKE '%a%';
id   nome   sobrenome
4   Martha   Maertis

SELECT id, nome, sobrenome FROM clientes WHERE nome LIKE '_a%';
id   nome    sobrenome
1   Caio    Zuretta
4   Martha    Maertis

SELECT id, nome, sobrenome FROM clientes WHERE nome LIKE '_a___a';
id   nome    sobrenome
4   Martha    Maertis

Para fazer outras consultas cruzadas, em mais de uma tabela, vamos entrar dados nas tabelas fornecedores e produtos.

-- inserindo fornecedores e produtos
INSERT INTO fornecedores (nome, cnpj, email, endereco, cidade, estado, cep)
    VALUES
    ('Microsoft', '234.456-098', 'ms@ms.com', 'R. Pedro Alves, 34', 'Sorocaba', 'SP', 1234567),
    ('Apple', '212.1226-128', 'apps@apps.com', 'R. Gerino Silva, 456', 'Brasília', 'DF', 61256767), 
    ('Lenovo', '2456.1567-676', 'lenovo@lenovo.com', 'R. Power Guido, 786', 'Manaus', 'AM', 23452345),
    ('Dell', '222.453-444', 'del@del.com', 'R. Vaga Errante, 13', 'Sorocaba', 'SP', 1234567),
    ('Logitec', '666.7777-888', 'logi@log.com', 'R. Ulva Gods Silva, 90', 'Brasília', 'DF', 61256767),
    ('Multilaser', '1111.9999-888', 'miltila@multi.com', 'R. Volvo Zona, 76', 'Itabira', 'MG', 3114045);
    
INSERT INTO produtos (fornecedor_id, descricao, preco)
    VALUES
    (2, 'iPAD', 12345.80),
    (1, 'Windows 11', 67.90),
    (5, 'Teclado sem fio', 99.00),
    (3, 'Notebook Intel', 1560.00),
    (15, 'Mouse Chines', 13.33),
    (16, 'Chingling Roteador', 59.89);

SELECT * FROM fornecedores;
SELECT * FROM produtos;
-- as tabelas resultado estão nas imagens abaixo

Com essas definições de valores vamos fazer algumas consultas para exibir a sintaxe de consultas JOIN. JOIN e INNER JOIN são idênticos.

SELECT p.id, f.nome, p.descricao, p.preco FROM produtos p
    JOIN fornecedores f ON  (p.fornecedor_id = f.id);
id  nome         descricao         preco
1   Apple        iPAD              12345.8
2   Microsoft    Windows 11        67.9
3   Logitec      Teclado sem fio   99
4   Lenovo       Notebook Intel    1560


SELECT p.id 'COD.', f.nome 'Vendedor', p.descricao 'Produto' , p.preco 'Preço'
    FROM produtos p RIGHT JOIN fornecedores f ON  (p.fornecedor_id = f.id);
COD.    Vendedor      Produto           Preço
1       Apple         iPAD              12345.8
2       Microsoft     Windows 11        67.9
3       Logitec       Teclado sem fio   99
4       Lenovo        Notebook Intel    1560
NULL    Dell          NULL              NULL
NULL    Multilaser    NULL              NULL

Suponha que desejamos fazer um relatório com todas as vendas para o cliente de sobrenome “Arbinger”. Podemos primeiro encontrar o id do cliente e, daí, todas as vendas para ele. Fazemos um JOIN para encontrar o nome dos funcionários que fizeram as vendas.

-- o id do cliente é
SELECT id FROM clientes WHERE  sobrenome="Arbinger";
3

-- as vendas desse cliente
SELECT * FROM vendas
    WHERE  cliente_id = (SELECT id FROM clientes WHERE  sobrenome="Arbinger");
    
--  as vendas associadas ao funcionário vendedor
SELECT v.data_venda, v.total, v.descricao, f.nome, f.sobrenome FROM vendas v
    JOIN funcionarios f 
    WHERE  f.id = v.funcionario_id 
    and v.cliente_id = (SELECT id FROM clientes WHERE  sobrenome="Arbinger");

data_venda   total   descricao   nome   sobrenome
10/12/2020   200   Filial 1   Levindo   Lopes
11/12/2020   100   Filial 1   Levindo   Lopes
13/12/2020   280   Filial 2   Pedro   Altusser
17/12/2020   290   Filial 4   Levindo   Lopes

Se, além disso quisermos saber quais os itens foram vendidos fazemos:

SELECT v.data_venda, v.total, v.descricao, f.nome, f.sobrenome, p.descricao as "Produto"
    FROM vendas v
    JOIN funcionarios f
    JOIN itens_vendas i
    JOIN produtos p 
    WHERE  f.id = v.funcionario_id and v.id = i.venda_id and i.produto_id = p.id
    and v.cliente_id = (SELECT id FROM clientes WHERE  sobrenome="Arbinger");

data_venda   total   descricao   nome       sobrenome    Produto
10/12/2020   200     Filial 1    Levindo    Lopes        Windows 11
10/12/2020   200     Filial 1    Levindo    Lopes        Teclado sem fio
11/12/2020   100     Filial 1    Levindo    Lopes        iPAD

Observe que dois itens foram listados relativos à venda do dia 10/12/2020 e um item para o dia 11/12. Nenhum item foi listado para as vendas dos dias 13 e 17/12.

sqlite3 e Python

O python traz um módulo instalado com a biblioteca padrão para integrar o SQLite com o código. Para usá-lo basta importar esse módulo chamado sqlite3.

import sqlite3
connection = sqlite3.connect("escola.db")
cursor = connection.cursor()

sql = """
      CREATE TABLE IF NOT EXISTS  alunos (
        id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
        nome TEXT,
        idade INTEGER
        );
      """
cursor.execute(sql)

sql = """
    INSERT INTO alunos (nome, idade) VALUES
    ('Alice', 21),
    ('Letícia', 22),
    ('Vinicius', 23),
    ('Guilherme', 12),
    ('Marcos', 37),
    ('Pedro', 6),
    ('Wanda', 20),
    ('Aluísio', 10);
    """
cursor.execute(sql)
connection.commit()

dados = cursor.execute("SELECT * FROM alunos WHERE idade <=' 22")
for linha in dados:
    print(linha[0], linha[1], linha[2])

1 Alice 21
4 Guilherme 12
6 Pedro 6
7 Wanda 20
8 Aluísio 10

Com o comando connection = sqlite3.connect("escola.db") o banco de dados é criado, se já não existe. Um cursor é um objeto usado para fazer a relação entre o código (e as queries) com o banco. O objeto dados é um iterável que retorna tuplas com os resultados da query.

Digamos que queremos fazer uma consulta ao banco de dados pre-existente phylos.db, usado nos exercícios anteriores. Para isso estabelecemos uma nova conecção com esse banco de dados.

connection = sqlite3.connect("vendas.db")
sql = """
   SELECT v.data_venda, v.total, v.descricao, f.nome, f.sobrenome, p.descricao as "Produto"
      FROM vendas v
      JOIN funcionarios f
      JOIN itens_vendas i
      JOIN produtos p 
      WHERE  f.id = v.funcionario_id and v.id = i.venda_id and i.produto_id = p.id
      and v.cliente_id = (SELECT id FROM clientes WHERE  sobrenome="Arbinger");
"""
cursor = connection.cursor()
data = cursor.execute(sql)
for row in data:
    print(row)
    
# o resultado é:
('10/12/2020', 200, 'Filial 1', 'Levindo', 'Lopes', 'Windows 11')
('10/12/2020', 200, 'Filial 1', 'Levindo', 'Lopes', 'Teclado sem fio')
('11/12/2020', 100, 'Filial 1', 'Levindo', 'Lopes', 'iPAD')

sqlite3 e pandas

Veja o artigo Pandas e Dataframes e artigos subsequentes.

Uma outra possibilidade interessante é a de integrar os dataframes do pandas com o SQLite. Para isso importamos o sqlite3 e o pandas e estabelecemos uma conexão com o banco de dados. Um dataframe pode ser carregado diretamente com o resultado da consulta com pandas.read_sql(sql, connection).

import sqlite3
import pandas as pd

connection = sqlite3.connect("vendas.db")
sql = """
   SELECT v.data_venda, v.total, v.descricao, f.nome, f.sobrenome
      FROM vendas v JOIN funcionarios f
      WHERE f.id = v.funcionario_id;
"""
cursor = connection.cursor()
# apenas para ver o resultado da consulta fazemos:
data = cursor.execute(sql)
for row in data:
    print(row)

# o resultado:
('10/12/2020', 200, 'Filial 1', 'Levindo', 'Lopes')
('11/12/2020', 100, 'Filial 1', 'Levindo', 'Lopes')
('12/12/2020', 120, 'Filial 2', 'Levindo', 'Lopes')
('13/12/2020', 280, 'Filial 2', 'Pedro', 'Altusser')
('17/12/2020', 290, 'Filial 4', 'Levindo', 'Lopes')

df = pd.read_sql(sql, connection)
print(df)

# o resultado:
   data_venda  total descricao     nome sobrenome
0  10/12/2020    200  Filial 1  Levindo     Lopes
1  11/12/2020    100  Filial 1  Levindo     Lopes
2  12/12/2020    120  Filial 2  Levindo     Lopes
3  13/12/2020    280  Filial 2    Pedro  Altusser
4  17/12/2020    290  Filial 4  Levindo     Lopes

df.to_sql("tabela_2", connection)

O último comando insere uma tabela tabela_2 no banco de dados vendas.db com os campos e valores desse dataframe.

Bibliografia

Sobre SQLite

SQLite.org: SQLite Home
SimpliLearn: What is SQLite
Tutorialspoint: SQLite Tutorial
SQLiteTutorial.net: SQLite Tutorial
GeeksForGeeks: Intoduction to SQLite

Sobre o SQLite3, no Python

Python Docs: SQLite3
Tutorialspoint: SQLite – Python
GeeksforGeeks: Python SQLite

Instalações diversas do Python, pip e venv

Posted on 25/11/202225/11/2022 by Guilherme Silva

Onde o Python está instalado

Muitas vezes precisamos saber em que diretório está nossa instalação do python. Representaremos por $ o prompt do terminal do sistema, e comentários por #. O sinal [...] indica linhas omitidas.

# Primeiro verificamos se o python está instalado
$ python --version
  Python 3.9.13
# Essa versão pode ser inicializada
$ python
  Python 3.9.13 (main, Aug 25 2022, 23:26:10) 
  [GCC 11.2.0] :: Anaconda, Inc. on linux
  Type "help", "copyright", "credits" or "license" for more information.
>>> import sys
>>> sys.path
  ['', '/home/usuario/.anaconda3/lib/python39.zip', '/home/usuario/.anaconda3/lib/python3.9', '/home/usuario/.anaconda3/lib/python3.9/lib-dynload', '/home/usuario/.anaconda3/lib/python3.9/site-packages']
>>> exit()

O terminal interativo do python pode ser abandonado com exit(), quit() ou CTRL-D.

Não há problema em ter mais de uma versão instalada. Para ter um controle de qual versão estamos usando, e quais os módulos foram instalados para um projeto, devemos usar um ambiente virtual, como o venv.

Vemos que o Python 3.9.13, instalado com o Anaconda (usado para disparar o Jupyter Notebook) teve prioridade na chamada. Aproveitamos para importar o módulo sys e verificar os caminhos válidos para essa instalação.

No entanto, tenho outra versão, mais recente, do Python instalada. Versões diferentes podem ser usadas para abrir um terminal de comando de linha, como acima, ou para carregar um ambiente virtual, por exemplo. Temos, portanto, que encontrar quais são as versões instaladas.

Existem algumas maneiras de descobrir onde o Python está instalado, tipicamente com os comandos which, whereis e find.

`which`	exibe o diretório de instalação do executável Python que está no `PATH`,
`whereis`	exibe o diretórios de instalação do código fonte, binários e páginas `man`,
`find`	procura no sistema onde estão os arquivos `*.py`.

Por exemplo:

$ which python
  /home/guilherme/.anaconda3/bin/python
$ whereis python
  python: /usr/bin/python /home/guilherme/.anaconda3/bin/python /usr/share/man/man1/python.1.gz
$ find ~/Projetos -name *.py
# uma lista de arquivos com extensão py é exibida.

No último comando usamos find ~/Projetos -name *.py para encontrar arquivos com extensão .py partindo do diretório ~/Projetos, lembrando que ~ significa a pasta do usuário, /home/usuario. O comando find é muito mais geral que o exemplo mostrado e pode ser usado de muitas formas diferentes. Nenhum desses comandos são específicos para o python.

O comando whereis python mostra que tenho outra versão instalada, além do python da Anaconda, em /usr/bin/python . Ela pode ser executada diretamente do terminal:

$ /usr/bin/python
  Python 3.11.0 (main, Oct 24 2022, 00:00:00) [GCC 12.2.1 20220819 (Red Hat 12.2.1-2)] on linux
  Type "help", "copyright", "credits" or "license" for more information.
>>> import sys
>>> sys.path
  ['', '/usr/lib64/python311.zip', '/usr/lib64/python3.11', '/usr/lib64/python3.11/lib-dynload', '/usr/lib64/python3.11/site-packages', '/usr/lib/python3.11/site-packages']

Vimos assim que há uma versão mais recente do python instalada, o python3.11. Como uma ilustração do uso de versões diferentes do python vamos estender a discussão para a instalação e uso de módulos instalados.

Módulos em diferentes versões

Vimos acima que, no caso de meu computador o comando python está associado ao python3.9.3 da anaconda (porque é o caminho que ele encontra primeiro no PATH (exibido abaixo). Essa instalação já contém o pip. No entanto não há pip instalado para a versão do python3.11.

# exeminando o PATH
$ echo $PATH
  /home/usuario/.anaconda3/share/rubygems/bin:/home/usuario/.anaconda3/share/rubygems/bin:
  /home/usuario/.anaconda3/bin:/home/usuario/.anaconda3/condabin:/home/usuario/.local/bin:
  /home/usuario:/usr/local/bin:/usr/local/sbin:/usr/bin:/usr/sbin

# pip está instalado no python3.9
$ python -m pip --version
pip 22.2.2 from /home/guilherme/.anaconda3/lib/python3.9/site-packages/pip (python 3.9)

# pip não está instalado no python3.11
$ python3.11 -m pip --version
/usr/bin/python3.11: No module named pip

O pip não está instalado para o python3.11. Para essa instalação vamos usar o ensurepip, descrito no artigo sobre ambientes virtuais.

$ python3.11 -m ensurepip
  [...]
  Successfully installed pip-22.2.2

# para verificar a instalação
$ python3.11 -m pip --version
  pip 22.2.2 from /home/usuario/.local/lib/python3.11/site-packages/pip (python 3.11)

# caso um upgrade do pip seja necessário
$ python3.11 -m ensurepip --upgrade

Vemos assim que essa instalação do pip está em diretório diferente da instalação do anaconda. Para instalar módulos fazemos:

# para instalar módulos no ambiente do python3.9
$ python -m pip install <modulo>
# ou    
$ pip install <modulo>
 
# para instalar módulos no ambiente do python3.11
$ python3.11 -m pip install <modulo>

Observe que essa diferenciação entre os nomes python e python3.11 ocorre no meu computador e pode ser diferente dependendo da forma de instalação usada. Um computador pode ter várias outras versões do python instaladas, como o python2. Da mesma forma a criação de um ambiente virtual depende da versão do python que se quer usar.

Por default o Python é instalado no Linux no diretório /usr/local/bin/ e no Windows em C:\Python39 ou C:\Users\YourUser\AppData\Local\Programs\Python\PythonX onde X é o número de versão. O Anaconda é instalado em diretório definido pelo usuário.

Usando o pip

Para instalar um módulo no python3.11:

$ python3.11 -m pip install django

Para verificar quais os módulos instalados usamos:

$ python3.11 -m pip freeze
  [..]
  beautifulsoup4==4.11.0
  Django==4.1.3
  Markdown==3.4.1
  Pillow==9.2.0
  [..]

Os módulos instalados junto com o Anaconda são outros:

$ python -m pip freeze
  [..]
  anaconda-client==1.11.0
  anaconda-navigator==2.3.2
  [..]

Em ambos os casos várias linhas foram omitidas.
Também podemos ver os módulos instalados de dentro de uma sessão do python.

$ python3.11
  Python 3.11.0 (main, Oct 24 2022, 00:00:00) [GCC 12.2.1 20220819 (Red Hat 12.2.1-2)] on linux
>>> help("modules")
  Please wait a moment while I gather a list of all available modules...
  /usr/lib/python3.11/site-packages/_distutils_hack/__init__.py:33: UserWarning: Setuptools is replacing distutils.
  warnings.warn("Setuptools is replacing distutils.")
  PIL                     bisect              glob                       re
  PyQt5               blivet               gnome_abrt           readline
  [..]

# para obter ajuda específica sobre um módulo digitamos help("nome_modulo")
>>> help("random")

Módulos em ambientes virtuais

Para criar um ambiente virtual em uma versão específica do python informamos qual versão deve ser usada. Por ex., para criar um ambiente virtual em ~/Projetos/.venv usamos:

$ python3.11 -m venv ~/Projetos/.venv

# para ativar esse ambiente
$ source ~/Projetos/.venv/bin/activate
# o prompt muda para refletir esse estado
(.venv) $ 

# para executar o python nesse ambiente
$ cd ~/Projetos/.venv
(.venv) (~/Projetos/.venv)$ python
  Python 3.11.0

Como visto, toda a operação feita dentro do ambiente se refere à versão do python que instalou o ambiente. Nesse ambiente a biblioteca pandas não está instalada. Para instalar essa biblioteca saimos do python e usamos pip:

>>> import pandas
  [..]
  ModuleNotFoundError: No module named 'pandas'
# saimos do python
>>> exit()

# instalamos o módulo pandas
(.venv) (~/Projetos/.venv)$ python -m pip install pandas
  Collecting pandas
  [..]
  Successfully installed numpy-1.23.5 pandas-1.5.2 python-dateutil-2.8.2 pytz-2022.6 six-1.16.0

# o comando acima sugere um upgrade do pip
  [notice] A new release of pip available: 22.2.2 -> 22.3.1
  [notice] To update, run: pip install --upgrade pip

(.venv) (~/Projetos/.venv)$ pip install --upgrade pip
  [..]
  Successfully installed pip-22.3.1

O uso do pip acima, após a instalação da biblioteca pandas, informa a existência de um upgrade e fornece o comando para fazer essa atualização.

Bibliografia

Sites

Phylos.net: Ambientes Virtuais, PIP e Conda,
Real Python: Why Can’t Python Find My Modules?,
javaTpoint: Check Installed Modules in Python.

todos acessados em dezembro de 2022.

Filtros em Templates no Django

Posted on 07/07/2022 by Guilherme

Django, Templates e Filtros

Já vimos em Templates do Django que os templates são modelos usados na arquitetura MTV para renderizar as requisições do cliente e gerar texto com marcação HTML. Dentro desses modelos podemos receber variáveis enviadas no contexto, com a marcação {{ var }}. Um contexto é um conjunto de valores, em um objeto tipo dicionário (um conjunto de chaves:valores), passado pelas views para o template. Os nomes das variáveis são passados como strings.

# se temos a variável    
» contexto = {"n1": "um"; "n2":"dois";}
# e o template
⎀ Temos no contexto {{ n1 }} e {{ n2 }}.
# será renderizado como
↳ Temos no contexto um e dois.

Filtros são formas de modificar as saídas de variáveis. Por ex.:

» contexto = {'django': 'o framework web para perfecionistas com prazos'}
⎀ {{ django|title }}
↳ O Framework Web Para Perfecionistas Com Prazos
# ou
» contexto = {'data': '2022-07-04'}
⎀ {{ data|date:"d/m/Y" }}
↳ 04/07/2022

Muitos outros filtros são predefinidos e outros mais podem ser definidos pelo programador.

Filtros do Django

add soma valor ao argumento. Tenta forçar a conversão de strings para numéricos.

» valor = 6
⎀ {{ valor|add:"2" }}
↳ 8

» lista1 = [2,3,4]; lista2 = [7,8,9]
⎀ {{ lista1|add:lista2 }}
↳ [2,3,4,7,8,9]

addslashes, insere “\” em aspas.

» valor = "Einstein disse: 'Não existe mais espaço e tempo'."
⎀ {{ valor|addslashes }}
↳ Einstein disse: \'Não existe mais espaço e tempo\'.

capfirst, capitaliza primeira letra de uma string.

» valor = "não existe mais espaço e tempo"
⎀ {{ valor|capfirst }}
↳ Não existe mais espaço e tempo

center, centraliza texto dentro do espaço dado.

» valor = "tempo"
⎀ {{ valor|center:"20" }}
↳ "       tempo        "

cut, remove valores do argumento do string dado.

» valor = "não existe mais espaço e tempo"
⎀ {{ valor|cut:" " }}
↳ nãoexistemaisespaçoetempo

cut, formata uma data (e hora) de acordo com especificação dada.

» data = "2022-02-30"
⎀ {{ data|date:"d/m/y" }}
↳ 30/02/22
⎀ {{ data|date:"d-m-Y" }}
↳ 30-02-2022

Alguns formatos são pre-definidos. Veja a lista completa em Django docs.

Caracter	Descrição	Exemplo
Dia
d	dia do mes com dois dígitos	’01’ até ’31’
j	dia do mes sem zeros.	‘1’ até ’31’
D	dia da semana em texto, 3 letras.	‘Fri’
l	dia da semana em texto completo.	‘Friday’
w	dia da semana, numérico.	‘0’ (Domingo) até ‘6’ (Sábado)
z	dia do ano.	1 to 366
Semana
W	número da semana no ano.	1, 53
Mês
m	mês, 2 dígitos.	’01’ até ’12’
n	mês sem zeros.	‘1’ até ’12’
M	mês, texto, 3 letras.	‘Jan’, ‘Dec’
b	mês, texto, 3 letras, ninúsculas.	‘jan’, ‘dec’
F	mês, texto, extenso.	‘January’
t	quantos dias no mês.	28 to 31
Ano
y	ano, 2 dígitos.	’00’ até ’99’
Y	ano, 4 dígitos.	‘0001’, …, ‘1999’, …, ‘9999’
L	Booleano, se ano é bissexto.	True ou False
Hora
g	hora, formato 12-hora sem zeros.	‘1’ até ’12’
G	hora, formato 24-hora sem zeros.	‘0’ até ’23’
h	hora, formato 12-hora.	’01’ até ’12’
H	hora, formato 24-hora.	’00’ até ’23’
i	minutos.	’00’ até ’59’
s	segundos, 2 dígitos.	’00’ até ’59’
u	microssegundos.	000000 até 999999
a	‘a.m.’ ou ‘p.m.’
A	‘AM’ ou ‘PM’.
f	hora, format 12-horas e minutos	‘1:30’
P	hora, formato 12-hora horas, minutos	‘a.m.’/’p.m.’
Timezone
e	nome da timezone	‘GMT’, ‘-500’, ‘US/Eastern’, etc.

O formato pode ser um dos predefinidos como DATE_FORMAT, DATETIME_FORMAT, SHORT_DATE_FORMAT ou SHORT_DATETIME_FORMAT ou um formato construído com os especificadores acima. Formatos predefinidos podem depender do ajuste local.

# se data_valor é um objeto datetime, como o resultante de datetime.datetime.now() 
# com hora 23:45, dia 01/11/2021
⎀ {{ data_valor|date:"D d M Y" }} {{ data_valor|time:"H:i" }}
↳ Mon 01 Nov 2021 23:45

# se o locale for pt-BR
⎀ {{ data_valor|date:"SHORT_DATE_FORMAT" }}
↳ 01/11/2021

default, fornece valor default se argumento for False.

»  valor = ""   
⎀ {{ valor|defalt:"nada aqui" }} 
↳ nada aqui

default_if_none, fornece valor default se argumento for None.

» valor = None
⎀ {{ valor|defalt_if_none:"recebemos None" }} 
↳ recebemos None

dictsort recebe uma lista de dicionários e ordena a lista por alguma das chaves do docionário.

# considerando o dicionário:    
» dicio = [
»     {'nome': 'Zuenir', 'idade': 9},
»     {'nome': 'Antônia', 'idade': 12},
»     {'nome': 'Jaime', 'idade': 3},
» ]

⎀ {{ dicio|dictsort:"nome" }}
# resulta em
↳ dicio = [
↳     {'nome': 'Antônia', 'idade': 12},
↳     {'nome': 'Jaime', 'idade': 3},
↳     {'nome': 'Zuenir', 'idade': 9},
↳ ]

Exemplos mais complexos podem ser obtidos:

# se livros é    
» livros = [
»     {'titulo': '1984', 'autor': {'nome': 'George', 'idade': 45}},
»     {'titulo': 'Timequake', 'autor': {'nome': 'Kurt', 'idade': 75}},
»     {'titulo': 'Alice', 'autor': {'nome': 'Lewis', 'idade': 33}},
» ]

# o código em template
⎀ {% for livro in livros|dictsort:"autor.idade" %}
⎀     * {{ livro.titulo }} ({{ livro.autor.nome }})
⎀ {% endfor %}

# resultaria em
↳ * Alice (Lewis)
↳ * 1984 (George)
↳ * Timequake (Kurt)

dictsortreversed tem o mesmo efeito que dictsort, mas ordenando em ordem invertida.

divisibleby returna True se o valor é divisível pelo argumento.

» valor = 171
⎀ {{ value|divisibleby:"3" }}
↳ True

escape promove remoção de tags html.

# esse exemplo mostra a exibição final no navegador
» string_html = "<b>Negrito<b>"
⎀ {{ string_html }}
↳ <b>Negrito<b>

⎀ {{ string_html|scape }}
↳ <b>Negrito</b>

escape converte:

< em <
> em >
' (aspas simples) em '
" (aspas duplas) em "
& em &

first retorna o 1º elemento de uma lista.

» lista = ["casa","da","sogra"]
⎀ {{ lista|first }}
↳ casa

floatformat promove o arredondamento de números flutuantes.

» valor = 34.23234
⎀ {{ valor|floatformat }}
↳ 34.2

» valor = 34.0000
⎀ {{ valor|floatformat }}
↳ 34

» valor = 34.26000
⎀ {{ valor|floatformat }}
↳ 34.3

O número de casas pode ser definido em valor|floatformat:n. Passando “0” como argumento o arredondamento será para o inteiro mais próximo. O sufixo g introduz separador de milhar, definido em THOUSAND_SEPARATOR.

valor	template	output
34.23234	{{ valor\|floatformat:2 }}	34.23
34.00000	{{ valor\|floatformat:2 }}	34.00
34.26000	{{ valor\|floatformat:2 }}	34.26
34.23234	{{ valor\|floatformat:”0″ }}	34
31.00000	{{ valor\|floatformat:”0″ }}	31
39.56000	{{ valor\|floatformat:”0″ }}	40
34232.34	{{ valor\|floatformat:”2g” }}	34,232.34
34232.06	{{ valor\|floatformat:”g” }}	34,232.1

get_digit retorna um inteiro na posição especificada, contando do final para o início. Se não for possível encontar esse dígito, retorna o valor original.

» valor = 9512845
⎀ {{ valor|get_digit:"4" }}
↳ 2

⎀ {{ valor|get_digit:"9" }}
↳ 9512845

join faz a união de elementos em uma lista em uma string (como em str.join(lista)).

» lista = ["casa", "da", "mãe", "Joana"]
⎀ {{ lista|join:" - " }}
↳ casa - da - mãe - Joana

last retorna o último elemento de uma lista.

»  lista = ["casa", "da", "mãe", "Joana"]
⎀ {{ lista|last}}
↳ Joana

len retorna o comprimento de uma lista.

»  lista = ["casa", "da", "mãe", "Joana"]
⎀ {{ lista|len}}
↳ 4

length_is retorna booleano, se o comprimento de uma lista é o dado em parâmetro.

»  lista = ["casa", "da", "mãe", "Joana"]
⎀ {{ lista|length_is:"4"}}
↳ True

linebreaks substitui quebras de linha em texto puro por uma quebra de linha html (<br>) e insere anova linha entre tags de parágrafo (<p> … </p>).

» texto_puro = "Essa é a linha 1\nEssa é a linha 2"
⎀ {{ texto_puro|linebreaks}}
↳ <p>Essa é a linha 1<br>Essa é a linha 2</p>

linebreaksbr faz a mesma coisa, sem inserir a linha em parágrafo.

linenumbers quebra texto em linhas e as numera.

» lista_compras = '''Leite
» Açucar
» Café
» Pão'''

⎀ {{ lista_compras|linenumbers }}
# resulta em
↳ 1. Leita
↳ 2. Açucar
↳ 3. Café
↳ 4. Pão

Observe que lista_compras = “Leite\nAçucar\nCafé\nPão”.

ljust alinha texto à esquerda dentro de espaço de n caracteres dado.

» comprar = "Pão"
⎀ {{ comprar|ljust:"9" }}
↳ "Pão      "

lower converte todas os caracters de uma string para caixa baixa (minúsculas).

» texto = "Pão COM Manteiga"
⎀ {{ texto|lower }}
↳ pão com manteiga

make_list retorna valor string ou inteiro em uma lista.

» texto = "Pão de Queijo"
⎀ {{ texto|make_list }}
↳ ["P", "ã", "o", " ", "d", "e", " ", "Q", "u", "e", "i", "j", "o"]

» numero = 1957
⎀ {{ numero|make_list }}
↳ ["1", "9", "5", "7"]

pluralize retorna sufixo para plurais se valor do parâmetro for maior que 1. Esse valor pode ser o comprimento do objeto. O sufixo default é s, mas isso pode ser alterado.

» itens_compra = 1
⎀ Você tem que comprar {{ itens_compra }} objeto{{ itens_compra|pluralize }}.
↳ Você tem que comprar 1 objeto.

» itens_compra = 23
⎀ Você tem que comprar {{ itens_compra }} objeto{{ itens_compra|pluralize }}.
↳ Você tem que comprar 23 objetos.

Sufixos alternativos podem ser inseridos como parâmetros:

» quantos = 1
⎀ Você fez o pedido de {{ quantos }} paste{{ quantos|pluralize:"l,is" }}.
↳ Você fez o pedido de 1 pastel.

» quantos = 45
⎀ Você fez o pedido de {{ quantos }} paste{{ quantos|pluralize:"l,is" }}.
↳ Você fez o pedido de 45 pasteis.

random retorna um elemento aleatório de uma lista.

»  lista = ["casa", "da", "mãe", "Joana"]
⎀ {{ lista|random }}
# um possível resultado é
↳ mãe

rjust alinha texto à direita dentro de espaço de n caracteres dado.

» comprar = "Pão"
⎀ {{ comprar|rjust:"9" }}
↳ "      Pão"

safe marca texto como não necessitando escapes.

escape promove remoção de tags html.

» string_html = "<b>Negrito<b>"
⎀ {{ string_html|escape }}
↳ <b>Negrito<b>

Se existirem tags html elas serão renderizadas no navegador.

slice retorna uma fatia (slice) de uma lista. Usa a mesma sintaxe de slicing de listas do python:

» lista = ["casa","da","sogra", "no", "domingo"]
⎀ {{ lista|slice:":3" }}
↳ ["casa","da","sogra"]

slugify converte texto em ASCII puro, convertendo espaços em hífens. Remove caracteres que não são alfanuméricos, sublinhados (underscores) ou hífens. Converte tudo para minúsculas eliminando espaços nas bordas.

» texto = " Artigo 31 das Notas "
⎀ {{ texto|slugfy }}
↳ artigo-31-das-notas

stringformat formata variável de acordo com o parâmetro especificador.

» valor = 10
⎀ {{ valor|stringformat:"E" }}
↳ 1.000000E+01

Mais caracteres de formatação em printf-style String Formatting.

striptags remove tags [X]Html sempre que possível.

» valor = "<b>Um texto pode ter</b> <button>várias tags</button> <span>(x)html</span>!"
⎀ {{ valor|striptags }}
↳ Um texto pode ter várias tags (x)html!

Observação: striptags não garante que o texto seja seguro. Não aplique a filtro safe sobre o resultado de striptags.

time formata variável tipo time de acordo com formato especificado.

» hora = datetime.datetime.now()
⎀ {{ hora|time:"H:i" }}
↳ 18:49

⎀ {{ hora|time:"H\h i\m" }}
↳ 01h 23m

No exemplo caracteres literais foram escapados (\h, \m).

timesince formata uma diferença de datas entre now (agora) e data fornecida em parâmetro.

# se artigo_gravado contem uma data e
» hora = datetime.datetime.now()
⎀ {{ artigo_gravado|timesince:hora }}
↳ 14 days, 18 hours

timeuntil é análoga à timesince mas retornando a diferença entre uma data data e data futura.

title formata string como título de artigos e livros, colocando em maiúsculas as primeiras letras de cada palavra.

» titulo = "análise auxiliar de enrolação científica"
⎀ {{ titulo|title }}
↳ Análise Auxiliar De Enrolação Científica

truncatechars realiza o truncamento de um texto em um número especificado de caracteres. O texto truncado é seguido de elipses ….

» texto = "Esta é uma nota grande."
⎀ {{ texto|truncatechars:15 }}
↳ Esta é uma nota...
# nada é feito de o texto for menor que o parâmetro de truncamento
⎀ {{ texto|truncatechars:35 }}
↳ Esta é uma nota grande.

truncatechars_html é similar à truncatechars mas evitando o descarte de tags html.

» texto = "Esta é uma nota grande."
⎀ {{ texto|truncatechars_html:15 }}
↳ <p>Esta é uma not...</p>

truncatewords trunca uma string após um número dado de palavras. O texto truncado é seguido de elipses …. Quebras de linha são removidas.

» texto = "Um texto com\n muitas palavras pode ser cortado"
⎀ {{ texto|truncatewords:4 }}
↳ Um texto com muitas...

truncatewords_html é similar à truncatewords, mas evitando eliminar tags html. Quebras de linha são mantidas.

» texto = "<p>Um texto com\n muitas palavras pode ser cortado</p>"
⎀ {{ texto|truncatewords:4 }}
↳ <p>Um texto coman muitas...</p>

unordered_list constroi uma lista html à partir de listas e listas aninhadas, inserindo recursivamente sublistas quando necessário.

» lista = ['Estados', ['Minas Gerais', ['Juiz de Fora', 'Belo Horizonte'], 'Paraná']]
⎀ {{ lista|unordered_list }}
↳
<li>Estados
<ul>
        <li>Minas Gerais
        <ul>
                <li>Juiz de Fora</li>
                <li>Belo Horizonte</li>
        </ul>
        </li>
        <li>Paraná</li>
</ul>
</li>

Observe que as tags de abertura e fechamento da lista externa (<ul></ul>) não são incluídas.

upper converte todos os caracteres de uma string em maiúsculas.

» texto = "Um texto com algumas palavras."
⎀ {{ texto|upper }}
↳ UM TEXTO COM ALGUMAS PALAVRAS.

urlencode transforma uma string para uso como url.

» url = "https://phylos.net/foo?a=b"
⎀ {{ url|urlencode }}
↳ https%3A//phylos.net/foo%3Fa%3Db

urlize converte uma URL ou endereço de email em links clicáveis.

» url = "Visite minha página em phylos.net"
⎀ {{ url|urlize }}
↳ Visite minha página em phylos.net

# emails também são convertidos
» email = "Mande sua mensagem para usuario@exemplo.com"
⎀ {{ email|urlize }}
↳ Mande sua mensagem para usuario@exemplo.com

O atributo rel=”nofollow” é acrescentado.

urlizetrunc age como urlize mas truncando urls longas para a exibição>

{{ url|urlizetrunc:15 }}

wordcount retorna número de palavras no parâmetro.

» texto = "Um texto com algumas palavras."
⎀ {{ texto|wordcount }}
↳ 5

wordwrap quebra o texto em comprimento especificado, inserindo quebra de linhas. wordwrap:n não quebra palavras mas sim a linha em valores inferiores a n dado como comprimento.

Implements word wrapping by inserting a newline character every n characters. Useful for plain text, but not typically for HTML.

» texto = "Um texto com algumas palavras."
⎀ {{ texto|wordwrap:17 }}
↳ Um texto com
↳ algumas palavras.

yesno retorna uma string especificada para o valor do parâmetro True, False ou None (opcional).

» condicao = False
⎀ {{ condicao|"Sim, Não, Talvez" }}
↳ Não

» condicao = True
⎀ Você respondeu: {{ condicao|"Afirmativo, Negativo" }}
↳ Você respondeu: Afirmativo

Bibliografia

Livros

Newman, Scott: Django 1.0 Template Development, 2008 Packt, 2008.

Sites

Django Project: Django, web framework for perfectionists with deadlines,
Django Project: Templates,
Django Project: Builtins Templates and Tags,

todos acessados em julho de 2022.

Templates do Django

Posted on 06/07/202214/10/2022 by Guilherme

Templates do Django

Como vimos em Introdução ao Django (1), (2) e (3) o framework usa a arquitetura MVT, Model, View, Template, para gerar HTML dinamicamente. Um quadro pode ajudar a esclarecer o modelo.

Descrição do modelo MVT

O navegador envia uma requisição para o servidor rodando django (1).
A URL é recebida por urls.py que atribui uma view para tratamento da requisição.
A camada view consulta Model (2) para receber os dados requisitados (3).
Depois de obter os dados View consulta a camada Template (4) para formatar a apresentação final (5) e envia páginas html formatadas para o navegador cliente (6).

Templates são as partes estáticas da saída HTML desejada, adicionadas de tags e variáveis que usam uma sintaxe especial para descrever como o conteúdo dinâmico será inserido, chamada de Django Model Language, (DTL). Por default o django usa o Jinja2, que pode ser alterado se necessário. Dizemos que o template renderiza o modelo inserindo nas variáveis os valores recebidos de acordo com o contexto. O suporte para modelos e templates (o DTL) estão no namespace Django.template.
Advertência: Em todo esse artigo nos referimos a dois tipos diferentes de tags: tags html são as marcações usuais para páginas na web, como <body>, <p>, <h1>, <table> , e possuem o mesmo significados que em páginas estáticas. Já as tags do djangos são as marcações próprias do jinja2 (por default) para indicar a inserção de conteúdo fornecido dinamicamente pelo aplicativo. Usaremos aqui, para facilitar a leitura, a seguinte representação:

# comentários    
» Código python, geralmente dentro de uma view,
⎀ representação de marcação do django, geralmente em pasta app/template,
↳ resultado após a renderização.

Por exemplo:

» nome = "Policarpo"; sobrenome= "Quaresma"
⎀ Meu nome é {{ nome }}, meu sobrenome é {{ sobrenome }}
↳ Meu nome é Policarpo, meu sobrenome é Quaresma

Sintaxe

O sistema de templates substitui tags e variáveis por seus valores fornecidos pelo contexto. Todo o restante do texto e tags html são retornados literalmente.

Variáveis

Uma variável é substituída por seu valor no contexto, com a marcação {{ var }}. Um contexto é um conjunto de valores, em um objeto tipo dicionário (um conjunto de chaves:valores), passado pelas views para o template. Os nomes das variáveis são passados como strings.

» contexto = {"n1": "um"; "n2":"dois";}
⎀ Temos no contexto {{ n1 }} e {{ n2 }}
↳ Temos no contexto {{ um }} e {{ dois }}

Usamos a notação de ponto para fazer a busca em chaves de um dicionário, exibição de atributo em objetos e recuperação de valor em lista por meio de seu índice:

» dicionario.chave
» um_objeto.atributo
» uma_lista.0

Se o valor da variável for uma função ou outro objeto que pode ser chamado (um callable) o sistema fará uma chamada ao objeto e retornará seu valor.

Tags e Filtros

Tags são mecanismos de controle geral da renderização de templates. Elas podem controlar o fluxo de código (como em if, for), podem dar acesso à outro arquivo de template ou inserir modos de controle. Tags aparecem dentro da marcação {% tag %}. Por exemplo:

# suponha que temos o objeto usuario com atribuitos nome e super
» usuario.nome = "Fulaninho del'Valle"; usuario.super = True
⎀ Nome do usuário: {{ usuario.nome }}. {% if usuario.super }} É supersuser! {% endif %}
↳ Nome do usuário: Fulaninho del'Valle. É supersuser!

Um exemplo de tag de controle é {% csrf_token %}, que colocado dentro de um formulário impede o ataque tipo CSRF ao site.

Filtros são formas de modificar as saídas de variáveis. Por ex.:

» contexto = {'django': 'o framework web para perfecionistas com prazos'}
⎀ {{ django|title }}
↳ O Framework Web Para Perfecionistas Com Prazos
# ou
» contexto = {'data': '2022-07-04'}
⎀ {{ data|date:"d/m/Y" }}
↳ 04/07/2022

Tags do Django

Uma descrição mais extensa das tags pode ser lida em
Docs: Built-in Tags.

Comentários podem ser inseridos das seguintes formas:

# isto é um comentário em uma linha
⎀  {# isto é um comentário e será ignorado #}
# comentários em várias linhas
⎀  {% comment %}
⎀    <p>Linha 1</p>
⎀    <p>Linha 2</p>
⎀  {% endcomment %}

csrf_token fornece uma forma de defesa contra a falsificação de solicitações entre sites (Cross Site Request Forgeries).

⎀ <form>{% csrf_token %} ... conteúdo do formulario</form>

Block e Extends são as ferramentas básicas para se usar herança de templates. block define uma área em um template base que pode ser substituído por conteúdo no arquivo que o importa.
base.html

<!DOCTYPE html>
<html>
<head>
</head>
<body>
  {% block bloco1 %} texto default 1 {% endblock %}
  {% block bloco2 %} texto default 2 {% endblock %}
</body>
</html>

e o template que o importa:
inicial.html

{% extends "base.html" %}
{% block title %} texto 1 {% endblock %}
{% block content %} texto 2 {% endblock %}

{% extends "base.html" %} faz com que inicial.html seja uma extensão do template base.html. Ele pode receber o nome (com caminho) do arquivo base ou uma variável que contém esse nome. Caso um bloco não exista na página que herda base.html o conteúdo inicial (ou nenhum, se não existir) é mantido.

{% extends arquivo %} pode ser usado de duas formas:

{% extends "base.html" %}
{% extends nome_do_arquivo %}

O arquivo base.html deve ser um arquivo html completo, com cabeçalhos, importações de css, etc. Ele contém blocks (que já descrevermos) que são substuídos pelos valores em nome_do_arquivo.html.

Por exemplo, considere o exemplo do arquivo base:
base.html

<!DOCTYPE html>
<html>
<head>
</head>
<body>
  <h1>Meu Aplicativo</h1>
  {% block title %}{% endblock %}
  
  {% block content %}Esse é meu site com o meu aplicativo  {% endblock %}
</body>
</html>

e o template que o importa:
inicial.html

{% extends "base.html" %}
{% block title %}<h1>Aplicativo de Notas</h1>{% endblock %}

{% block content %}
<h2>Esse é o conteúdo da bloco content</h2>
{% endblock %}

Quando o arquivo inicial.html é chamado ele usa base.html, substituindo nele os blocos title e content. O resultado final será a página com html puro:

<!DOCTYPE html>
<html>
<head>
</head>
<body>
  <h1>Meu Aplicativo</h1>
  <h1>Aplicativo de Notas</h1>
  <h2>Esse é o conteúdo da bloco content</h2>
</body>
</html>

Deve-se ter o cuidado para indicar os caminhos corretos.

# ambos os arquivos estão no mesmo diretório    
{% extends "base.html" %}
# ou
{% extends "./base.html" %}
# base02.html está um diretório acima
{% extends "../base02.html" %}
# base03.html está em outro diretório dentro do atual
{% extends "./outro/base03.html" %}

Os caminhos são relativos ao diretório informado em settings.py:

TEMPLATES = [{
    'BACKEND': 'django.template.backends.django.DjangoTemplates',
    'DIRS': [BASE_DIR / 'templates'],
}]

include é usado para inserir e renderizar outro template dentro do contexto atual. Ele pode ser usado com referências literais a um arquivo, ou com o nome referenciado em variável.

{% include "caminho/menu.html" %}
{% include nome_do_template %}

Por ex., suponha que o template
ola.html

{{ sauda }}, {{ nome }}!

é incluído em
saudacao.html

<h1>Página Inicial de nosso Site:</h1>    
{% include "ola.html" %}

Se temos um contexto contexto = {"sauda":"Bom dia";"nome":"Augusto dos Anjos"} e passamos esse contexto para saudacao.html temos o seguinte template final renderizado:

<h1>Página Inicial de nosso Site:</h1>
<p>Bom dia, Augusto dos Anjos!</h1p>

Variáveis pode ser passadas diretamente para o template usando keywords, ou com o parâmetro only para usar apenas essas variáveis, ignorando as demais.

{% include "ola.html" with sauda="Boa noite" nome="James Gomes" %}
{% include "ola.html" with sauda="Boa tarde" only%}

Observação: Blocos são avaliados antes da inclusão, ou seja, as variáveis de contexto são substituídas antes que a inserção em outro template seja feita.

load é usado para inserir no ambiente atual um conjunto de tags e filtros já importados em outro template. Por ex., se temos duas bibliotecas biblio1 e biblio2 localizadas em pacote1 e pacote2 respectivamente (como bibliotecas devem estar em diretórios contendo o arquivo __init__.py) poemos importar todos os templates e filtros neles definidos com a inserção de load. Para selecionar apenas alguns filtros ou tags usamos from.

{% load pacote1.biblio1 pacote2.biblio2 %}
{% load tag1 tag2 from pacote1.biblio1 %}

Essa tag é especialmente útil para tags e filtros customizados.

url retorna o caminho absoluto (a URL sem nome de domínio) apontando para uma view com seus parâmetros. Essa é uma forma de exibir links dentro de templates sem forçar a construção de URLs fixas, que podem ser quebradas se o aplicativo for realocado para outro diretório.

{% url 'nome_url' arg1 arg2 %}
# 'nome_url' é o padrão da url, os argumentos arg1 e arg2 são passados para a view

nome_url pode ser uma string literal ou variável de contexto. Os parâmetros são separados por espaços. Alternativamente podemos usar argumentos nomeados:

{% url 'nome_url' arg1=arg1 arg2=arg2 %}

Lembramos que um projeto django é formado por um ou mais aplicativos. O código relativo aos aplicativos ficam em subdiretórios do diretório geral do projeto. Temos portanto as pastas diretorio_projeto e diretorio_projeto/aplicativo.

Por ex., suponha que temos uma view para um aplicativo em aplicativo_views.livro que recebe um id para exibir um livro cadastrado, ou seja livro() é um método em aplicativo/views. Em aplicativo/urls.py existe a linha,

path('livro//', aplicativo_views.livro, name='view_livro')

No arquivo de urls do projeto (em nível acima), projeto.urls podemos ter

path('livros/', include('projeto.aplicativo.urls'))

Em um template podemos usar, para acessar essa view:

{% url 'view_livro' livro.id %}
# que vai ser renderizado como (por ex. para livro.id=456)
/livros/livro/456

Se a URL não tiver uma view correspondente uma exceção NoReverseMatch será lançada. Também é possível armazenar em variável uma url para uso posterior, dentro do mesmo bloco, como em

{% url 'nome_da_url' arg1 arg2 as minha_url %}
<a href="{{ minha_url }}">Visita a minha página em {{ minha_url }}</a>

Nenhum erro é lançado se a view não existe, o que torna essa forma útil para exibir links que podem não existir, no contexto:

{% url 'nome_da_url' arg1 arg2 as minha_url %}
{% if minha_url %}
  Link para a página opcional.
{% endif %}

Se a url estiver definida dentro de um namespace especificado, como meu_app (o que é útil para evitar conflitos de nomes, podemos usar seu nome “totalmente qualificado” (fully qualified name):

{% url 'meu_app:nome_da_view' %}

Os nomes de padrões para as urls devem ser colocados entre aspas ou serão interpretados como variáveis de contexto!

for é usado para percorrer um laço sobre uma sequência de valores passada no contexto.

# frutas é uma tupla no contexto
» frutas = ('pera','maça','limão')
⎀ <ul>
⎀   {% for fruta in frutas %}
⎀     <li>{{ fruta }}</li>
⎀   {% endfor %}
⎀ </ul>
# será renderizado como
↳ <ul>
↳   <li>pera</li>
↳   <li>maça</li>
↳   <li>limão</li>
↳ </ul>

# para percorrer a lista em ordem reversa usamos
⎀   {% for fruta in frutas reversed %}

Para percorrer uma lista de listas (ou outros objetos que são coleções ) é possível obter valores individuais em variáveis separadas. Por ex., se temos uma lista de pontos com suas respectivas coordenadas pode fazer:

# uma lista de pontos com lista de 2 coordenadas
» pontos = ((3,8), (7,4), (5,5))
⎀   {% for x, y in pontos %}
⎀     <p>Coordenada x={{ x }}, y={{ y }} </p>
⎀   {% endfor %}
# será renderizado como
↳ <p>Coordenada x=3, y=8</p>p>
↳ <p>Coordenada x=7, y=4</p>p>
↳ <p>Coordenada x=7, y=7</p>p>

Para listar ítens de dicionários, chaves e valores, fazemos calgo análogo:

# dado um dicionário dic_dados
⎀ {% for key, value in dic_dados.items %}
⎀     {{ key }}: {{ value }}
⎀ {% endfor %}

Observação: O operador de ponto para acesso à itens de dicionário tem precedência sobre o laço for. Se o dicionário contém uma chave ‘items’, dic_dados[‘items’] será retornado em lugar de dic_dados.items. Por isso se deve evitar dar nomes de chaves idênticos aos de métodos que podem ser usados em um template (como “items”, “values”, “keys”.)

for ... empty é uma condição alternativa de retorno quando a lista no laço está vazia. O texto explicitado é retornado:

# dias é uma lista vazia
» dias_livres = []
⎀ <ul>
⎀   {% for dia in dias_livres %}
⎀     <li>{{ dia }}</li>
    {% empty %}
⎀     <li>Não há nenhum dia livre!</li>
⎀   {% endfor %}
⎀ </ul>
# será renderizado como
↳ <ul>
↳   <li>Não há nenhum dia livre!</li>
↳ </ul>

Variáveis disponíveis no laço são variáveis que ficam disponíveis para uso durante um laço for.

Variável	Descrição
forloop.counter	o atual contador da iteração (base 1)
forloop.counter0	o atual contador da iteração (base 0)
forloop.revcounter	quantas iterações faltam para o final do laço (base 1)
forloop.revcounter0	quantas iterações faltam para o final do laço (base 0)
forloop.first	True apenas para a 1ª iteração no laço
forloop.last	True apenas para a última iteração no laço
forloop.parentloop	para laços aninhados esse é a iteração do laço externo

if é usado para testes condicionais. Sua sintaxe geral é:

{% if condicao1 %}
  Texto se condicao1==True
{% elif condicao2 %}
  Texto se condicao1==False e condicao2==True
{% else %}
  Texto se condicao1==False e condicao2==False
{% endif %}

Por ex., suponha que obj_carros é uma sequência de objetos carro com as propriedades carro.marca (str), carro.vendido (booleana). O template abaixo

  <p>Temos {{ obj_carros|length }} para venda</p>
  {% for carro in obj_carros %}
     Marca: {{ carro.marca }}  {%if carro.vendido %} Está vendido {% else %} Disponível {% endif %}
  {% empty %}
     Não há carros disponíveis!
  {% endfor %}

mostrará quantos carros existem para venda e uma listagem de todos os carros, com suas marcas e disponibilidade (ou não) para venda, ou a informação de que nenhum carro está disponível.

Operadores booleanos. As condições lógicas podem ser ligadas ou modificadas por operadores booleanos and, or e not, com significado usual no python.

{% if condicao1 and condicao2 %}
    Se ambas as condições são True.
{% endif %}

{% if condicao1 or condicao2 %}
    Se uma das, ou ambas as, condições são True.
{% endif %}

{% if not condicao1 %}
   Se a condições1 é False.
{% endif %}

{% if not condicao1 or condicao2 %}
    Se condicao1 == False ou condicao2==True.
{% endif %}

Se and e or são usadas na mesma tag and tem precedência sobre or. Ou seja

{% if condicao1 and condicao2 or condicao3 %}
# será interpretada como
{% if (condicao1 and condicao2) or condicao3 %}span>
# a sentença acima, no entanto, é inválida (parênteses não podem ser usados nesse ambiente)

Operadores: Os operadores válidos para construções de testes lógicos são ==, !=, <, >, <=, >=, in, not in, is, is not, com significado usual no python.

{% if var_string == "palavra" %} faça algo {% endif %}
{% if var_string != "palavra" %} faça algo {% endif %}
{% if var_numerica == 100 %} faça algo {% endif %}
{% if var_numerica !== 100 %} faça algo {% endif %}
{% if var_numerica > 100 %} faça algo {% endif %}
{% if var_numerica < 100 %} faça algo {% endif %}
{% if var_numerica <= 100 %} faça algo {% endif %}
{% if var_numerica >= 100 %} faça algo {% endif %}

# in procura por partes de uma string
{% if "adi" in "moradia" %}
  Texto caso "adi" seja parte (substring) de "moradia"
{% endif %}

{% if "adi" in var_string %}
  Texto caso "adi" seja substring da var_string
{% endif %}

{% if user in users %}
  Texto caso user (uma variável) seja um dos elementos da coleção users
{% endif %}

# is é um teste de identidade entre objetos. True se foram o mesmo objeto.
{% if var1 iscode> var2 %} var1 e var2 se referem ao mesmo objeto{% endif %}
{% if var iscode> None %} var é None ou não foi encontrada no contexto.{% endif %}

# is not é a negação do teste acima
{% if var1 is notcode> var2 %} var1 e var2 não se referem ao mesmo objeto{% endif %}
{% if var is notcode> None %}
  var não é None, portanto foi encontrada no contexto.
{% endif %}

Testes compostos podem ser construídos, seguindo a mesma ordem do python. Se A, B, C, E são booleanas ou expressões que avaliam em um booleano (expressões em negrito são inválidas):

{% if A ==code> B or C == D and E %}
# será interpretado como
{% (A ==code> B) or ((C ==code> D) and E) %}
# os parênteses não devem ser usados

# essa expressão é inválida
{% if A > B > C %}
# e deve ser escrita como
{% if A > B and B > C %}

# diferente do python isso não pode ser feito
{% if A > B > C %}
# faça
{% if A > B and B > C %}

Se for necessário usar regras de precedência diferentes use tags aninhadas.

if changed é uma tag que verifica se um ítem de uma lista foi alterado entre iterações dentro de um laço. Se passamos um contexto com uma lista de fornecedores como um objeto com nome e 3 ítens quaisquer, sendo que mais de um registro existe para cada um deles, podemos exibir o nome do fornecedor apenas uma vez.

<h1>Lista de Fornecedores</h1>
{% for fornecedor in fornecedores %}
    {% ifchanged %}<h3>Nome do fornecedor {{ fornecedor.nome }}</h3>{% endifchanged %}
    <p>{{ fornecedor.item1 }}, {{ fornecedor.item2 }}, {{ fornecedor.item3 }}</p>
{% endfor %}

Se mais de uma variável é verificada, cada mudança de valor produz uma saída. No template seguinte, supondo que o objeto fornecedor possui um atributo fornecedor.cidade e que cada um pode estar em mais de uma cidade, o nome é exibido sempre que trocado, e a cidade é exibida quando o nome ou a cidade são trocados.

<h1>Lista de Fornecedores</h1>
{% for fornecedor in fornecedores %}
    {% ifchanged fornecedor.nome %}
      <h3>Nome do fornecedor {{ fornecedor.nome }}</h3>
    {% endifchanged %}
    {% ifchanged fornecedor.cidade %}
      <h3>Cidade {{ fornecedor.cidade }}</h3>
    {% endifchanged %}
    <p>{{ fornecedor.item1 }}, {{ fornecedor.item2 }}, {{ fornecedor.item3 }}</p>
{% endfor %}

Claro que uma boa exibição desses templates depende se estar a lista ordenada nos campos verificados. Uma cláusula else pode ser fornecida para inserir conteúdo se não houver qualquer mudança.

<h1>Lista de Fornecedores</h1>
{% for fornecedor in fornecedores %}
    {% ifchanged fornecedor.nome %}<h3>Nome do fornecedor {{ fornecedor.nome }}</h3>
    {% else %} <p>(continuando...)</p>
    {% endifchanged %}
    <p>{{ fornecedor.item1 }}, {{ fornecedor.item2 }}, {{ fornecedor.item3 }}</p>
{% endfor %}

cycle é uma tag que retorna um de seus argumentos, em ciclo, a cada vez que é acessada. Quando todos os argumentos forem esgotados o primeiro deles é produzido novamente. Qualquer número de valores pode ser usado nos ciclos. Por ex.:

# considere que classe1 e classe2 estão definidas como classes no arquivo css
<table>
{% for obj in alguma_lista %}
    <tr class="{% cycle 'classe1' 'classe2' %}"><td>...</td></tr>
{% endfor %}
</table>

O template exibirá uma tabela com linhas alternadas com formatação da classe1 e classe2, até o fim do laço.
Variáveis podem ser usadas:

{% for obj in alguma_lista %}
    <tr class="{% cycle var_linha1 var_linha2 %}"> ... </tr>
{% endfor %}

# variáveis são escapadas. Isso pode ser alterado com
{% for obj in alguma_lista %}
    <tr class="{% autoescape off %}{% cycle rowvalue1 rowvalue2 %}{% endautoescape %}"> ... </tr>
{% endfor %}

# podemos usar strings e variáveis juntas
{% for obj in alguma_lista %}
    <tr class="{% cycle 'linha1' var_linha2 'linha3' %}"> ... </tr>
{% endfor %}

Podemos atribuir uma aliás ao ciclo e utilizá-lo depois, com seu valor atual. Para progredir no ciclo reusamos {% cycle %}:

# o template
⎀ <tr>
⎀     <td class="{% cycle 'linha1' 'linha2' as linhas %}">...</td>
⎀     <td class="{{ linhas }}">...</td>
⎀ </tr>
⎀ <tr>
⎀     <td class="{% cycle linhas %}">...</td>
⎀     <td class="{{ linhas }}">...</td>
⎀ </tr>

# será renderizado como
↳ <tr>
↳     <td class="linha1">...</td>
↳     <td class="linha1">...</td>
↳ </tr>
↳ <tr>
↳     <td class="linha2">...</td>
↳     <td class="linha2">...</td>
↳ </tr>

Podemos usar a tag resetcycle para zerar o ciclo. Por ex., se temos uma lista de objetos pessoa, com propriedades pessoa.nome (uma string) e pessoa.filhos (uma lista de strings):

{% for pessoa in lista_de_pessoas %}
    <h1>{{ pessoa.nome }}</h1>
    {% for filho in pessoa.filhos %}
        <p class="{% cycle 'par' 'impar' %}">{{ filho.nome }}</p>
    {% endfor %}
    {% resetcycle %}
{% endfor %}

Nesse template toda lista de filhos, para cada pessoa, começa com formatação de classe “par”.

firstof recebe diversas variáveis como argumento e exibe o primeiro argumento que não é False. Lembrando, são avaliadas como True as variáveis que existem, não são vazias ou Null, não são o booleano False e não são o 0 numérico. firstof não exibe coisa alguma se todas as variáveis incluídas são False.

# o template
{% firstof var1 var2 var3 %}

# é o mesmo que
{% if var1 %} 
  {{ var1 }}
{% elif var2 %}
  {{ var2 }}
{% elif var3 %}
  {{ var3 }}
{% endif %}

Um valor default pode ser exibido se nenhuma das variáveis é True

{% firstof var1 var2 var3 "valor default" %}

Os valores exibidos são “escapados”. Esse comportamento pode ser revertido com:

{% autoescape off %}
    {% firstof var1 var2 var3 "<b>Texto em negrito</b>" %}
{% endautoescape %}

# para escapar apenas algumas variáveis usamos o filtro escape sobre essas variáveis
{% firstof var1 var2|safe var3 "<b>Negrito</b>|safe" %}

# também podemos armazenar a variável em um aliás para uso posterior:
{% firstof var1 var2 var3 as valor_alias %}

regroup realiza um reagrupamento de uma lista de objetos baseado em um atributo comum. Por ex., suponha que temos um dicionário que descreve alunos de uma escola, listando seus nomes, idades e series:

alunos = [
    {'nome':'Marcos', 'idade':'8','serie':'1'}
    {'nome':'Ana', 'idade':'7','serie':'1'}
    {'nome':'Marta', 'idade':'10','serie':'1'}
    {'nome':'Pedro', 'idade':'9','serie':'2'}
]

Para exibir uma lista organizada hieraquicamente pela série, usamos {% regroup %}

{% regroup alunos by serie as aluno_serie %}
<ul>
{% for serie in aluno_serie %}
    <li>Série: {{ serie.grouper }}
    <ul>
        {% for aluno in aluno_serie %}
          <li>Nome: {{ aluno.nome }}, Idade: {{ aluno.idade }}</li>
        {% endfor %}
    </ul>
    </li>
{% endfor %}

O resultado desse template é o seguinte:

Serie: 1
- Nome: Marcos, Idade: 8
- Nome: Ana, Idade: 7
- Nome: Marta, Idade: 10
Serie: 2
- Nome: Pedro, Idade: 9

Nesse exemplo alunos a a coleção que queremos ordenar, serie o atributo usado na ordenação, e aluno_serie um alias para a lista resultante. O objeto gerado, nomeado por aluno_serie, é do tipo namedtuple com 2 campos:

grouper – o item usado no agrupamento, no caso as strings “1” e “2”.
list – uma lista dos ítems do grupo, no caso alunos com atributos aluno.nome e aluno.idade.

Considerando que aluno_serie é uma namedtuple o mesmo código poderia ser escrito dessa forma:

{% regroup alunos by serie as aluno_serie %}
<ul>
{% for serie, alunos in aluno_serie %}
    <li>Série:  {{ serie }}
    <ul>
        {% for aluno in alunos %}
          <li>Nome: {{ aluno.nome }}, Idade: {{ aluno.idade }}</li>
        {% endfor %}
    </ul>
    </li>
{% endfor %}
</ul>

É importante notar que {% regroup %} não faz um reordenamento dos listas, o que deve ser feito previamente, em geral dentro da view que retoena esses valores. Alternativamente, se os dados a serem agrupados estão em uma lista de dicionários, como no exemplo, podemos fazer um ordenamento dentro do template usando o filtro dictsort.

{% regroup alunos|dictsort:"serie" by serie as aluno_serie %}
# alunos|dictsort:"serie" retorna a lista ordenada no campo "serie"

Qualquer outra propriedade dos ojetos ordenados pode ser usada por regroup, incluindo propriedades de objetos, chaves e itens de dicionários.

with é usada para armazenar uma variável sob um nome simples. Essa variável pode envolver, por ex., uma operação complexa em uma query em um banco de dados:

{% with total=escola.professores.count %}
    Essa escola tem {{ total }} professor.
{% endwith %}

# alternativamente
{% with escola.professores.count as total %} ... {% endwith %}

# mais de uma variável pode ser definida
{% with alpha=1 beta=2 %}
    ...
{% endwith %}

A variável tem como escopo a intervalo entre tags {% with %} e {% endwith %}.

autoescape controla o comportamento de escape de marcações html dentro do bloco. Por default tags html são exibidos sem renderização, por motivo de segurança. Com autoescape on as tags se tornam funcionais, como em uma página usual de html. Recebe apenas os parâmetros on ou off.

# por ex.
» variavel = "<p>Texto de <b>teste</b>!</p>"
⎀ {% autoescape off %}
⎀    {{ variavel }}
⎀ {% endautoescape %}
# renderiza como
↳ <p>Texto de <b>teste</b>!</p>

# por outro lado, se autoescape on
⎀ {% autoescape on %}
⎀    {{ variavel }}
⎀ {% endautoescape %}
# o texto é renderizado dentro de um parágrafo html

Isso é equivalente a usar o filtro var|safe em todas as variáveis do bloco. Variáveis marcadas com var|safe são renderizadas mesmo que estejam dentro de bloco {% autoescape off %}.

lorem é a tag usada para exibir texto em latim, geralmente usado para testes. Seu uso é

{% lorem [count] [method] [random] %}

onde todos os argumentos são opcionais.

Argumento	Descrição
count	número ou variável com o número de parágrafos ou palavras a serem gerados. (default é 1).
method	pode ser `w`, palavras, `p` parágrafos ou `b` para texto puro (default é b).
random	“random” gera texto aleatório e não (“Lorem ipsum dolor sit amet…”).

{% lorem %}
# gera parágrafo "lorem ipsum".
{% lorem 3 p %}
# gera parágrafo "lorem ipsum" e 2 parágrafos aleatórios entre tags <p>.
{% lorem 2 w random %}
# gera 2 palavras latinas aleatórios.

now exibe data/hora corrente como string, em formato especificado. Veja sobre filtros para maiores descrição da formatação.

⎀ {% now "D M Y H T " %}
# dia, mes, ano, hora

⎀ It is the {% now "jS \o\f F" %}
↳ It is the 4th of February

⎀ Hoje é {% now "D/M/Y" %}
↳ Hoje é 04/06/2022

⎀ {% now "SHORT_DATETIME_FORMAT" %}

O último exemplo usa formatos predefinidos, como DATE_FORMAT, DATETIME_FORMAT, SHORT_DATE_FORMAT ou SHORT_DATETIME_FORMAT que são renderizados de acordo com as variáveis de ambiente.

A sintaxe {% now “Y” as ano_atual %} armazena uma string de representação da data na variável ano_atual.

{% now "Y" as ano_atual %}
Copyright {{ ano_atual }}

verbatim é uma tag usada para interromper a renderização dos templates e apresentá-los literalmente.

⎀ {% verbatim %}
⎀     {{if certo}} Está certo! {{ endif }}
⎀ {% endverbatim %}

# será exibido
↳ {{if certo}} Está certo! {{ endif }}

Essa tag pode ser usada para evitar conflito com código javascript inserido em templates.

spaceless é a tag usada para remover espaços am branco, controles de tab e newline inseridos entre tags.

⎀ {% spaceless %}
⎀     <p>
⎀         <a href="http://meu_link.com/">Meu site</a>
⎀     </p>
⎀  {% endspaceless %}

# retorna
↳ <p><a href="http://meu_link.com/">Meu site</a></p>

# Espaços entre tags e texto não são alterados
{% spaceless %}
    <strong>
        Olá mundo!
    </strong>
{% endspaceless %}
# não sofre alterações

Essa tag tem uso limitado pois a renderização usual de html pelos navegadores ignoram esses espaços, tabs e newlines.

templatetag é usado para exibir os caracteres de tags em templates, similar a um escape desses caracteres.

Argumento	exibe	Argumento	exibe
openblock	{%	openbrace	{
closeblock	%}	closebrace	}
openvariable	{{	opencomment	{#
closevariable	}}	closecomment	#}

⎀ {% templatetag openblock %}
# retorna
↳ {%

widthratio é tag usada para gráficos de barras. Ela calcula a razão entre um valor variável dado e um valor máximo e aplica o resultado à uma constante. Por exemplo,

<img src="barra.png" height="10" width="{% widthratio valor_variavel valor_maximo largura_maxima %}">
# esse valor pode ser armazenado para uso posterior
{% widthratio valor_variavel valor_maximo largura_maxima as largura_calculada %}

Se valor_variavel=175, valor_maximo=200 e largura_maxima=100 a imagem acima barra.png terá a largura de 88 pixels pois 175/200 = .875; .875 * 100 = 87.5 arredondada para 88. Ou seja largura_calculada = 88.

Internacionalização: traduzindo aplicativos

Existem tags e filtros voltados para a internacionalização de aplicativos, facilitando a tradução de textos especificados. Mais detalhes em Django Docs: Internacionalização e
Tradução.

i18n é uma biblioteca que permite especificar qual texto dentro de templates devem ser traduzidos. Para isso deve-se ajustar a variável USE_I18N=True e carregar o código necessário com a tag {% load i18n %}.

l10n é uma biblioteca que permite localizar valores dentro de templates. Ela deve ser carregada com a tag {% load l10n %}.

tz é biblioteca para estabelecer conversões entre time zones. Deve ser carregada com a tag {% load tz %}. Também se pode ajustar a variável USE_TZ=True para a conversão ser automática para a time zone local.

Outras tags importantes

static é uma tag para estabelecer links para arquivos estáticos gravados no diretório estabelecida na variável STATIC_ROOT, dentro de settings.py. Se django.contrib.staticfiles é um dos aplicativos instalados (ou seja, se é um dos ítem da lista INSTALLED_APPS) essa tag indicará o caminho dos arquivos estáticos usando o método url(), … especificado em STATICFILES_STORAGE.

{% load static %}
<img src="{% static 'imagens/ola.jpg' %}">

{% load static %}
<link rel="stylesheet" href="{% static arquivo_css %}" type="text/css" media="screen">

{% load static %}
{% static "imagens/ola.jpg" as ola %}
<img src="{{ ola }}">

get_static_prefix pode ser usada juntamente com static para garantir maior controle sobre o local onde STATIC_URL é inserida no template:

{% load static %}
<img src="{% get_static_prefix %}imagens/ola.jpg">

# se o valor será usado várias vezes ele pode receber uma alias
{% load static %}
{% get_static_prefix as STATIC_PREFIX %}

<img src="{{ STATIC_PREFIX }}imagens/logo.jpg">
<img src="{{ STATIC_PREFIX }}imagens/ola.jpg">

get_media_prefix é similar à get_static_prefix mas insere no template o valor em MEDIA_URL:

{% load static %}
<body data-media-url="{% get_media_prefix %}">

Arquivos estáticos

Arquivos adicionais, e não apenas texto html, fazem parte de qualquer website. Esses arquivos incluem arquivos de imagens ou vídeos, de formatação css e javascript e o django os denomina arquivos estáticos (static files). Para gerenciar esses arquivos o django inclui o módulo django.contrib.staticfiles.

Para servir arquivos estáticos precisamos fazer:

incluir o módulo django.contrib.staticfiles na lista INSTALLED_APPS, em settings.py,
ajustar a variável STATIC_URL', também em settings.py,
armazenar arquivos estáticos em diretório static, dentro do diretório raiz do projeto,
nos templates, use a tag static para construir a URL para uma posição relativa, configurada em STATICFILES_STORAGE.

Por exemplo:

# em settings.py
INSTALLED_APPS = [
           ...,
           'django.contrib.staticfiles',
           ...
]
...
STATIC_URL = 'static/'

# armazena
meu_app/static/imagens/meu_logo.jpg.

# no template
{% load static %}
<img src="{% static 'imagens/meu_logo.jpg' %}">

É possível também incluir no projeto arquivos estáticos usados mais de um aplicativo. Além dos diretórios applicativo/static podemos ter uma lista de diretórios alternativos na lista STATICFILES_DIRS em settings.py. O django vai procurar em todos eles esses arquivos estáticos.

Por exemplo,

from pathlib import Path
import os

BASE_DIR = Path(__file__).resolve().parent.parent
    
STATICFILES_DIRS = [
       os.path.join(BASE_DIR, 'static'),
       os.path.join(BASE_DIR, 'base_static'),
       ...,
]

Nesse caso BASE_DIR é o diretório um dois níveis acima daquela onde reside o arquivo settings.py. À partir deles contruimos BASE_DIR/static e BASE_DIR/base_static.

Advertência: durante a fase de desenvolvimento, com a variável DEBUG=True em settings.py e você está usando django.contrib.staticfiles, os arquivos estáticos são entregues pelo servidor quando se roda runserver. Esse é, no entanto, um método pouco eficiente e inseguro, não apropriado para a produção.

Uma consideração especial deve ser dada à fase de implantação em produção. Basicamente é necessário rodar o comando collectstatic para que os arquivos estáticos sejam coletados no diretório apropriado STATIC_ROOT. Em seguida esse diretório deve ser movido para local que depende de qual servidor é usado.

Filtros do Django

Continua a leitura em Filtros em Templates no Django.

Bibliografia

Livros

Newman, Scott: Django 1.0 Template Development, 2008 Packt, 2008.

Sites

Django Project: Django, web framework for perfectionists with deadlines,
Django Project: Templates,
Django Project: Builtins Templates and Tags,

todos acessados em julho de 2022.

Outros artigos nesse site:

Django, incrementando o Projeto

Posted on 01/07/2022 by Guilherme

Sofisticando o modelo com chaves externas e datas

Vamos aprimorar o aplicativo classificando nossas notas por categorias, permitindo diversos cadernos separados. Para efeito didático usaremos apenas um nível de categoria. Idealmente essas poderiam ser subdivididas em subcategorias de vários níveis.

Para isso criaremos modelos para as seguintes tabelas:

Caderno
id	pk, automatico
caderno	texto

Categoria
id	pk, automatico
categoria	texto

Notas
id	pk, automatico
caderno	fk –> caderno
categoria	fk –> categoria
titulo	texto
texto	texto
slug	texto

Vamos fazer alterações nos modelos, que serão aplicadas no banco de dados com migrations.
app_notas/notas/models.py

from django.db import models
from django.utils.text import slugify

class Caderno(models.Model):
    titulo = models.CharField(default='', max_length=150)
    def __str__(self):
        return self.titulo

class Categoria(models.Model):
    categoria = models.CharField(default='', max_length=150)
    def __str__(self):
        return self.categoria

class Nota(models.Model):
    titulo = models.CharField(default='', max_length=150, help_text="Título da Nota")
    texto = models.TextField(default='', blank=True)
    slug = models.SlugField(default='', blank=True, max_length=255)
    data_criada = models.DateField(auto_now_add=True, help_text="Data de Criação")
    data_editada = models.DateField(auto_now=True, help_text="Data de Edição")
    caderno = models.ForeignKey(Caderno, on_delete=models.CASCADE)
    categoria = models.ForeignKey(Categoria, on_delete=models.CASCADE)

    def __str__(self):
        return self.titulo

    def save(self,*args,**kwargs):
        self.slug = slugify(self.titulo)
        super().save(*args,**kwargs)

As partes alteradas estão em negrito. Inserimos duas novas tabelas: Caderno e Categoria. Cada uma delas tem seu id criado automaticamente pelo django. Na tabela Nota acrescentamos caderno e categoria. Definidos como ForeignKey eles ficam associados às novas tabelas da seguinte forma:

nota.caderno (fk)	⟼ caderno.id
nota.categoria (fk)	⟼ categoria.id

O argumento help_text permite a especificação de texto descritivo que será exibido juntos com forms, como veremos.

Os campos do tipo DateField armazenam datas. (DateTimeField armazenam datas e horas). O argumento auto_now_add=True ajusta a campo para a hora do momento de criação do valor do campo enquanto
auto_now=True ajusta a campo na hora da última edição, quando o objeto é gravado.

A chave externa (fk) define uma relação de muitos-para-um. Por ex., cada nota tem apenas uma categoria mas podem haver diversas outras notas na mesma categoria.

O argumento on_delete informa como registros ligados por chaves externas devem ser tratados em caso de apagamento do registro principal. Ele pode receber diversos valores:

CASCADE	força o apagamento de dados conectados pela chave externa.
PROTECT	impede o apagamento de dados conectados pela chave externa. Levanta exceção ProtectedError.
RESTRICT	similar à PROTECT, mas levanta erro RestrictedError.
SET_NULL	mantém dados conectados pela chave externa, substituindo seu valor por NULL. O parâmetro deve ser ajustado na definição do modelo.
SET_DEFAULT	similar à SET_NULL mas substitui o valor da chave por seu valor default. O parâmetro deve ser ajustado na definição do modelo.
SET( )	permite a construção de funções customizadas para atribuir valores ao campo fk apagado.
DO_NOTHING	nenhuma atitude é tomada e os dados ligados por fk permanecem inalterados.

Uma descrição mais detalhadas por ser vista em Zerotobyte: On delete explained.

Além disso inserimos dois campos de data, data_criada e data_editada, que são preenchidas por default com a data do momento da operação.

Com essas alterações o banco de dados atual fica inconsistente, pois existem campos que demandariam fk e que estão vazios. Como estamos em desenvolvimento e os dados que temos são apenas experimentais, uma boa tática é a de resetar o banco de dados. Essa é também uma boa oportunidade para mencionar como isso é feito.

Resetando o banco de dados

Podemos ver todas as migrations feitas até agora com o comando:

$ python manage.py showmigrations

Para remover as alterações no BD e as migrations do django, removemos o conteudo do BD. No nosso caso, como estamos usando o SQLite, basta apagar o arquivo db.sqlite3 que fica na pasta do projeto app_notas/app_notas. Em seguido removemos todo o conteúdo da pasta migrations, na pasta de cada aplicativo (app_notas/notas no caso), exceto o arquivo __init__.py.

Podemos agora reconstruir o BD e refazer a conta de superuser:

$ python manage.py makemigrations
$ python manage.py migrate
$ python manage.py createsuperuser

Observações: Se você está usando outro BD, como PostGRE ou MySQL você deve apagar os bancos usando a sintaxe própria de seu gerenciador. Outras informações podem ser encontradas em Techiediaries: Resetting Django Migrations, inclusive sobre como zerar as migrations em produção, manualmente ou usando um app para isso.

Usando o app ampliado

Depois de termos ampliado as definições de nossos modelos, devemos incluir nos novos modelos em admin.py para que eles possam ser gerenciados pelo admin.
app-notas/notas/admin.py

from django.contrib import admin
from notas.models import Caderno, Categoria, Nota

admin.site.register(Caderno)
admin.site.register(Categoria)
admin.site.register(Nota)

Agora é possível entrar dados visitando o site de administração do django. Notamos que, se definirmos antes categorias e cadernos o admin apresenta uma caixa dropdown contendo as escolhas possíveis desses dados sempre que inserimos ou editamos notas.

Para finalizar essa etapa vamos exibir os novos campos na página inicial e nos detalhes exibidos. Ajustamos o arquivo inicial de templates para receber os dados adicionais.
app_notas/templates/home.html

{% extends "base.html" %}
{% block title %}
  <h1>Aplicativo de Notas</h1>
{% endblock %}
{% block content %}
  <h1>Página inicial</h1>
  <p>Essas são as minhas notas (clique para visualizar).</p>
  <table class="dados_notas">
    <tr><th>Caderno</th><th>Categoria</th>
    <th>Nota</th><th>Criada em</th><th>Editada em</th>
    <th>clique para editar</th></tr>
    {% for nota in notas %}
      <tr>
        <td>{{ nota.caderno }}</td><td>{{ nota.categoria }}</td>
        <td>{{ nota }}</td>
        <td>{{ nota.data_criada|date:"d/m/Y" }}</td><td>
        {{ nota.data_editada|date:"d/m/Y" }}</td>
        <td><a href="/nota/{{ nota.pk }}/">{{ nota.pk }}</a></td>
      </tr>
    {%endfor%}
  </table>
{% endblock %}

E acrescentamos formatação css para essa tabela, definida sob a classe dados_notas (mostrando só os acréscimos):
app_notas/static/css/estilo.css

.dados_notas {text-align:left; border-collapse:collapse;}
.dados_notas tr, th, td {border:1px solid #aaa; padding:10px 30px;}

Visitando a página incial veremos, no navegador:

Para visualizar detalhes da nota alteramos:
app_notas/templates/detalhe.html

{% extends "base.html" %}
{% block title %}
  <h1>Aplicativo de Notas: Detalhes</h1>
{% endblock %}

{% block content %}
  {% if secao == "erro" %}
    <h1>Nenhuma nota foi encontrada!</h1>
    <h2>Nota: {{ nota }}</h2>
  {% else %}
    <h1>Detalhe de uma nota</h1>
    <p><b>Caderno:</b> {{ nota.caderno }}</p>
    <p><b>Categoria:</b> {{ nota.categoria }}</p>
    <h2>Nota: {{ nota.titulo }}</h2>
    <p>{{ nota.texto|safe }}</p>
    <p>Postada em: {{nota.data_criada|date:"d/m/Y"}}</p>
    <p>Editada em: {{nota.data_editada|date:"d/m/Y"}}</p>
    <p><small>Id: {{ nota.pk }} Slug: {{ nota.slug }}</small></p>
  {% endif %}
{% endblock %}

Visitando uma página de detalhes (por exemplo clicando no link da página home) veremos:

Django Admin

A página de administração do django funciona como um app pre-desenvolvido. Ela é voltada para administradores e não deve ser usada para a interação de usuários com o site. Mesmo assim vale considerar algumas formas possíveis de aprimorar esse uso.

A referência para todo o controle do admin está em settings.py do aplicativo notas, enquanto a url que direciona para esse aplicativo está em urls.py do projeto:

# Em notas/settings.py:
INSTALLED_APPS = ['django.contrib.admin',  ... ]

# Em app_notas/urls.py
urlpatterns = [path('admin/', admin.site.urls), ...]

Para acessar o admin precisamos criar um usuário com poderes de administrador, (superuser)

$ python manage.py createsuperuser

Já vimos que, ao listar objetos de qualquer tabela, o admin usa a representação de string, definida com o modelo. Foi o que fizemos em, por ex. em models.py:
app_notas/notas/models.py

class Nota(models.Model):
    titulo = models.CharField(default='', max_length=150)
    ...
    def __str__(self):
        return self.titulo

Para customizar o texto exibido na página do admin acrescentamos as linhas à urls.py:
apps_notas/urls.py

urlpatterns = [ ... ]
admin.site.site_header  =  "Admin: Anotações"
admin.site.index_title  =  "Controle de Tabelas:  Anotações"

Vimos também quem nossos modelos são registrados em notas/admin.py. Podemos também ajustar a forma de exibição dos campos, acrescentar um campo de pesquisa sobre campos escolhidos da tabela e inserir filtros que limitam a exibição dos dados. Para isso criamos classes que herdam de admin.ModelAdmin e as registramos junto com os modelos.
apps_notas/notas/admin.py

from django.contrib import admin
from notas.models import Caderno, Categoria, Nota
 
class CadernoAdmin(admin.ModelAdmin):
    search_fields = ('titulo',)

class CategoriaAdmin(admin.ModelAdmin):
    list_filter = ('categoria',)
    search_fields = ('categoria',)

class NotaAdmin(admin.ModelAdmin):
    list_display = ('titulo', 'caderno', 'categoria', 'data_criada', 'data_editada',)
    list_filter = ('categoria', 'data_criada',)
    search_fields = ('titulo',)

admin.site.register(Caderno, CadernoAdmin)
admin.site.register(Categoria, CategoriaAdmin)
admin.site.register(Nota, NotaAdmin)

Feito isso visitamos a página ao admin e clicamos em Notas. A seguinte página é exibida:

O registro em admin.site disponibiliza o modelo para o app admin. Caso não se queira exibir um dos modelos no admin basta omitir esse registro.

Duas outras possibilidades são as de excluir um campo na página de edição das tabelas do admin e agrupar a exibição de dados sob um campo tal como datas.
apps_notas/notas/admin.py

class NotaAdmin(admin.ModelAdmin):
    ....
    date_hierarchy = 'data_criada'
    exclude = ( 'data_criada', 'data_editada',)

O primeiro filtro cria uma seleção acima da lista, por data de edição. O segundo exclue, no formulário do admin para edição de notas, os campos data_criada e data_editada. Isso é possível uma vez que ambas as datas são inseridas automaticamente com o parâmetro auto_now=True.

Formulários html no Django

Um formulário HTML é uma página construída com marcação html usada para o preenchimento de dados no navegador e habilitada para enviar esses dados para o servidor. Ela é formada por widgets que são campos de textos, caixas de seleção, botões, botões de rádio e checkboxes.

Formulários são uma das formas mais comuns de interação entre usuários e um aplicativo Web. O django traz mecanismos para facilitar a criação de formulários e integrá-los com o aplicativo. Para experimentar com os formulários vamos criar um formulário de inserção e outro de edição de notas existente.

Inserindo uma nota: Primeiro inserir uma url para requisitar uma páginas de inserção de dados. Podemos fazer isso como (mostrando só alterações):
notas/urls.py

urlpatterns = [
    ...
    path('inserir/', views.inserir, name='inserir'),
]

Claro que temos que criar uma view para receber essa solicitação. Mas antes criaremos uma classe derivada de django.forms.ModelForm, responsável pela geração de um formulário. Gravamos o seguinte arquivo em
app_notas/notas/forms.py

from django.forms import ModelForm
from .models import Nota

class NotaForm(ModelForm):
    class Meta:
        model=Nota
        fields=['titulo', 'texto', 'caderno', 'categoria']

A classe ModelForm descreve um formulário. Ela pode acessar as informações sobre os campos do modelo e os representar em um formulário. A class Meta é usada para modificar as propriedades da classe externa onde foi definida. Dentro dela fazemos a associação com o modelo sendo exibido e os campos que se pretende editar.

Os campos data_criada, data_editada e slug são criados automaticamente e não precisam estar no formulário de inserção de notas. Para inserir todos os campos, caso isso seja o desejado, podemos usar fields = ‘__all__’. A ordem dos campos declarados será respeitada na exibição do formulário.

Inserimos uma view para processar a requisição:
app_notas/notas/views.py

from django.shortcuts import render, get_object_or_404, redirect
from .models import Nota
from .forms import NotaForm
...

def inserir(request):
    form=NotaForm()
    if request.method=='POST':
        form=NotaForm(request.POST)
        if form.is_valid():
            nota=form.save(commit=False)
            nota.save()
            return detalhePK(request, nota.pk)

    contexto = {'section':'inserir','form': form,}    
    return render(request, 'inserir.html', contexto)

O parâmetro request recebe os dados inseridos na requisição. Se um formulário foi preenchido e enviado o método de requisição foi POST. Um objeto form é carregado com esses dados. Se os dados estão inseridos corretamente eles são gravados. Se não for válida (se algum campo necessário não foi preenchido, ou um email está em formato incorreto, por ex.) um formulário vazio é reexibido.

O uso de nota=form.save(commit=False) armazena na variável um objeto ainda não gravado. Neste intervalo uma validação ou manipulação manual de dados pode ser feita. Se considerado correto o objeto é gravado com nota.save(). Se a nota for gravada corretamente a função view detalhePK() é chamada para exibir a nota recém gravada. O template inserir.html deve conter os comandos para a exibição do formulário.
app_notas/templates/inserir.html

{% extends "base.html" %}
{% block title %}<h1>Inserindo uma nota</h1>{% endblock %}
{% block content %}
<form action="{% url 'inserir' %}" method="post">
    {% csrf_token %}
    {{ form.as_p }}
    <button class="button" type="submit">Inserir</button>
</form>
{% endblock %}

A ação disparada pelo clique no botão de inserir está descrita na tag action = {% url 'inserir' %}, um acesso à view descrita acima, com método POST. O token {% csrf_token %} acrescenta uma proteção contra ataques do tipo CSRF (Cross Site Request Forgeries), e {{ form.as_p }} faz a listagem HTML dos campos definidos em notas/forms.py, dispostos em parágrafos sucessivos. Também podemos dispor as campos do formulário como {{ form.as_ul }}, dispostos em uma lista não ordenada, ou {{ form.as_table }}, com os campos dentro de uma tabela.

Para acessar a página de inserção sem necessidade de escrever a URL completa, vamos colocar no cabeçalho de base.html um link com esse endereço. Alterando o código logo abaixo de <body> em (exibindo só trecho modificado):
app_notas/templates/base.htlm

<ul class="menu">
    <li><a class="{% if secao == 'home' %}
    active {% endif %}" href="{% url 'index' %}">Início</a></li>
    <li><a href="{% url 'inserir' %}">Inserir Nova Nota</a></li>
    <li><a href="/admin/">Admin</a></li>
  </ul>

Ao clicar em Inserir Nova Nota o seguinte formulário é exibido. Quando preenchido, se clicamos no botão Inserir a nota é gravada e exibida. Resta-nos agora escrever o código para editar ou apagar uma nota já inserida.

Editando uma nota: de forma similar, vamos alterar o projeto para que possamos editar notas existente e apagá-las. A url será recebida por:
app_notas/notas/urls.py

urlpatterns = [
    ...
    path('inserir/', views.inserir, name='inserir'),
    ...
    path('editar/<int:pk>/', views.editar, name='editar'),
]

Podemos usar o mesmo objeto NotaForm já criado, desde que aceitemos que os campos data_criada, data_editada e slug continuem sendo geridos automaticamente.

Uma nova view será incluída para a edição:
notas/views.py

...

def editar(request, pk):
    nota = get_object_or_404(Nota, pk=pk)
    form=NotaForm()
    if request.method=='POST':
        form=NotaForm(request.POST, instance=nota)
        if form.is_valid():
            nota=form.save(commit=False)
            nota.save()
            return detalhePK(request, nota.pk)

    contexto = {'section':'editar','form': form,}
    return render(request, 'editar.html', contexto)

Como antes, um formulário preenchido corretamente é gravado e a nota editada é exibida. O template editar.html deve conter os comandos para a exibição do formulário.

app_notas/templates/editar.html

{% extends "base.html" %}
{% block title %}<h1>Editando uma nota</h1>{% endblock %}
{% block content %}
<form action="{% url 'editar' %}" method="post">
    {% csrf_token %}
    {{ form.as_p }}
    <button class="button" type="submit">Gravar</button>
</form>
{% endblock %}

Para dar acesso à página de edição, tendo escolhido uma nota, inserimos uma coluna na página que lista todas as notas em home.html (exibindo só a tabela que contém a lista de notas):
app_notas/templates/home.html

<table class="dados_notas">
    <tr><th>Caderno</th><th>Categoria</th><th>Nota</th>
    <th>Criada em</th><th>Editada em</th><th>visualizar</th>
    <th>editar</th></tr>
    {% for nota in notas %}
      <tr>
        <td>{{ nota.caderno }}</td>
        <td>{{ nota.categoria }}</td>
        <td>{{ nota }}</td>
        <td>{{ nota.data_criada|date:"d/m/Y" }}</td>
        <td>{{ nota.data_editada|date:"d/m/Y" }}</td>
        <td><a href="/nota/{{ nota.pk }}/">Índice = <b>{{ nota.pk }}</b></a></td>
        <td><a href="/editar/{{ nota.pk }}/">Editar: <b>{{ nota.pk }}</b></a></td>
      </tr>
    {%endfor%}
  </table>

Ao clicar em Editar um formulário idêntico ao formulário de edição aparece com os campos já preenchidos, permitindo sua edição. Novamente, ao serem gravados a tela de exibição de notas é mostrada.

Em app_notas/notas/views.py, na função editar(), uma nota específica é carregada e usada para instanciar um objeto NotaForm. Uma NotaForm(instance=nota) é exibida para edição no primeiro carregamento do formulário. Se o botão de Gravar foi clicado a view é acionada com request.method=='POST'. Isso faz com que os dados do formulário sejam gravados, se o formulário é válido.

Apagar uma nota já inserida: Para apagar uma nota já inserida, em urls.py inserimos
app_notas/notas/urls.py

urlpatterns = [
    ...
    path('apagar//', views.apagar, name='apagar'),
]

Em forms.py inserimos a definição de um formulário sem a exibição de campos:
notas/forms.py

class ApagarNotaForm(ModelForm):
    class Meta:
        model=Nota
        fields=[]

O próximo passo é acrescentar uma view para o apagamento em views.py
notas/views.py

...
from .forms import NotaForm, ApagarNotaForm
...
def apagar(request, pk=None):
    nota=get_object_or_404(Nota, pk=pk)
    if request.method=="POST":
        form=ApagarNotaForm(request.POST, instance=nota)
        if form.is_valid():
            nota.delete()
            return redirect('home')
    else:
        form=ApagarNotaForm(instance=nota)
        contexto={'section':'apagar', 'form': form, 'nota': nota,}
        return render(request,'blog/delete.html', contexto)

Um template para confirmar a escolha do usuário em apagar uma nota pode ter o seguinte conteúdo:
app_notas/templates/apagar.html

{% extends "base.html" %}
{% block title %}<h1>Apagando uma Nota</h1>{% endblock %}
{% block content %}
<form action="{% url 'apagar' nota.pk%}" method="post">
    {% csrf_token %}
    {{ form }}
    <h2>Título da Nota: {{ nota.titulo }}</h2>
    <p style="margin-left:10em;">{{ nota.texto | safe }}</p>
    <p>Tem certeza de que deseja apagar essa nota:? <button class="button" type="submit">Apagar</button>
    Cancelar</p>
</form>
{%endblock%}

Agora, na lista geral de notas, ao clicar Apagar somos levados à página com a opção de apagamento, que lista título e conteúdo da nota. Confirmando o apagamento a view.apagar é acionada com method=="POST". Clicando Cancelar a página inicial, com a lista de notas é exibida.

É claro que muitas otimizações podem ser feitas nesse código. Por exemplo, poderíamos ter apenas uma view para inserir | editar | apagar, passando parâmetros que executem cada uma das opções. Também podemos fazer muitos aperfeiçoamentos na interface visível para o usuário, melhorando os templates e usando CSS. Para ilustrar alguns possíveis aprimoramentos vamos melhorar a aparência da página inicial, que contém uma lista de notas, com as várias possibilidades de edição.

Primeiro alteramos views.py (mostrando só alterações):
app_notas/notas/views.py

from . import funcoes

def index(request):
    #notas = Nota.objects.all() (removemos essa linha)
    notas = Nota.objects.order_by('caderno','categoria', 'data_editada')
    notas = funcoes.transforma_notas(notas)
    contexto = {'secao':'home', 'mensagem':'Item de Menu', 'notas': notas,}
    return render(request, 'home.html', contexto )

Fazendo notas = Nota.objects.order_by('caderno','categoria', 'data_editada') o objeto notas será uma coleção de notas, agora ordenadas por ‘caderno’,’categoria’, ‘data_editada’, nessa ordem de prioridade.

Suponha agora que queremos montar uma lista onde nomes de cadernos e categorias repetidas não apareçam, na lista. Isto é: em cada linha cadernos e categorias, agora ordenados nessa ordem, não são exibidos repetidamente. Para isso acrescentamos um arquivo funcoes.py:
app_notas/notas/funcoes.py

def transforma_notas(notas):
    cat = ""
    cad = ""
    for nota in notas:
        if cat == nota.categoria.categoria:
            nota.categoria.categoria = ""
        else:
            cat = nota.categoria.categoria

        if cad == nota.caderno.titulo:
            nota.caderno.titulo = ""
        else:
            cad = nota.caderno.titulo
    return notas

Essa função transforma em strings vazias os cadernos e categorias repetidos, de forma a que a lista só contenha essa informação quando ela é alterada. Por fim alteramos home.html:
templates/home.html

{% extends "base.html" %}
{% block title %}<h1 style="margin-top:80px;" >Aplicativo de Notas</h1>{% endblock %}

{% block content %}
<table class="dados_notas">
  <tr><th>Caderno</th><th>Categoria</th><th>Nota</th><th>Criada</th><th>Editada</th><th>Ver</th><th>Editar</th><th>Apagar</th></tr>
  <tr><td colspan="8" style="border-bottom: 1px solid #999;"></td></tr>
  <tr><td colspan="8"></td></tr>
  {% for nota in notas %}
  <tr>
    <td style="font-size:2em; font-weight: bold;">{{ nota.caderno }}</td>
    <td style="font-size:1.5em; font-weight: bold;">{{ nota.categoria }}</td>
    <td>{{ nota.titulo }}</td>
    <td>{{ nota.data_criada|date:"d/m/Y" }}</td>
    <td>{{ nota.data_editada|date:"d/m/Y" }}</td>
    <td><a href="/nota/{{ nota.pk }}/">📖</a></td>
    <td><a href="/editar/{{ nota.pk }}/">📝</a></td>
    <td><a href="/apagar/{{ nota.pk }}/">🚮</a></td>
  </tr>
  {%endfor%}
{% endblock %}

Os caracteres 📖, 📝 e 🚮 podem ser copiados e colados diretamente nos templates (ou qualquer outra página html). Alteramos ainda o arquivo estilo.css (mostrando só as alterações):
app_notas/static/estilo.css

.menu > li > a {
    background-color: rgb(21, 59, 141);
    border: 1px solid rgb(6, 6, 39);
    color: #fff;
    padding: 10px 20px;
    text-align: center;
    font-size: 16px;
    margin: 4px 2px;
    cursor: pointer;
}
.menu > li > a.ativa { font-weight:bold; }

Acessando nossa homepage temos agora:

Na imagem acima supomos que temos notas inseridas com esses cadernos, categorias, e títulos.

Muitos outras aperfeiçoamentos podem ser pensados para esse aplicativo de notas. Podemos, por exemplo, fazer as categorias serem dependentes dos cadernos, abrir um conjunto novo de notas para cada usuário, permitir a entrada de sub-categorias em vários níveis, criar um conjunto de tags para aplicar a cada nota, criando uma estrutura de relacionamentos entre tags de modo que o usuário possa navegar entre notas associadas a uma mesma ideia ou conceito.

Importante lembrar que, como estamos dentro de uma estrutura de programação do python, qualquer módulo importado para finalidades diversas pode ser facilmente incorporados no site ou aplicativo. Isso inclue pacotes de análise de texto, tratamento e exibição de dados, recursos de geo-referência, etc.

Artigos Django

1- Django, Websites com Python
2- Django, um Projeto
3- Django, incrementando o Projeto (esse artigo)

Bibliografia

Consulte a bibliografia no artigo inicial.

SQLAlchemy: UPDATE e DELETE

UPDATE e DELETE

UPDATE

Outras possibilidades de uso de UPDATE

Função delete()

Bibliografia

SQLAlchemy: INSERT e SELECT

Inserindo e selecionando dados

INSERT

SELECT

SELECT com ORM

Cláusula WHERE

Bibliografia

SQLAlchemy: Agrupamentos e Subqueries

Agrupamentos e ordenações

Subqueries e CTEs

Subqueries e CTEs com ORM

Subqueries escalares e correlacionadas

Uniões entre tabelas: UNION

Uniões com ORM

A subquery EXISTS

Funções SQL

Funções de janela SQL

Coerção de tipos de dados

Bibliografia

SQLAlchemy ORM

SQLAlchemy ORM

Python: SQLAlchemy

SQL e SQLAlchemy

SQLAlchemy Core e ORM

Instalando o SQLAlchemy

A engine do SQLAlchemy

Conexão

Passando parâmetros

Metadata, Table e Column

Inserção de dados

Reflexão de tabelas

Tabelas e tipos de dados

Bibiografia

Projeto SQLite

SQLite

SQLite no VSCode

Comandos SQLite

Projeto: vendas

Consultas de definição das tabelas

Consultas de inserção e leitura de dados

sqlite3 e Python

sqlite3 e pandas

Bibliografia

Onde o Python está instalado

Módulos em diferentes versões

Usando o pip

Módulos em ambientes virtuais

Bibliografia

Django, Templates e Filtros

Filtros do Django

Bibliografia

Templates do Django

Sintaxe

Variáveis

Tags e Filtros

Tags do Django

Internacionalização: traduzindo aplicativos

Outras tags importantes

Arquivos estáticos

Filtros do Django

Bibliografia

Sofisticando o modelo com chaves externas e datas

Resetando o banco de dados

Usando o app ampliado

Django Admin

Formulários html no Django

Artigos Django

Bibliografia