Block 123: pandas ↔ SQLite Integration
Move data between pandas DataFrames and SQLite.
Concepts
- pd.read_sql_query(sql, conn)
- df.to_sql('table_name', conn, if_exists='replace')
- Filtering and aggregating from SQL via pandas
- Comparing SQL GROUP BY vs pandas groupby
Code Examples
See exercise below.
Exercise
Load a CSV into a SQLite table via pandas. Query with pd.read_sql. Write a SQL query with GROUP BY and compare the result to a pandas groupby.
Homework
When would you choose SQL aggregations over pandas groupby? Give 2 scenarios for each.