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', '')

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

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.