Introducing duckdb-assistant: an aid for DuckDB SQL

Install duckdb-assistant from PyPi

DuckDB benefits analytical pipelines; this is due to its role as a universal access component reaching several data stores, its lightweight and inexpensive footprint as an in-process database and its performant execution.

As more data engineering pipelines include DuckDB, convenient access to syntax and guidance helps you productively build your pipeline. As its name indicates, [duckdb-assistant](https://pypi.org/project/duckdb-assistant/), a Python package to generate and execute DuckDB SQL, aims to provide this assistance.

The DuckDB User Survey Analysis mentions its Python client as the most popular with 73% of those surveyed using the same (a side-note is that it’d be interesting to see if things have changed since 2024, the date of this survey). In its initial versions, duckdb-assistant offers the basics. It includes the mainstream [duckdb](https://pypi.org/project/duckdb/) package keeping it close at hand as a target execution mechanism after users explore, discover and (we fervently hope) test SQL generated from duckdb-assistant.

Installing the package

Install the package from the PyPi Python package registry through pip or uv.

pip install duckdb-assistant

Note that the following dependencies are also installed.

1. duckdb : DuckDB’s Python API which executes SQL statements that duckdb-assistant generates (in addition to user-supplied statements)
2. google-genai: Google’s Generative AI SDK in Python which offers an interface to the Gemini family of models that are called by duckdb-assistant to generate SQL in DuckDB dialect.
3. python-dotenv: a package which aides users in supplying environment variables to duckdb-assistant

Instructions provided here also detail how to install this from GitHub.

Getting familiar

duckdb-assistant offers a simple interface to generate and execute SQL. These two operations are available in the following methods.

1. Code generation : the generate method with an toggle to generate background explanation
2. Code execution : either the execute or sql method. duckdb-assistant’s execute and sql methods both call duckdb’s execute or sql method respectively after the generate method, offering the execute or sql experience directly through a prompt.

Let’s start with a simple example. Let’s suppose we have a CSV in a remote location. We’d like to read that CSV, apply a filter, and then write it out to a target location as a parquet file. A simple ETL pipeline.

For example purposes, I use a CSV file provided as part of a public GitHub repository. Change the prompt to reflect your desired CSV location.

from duckdb_assistant import DuckDBAssistant

dda = DuckDBAssistant()

prompt = """
I have a CSV located in https://path-to-your-csv. 
I want to read it, filter out only bad records (i.e. defaulters) and write them
out to a parquet file in my laptop at /path/to/your/parquet.
"""

You have two paths. If you just want to review generated code, the generate method is sufficient. This outputs a dict.

response_dict = dda.generate(prompt)

from pprint import pprint
pprint(response_dict, indent=4)

You’ll notice a couple of interesting things. Firstly, the requested query is generated and output under a "query" key within the response dict. Notice how, taking into account the characteristics of the input file, duckdb-assistant accounts for any extensions that are required (this is done through instructions to the LLM) making the code as whole as possible. In this case, the [httpfs](https://duckdb.org/docs/current/core_extensions/httpfs/overview) extension is used because the input file happens to be located online.

You’d also notice that the response dict contains a field called response_text which includes the SQL generated. This is where we see the explain_results parameter in action. For cases where you are interested more in explanation rather than execution, i.e. you require an understanding of code, then the response text holds such an explanation. This is triggered by setting the explain_results parameter to True.

response_dict = dda.generate(prompt, explain_results = True)

pprint(response_dict["response_text"], indent=4)

Rendering the text in Markdown, we get

### Summary Explanation\n'
 '\n'
 '1. **Dependencies (`httpfs` extension)**: \n'
 '   To read files directly over HTTP/HTTPS URLs, DuckDB relies on the '
 '`httpfs` extension. Modern versions of DuckDB automatically install and load '
 'extensions on demand, but explicitly running `INSTALL httpfs;` and `LOAD '
 'httpfs;` ensures compatibility across environments where autoloading might '
 'be disabled.\n'
 '\n'
 '2. **Reading and Auto-detection**: \n'
 '   The `read_csv_auto()` function reads the CSV file directly from the '
 'GitHub URL without requiring manual download. It automatically infers column '
 'names and data types (such as interpreting the `BAD` column as an integer).\n'
 '\n'
 '3. **Filtering Defaulters**: \n'
 '   In the standard XXXXX dataset, the `BAD` column is a flag where `1` '
 'indicates that an applicant defaulted on their loan (a "bad" record) and `0` '
 'indicates repayment. The query filters for `WHERE BAD = 1`.\n'
 '\n'
 '4. **Exporting to Parquet**: \n'
 '   The `COPY (...) TO ... (FORMAT PARQUET)` statement writes the query '
 'result directly out to a compressed, optimized Parquet file at the specified '
 'local path (`/Users/ss/duckdb-assistant/example_file.parquet`).')

This way, duckdb-assistant helps users, especially those new to SQL or those new to DuckDB SQL understand and hopefully test their generated code prior to actual execution. Which brings us to the next section.

Execution

You can always execute the resultant generated code (or even drop in your own SQL) using the built-in dd, a DuckDB connection object that’s created as soon as the DuckDBAssistant instance is initialised, like this.

dda.dd.execute(response_dict["query"])

This is made more convenient through the execute and sql methods forming part of DuckDBAssistant which take the prompt instead of a SQL query as a parameter.

dda.execute(prompt)
# or
dda.sql(prompt)

This gives you straight-through execution of SQL generated from the prompt.

Where do we go from here?

As mentioned, initial versions of this package offer fundamentals such as code generation and a pass through to execution. Building upon this base, potential future areas include more automation and configurable code generation capabilities. Hope you enjoy playing around with the package, get in touch here in case of any questions and watch this repo for updates.