Página inicial
Programação
Física e Matemática
Contos de Ficção

SQL

SQLAlchemy: UPDATE e DELETE

Posted on 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 by Guilherme Silva

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 by Guilherme Silva


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 by Guilherme Silva


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 by Guilherme Silva

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

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 by Guilherme Silva


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

Sobre o SQLite3, no Python

Linguagem de Consulta SQL

Posted on by Guilherme


Definições

Um banco de dados é uma coleção de dados armazenada e acessada eletronicamente. Tipicamente consiste em várias tabelas e das relações predefinidas entre elas. Cada tabela contém colunas ou campos, cada uma delas com seu nome exclusivo e tipos de dados definido. Elas podem ter atributos que definem a funcionalidade da coluna (se é uma chave primária, se há um valor default, etc.). As linhas da tabela são chamadas de registros e contêm os dados armazenados.

Banco de dados relacional é um banco de dados digital baseado no modelo relacional proposto por E. F. Codd em 1970. Sistema de software usados para construir e manter bancos de dados relacionais são chamados Sistema de Gerenciamento de Banco de Dados Relacionais (RDBMS).

SQL, (Structured Query Language) ou Linguagem de Consultas Estruturada é uma linguagem de programação usada para manipular de bancos de dados relacionais. Usando SQL é possível construir o BD, estabelecer relações entre dados nas tabelas, fazer consultas, inserir, apagar ou editar dados.

Para que serve a SQL?

Consultas SQL podem ser feitas dentro de diversos aplicativos tais como em suites MS Office, em particular no MS Access, e seus equivalentes open source. Elas também são frequentes em aplicativos que armazenam e manipulam informações, de Business Inteligence e análises de dados tais como Qlikview e MS PowerBI. A maior parte dos sistemas de gerenciamento de conteúdo usados em sites na Internet, como WordPress e Joomla usam bancos de dados controlados por SQL.

A maioria das linguagens de programação fornecem interfaces com as variantes de bancos de dados SQL. Além disso podemos instalar um Sistema Gerenciador de Banco de Dados (DBMS) em seu computador, seja ele um servidor ou sua desktop pessoal, disponíveis em todas as plataformas. Os DBMS mais populares são: SQLite, MySQL, PostgreSQL, LibreOffice Base (todos eles open source) e Microsoft Access, SQL Server, FileMaker, Oracle (proprietários).

Comandos e instruções do SQL

Vamos considerar o exemplo de uma escola que mantém um banco de dados com informações sobre alunos, professores, funcionários, disciplinas lecionadas, salas de aulas, e a descrição de relacionamentos entre eles. Segue um exemplo simples de uma tabela contendo o dados (simplificados e fictícios) de alunos. A primeira linha, em negrito, contém os nomes das colunas da tabela (os campos):

Tabela (1): Alunos
Matricula Nome Sobrenome email Nascimento Fone
734236 João Santos joao@yahoo.com 02-04-1998 61 123455667
789234 George Pereira george@gmail.com 04-04-2000 41 345678987
654987 Paula Torres ptorres@globo.com 25-01-2004 31 987854543
765098 Marcos Melo mamelo@gmail.com 25-10-2004 31 987843231

Nessa tabela Matricula é um campo numérico que, sendo composto de valores únicos, pode ser usado como um id ou identificador. Nome, Sobrenome, email e Fone são campos de strings, Nascimento é um campo de datas.

SELECT

Comandos SQL não são sensíveis à maiúsculas e minúsculas mas é uma convenção escrevê-los em maiúsculas, o que ajuda a diferenciá-los quando embutidos dentro de códigos de outra linguagem.

A instrução SELECT é usada para recuperar (ler e retornar) dados de uma tabela. Ela tem a seguinte sintaxe geral:

SELECT Coluna1, ..., Colunan FROM Tabela1
SELECT * FROM Tabela1

A primeira consulta retorna todos os registros (linhas) da Tabela1, existentes nos campos listados, Coluna1, …, Colunan, sem qualquer critério de seleção. A segunda consulta retorna todas as linhas e todos os campos da Tabela1. O asterisco * substitui a lista de todos os campos. Devemos observar que a declaração explícita de quais campos se pretende retornar torna a consulta mais eficiente (rápida). O sinal -- (dois hífens) marca o início de um comentário em SQL, uma parte da instrução que será ignorada.

Por exemplo, para retornar todos os Nomes e Sobrenomes da tabela usamos

SELECT Nome, Sobrenome FROM Alunos
-- a tabela abaixo será retornada
Tabela (2):
Nome Sobrenome
João Santos
George Pereira
Paula Torres
Marcos Melo

Cláusula WHERE

A Cláusula WHERE é uma modificação da consulta com SELECT especificando condições de retorno. Ela restringe os dados retornados para apenas aqueles satisfeitos pela cláusula. Por exemplo, lembrando que o campo matrícula é numérico:

SELECT Nome, Sobrenome, email FROM Alunos WHERE Matricula = 654987
Tabela (3):
Nome Sobrenome email
Paula Torres ptorres@globo.com 
SELECT Matricula, email FROM Alunos WHERE Nome = 'Marcos'
Tabela (4):
Matricula email
765098 mamelo@gmail.com

Os seguintes operadores de comparação podem ser usados com WHERE:

= igual, usado acima 🔘 < menor que
<> diferente 🔘 =< menor ou igual
> maior que 🔘 LIKE similar
>= maior ou igual 🔘 BETWEEN entre (define uma faixa)

Podemos então requisitar todos os registros com Matricula > 740000 ou Nascimento <> '25-01-2004':

SELECT Matricula, Nome, Sobrenome FROM Alunos WHERE Matricula > 740000
Tabela (5):
Matricula Nome Sobrenome
789234 George Pereira
765098 Marcos Melo 
SELECT Matricula, Nome, Sobrenome FROM Alunos WHERE Nascimento <> '25-01-2004'
Tabela (6):
Matricula Nome Sobrenome
734236 João Santos
789234 George Pereira

O mesmo procedimento é usado para os demais operadores.

O Operador LIKE pode ser usado junto com %, um wildcard ou coringa que representa qualquer string (um ou mais caracteres). Ele pode ser posto em qualquer lugar, quantas vezes for necessário.

SELECT * FROM Alunos WHERE Nome LIKE 'PA%'
-- Nomes que começam com 'PA'
Tabela (7):
Matricula Nome Sobrenome email Nascimento Fone
654987 Paula Torres ptorres@globo.com 25-01-2004 31 987854543

O Operador BETWEEN possui sintaxe um pouco diferente para cada RDBMS. Em geral é algo do tipo:

SELECT * FROM Alunos WHERE Nascimento BETWEEN '01-01-2002' AND '01-01-2005'
-- Nascidos no intervalo
Tabela (8):
Matricula Nome Sobrenome email Nascimento Fone
654987 Paula Torres ptorres@globo.com 25-01-2004 31 987854543
765098 Marcos Melo mamelo@gmail.com 25-01-2004 31 987843231

O coringa _ representa um único caracter e [] uma faixa de valores. Por exemplo, a consulta seguinte retorna todos os registros onde Fone começa com 3, 4, 5 ou 6 e tem qualquer segundo dígito. No caso de nossa tabela Alunos seriam todos os registros.

SELECT * FROM Alunos WHERE Fone LIKE '[3-6]_%'

SELECT DISTINCT

Se você quiser obter valores únicos de uma tabela, sem repetições, use a cláusula DISTINCT.

SELECT DISTINCT Nascimento FROM Alunos
Tabela (9):
Nascimento
02-04-1998
04-04-2000
25-01-2004

ORDER BY

A cláusula ORDER BY é usada junto com SELECT para ordenar os resultados de uma consulta. Ela tem a seguinte sintaxe geral:

SELECT campo1, ..., campon FROM Tabela1 ORDER BY (lista de campos)

Por exemplo:

-- Para retornar todos os Nomes e Sobrenomes da tabela, em ordem de Nome
SELECT Nome, Sobrenome FROM Alunos ORDER BY Nome
Tabela (10):
Nome Sobrenome
George Pereira
João Santos
Marcos Melo
Paula Torres

ORDER BY pode ser usada com os modificadores ASC ou DESC, para produzir a lista em ordem crescente ou descendente. ASC é o default e não precisa ser especificado. Para obter a mesma lista anterior em ordem descendente no sobrenome:

-- Para retornar todos os Nomes e Sobrenomes da tabela, em ordem de Nome
SELECT Nome, Sobrenome FROM Alunos ORDER BY Sobrenome DESC
Tabela (11):
Nome Sobrenome
Paula Torres
João Santos
George Pereira
Marcos Melo

TOP

A cláusula TOP é usada junto com SELECT e, geralmente com ORDER BY para selecionar apenas um número fixo de resultados retornados por uma consulta. Ela tem a seguinte sintaxe geral:

SELECT TOP número| % campo1, ..., campon FROM Tabela1 ORDER BY (lista de campos)

Por exemplo:

-- Para retornar os dois últimos Nomes da tabela, em ordem de Nome
SELECT TOP 2 Nome, Sobrenome FROM Alunos ORDER BY Nome DESC
Tabela (12):
Nome Sobrenome
Paula Torres
Marcos Melo

Para ver 10% dos primeiros registros de uma lista podemos usar:

SELECT TOP 10% * FROM Tabela ORDER BY campo

Operadores lógicos

Operadores lógicos são usados para testes compostos com mais de uma condição.

Operador Descrição
AND TRUE se ambas as condições ligadas forem TRUE,
OR TRUE se ambas ou uma delas forem TRUE,
IN se um valor está presente em uma lista
NOT nega uma expressão lógica
IS NULL se um valor é nulo (NULL)
IS NOT NULL se um valor não é nulo

Operadores AND e OR

Mais de uma condição podem ser anexadas à cláusula WHERE usando os operadores AND e OR.

SELECT * FROM Alunos WHERE Matricula > 700000 AND Fone LIKE '31%'
Tabela (13):
Matricula Nome Sobrenome email Nascimento Fone
765098 Marcos Melo mamelo@gmail.com 25-10-2004 31 987843231 
SELECT * FROM Alunos WHERE Matricula > 700000 OR Sobrenome = 'Torres'

Nessa última consulta todos os registros seriam retornados pois Paula Torres é a única aluna com matrícula inferior à 700000.

Condições mais complexas podem ser expressas usando-se parênteses.

SELECT Matricula, Nome, Sobrenome FROM Alunos
   WHERE (Nome = 'George' OR Nome = 'Paula') AND Sobrenome = 'Pereira'
Tabela (14):
Matricula Nome Sobrenome
789234 George Pereira

Operador lógico IN

O operador IN é usado junto com WHERE para selecionar um campo com valor dentro de um conjunto de valores discretos. A sintaxe é

SELECT campo1, ..., campon
   FROM Tabela
   WHERE campo1 IN (valor1, ..., valorr)

SELECT * FROM Alunos WHERE Matricula IN (654987, 765098)
Tabela (15):
Matricula Nome Sobrenome
654987 Paula Torres
765098 Marcos Melo

SELECT INTO

A instrução SELECT INTO é usada para selecionar registros e campos de uma tabela e copiar o resultado retornado em uma nova tabela.

SELECT Coluna1, ..., Colunan INTO Tabela2 FROM Tabela1	WHERE <condições>

A Tabela2 será criada com os mesmos campos e relacionamentos da tabela inicial. Alguns casos são listados a seguir:

-- Faça uma cópia backup de Alunos
SELECT * INTO AlunosBackup FROM Alunos;

-- Faça uma cópia backup de Alunos cujo nome começa com 'Pa'
SELECT * INTO AlunosBackup FROM Alunos WHERE Nome LIKE 'PA%';

-- Faça uma cópia backup de Alunos em outro banco de dados (BDCopia)
SELECT * INTO AlunosBackup IN 'BDCopia' FROM ALunos;

-- Cria tabela vazia novoAlunos com estrutura idêntica a de Alunos
SELECT * INTO novoAlunos FROM Alunos WHERE 1 = 0;

Obs.: Aqui o teste 1 = 0 foi introduzida apenas para gerar um booleano FALSE. Nem todas as versões de gerenciadores comportam dados booleanos. Em alguns podemos usar WHERE TRUE, WHERE 1 (que são equivalentes), ou WHERE FALSE, WHERE 0 ou WHERE NOT TRUE(também equivalentes).

INSERT INTO

A instrução INSERT INTO é usada para inserir novos registros em uma tabela. Ele pode ser usado em dois formatos:

INSERT INTO Tabela1 VALUES (valor1, ..., valorn)
-- ou
INSERT INTO Tabela1 (campo1, ..., campon)  VALUES (valor1, ..., valorn)

No primeiro caso \(n\), o número de valores inseridos deve ser igual ao número de campos da tabela e devem estar na ordem default. No segundo caso a ordem pode ser alterada mas campon) deve corresponder à valorn. Em ambos os casos o tipo de dado do campo deve ser respeitado. Por exemplo: as seguintes instruções aplicadas sobre a tabela Alunos:

-- Inserindo todos os campos
INSERT INTO Alunos VALUES (854254, 'João', 'Alves', 'jalves@yahoo.com', '15-03-2004', '31 885466112')
-- Inserindo alguns campos
INSERT INTO Alunos (Matricula, Nome, Sobrenome) VALUES (785294, 'Marta', 'Soares')

alteraria a tabela para

Tabela (16):
Matricula Nome Sobrenome email Nascimento Fone
734236 João Santos joao@yahoo.com 02-04-1998 61 123455667
789234 George Pereira george@gmail.com 04-04-2000 41 345678987
654987 Paula Torres ptorres@globo.com 25-01-2004 31 987854543
765098 Marcos Melo mamelo@gmail.com 25-01-2004 31 987843231
854254 João Alves jalves@yahoo.com 15-03-2004 31 885466112
785294 Marta Soares

Os valores de campos não fornecidos na última instrução ficam nulos (null, inexistência de valor) ou assumem um valor default definido na construção da estrutura da tabela.

UPDATE

A instrução UPDATE é usada para alterar registros já inseridos em uma tabela. Ela tem a sintax geral:

UPDATE Tabela1
   SET campo1 = valor1, ..., campon = valorn
   WHERE <condições>

O formato acima mostra que podemos quebrar as linhas de uma instrução SQL para torná-la mais legível. A cláusula WHERE <condições> limita quais os registros serão alterados. Sem ela todos os registros (todas as linhas da tabela) seriam alterados.

Por exemplo, podemos completar o registro relativo à aluna Marta em nossa tabela:

UPDATE Alunos
   SET Sobrenome = 'Alves', email = 'marta123@yahoo.com', Nascimento = '14-03-2001', fone = '21 956855441'
   WHERE Matricula = 785294
-- Para ver o resultado (os demais registros ficam inalterados)
SELECT * FROM Alunos WHERE Matricula = 785294

O resultado seria a tabela:

Tabela (17):
Matricula Nome Sobrenome email Nascimento Fone
785294 Marta Alves marta123@yahoo.com 14-03-2001 21 956855441

Novamente, se não tivéssemos especificado a condição Matricula = 785294 todos os registros, de todos os alunos seriam alterados.

Suponha que, por algum motivo, a escola tenha decido alterar o padrão de numeração das matrículas acrescentando o dígito 1 à esquerda de todas as matrículas. Isso seria o mesmo que somar 1000000 à todas as matrículas. Podemos conseguir isso com a seguinte operação:

UPDATE Alunos SET Matricula = Matricula + 1000000
-- Para ver o resultado (os demais campos ficam inalterados)
SELECT Matricula FROM Alunos
Tabela (18):
Matricula
1734236
1789234
1654987
1765098
1854254
1785294

DELETE

A instrução DELETE permite o apagamento de registros em uma tabela. Ela tem a sintax geral:

DELETE FROM Tabela1 WHERE <condições>

O formato acima mostra que podemos quebrar as linhas de uma instrução SQL para torná-la mais legível. A cláusula WHERE <condição> limita quais os registros serão apagados. Sem ela todos os registros (todas as linhas da tabela) seriam apagadas.

Por exemplo, podemos apagar os registros relativos aos alunos com sobrenome “Alves” de nossa nossa tabela:

DELETE FROM Alunos WHERE Sobrenome = 'Alves'

Dois alunos seriam removidos da tabela que ficaria assim:

Tabela (19):
Matricula Nome Sobrenome email Nascimento Fone
1734236 João Santos joao@yahoo.com 02-04-1998 61 123455667
1789234 George Pereira george@gmail.com 04-04-2000 41 345678987
1654987 Paula Torres ptorres@globo.com 25-01-2004 31 987854543
1765098 Marcos Melo mamelo@gmail.com 25-01-2004 31 987843231

Se não tivéssemos especificado uma condição para o apagamento todos os registros seriam apagados. A linha abaixo apagaria todos os registros da tabela Alunos:

DELETE FROM Alunos

A tabela e sua estrutura continuaria existindo.

Aliases

Aliases (nomes alternativos) são usados para simplificar uma consulta. Nossa tabela de exemplo é uma tabela pequena e simples. Na prática os bancos de dados e tabelas podem conter muitos campos. A possibilidade de renomear tabelas e campos pode ser muito útil, principalmente quando a consulta envolve mais de uma tabela e a consulta se refere às tabelas e campos mais de uma vez.

Por exemplo considerando que nossa tabela está no estado da Tabela (19) a consulta

SELECT Matricula, Nome + ', ' + Sobrenome AS NomeCompleto FROM Alunos

resulta em

Tabela (20):
Matricula NomeCompleto
1734236 João Santos
1789234 George Pereira
1654987 Paula Torres
1765098 Marcos Melo

Aqui foi feita uma concatenação das strings Nome + Sobrenome e o resultado renomeado como NomeCompleto.

Quando usamos várias tabelas de um banco de dados pode ocorrer que mais de uma delas tenha um campo com o mesmo nome. Nesse caso é obrigatória descriminar a que tabela nos referimos. A mesma consulta acima pode ser colocada na seguinte forma, com o mesmo resultado:

SELECT a.Matricula, a.Nome + ', ' + a.Sobrenome AS NomeCompleto FROM Alunos a

Aqui a tabela Alunos ganhou o alias a. a.Matricula se refere ao campo Matricula da tabela Alunos.

Agrupamentos e funções de grupos

Vamos acrescentar novas tabelas para considerar os agrupamentos. Suponha que a escola possui 4 funcionários identificados por um id único (um número de indentificação). Uma tabela armazena a relação entre id e nome (nome do funcionário). Outras tabela contém o número de horas trabalhadas por dia, para cada funcionário.

Tabela (21): Horas_trabalhadas
id dia horas
36 ’01-03-2019′ 6
41 ’01-03-2019′ 8
48 ’01-03-2019′ 2
58 ’01-03-2019′ 8
36 ’02-03-2019′ 5
41 ’02-03-2019′ 8
48 ’02-03-2019′ 1
58 ’02-03-2019′ 4
Tabela (22): Funcionarios
id Nome
36 Mariana Goulart
41 Tânia Ferreira
48 Humberto Torres
58 Francisco Pedroso

Diversas operações são permitidas sobre as linhas de uma tabela. Em particular

Instrução Efeito
GROUP BY Agrupa registros por valores do campo
Função Efeito
COUNT Conta o número de ocorrências do campo
MAX Fornece o valor máximo do campo
MIN Fornece o valor mínimo do campo
AVG Fornece a média dos valores do campo
SUM Fornece a soma dos valores do campo

Por exemplo, para calcular o número de horas trabalhadas por todos os funcionários podemos usar a consulta

SELECT SUM(horas) as soma FROM Horas_trabalhadas

que resulta em um único registro para um único campo:

Tabela (23):
soma
42

Para calcular o número de horas trabalhadas por cada funcionário fazemos a soma das horas com os campos de horas agrupados por cada funcionário.

SELECT id, SUM(horas) as soma
   FROM Horas_trabalhadas
   GROUP BY id
Tabela (24): Horas_trabalhadas
id horas
36 11
41 16
48 3
58 12

A consulta a seguir mostra o cálculo da média de horas trabalhadas por funcionário e de quantos dias cada um trabalhou.

SELECT id, AVG(horas) as media, COUNT(id) as numDias
   FROM Horas_trabalhadas
   GROUP BY id
Tabela (25): Média e número de dias trabalhados
id media numDias
36 5.5 2
41 8 2
48 1.5 2
58 6 2

Cláusula HAVING

A cláusula HAVING é usada para se estabelecer critérios sobre valores obtidos em funções agregadas. No SQL não é permitido usar WHERE para restringir resultados de uma consulta agregada. A consulta seguinte resultaria em erro:

SELECT id, SUM (horas) 
    FROM Horas_trabalhadas
    WHERE SUM (horas) > 11
    -- Isso geraria um erro
    GROUP BY id

Para esse efeito usamos HAVING, uma forma de se especificar condições sobre o resultado de uma função agregada.

SELECT id, SUM (horas) as soma
   FROM Horas_trabalhadas
   GROUP BY id
   HAVING soma > 11

Essa consulta gera a tabela (uma modificação da Tabela 24), satisfeita a condição soma > 11:

Tabela (24′): Horas_trabalhadas
id soma
41 16
58 12

Instrução JOIN

É claro que a última tabela ficaria mais fácil de interpretar se, ao invés de conter apenas ids, ela contivesse também os nomes dos funcionários. Essa informação está contida na Tabela (22): Funcionarios. A instrução JOIN serve para ler dados relacionados, gravados em mais de uma tabela. Sua forma geral é:

SELECT tabela1.campo11, tabela1.campo12, tabela2.campo21, tabela2.campo22, ...
   FROM tabela1 INNER JOIN tabela2 ON tabela1.id1 = tabela2.id2

Esse comando seleciona todos os campo11 e campo12 da tabela1, campo21, campo22, … da tabela2, relacionados pela condição tabela1.id1 = tabela2.id2. OS campos id1 e id2 podem ter ou não os mesmos nomes. Usando aliáses essa consulta pode ser deixada mais clara (e isso se torna mais importante para consultas mais longas envolvendos muitos campos e tabelas):

SELECT t1.campo11, t1.campo12, t2.campo21, t2.campo22, ...
   FROM tabela1 t1 INNER JOIN tabela2 t2 ON t1.id1 = t2.id2

Por exemplo, se quisermos uma tabela com os mesmos resultados da tabela (24) mas incluindo nomes dos funcionários fazemos

SELECT ht.id, f.Nome, SUM(horas) as soma
   FROM Horas_trabalhadas ht
   INNER JOIN Funcionarios f ON ht.id = f.id
   GROUP BY ht.id HAVING soma > 11

Com o resultado:

Tabela (26): Horas trabalhadas por funcionário
id Nome soma
41 Tânia Ferreira 16
58 Francisco Pedroso 12

Existem outros tipos de junções ou JOINs no SQL:

  • (INNER) JOIN: Retorna registros com valores correspondentes nas duas tabelas.
  • LEFT (OUTER) JOIN: Retorna registros da tabela esquerda (a primeira na query) e os correspondentes na tabela direita.
  • RIGHT (OUTER) JOIN: Retorna registros da tabela direita e os registros correspondentes da tabela esquerda.
  • FULL (OUTER) JOIN: Retorna registros quando houver correspondência em qualquer uma das tabelas.
Variantes do operador JOIN

Bibliografia

  • Faroult, Stéphane; Robson, Peter: The Art of SQL, O’Reilly Media, Sebastopol, CA, 2006.
  • Taylor,Allen: SQL For Dummies, 9th Edition John Wiley & Sons, New Jersey, 2019.

Páginas na Web: