Course contentsShow
← Data Science
Lesson 1099 of 2,14528. Working with Databases from PythonFree lesson

Converting Results to DataFrames

Transform query results into pandas DataFrames using read_sql_query() and read_sql_table().

Converting Results to DataFrames

What You'll Learn

You'll learn how to transform SQL query results directly into pandas DataFrames using built-in pandas functions, eliminating the need for manual conversion loops.

Why This Matters

So far, you've been executing queries and fetching results as tuples or lists using cursor methods like fetchall(). While this works, data scientists typically want results in a pandas DataFrame for easier analysis, visualization, and manipulation. Manually converting fetch results into DataFrames is tedious—pandas provides shortcuts that do this automatically.

The Two Key Functions

read_sql_query()

This function executes a SQL query and returns the results as a DataFrame in one step:

import pandas as pd
import sqlite3

conn = sqlite3.connect('sales.db')

# Execute query and get DataFrame directly
df = pd.read_sql_query("SELECT * FROM products WHERE price > 50", conn)

print(df.head())
conn.close()

The query runs, results are fetched, and you immediately have a DataFrame with proper column names.

read_sql_table()

This function reads an entire table into a DataFrame without writing SQL:

conn = sqlite3.connect('sales.db')

# Load entire table as DataFrame
df = pd.read_sql_table('customers', conn)

print(df.shape)
conn.close()

Note: read_sql_table() doesn't work with SQLite—it's designed for database systems like PostgreSQL that support table introspection.

When to Use Which

  • Use read_sql_query() when you need filtered, joined, or aggregated data
  • Use read_sql_table() when you want an entire table and your database supports it

Both methods handle data type conversion automatically, turning SQL types into appropriate pandas/NumPy types.

Key Takeaway: Use pd.read_sql_query() to execute queries and get DataFrames directly, avoiding manual tuple-to-DataFrame conversion and making your data immediately ready for pandas operations.