Query Files Where They Are in Python

Before a data engineer loads a file into the warehouse, they look at it: which columns it has, how many rows, whether the money adds up. DuckDB is a database that runs inside your Python program, and it can run SQL straight on a file. You open it with duckdb.connect(). With nothing between the brackets, the database lives in memory: there is no server to start and nothing is written to disk.

Reading a file in place: in DuckDB, a file name in single quotes can stand where a table name goes. FROM 'customers.csv' reads that CSV as it is and runs the query on its rows. No table is made and the file is not changed. Without this, you would load every file into a database just to ask it one question, and at work a new file lands every day.

query = (
    "SELECT newsletter, COUNT(*) AS customers "
    "FROM 'customers.csv' "
    "GROUP BY newsletter "
    "ORDER BY newsletter "
)

Each clause is a string piece that ends with a space, and Python joins the pieces into one query. con.execute(query) would run it. In this lesson the check does that for you with show(con, query): it prints the column names, the rows joined with |, and the row count.

Guessed types: a CSV file is only text. DuckDB looks at the values and guesses a type for each column: whole numbers become BIGINT, dates become DATE, and the rest is VARCHAR (text). In orders.csv, quantity and unit_price come out as numbers, so you can multiply them. The guess is only as good as the file: one word such as two in a number column makes the whole column text. Later lessons deal with that.

The check prints your query's rows, then the types DuckDB guessed for orders.csv (with DESCRIBE):

category | revenue
gifts | 20750
home | 16809
kitchen | 12682
office | 6737
(4 rows)

column_name | column_type
order_id | BIGINT
customer_id | BIGINT
date | DATE
product | VARCHAR
category | VARCHAR
quantity | BIGINT
unit_price | BIGINT
status | VARCHAR
(8 rows)

Your task: open a DuckDB database in memory named con. Then, in query, add up quantity * unit_price as revenue for each category, reading straight from 'orders.csv'. Count only the lines whose status is 'paid', and put the biggest revenue first.

Assignment
Follow the Coding Tutorial to open DuckDB in memory and write the revenue query on the file orders.csv.

Hint
The file name goes in single quotes where a table name would go: FROM 'orders.csv'. WHERE sits between FROM and GROUP BY, so cancelled lines are dropped before the sums. ORDER BY comes last.