SQL Analysis with Python

Combining SQL with Python brings together the strengths of both - SQL efficiently filters and aggregates data close to its source, while Python's pandas library adds flexible analysis, transformation, and visualization on top of the query results.

Loading Query Results into pandas

import pandas as pd
import sqlite3

conn = sqlite3.connect("uncodemy.db")

query = (
    "SELECT city, SUM(amount) AS total_revenue "
    "FROM orders o "
    "JOIN customers c ON o.customer_id = c.customer_id "
    "GROUP BY city"
)

df = pd.read_sql_query(query, conn)
conn.close()

print(df.head())

Analyzing the Results in Python

import matplotlib.pyplot as plt

df.sort_values("total_revenue", ascending=False, inplace=True)
plt.bar(df["city"], df["total_revenue"], color="#ff5421")
plt.title("Total Revenue by City")
plt.show()

Where to Draw the Line Between SQL and Python

A common rule of thumb: use SQL for filtering, joining, and aggregating large volumes of data efficiently at the source, and use Python for the more flexible, iterative parts of analysis - statistical testing, visualization, and machine learning.

You've Completed This Section

This wraps up Python Database Connectivity - from understanding the DB-API standard and managing credentials securely, through opening and closing connections properly, to pulling SQL query results directly into pandas for analysis. With SQL and Python working together, you now have a complete pipeline from raw database to finished analysis.