0Pricing
Pandas & NumPy Academy · Lección

Ejecutar consultas SQL desde Pandas

Ejecute sentencias SELECT arbitrarias con pd.read_sql_query y parametrice las consultas de forma segura para evitar la inyección SQL.

Ejecutar consultas SQL desde Pandas es una lección gratuita de Pandas & NumPy Academy en CoddyKit. Esta es la lección 2 de 4. Puedes leer la lección completa abajo gratuitamente — luego la practicas en el navegador con un editor de código integrado y un tutor de IA 24/7. Forma parte de la ruta de aprendizaje de Pandas & NumPy Academy, y tu progreso se sincroniza en la web y la app de CoddyKit. El curso de Pandas & NumPy Academy incluye 4 lecciones en total.

Partes de esta lección aún no han sido traducidas y se muestran en 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.

Preguntas frecuentes

¿La lección «Ejecutar consultas SQL desde Pandas» es gratis?

Sí — el texto completo de «Ejecutar consultas SQL desde Pandas» es gratis para leer aquí en la web. Para practicarla de forma interactiva (editor de código integrado y tutor de IA 24/7) y desbloquear el resto del curso de Pandas & NumPy Academy, actualiza a CoddyKit PRO. El curso de Pandas & NumPy Academy incluye 4 lecciones en total.

¿Qué aprenderé en «Ejecutar consultas SQL desde Pandas»?

Ejecute sentencias SELECT arbitrarias con pd.read_sql_query y parametrice las consultas de forma segura para evitar la inyección SQL. Practicas Pandas & NumPy Academy con código real que ejecutas directamente en el navegador, y un tutor de IA 24/7 responde tus preguntas mientras trabajas en la lección.

¿Necesito experiencia previa para empezar Pandas & NumPy Academy?

No se requiere experiencia previa. Pandas & NumPy Academy en CoddyKit está estructurado para principiantes hasta estudiantes avanzados, así que puedes empezar aquí o desde el inicio y avanzar a tu ritmo. Esta es la lección 2 de 4.

¿Cuánto tiempo toma la lección «Ejecutar consultas SQL desde Pandas»?

La mayoría de las lecciones de CoddyKit toman alrededor de 5–10 minutos. Cada una es compacta e interactiva, así que avanzas constantemente y retomas exactamente por donde dejaste en la web y la app.

¿Puedo escribir y ejecutar código en esta lección de Pandas & NumPy Academy?

Sí. Cada lección de Pandas & NumPy Academy incluye un editor de código integrado, así que escribes y ejecutas código real directamente en tu navegador y obtienes retroalimentación instantánea de IA — sin configuración local necesaria.

Todas las lecciones de este curso

  1. Conectarse a una base de datos con SQLAlchemy
  2. Ejecutar consultas SQL desde Pandas
  3. Escribir DataFrames en tablas de bases de datos
  4. Pandas frente a SQL: cómo elegir la herramienta adecuada
← Volver a Pandas & NumPy Academy