Querying with DuckDB

Every CSV, Excel, and Parquet reader (and writer) can run SQL against your file via DuckDB — see query_data in the Readers reference. With CSVReader, choose the duckdb engine and write your query against the auto-generated table name:

from datagrunt import CSVReader

dg = CSVReader('electric_vehicle_population_data.csv', engine='duckdb')

# Construct your SQL query using the auto-generated table name
query = f"""
WITH core AS (
    SELECT
        City AS city,
        "VIN (1-10)" AS vin
    FROM {dg.db_table}
)
SELECT
    city,
    COUNT(vin) AS vehicle_count
FROM core
GROUP BY 1
ORDER BY 2 DESC
"""

# Execute the query and get results as a Polars DataFrame
df = dg.query_data(query).pl()
print(df)

With the DuckDB engine, Datagrunt imports the CSV once per reader or writer and reuses it: repeated reads (to_dataframe, to_arrow_table, get_sample), query_data calls, and multi-format exports all skip re-importing the file, so follow-up operations run dramatically faster. Generated SQL safely escapes filenames, headers, and inferred delimiters, so special characters such as apostrophes cannot break a query.

You never have to manage the connection — it is released automatically when the reader or writer goes out of scope. If you want to release it early (for example, to free memory deterministically in a long-running process), use the reader as a context manager or call close(); this is optional and the object stays usable afterward:

with CSVReader('vehicles.csv', engine='duckdb') as dg:
    df = dg.query_data(f"SELECT city, COUNT(*) FROM {dg.db_table} GROUP BY 1").pl()
# connection released here, even if the block raises

ExcelReader and ParquetReader work the same way — query_data opens a DuckDB connection on demand and registers the file (or selected sheet) under db_table.