0Pricing
Pandas & NumPy Academy · Aula

Executando consultas SQL no Pandas

Execute instruções SELECT arbitrárias com pd.read_sql_query e parametrize as consultas com segurança para evitar injeção de SQL.

Executando consultas SQL no Pandas é uma aula grátis de Pandas & NumPy Academy no CoddyKit. Esta é a aula 2 de 4. Você pode ler a aula completa abaixo gratuitamente — depois pratica ao vivo no navegador com um editor de código integrado e um tutor de IA 24/7. Faz parte do caminho de aprendizado de Pandas & NumPy Academy, e seu progresso é sincronizado entre a web e o app CoddyKit. O curso de Pandas & NumPy Academy inclui 4 aulas no total.

Partes desta aula ainda não foram traduzidas e aparecem em inglês.

pd.read_sql: The Unified Interface

Pandas provides three SQL reading functions: pd.read_sql() (generic wrapper), pd.read_sql_table() (reads a full table by name), and pd.read_sql_query() (executes arbitrary SQL). For most analytical workflows, pd.read_sql_query() is the most powerful because it lets you write any SELECT statement with filtering, joining, and aggregating before data reaches Pandas. Using SQL for heavy lifting and Pandas for the final analysis is often more efficient than loading everything and filtering in Python.

import pandas as pd
import sqlalchemy as sa

engine = sa.create_engine('sqlite:///ecommerce.db')

# Three equivalent patterns
df1 = pd.read_sql('SELECT * FROM orders LIMIT 100', con=engine)
df2 = pd.read_sql_table('orders', con=engine)  # full table
df3 = pd.read_sql_query('SELECT * FROM orders LIMIT 100', con=engine)

print(df3.head())
print(df3.columns.tolist())

Filtering at the Database Level

Always filter data in SQL rather than loading everything and filtering in Pandas. A database with proper indexes can execute a WHERE clause on millions of rows and return only thousands in milliseconds, while Pandas would need to load gigabytes of data first. The golden rule: push predicates to the database. Use WHERE for row filters, SELECT col1, col2 for column selection, and LIMIT during development to preview results quickly.

import pandas as pd
import sqlalchemy as sa

engine = sa.create_engine('sqlite:///sales.db')

# Filter and project at SQL level — only fetch what you need
query = '''
    SELECT order_id, customer_id, amount, status
    FROM orders
    WHERE status = 'completed'
      AND order_date >= '2024-01-01'
      AND amount > 50
    LIMIT 1000
'''

df = pd.read_sql_query(query, con=engine)
print(f'Rows: {len(df)}, Columns: {list(df.columns)}')

Aggregating in SQL vs Pandas

For simple group-level summaries over large tables, SQL aggregations outperform Pandas because the database engine can use indexes, parallel execution, and hash aggregation on disk. Use SQL for GROUP BY and SUM/COUNT/AVG when the table is large. Load the aggregated result (a small DataFrame) into Pandas for further analysis, visualisation, or combination with other data. For complex custom aggregations that SQL cannot express, load a filtered subset into Pandas and use groupby there.

import pandas as pd
import sqlalchemy as sa

engine = sa.create_engine('sqlite:///sales.db')

# Aggregate in SQL — returns a small result set
query = '''
    SELECT region,
           COUNT(*) AS order_count,
           ROUND(SUM(amount), 2) AS total_revenue,
           ROUND(AVG(amount), 2) AS avg_order_value
    FROM orders
    WHERE status = 'completed'
    GROUP BY region
    ORDER BY total_revenue DESC
'''

df = pd.read_sql_query(query, con=engine)
print(df)

JOINs in SQL Queries

SQL JOIN operations are more efficient than Pandas merge() for joins on large tables because the database can use indexed lookups. Write your join in SQL and receive a pre-joined, potentially filtered result in Pandas. For multi-table analysis, a single SQL query with multiple JOINs is typically faster than reading each table separately and merging in Pandas, especially when one table has millions of rows and the join reduces the result significantly.

import pandas as pd
import sqlalchemy as sa

engine = sa.create_engine('sqlite:///ecommerce.db')

query = '''
    SELECT o.order_id,
           c.customer_name,
           c.country,
           p.product_name,
           o.amount
    FROM orders o
    JOIN customers c ON o.customer_id = c.id
    JOIN products p ON o.product_id = p.id
    WHERE o.status = 'completed'
    LIMIT 500
'''

df = pd.read_sql_query(query, con=engine)
print(df.head())

Using Python Variables in Queries

To inject Python variables into SQL queries safely, use SQLAlchemy's text() with named parameters. Define placeholders with :param_name in the query string and pass a dictionary to the params argument of read_sql_query. This works for both single values and — with some databases — for lists. Avoid f-strings or % formatting to build query strings from variables; they are unsafe even for internal use.

import pandas as pd
import sqlalchemy as sa

engine = sa.create_engine('sqlite:///sales.db')

# Python variables to inject
min_amount = 200.0
start_date = '2024-01-01'
end_date = '2024-12-31'

query = sa.text('''
    SELECT * FROM orders
    WHERE amount > :min_amount
      AND order_date BETWEEN :start_date AND :end_date
''')

with engine.connect() as conn:
    df = pd.read_sql_query(query, con=conn,
                           params={'min_amount': min_amount,
                                   'start_date': start_date,
                                   'end_date': end_date})
print(f'{len(df)} orders found')

Using CTEs and Subqueries

Complex analyses often require Common Table Expressions (CTEs) or subqueries. These are fully supported by pd.read_sql_query — just pass the entire multi-clause SQL as the query string. CTEs (introduced with the WITH keyword) make complex queries more readable by naming intermediate results. This is useful for running-total calculations, ranking within groups, and multi-step filtering that would be verbose in Pandas.

import pandas as pd
import sqlalchemy as sa

engine = sa.create_engine('sqlite:///sales.db')

query = '''
    WITH monthly_revenue AS (
        SELECT strftime('%Y-%m', order_date) AS month,
               SUM(amount) AS revenue
        FROM orders
        WHERE status = 'completed'
        GROUP BY month
    )
    SELECT month,
           revenue,
           revenue - LAG(revenue) OVER (ORDER BY month)
               AS month_over_month_change
    FROM monthly_revenue
    ORDER BY month
'''

df = pd.read_sql_query(query, con=engine)
print(df.tail())

Reading with a DatetimeIndex

When reading time series data from a database, set the timestamp column as the DataFrame's index by passing index_col='date_column' and parse_dates=['date_column'] to read_sql_query. This gives you a DatetimeIndex directly, enabling Pandas time-based slicing (df['2024-01']), resampling, and rolling calculations without extra post-processing steps. The parse_dates argument tells Pandas to convert the column to datetime64.

import pandas as pd
import sqlalchemy as sa

engine = sa.create_engine('sqlite:///metrics.db')

df = pd.read_sql_query(
    'SELECT recorded_at, metric_value FROM daily_metrics ORDER BY recorded_at',
    con=engine,
    index_col='recorded_at',
    parse_dates=['recorded_at']
)
print(df.index.dtype)   # datetime64[ns]
print(df['2024-06'])    # Slice by month directly

Profiling Slow Queries

When a query is slow, add the SQL keyword EXPLAIN (or EXPLAIN QUERY PLAN in SQLite) before your SELECT to see the database's execution plan. Look for full table scans ('SCAN TABLE') where you would expect index lookups ('SEARCH TABLE'). Missing indexes on WHERE and JOIN columns are the most common cause of slow queries. Create the appropriate index in the database and re-check with EXPLAIN before re-running the Pandas pipeline.

import sqlalchemy as sa

engine = sa.create_engine('sqlite:///sales.db')

# Check if the query uses an index
with engine.connect() as conn:
    plan = conn.execute(sa.text(
        'EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 42'
    )).fetchall()
    for row in plan:
        print(row)
    # Look for 'SEARCH TABLE orders USING INDEX' — not 'SCAN TABLE'

Pagination for Large Result Sets

When iterating over a large result set interactively (e.g., processing one page of results at a time), use SQL LIMIT and OFFSET to implement pagination. Fetch N rows at a time, process them, then fetch the next N. While this is less efficient than the chunksize approach (which maintains a cursor), pagination is useful when rows must be shown incrementally in a report or when combining results from multiple queries.

import pandas as pd
import sqlalchemy as sa

engine = sa.create_engine('sqlite:///sales.db')
page_size = 10000
offset = 0

while True:
    query = sa.text(
        'SELECT * FROM orders ORDER BY order_id LIMIT :limit OFFSET :offset'
    )
    with engine.connect() as conn:
        df = pd.read_sql_query(query, con=conn,
                               params={'limit': page_size, 'offset': offset})
    if len(df) == 0:
        break
    print(f'Page at offset {offset}: {len(df)} rows')
    offset += page_size

Combining SQL Queries with Pandas Logic

The most powerful pattern is a hybrid pipeline: use SQL for coarse-grained filtering and aggregation, then use Pandas for fine-grained transformations that SQL expresses awkwardly (pivot tables, string parsing, apply functions, rolling windows). Read a manageable result set from SQL (thousands of rows), then chain Pandas operations on the resulting DataFrame. This combines the strengths of both tools while keeping the data flowing through a single Python process.

import pandas as pd
import sqlalchemy as sa

engine = sa.create_engine('sqlite:///sales.db')

# SQL: coarse filter and join
df = pd.read_sql_query('''
    SELECT o.customer_id, o.amount, o.order_date, c.country
    FROM orders o JOIN customers c ON o.customer_id = c.id
    WHERE o.status = 'completed'
''', con=engine, parse_dates=['order_date'])

# Pandas: rolling 30-day revenue per country
df = df.sort_values('order_date')
df['rolling_30d'] = (
    df.groupby('country')['amount']
    .transform(lambda x: x.rolling('30D').sum())
)
print(df.head())

Error Handling for Database Queries

Database queries can fail due to network timeouts, syntax errors, or lost connections. Wrap database calls in try-except blocks that catch sqlalchemy.exc.OperationalError for connection issues and sqlalchemy.exc.ProgrammingError for SQL syntax errors. Log the error with context (query, parameters) and either retry with exponential backoff or fail gracefully. In production pipelines, distinguishing transient errors (retry-able) from permanent errors (fix the SQL) is essential.

import pandas as pd
import sqlalchemy as sa

engine = sa.create_engine('sqlite:///sales.db')

try:
    df = pd.read_sql_query(
        'SELECT * FROM nonexistent_table',
        con=engine
    )
except sa.exc.OperationalError as e:
    print(f'Connection or table error: {e}')
except sa.exc.ProgrammingError as e:
    print(f'SQL syntax error: {e}')
except Exception as e:
    print(f'Unexpected error: {type(e).__name__}: {e}')

Quick Check

Test your understanding of Data Analysis concepts from this lesson.

Lesson Recap

In this lesson you learned: pd.read_sql_query() executes any SQL SELECT and returns a DataFrame, pushing filters and aggregations to SQL is more efficient than loading full tables into Pandas, and hybrid pipelines combine SQL for coarse data reduction with Pandas for fine-grained custom transformations. Next up we learn how to write DataFrames back into database tables.

Perguntas Frequentes

A aula “Executando consultas SQL no Pandas” é grátis?

Sim — o texto completo de “Executando consultas SQL no Pandas” é grátis para ler aqui na web. Para praticá-la interativamente (um editor de código integrado e um tutor de IA 24/7) e desbloquear o restante do curso de Pandas & NumPy Academy, atualize para CoddyKit PRO. O curso de Pandas & NumPy Academy inclui 4 aulas no total.

O que vou aprender em “Executando consultas SQL no Pandas”?

Execute instruções SELECT arbitrárias com pd.read_sql_query e parametrize as consultas com segurança para evitar injeção de SQL. Você pratica Pandas & NumPy Academy com código prático que executa diretamente no navegador, e um tutor de IA 24/7 responde suas dúvidas enquanto trabalha na aula.

Preciso ter experiência prévia para começar Pandas & NumPy Academy?

Nenhuma experiência prévia é necessária. Pandas & NumPy Academy no CoddyKit é estruturado para alunos iniciantes até avançados, então você pode começar aqui ou desde o início e aprender no seu ritmo. Esta é a aula 2 de 4.

Quanto tempo leva a aula “Executando consultas SQL no Pandas”?

A maioria das aulas CoddyKit leva cerca de 5–10 minutos. Cada uma é compacta e interativa, então você faz progresso constante e retoma exatamente de onde parou entre web e app.

Posso escrever e executar código nesta aula de Pandas & NumPy Academy?

Sim. Cada aula de Pandas & NumPy Academy inclui um editor de código integrado, então você escreve e executa código real direto no navegador e recebe feedback de IA instantaneamente — nenhuma configuração local necessária.

Todas as aulas deste curso

  1. Conectando-se a um banco de dados com SQLAlchemy
  2. Executando consultas SQL no Pandas
  3. Gravando DataFrames em tabelas de banco de dados
  4. Pandas vs. SQL: escolhendo a ferramenta certa
← Voltar para Pandas & NumPy Academy