Course contentsShow
← Data Science
Lesson 1132 of 2,14529. Data Wrangling WorkflowsFree lesson

Database Exports: SQL and Data Dumps

Load data from SQL query results and database dumps directly into DataFrames.

Database Exports: SQL and Data Dumps

What you'll learn: How to load data directly from SQL query results and database dumps into DataFrames for analysis.

The Core Idea

Instead of connecting to a live database every time you need data, you can work with database exports—static snapshots of your data. These come in two main flavors: SQL query results saved to files, and full database dumps that capture entire table structures and contents. Loading these into DataFrames gives you the speed of file-based analysis with the richness of database data.

Think of it like taking a photograph of your database: you capture the state at a specific moment, then analyze that snapshot without needing the original subject to stay still.

SQL Query Results

You can execute a query and immediately load results into a DataFrame using pd.read_sql() or pd.read_sql_query():

import pandas as pd
import sqlite3

# Connect and load query directly
conn = sqlite3.connect('sales.db')
df = pd.read_sql_query("SELECT * FROM orders WHERE status = 'shipped'", conn)
conn.close()

This approach combines database querying with DataFrame creation in one step—no manual cursor or fetchall() needed.

Database Dumps

Database dumps are complete exports (often .sql files) containing CREATE TABLE statements and INSERT commands. While you typically load these back into a database first, you can also parse simpler formats or use intermediate tools to convert them to CSV/Parquet for direct DataFrame loading.

For structured exports like CSV or JSON from database tools (pg_dump, mysqldump with custom formats), use standard Pandas readers:

df = pd.read_csv('orders_export.csv')

Key Takeaway: Use pd.read_sql_query() to load SQL results directly into DataFrames for streamlined database-to-analysis workflows, and leverage standard file readers for pre-exported database dumps.