Pandas & NumPy Academy · 강의

Pandas에서 SQL 쿼리 실행하기

pd.read_sql_query로 임의의 SELECT 문을 실행하고 SQL 삽입을 방지하도록 쿼리를 안전하게 매개변수화합니다.

Pandas에서 SQL 쿼리 실행하기은(는) CoddyKit의 무료 Pandas & NumPy Academy 강의입니다. 이것은 4개 중 2번째 강의입니다. 아래에서 전체 강의를 무료로 읽을 수 있으며, 내장 코드 에디터와 24/7 AI 튜터와 함께 브라우저에서 직접 실습할 수 있습니다. 이 강의는 Pandas & NumPy Academy 학습 경로의 일부이며, 진행 상황이 웹과 CoddyKit 앱에 동기화됩니다. Pandas & NumPy Academy 강의에는 총 4개의 강의가 포함되어 있습니다.

이 강의의 일부는 아직 번역되지 않았으며 영어로 표시됩니다.

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.

자주 묻는 질문

“Pandas에서 SQL 쿼리 실행하기” 강의는 무료인가요?

네 — “Pandas에서 SQL 쿼리 실행하기” 전체 내용을 이 웹사이트에서 무료로 읽을 수 있습니다. 인터랙티브하게 실습하려면(내장 코드 에디터와 24/7 AI 튜터), CoddyKit PRO로 업그레이드하면 Pandas & NumPy Academy 강의 전체를 잠금 해제할 수 있습니다. Pandas & NumPy Academy 강의에는 총 4개의 강의가 포함되어 있습니다.

“Pandas에서 SQL 쿼리 실행하기”에서 뭘 배우나요?

pd.read_sql_query로 임의의 SELECT 문을 실행하고 SQL 삽입을 방지하도록 쿼리를 안전하게 매개변수화합니다. 브라우저에서 직접 실행하는 실습 코드로 Pandas & NumPy Academy을(를) 배우며, 24/7 AI 튜터가 강의를 진행하면서 질문에 답변해줍니다.

Pandas & NumPy Academy을(를) 시작하는 데 경험이 필요한가요?

사전 경험은 필요하지 않습니다. CoddyKit의 Pandas & NumPy Academy은(는) 초급자부터 고급 학습자까지를 위해 구성되어 있으므로, 여기서 시작하거나 처음부터 시작할 수 있으며 자신의 속도대로 진행할 수 있습니다. 이것은 4개 중 2번째 강의입니다.

“Pandas에서 SQL 쿼리 실행하기” 강의는 얼마나 걸리나요?

대부분의 CoddyKit 강의는 약 5~10분이 소요됩니다. 각 강의는 간결하고 인터랙티브하여 꾸준한 진행이 가능하며, 웹과 앱에서 중단한 부분부터 바로 시작할 수 있습니다.

이 Pandas & NumPy Academy 강의에서 코드를 작성하고 실행할 수 있나요?

네. 모든 Pandas & NumPy Academy 강의에는 내장 코드 에디터가 포함되어 있으므로, 브라우저에서 바로 실제 코드를 작성하고 실행한 후 즉시 AI 피드백을 받을 수 있습니다 — 로컬 설정이 필요 없습니다.

이 강의의 모든 강의

← Pandas & NumPy Academy(으)로 돌아가기