Export a CSV file from a table

📖 Theory

The principal wants it in Excel

ResultDesk's weak-subject report (your GROUP BY query from Unit 1) impressed everyone. The principal's follow-up: "mail it to me, I will open it in Excel."

Excel does not read SQL result grids off your terminal. It reads CSV.

Last lesson data flowed in from CSV; today it flows out. Same shell, four settings, and here is the pleasant surprise: you can export not just tables, but the result of any query you can write.

📖 Theory

Redirecting the printer

Until now, every SELECT printed to your screen: the shell's default printer.

Exporting is just redirecting that printer: .mode csv chooses the paper format, .headers on prints the column names as the first line, .output result.csv swaps the screen for a file. Then you run the SELECT exactly as always, and the "printout" lands in the file. Swap the printer back when done.

🪜 Follow along

The canonical export sequence (order matters)

1. .headers on The first line of the file will carry column names: Excel and pandas expect this.
2. .mode csv Output format becomes comma-separated (quoted properly where values contain commas).
3. .output weak_subjects.csv From here, results go to the file, not the screen.
4. Run the SELECT SELECT subject, AVG(score) AS average FROM marks GROUP BY subject HAVING AVG(score) < 50; The result set IS the file body.
5. .output stdout Point the printer back at the screen. Skip this and your next hour of queries vanishes into the file.

📖 Theory

You export queries, not just tables

A whole table is just the simplest query: SELECT * FROM marks;

But the real power: anything Unit 1 taught you exports identically. The join of students and marks. The CASE grade bands. The HAVING-filtered averages. Write the query, and its result becomes the CSV.

That means reports are shaped in SQL first (filter, join, summarise), exported second. Shaping data in Excel afterwards is the amateur path; you have a database.

🧩 Quiz

A student exports without .headers on. The principal opens the CSV in Excel. What does the FIRST ROW of the spreadsheet show?

1. The first data row (e.g. DBMS, 43.5), which Excel then treats as the column titles
2. Empty cells where the header should be
3. The SQL query text
4. Nothing: the file fails to open without headers

💭 Think first

Dump or CSV? Choose per destination

Two requests: (1) the exam cell wants marks in a file they can rebuild into their OWN SQLite database next year; (2) the principal wants per-city average scores to chart in Excel. Before tapping: which export mechanism fits each, and why?

⚠️ Watch out

The two lingering-state traps

The shell keeps your settings until changed:

• .output left on a file swallows every later result: always return to stdout.
• .mode csv left on makes your next interactive SELECT print bare commas instead of aligned columns: switch back with .mode column for on-screen reading.

Settings first, query second, cleanup third: the sequence IS the answer exams want.

📖 Theory

This exact file returns in Unit 4

Keep weak_subjects.csv: in Unit 4, pandas will read it back with pd.read_csv() and plot it in Unit 5 with matplotlib. The full circle of this subject: SQL shapes the data, CSV carries it, Python analyses and charts it. You have now built both halves of the CSV bridge; next, Python takes over the driving.

✅ Summary

Key takeaways

• Export sequence: .headers on, .mode csv, .output file.csv, run the SELECT, .output stdout.
• Any query result exports: joins, GROUP BY summaries, CASE bands, not just whole tables.
• .headers on matters: without it, the first data row masquerades as column titles downstream.
• Dump vs CSV: SQL for rebuilding in SQLite vs data-only for spreadsheets and pandas.
• Shell settings persist: return .output to stdout and .mode to column after exporting.
• Memory hook: redirect the printer, print the query, redirect back.