0Pricing
Pandas & NumPy Academy · Lektion

SQL-Abfragen aus Pandas ausführen

Führen Sie beliebige SELECT-Anweisungen mit pd.read_sql_query aus und parametrisieren Sie Abfragen sicher, um SQL-Injection zu vermeiden.

SQL-Abfragen aus Pandas ausführen ist eine kostenlose Pandas & NumPy Academy-Lektion auf CoddyKit. Dies ist Lektion 2 von 4. Du kannst die komplette Lektion unten kostenlos lesen – dann übst du sie direkt im Browser mit einem integrierten Code-Editor und einem KI-Tutor rund um die Uhr. Sie ist Teil des Pandas & NumPy Academy-Lernpfads, und dein Fortschritt wird über Web und CoddyKit-App synchronisiert. Der Pandas & NumPy Academy-Kurs umfasst insgesamt 4 Lektionen.

Teile dieser Lektion wurden noch nicht übersetzt und werden auf Englisch angezeigt.

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.

Häufig gestellte Fragen

Ist die Lektion „SQL-Abfragen aus Pandas ausführen“ kostenlos?

Ja — der vollständige Text von „SQL-Abfragen aus Pandas ausführen“ ist hier im Web kostenlos zu lesen. Um sie interaktiv zu üben (integrierter Code-Editor und 24/7 KI-Tutor) und den Rest des Pandas & NumPy Academy-Kurses freizuschalten, upgrade auf CoddyKit PRO. Der Pandas & NumPy Academy-Kurs umfasst insgesamt 4 Lektionen.

Was lerne ich in „SQL-Abfragen aus Pandas ausführen“?

Führen Sie beliebige SELECT-Anweisungen mit pd.read_sql_query aus und parametrisieren Sie Abfragen sicher, um SQL-Injection zu vermeiden. Du übst Pandas & NumPy Academy mit praktischem Code, den du direkt im Browser ausführst, und ein 24/7 KI-Tutor beantwortet deine Fragen während du die Lektion bearbeitest.

Brauche ich Erfahrung, um Pandas & NumPy Academy zu starten?

Keine Vorkenntnisse erforderlich. Pandas & NumPy Academy auf CoddyKit ist für Anfänger bis fortgeschrittene Lernende strukturiert, sodass du hier starten oder von Anfang an beginnen und in deinem eigenen Tempo voranschreiten kannst. Dies ist Lektion 2 von 4.

Wie lange dauert die Lektion „SQL-Abfragen aus Pandas ausführen“?

Die meisten CoddyKit-Lektionen dauern etwa 5–10 Minuten. Jede ist kompakt und interaktiv, sodass du stetig Fortschritte machst und genau dort weitermachst, wo du aufgehört hast – im Web und in der App.

Kann ich in dieser Pandas & NumPy Academy-Lektion Code schreiben und ausführen?

Ja. Jede Pandas & NumPy Academy-Lektion enthält einen integrierten Code-Editor, sodass du echten Code direkt in deinem Browser schreibst und ausführst und sofort KI-Feedback erhältst — ohne lokale Einrichtung erforderlich.

Alle Lektionen in diesem Kurs

  1. Mit SQLAlchemy eine Datenbank verbinden
  2. SQL-Abfragen aus Pandas ausführen
  3. DataFrames in Datenbanktabellen schreiben
  4. Pandas vs. SQL: Das richtige Werkzeug wählen
← Zurück zu Pandas & NumPy Academy