Export a Table in Python

A warehouse table is only useful when it reaches the people and tools that need it: the finance team's spreadsheet, a dashboard, a data scientist's notebook. The tables in this lesson live in a DuckDB database in memory, so they vanish when the program ends. Exporting writes a table, or the rows of any query, to a file that stays.

COPY (SELECT …) TO 'file' (options) runs the query in the brackets and writes its rows to the file. With (FORMAT parquet) it writes Parquet: a file that stores the data column by column, compressed, together with each column's type, so the next tool doesn't have to guess them. A CSV file is plain text that any tool opens, but it carries no types; (HEADER) writes the column names as its first line.

con.execute("COPY (SELECT DISTINCT product FROM stg_orders) TO 'products.parquet' (FORMAT parquet)")
print(con.execute("SELECT COUNT(*) FROM 'products.parquet'").fetchone()[0])

Right after writing a file, check it: DuckDB reads a Parquet file in place, just like a CSV, when you name it in FROM. Without that check, an export that wrote the wrong rows is found days later by the team that reads it.

The starting code builds stg_orders: the week's 75 orders, typed, one row each; 64 of them are paid. The check lists the Parquet file's columns with their types (DESCRIBE), prints the CSV's first line, counts the CSV's rows and runs your query. Your program must print:

Columns in the Parquet file:
column_name | column_type
order_id | INTEGER
customer_id | INTEGER
product | VARCHAR
quantity | INTEGER
unit_price | INTEGER
status | VARCHAR
ordered_at | TIMESTAMP
filename | VARCHAR
(8 rows)
First line of the CSV file:
order_id,customer_id,product,quantity,unit_price,status,ordered_at,filename
csv_rows
64
(1 row)
paid_orders
64
(1 row)

Your task: write paid, the query for the paid orders of stg_orders; copy its rows to paid_orders.parquet and to paid_orders.csv with a header; then make query count the rows of the Parquet file.

Assignment
Follow the Coding Tutorial to export the paid orders to a Parquet file and a CSV file, then count the Parquet file's rows.

Hint
The query goes inside the brackets of COPY, so build the statement from pieces: "COPY (" + paid + ") TO …". File names go in single quotes inside the SQL string.