Como Executar SQL Puro no SQLAlchemy

Name

Sebastian Brandt

Published on 05/06/2023

(opens in a new tab) (opens in a new tab) (opens in a new tab) (opens in a new tab) (opens in a new tab) (opens in a new tab)

Nos domínios da ciência de dados e desenvolvimento de web, o uso de bancos de dados é inevitável. Esses poderosos sistemas armazenam e gerenciam grandes blocos de dados, tornando-os inestimáveis em um mundo que é amplamente dependente de informações. O Python, uma linguagem de programação proeminente nesses domínios, alavanca diversas ferramentas para se comunicar com bancos de dados, e uma dessas ferramentas é o SQLAlchemy. Embora a camada Object-Relational Mapping (ORM) do SQLAlchemy seja uma bênção para os desenvolvedores que procuram abstrações de alto nível para suas operações de banco de dados, há ocasiões em que a execução de consultas SQL cruas é mais efetiva ou intuitiva. Este artigo oferece uma imersão profunda na execução de consultas SQL cruas no SQLAlchemy, fornecendo exemplos de código concretos para clareza e entendimento.

Compreendendo o SQLAlchemy: Uma visão geral

Para compreender o conceito de execução de consultas SQL cruas no SQLAlchemy, primeiro precisamos compreender o que é o SQLAlchemy. Como kit de ferramentas SQL, o SQLAlchemy fornece um conjunto de ferramentas para que as aplicações Python possam interagir com bancos de dados SQL. Ele oferece um conjunto completo de padrões de persistência de nível empresarial bem conhecidos, projetados para garantir eficiência e acesso de banco de dados com alta performance.

O SQLAlchemy almeja acomodar operações de banco de dados de alto e baixo nível. Seus dois principais componentes, SQLAlchemy Core e SQLAlchemy ORM, tratam dessas operações. O SQLAlchemy Core é centrado na abstração SQL, metadados de esquema, pooling de conexão, coercão de tipo e mais. Ele fornece uma interface SQL de baixo nível para Python, permitindo que os desenvolvedores interajam com o banco de dados de maneira Pythônica. Por outro lado, o SQLAlchemy ORM é uma API centrada em dados de nível elevado que fornece padrões de modelos de domínio, encapsulando bancos de dados e tabelas como classes e objetos Python.

O Poder e Potencial do SQL Puro

Com o pano de fundo do SQLAlchemy e seus componentes, vamos focar em consultas SQL cruas. SQL puro se refere a declarações e consultas SQL escritas como simples strings de texto. Como linguagem de banco de dados, o SQL (Structured Query Language) nos permite acessar e manipular bancos de dados. Consultas SQL cruas são frequentemente utilizadas quando há necessidade de executar operações complexas, onde a abstração ORM pode parecer restritiva.

Essas consultas SQL cruas são extremamente úteis quando uma operação de banco de dados específica é difícil de expressar através da camada ORM ou quando a sintaxe ORM é menos intuitiva do que a operação SQL equivalente. Além disso, desenvolvedores que se sentem mais confortáveis escrevendo consultas SQL podem encontrar no SQLAlchemy SQL puro uma excelente maneira de obter o melhor dos dois mundos - o poder do SQL com a conveniência do Python.

Como Executar SQL Puro no SQLAlchemy

No SQLAlchemy, SQL puro pode ser executado diretamente usando o método execute() oferecido pelos objetos Engine e Session.

Utilizando o Método Execute do Engine

Vamos dar uma olhada em um exemplo de execução de SQL puro usando o método engine.execute():

from sqlalchemy import create_engine
 
engine = create_engine('sqlite:///:memory:')
result = engine.execute("SELECT * FROM users WHERE age >= :age", {'age': 21})
for row in result:
    print(row)

No código acima, estamos executando uma consulta SQL crua que busca todos os usuários com 21 anos ou mais. A função create_engine() estabelece uma conexão com o banco de dados (neste caso, um banco de dados SQLite na memória). O método execute() na instância do engine, então, pega a consulta SQL como uma string e a executa contra o banco de dados.

Utilizando o Método Execute do Session

Como alternativa, SQL puro também pode ser executado usando o método session.execute():

from sqlalchemy.orm import Session
 
session = Session(bind=engine)
result = session.execute("SELECT * FROM users WHERE age >= :age", {'age': 21})
for row in result:
    print(row)

Este exemplo parece bastante semelhante ao anterior. No entanto, existe uma distinção sutil, mas crucial. O método session.execute() garante que as consultas são gerenciadas por uma transação. Na linguagem de bancos de dados, uma transação é uma sequência de operações executadas como uma única unidade lógica de trabalho. Assim, a sessão permite que várias consultas na mesma solicitação sejam confirmadas ou desfeitas juntas, evitando inconsistências e melhorando a confiabilidade do sistema. Por outro lado, o uso de engine.execute() pode deixar o sistema mais propenso a bugs que possam levar à corrupção de dados.

A Arte de Confirmar Transações

Ao executar consultas SQL brutas, é essencial entender o processo de confirmação das transações. Isso se torna significativo, especialmente quando você está realizando operações que modificam dados, como INSERT, UPDATE ou DELETE. O SQLAlchemy, por meio de seu gerenciamento de sessão, permite controlar quando essas alterações devem ser permanentes.

Aqui está um exemplo:

from sqlalchemy import text
 
with session.begin():
    session.execute(
        text("UPDATE users SET age = :new_age WHERE age = :old_age"),
        {'new_age': 30, 'old_age': 20}
    )

No código acima, primeiro iniciamos uma nova transação usando session.begin(). Esta transação inclui a consulta SQL bruta que atualiza a idade de todos os usuários de 20 para 30. A transação é então automaticamente confirmada no final do bloco with. Caso ocorra uma exceção durante a execução da consulta, a transação é revertida, mantendo o estado do banco de dados consistente.

O Papel Vital da Parametrização

Vale ressaltar que consultas SQL brutas podem às vezes ser vulneráveis a ataques de injeção SQL, uma vulnerabilidade comum de segurança na web. A parametrização é uma técnica para evitar esses ataques. A função text() do SQLAlchemy ajuda a parametrizar consultas SQL brutas.

Aqui está um exemplo ilustrativo:

from sqlalchemy import text
 
query = text("SELECT * FROM users WHERE name = :name")
result = session.execute(query, {'name': 'Alice'})

No trecho de código acima, o :name dentro da string da consulta é um parâmetro de conexão. Quando a função execute() é chamada, passamos um dicionário que mapeia esses parâmetros de conexão para seus respectivos valores. Essa funcionalidade garante que os dados sejam corretamente escapados, diminuindo o risco de injeção SQL.

Dominando Visualizações e Junções Inline

Consultas complexas muitas vezes exigem o uso de visualizações e junções inline. Visualizações inline são subconsultas na cláusula FROM, permitindo-nos usar o resultado de uma consulta como se fosse uma tabela. As junções, por outro lado, combinam linhas de duas ou mais tabelas com base em colunas relacionadas. Aqui está um exemplo de SQL bruto com uma visualização inline e uma junção:

query = text("""
SELECT users.name, counts.invoice_count
FROM users
JOIN (SELECT user_id, COUNT(*) as invoice_count FROM invoices GROUP BY user_id) AS counts
ON users.id = counts.user_id
WHERE counts.invoice_count > :count
""")
result = session.execute(query, {'count': 10})

Esta consulta recupera todos os usuários com mais de 10 faturas. A visualização inline aqui é a tabela counts, que é o resultado da subconsulta que calcula o número de faturas para cada usuário.

Conclusão

A capacidade de executar SQL bruto no SQLAlchemy fornece a flexibilidade para lidar com consultas complexas enquanto preserva a conveniência e segurança de usar uma linguagem de alto nível como o Python. Essa abordagem combina as capacidades completas do SQL com a usabilidade do Python, dando aos desenvolvedores a vantagem de criar operações de banco de dados eficientes, intuitivas e seguras.

Perguntas frequentes

Q1: Como executar uma consulta de SQL bruta no SQLAlchemy?

Você pode usar os métodos session.execute() ou engine.execute() para executar consultas de SQL brutas no SQLAlchemy. O método session.execute() é recomendado, pois garante que as consultas sejam gerenciadas adequadamente por uma transação.

Q2: Como realizar uma consulta SELECT no SQLAlchemy?

Para realizar uma consulta SELECT, passe a string da consulta de SQL bruto para o método execute(). Por exemplo, result = session.execute("SELECT * FROM users") irá buscar todas as linhas da tabela users.

Q3: Como posso evitar a injeção de SQL ao usar consultas de SQL brutas no SQLAlchemy?

Para evitar a injeção de SQL ao usar consultas de SQL brutas no SQLAlchemy, utilize a parametrização passando parâmetros de consulta como valores vinculados, garantindo a escape e validação adequadas da entrada do usuário.