Skip to content

Commit 52f972f

Browse files
x4nth055x4nth055
committed
Add DuckDB Python tutorial
1 parent 6090461 commit 52f972f

1 file changed

Lines changed: 319 additions & 0 deletions

File tree

‎database/duckdb-python/duckdb_tutorial.py‎

Lines changed: 319 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,319 @@
1+
"""
2+
DuckDB + Python — Complete Tutorial Code
3+
=========================================
4+
SQL Analytics at Lightning Speed with DuckDB
5+
6+
Requirements: pip install duckdb pandas polars pyarrow numpy
7+
8+
This script covers:
9+
1. Basic DuckDB connection and SQL queries
10+
2. Querying CSV files directly (no import needed!)
11+
3. DuckDB vs Pandas performance comparison
12+
4. Querying Parquet files
13+
5. Window functions for ranking
14+
6. Hybrid workflow: DuckDB → Pandas → Polars
15+
7. Persistent databases (.duckdb files)
16+
8. Exporting results to CSV and Parquet
17+
"""
18+
import duckdb
19+
import pandas as pd
20+
import polars as pl
21+
import numpy as np
22+
import time
23+
import os
24+
25+
print(f"DuckDB version: {duckdb.__version__}")
26+
27+
# ═══════════════════════════════════════════════════════════════
28+
# 1. GENERATE SAMPLE DATA
29+
# ═══════════════════════════════════════════════════════════════
30+
print("\n" + "=" * 60)
31+
print("GENERATING SAMPLE DATA (500K rows)")
32+
print("=" * 60)
33+
34+
np.random.seed(42)
35+
n = 500_000
36+
37+
regions = ["North", "South", "East", "West"]
38+
products = ["Widget A", "Widget B", "Gadget X", "Gadget Y", "Doohickey Z"]
39+
categories = ["Electronics", "Home", "Office", "Electronics", "Office"]
40+
41+
df_sales = pd.DataFrame({
42+
"order_id": range(1, n + 1),
43+
"region": np.random.choice(regions, n),
44+
"product": np.random.choice(products, n),
45+
"category": np.random.choice(categories, n),
46+
"quantity": np.random.randint(1, 20, n),
47+
"unit_price": np.round(np.random.uniform(5, 500, n), 2),
48+
"order_date": pd.date_range("2025-01-01", periods=n, freq="90s"),
49+
})
50+
51+
df_sales["total_amount"] = df_sales["quantity"] * df_sales["unit_price"]
52+
df_sales["customer_id"] = np.random.randint(1000, 5000, n)
53+
54+
csv_path = "sales_data.csv"
55+
parquet_path = "sales_data.parquet"
56+
df_sales.to_csv(csv_path, index=False)
57+
df_sales.to_parquet(parquet_path, index=False)
58+
59+
csv_size = os.path.getsize(csv_path) / (1024 * 1024)
60+
pq_size = os.path.getsize(parquet_path) / (1024 * 1024)
61+
print(f"CSV saved: {csv_size:.1f} MB ({n:,} rows)")
62+
print(f"Parquet saved: {pq_size:.1f} MB ({n:,} rows)")
63+
64+
# ═══════════════════════════════════════════════════════════════
65+
# 2. BASIC DUCKDB: IN-MEMORY CONNECTION
66+
# ═══════════════════════════════════════════════════════════════
67+
print("\n" + "=" * 60)
68+
print("BASIC DUCKDB: Creating tables & querying")
69+
print("=" * 60)
70+
71+
conn = duckdb.connect() # in-memory database
72+
73+
conn.execute("""
74+
CREATE TABLE employees (
75+
id INTEGER,
76+
name VARCHAR,
77+
department VARCHAR,
78+
salary DECIMAL(10, 2)
79+
)
80+
""")
81+
82+
conn.execute("""
83+
INSERT INTO employees VALUES
84+
(1, 'Alice', 'Engineering', 95000),
85+
(2, 'Bob', 'Engineering', 87000),
86+
(3, 'Charlie', 'Marketing', 72000),
87+
(4, 'Diana', 'Marketing', 78000),
88+
(5, 'Eve', 'Engineering', 105000),
89+
(6, 'Frank', 'Sales', 65000),
90+
(7, 'Grace', 'Sales', 71000)
91+
""")
92+
93+
print("\nAll employees (ordered by salary):")
94+
print(conn.execute("SELECT * FROM employees ORDER BY salary DESC").fetchdf())
95+
96+
print("\nAverage salary by department:")
97+
print(conn.execute("""
98+
SELECT department,
99+
ROUND(AVG(salary), 2) AS avg_salary,
100+
COUNT(*) AS headcount
101+
FROM employees
102+
GROUP BY department
103+
ORDER BY avg_salary DESC
104+
""").fetchdf())
105+
106+
# ═══════════════════════════════════════════════════════════════
107+
# 3. QUERY CSV DIRECTLY — THE KILLER FEATURE
108+
# ═══════════════════════════════════════════════════════════════
109+
print("\n" + "=" * 60)
110+
print("QUERYING CSV DIRECTLY (No pd.read_csv() needed!)")
111+
print("=" * 60)
112+
113+
t0 = time.time()
114+
result = conn.execute(f"""
115+
SELECT
116+
region,
117+
category,
118+
COUNT(*) AS num_orders,
119+
ROUND(SUM(total_amount), 2) AS revenue,
120+
ROUND(AVG(total_amount), 2) AS avg_order_value
121+
FROM read_csv('{csv_path}', AUTO_DETECT=TRUE)
122+
GROUP BY region, category
123+
ORDER BY revenue DESC
124+
LIMIT 10
125+
""").fetchdf()
126+
duckdb_time = time.time() - t0
127+
print(f"DuckDB direct CSV query: {duckdb_time:.3f}s")
128+
print(result)
129+
130+
# ═══════════════════════════════════════════════════════════════
131+
# 4. DUCKDB vs PANDAS — PERFORMANCE SHOWDOWN
132+
# ═══════════════════════════════════════════════════════════════
133+
print("\n" + "=" * 60)
134+
print("DUCKDB vs PANDAS — Same query, who wins?")
135+
print("=" * 60)
136+
137+
t0 = time.time()
138+
df = pd.read_csv(csv_path)
139+
pandas_result = (df.groupby(["region", "category"])
140+
.agg(
141+
num_orders=("order_id", "count"),
142+
revenue=("total_amount", "sum"),
143+
avg_order_value=("total_amount", "mean")
144+
)
145+
.sort_values("revenue", ascending=False)
146+
.head(10)
147+
.round(2))
148+
pandas_time = time.time() - t0
149+
150+
print(f"Pandas read_csv + groupby: {pandas_time:.3f}s")
151+
print(f"DuckDB direct query: {duckdb_time:.3f}s")
152+
print(f"Speedup: {pandas_time/duckdb_time:.1f}x faster with DuckDB!")
153+
154+
# ═══════════════════════════════════════════════════════════════
155+
# 5. QUERY PARQUET FILES
156+
# ═══════════════════════════════════════════════════════════════
157+
print("\n" + "=" * 60)
158+
print("QUERYING PARQUET FILES")
159+
print("=" * 60)
160+
161+
t0 = time.time()
162+
result = conn.execute(f"""
163+
SELECT
164+
product,
165+
ROUND(SUM(total_amount), 2) AS total_revenue,
166+
COUNT(*) AS units_sold,
167+
ROUND(AVG(quantity), 1) AS avg_qty_per_order
168+
FROM read_parquet('{parquet_path}')
169+
GROUP BY product
170+
ORDER BY total_revenue DESC
171+
""").fetchdf()
172+
pq_time = time.time() - t0
173+
print(f"Parquet query: {pq_time:.3f}s")
174+
print(result)
175+
176+
# ═══════════════════════════════════════════════════════════════
177+
# 6. WINDOW FUNCTIONS — Top 3 products per region
178+
# ═══════════════════════════════════════════════════════════════
179+
print("\n" + "=" * 60)
180+
print("WINDOW FUNCTIONS — Top 3 Products per Region")
181+
print("=" * 60)
182+
183+
result = conn.execute(f"""
184+
WITH ranked AS (
185+
SELECT
186+
region,
187+
product,
188+
ROUND(SUM(total_amount), 2) AS revenue,
189+
ROW_NUMBER() OVER (
190+
PARTITION BY region
191+
ORDER BY SUM(total_amount) DESC
192+
) AS rank
193+
FROM read_parquet('{parquet_path}')
194+
GROUP BY region, product
195+
)
196+
SELECT * FROM ranked WHERE rank <= 3
197+
ORDER BY region, rank
198+
""").fetchdf()
199+
print(result)
200+
201+
# ═══════════════════════════════════════════════════════════════
202+
# 7. HYBRID WORKFLOW: DuckDB → Pandas → Polars
203+
# ═══════════════════════════════════════════════════════════════
204+
print("\n" + "=" * 60)
205+
print("HYBRID WORKFLOW: DuckDB → Pandas → Polars")
206+
print("=" * 60)
207+
208+
# Step 1: DuckDB does the heavy aggregation
209+
print("Step 1: DuckDB aggregates 500K rows → summary...")
210+
t0 = time.time()
211+
summary = conn.execute(f"""
212+
SELECT
213+
region,
214+
category,
215+
DATE_TRUNC('month', order_date) AS month,
216+
COUNT(*) AS order_count,
217+
ROUND(SUM(total_amount), 2) AS monthly_revenue
218+
FROM read_parquet('{parquet_path}')
219+
GROUP BY region, category, DATE_TRUNC('month', order_date)
220+
""").fetchdf()
221+
print(f" Done in {time.time() - t0:.3f}s → {len(summary)} rows")
222+
223+
# Step 2: Pandas for pivot table
224+
print("\nStep 2: Pandas pivot table...")
225+
t0 = time.time()
226+
pivot = summary.pivot_table(
227+
index="month",
228+
columns="region",
229+
values="monthly_revenue",
230+
aggfunc="sum"
231+
).round(2)
232+
print(f" Done in {time.time() - t0:.3f}s")
233+
print(pivot.head(6))
234+
235+
# Step 3: Polars for final polish
236+
print("\nStep 3: Polars for final formatting...")
237+
t0 = time.time()
238+
pl_df = pl.from_pandas(summary)
239+
top_month = (pl_df
240+
.group_by("region")
241+
.agg(pl.col("monthly_revenue").max().alias("best_month_revenue"))
242+
.sort("best_month_revenue", descending=True))
243+
print(f" Done in {time.time() - t0:.3f}s")
244+
print(top_month)
245+
246+
# ═══════════════════════════════════════════════════════════════
247+
# 8. PERSISTENT DATABASE
248+
# ═══════════════════════════════════════════════════════════════
249+
print("\n" + "=" * 60)
250+
print("PERSISTENT DATABASE — Save to .duckdb file")
251+
print("=" * 60)
252+
253+
db_path = "analytics.duckdb"
254+
persistent_conn = duckdb.connect(db_path)
255+
256+
persistent_conn.execute(f"""
257+
CREATE OR REPLACE TABLE sales AS
258+
SELECT * FROM read_parquet('{parquet_path}')
259+
""")
260+
261+
row_count = persistent_conn.execute("SELECT COUNT(*) FROM sales").fetchone()[0]
262+
db_size = os.path.getsize(db_path) / (1024 * 1024)
263+
print(f"Database file: {db_path} ({db_size:.1f} MB)")
264+
print(f"Sales table: {row_count:,} rows persisted")
265+
266+
print("\nTop 5 customers by lifetime value:")
267+
print(persistent_conn.execute("""
268+
SELECT
269+
customer_id,
270+
COUNT(*) AS orders,
271+
ROUND(SUM(total_amount), 2) AS lifetime_value
272+
FROM sales
273+
GROUP BY customer_id
274+
ORDER BY lifetime_value DESC
275+
LIMIT 5
276+
""").fetchdf())
277+
278+
persistent_conn.close()
279+
280+
# ═══════════════════════════════════════════════════════════════
281+
# 9. EXPORT RESULTS
282+
# ═══════════════════════════════════════════════════════════════
283+
print("\n" + "=" * 60)
284+
print("EXPORTING RESULTS")
285+
print("=" * 60)
286+
287+
conn.execute(f"""
288+
COPY (
289+
SELECT region, product, ROUND(SUM(total_amount), 2) AS revenue
290+
FROM read_parquet('{parquet_path}')
291+
GROUP BY region, product
292+
ORDER BY revenue DESC
293+
) TO 'revenue_summary.csv' (HEADER, DELIMITER ',')
294+
""")
295+
296+
conn.execute(f"""
297+
COPY (
298+
SELECT region, product, ROUND(SUM(total_amount), 2) AS revenue
299+
FROM read_parquet('{parquet_path}')
300+
GROUP BY region, product
301+
ORDER BY revenue DESC
302+
) TO 'revenue_summary.parquet' (FORMAT PARQUET)
303+
""")
304+
305+
print("Exported: revenue_summary.csv")
306+
print("Exported: revenue_summary.parquet")
307+
308+
exported = pd.read_csv("revenue_summary.csv")
309+
print(f"\nExported CSV preview ({len(exported)} rows):")
310+
print(exported.head())
311+
312+
# ═══════════════════════════════════════════════════════════════
313+
# CLEANUP
314+
# ═══════════════════════════════════════════════════════════════
315+
conn.close()
316+
317+
print("\n" + "=" * 60)
318+
print("DONE! All examples completed successfully.")
319+
print("=" * 60)

0 commit comments

Comments
 (0)