duckdb-assistant: Generate & Execute DuckDB SQL

This repository provides a Python class and associated methods to generate and execute DuckDB SQL.

DuckDB is an open-source, low-footprint, in-process query processing engine which can access several data stores and structures like Parquet, CSV, JSON as well as data in conventional Relational Database Management Systems (RDBMS). This package uses the duckdb Python package along with methods to call a Large Language Model (LLM) from Google Gemini to generate code in a convenient and conversational manner.

A wiki of this repo has been generated using DeepWiki and is available here:

Refer this document for more details on how this project will evolve.

Installation

Local installation

1. Clone this repository
2. To install locally in editable mode, refer here

From PyPi

Run the following command for a pip installation of the package from PyPi.

pip install --upgrade duckdb-assistant

Usage - quick example

To initialise the DuckDBAssistant class:

from duckdb_assistant import DuckDBAssistant

dda = DuckDBAssistant()

Then, to generate a query in natural language,

duckdb_query = dda.generate("Create an empty customer table.")
print(duckdb_query)

Result:

>>> duckdb_query = dda.generate("Create an empty customer table.")
>>> print(duckdb_query)
CREATE TABLE IF NOT EXISTS customer (
    customer_id INTEGER PRIMARY KEY,
    first_name VARCHAR,
    last_name VARCHAR,
    email VARCHAR,
    phone VARCHAR,
    address VARCHAR,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Then, you execute the generated query either through a DuckDB connection or through the inbuilt duckdb Python connection object as follows:

dda.dd.execute(duckdb_query)

which is another way of running

import duckdb as dd
dd.execute(duckdb_query)

Or, you can choose to call the execute and sql methods available with the class that directly call duckdb's execute and sql methods after generation.

dda.execute("Create an empty customer table.")

dda.sql("Print Hello World through SQL")

Result:

>>> dda.execute("Create an empty customer table.")
<_duckdb.DuckDBPyConnection object at 0x10c194af0>
>>> 
>>> dda.sql("Print Hello World through SQL")
┌───────────────┐
│ 'Hello World' │
│    varchar    │
├───────────────┤
│ Hello World   │
└───────────────┘

>>> 

Documentation

Refer this page for a list of all available methods and attributes.

Generative AI usage

Core functions (described in Documentation) use Large Language Models (LLM, starting with Gemini 3.6 Flash) from the Google Gemini family. While you are free to modify the code to accommodate other LLMs, these are at present the only LLMs supported. Read this important note regarding functions that make use of Generative AI.

IMPORTANT: All outputs returned from Generative AI tools such as LLMs should be carefully reviewed prior to actual use. Quality of Generative AI outputs are determined by the Large Language Model in use and may be incorrect. Always review the same.

Add the following environmental variable to a .env file supplying variables to your environment. Get your Gemini API key from Google AI Studio.

GEMINI_API_KEY = <your_key>

An example env file (sample.env) is provided for this purpose. Rename this to .env and use.

Retrieval Augmented Generation (RAG)

This package uses Retrieval Augmented Generation (RAG) based on DuckDB documentation to provide context that can assist the LLM in generating SQL. This is controlled through a use_rag parameter in the generate method which can be turned off if desired. RAG tends to be useful when dealing with complex SQL generation.

To facilitate RAG, a search method and a sync_docs method are also provided in the package. The search method helps you search against local, i.e. documentation-based knowledge without having to use an LLM. The sync_docs method can be run at periodic intervals to ensure current documentation from the DuckDB project reflects in a local vector database. The DOC_REPO_URL and DOC_FOLDER_PATH parameters in your .env can also be modified to point to other (for e.g. customised) documents you wish to use in RAG. DOC_REPO_URL points to the GitHub (or other web) URL you want to use and DOC_FOLDER_PATH refers to the folder path within the repo identified by DOC_REPO_URL.

Convenience: tasks.json

This repository contains a tasks.json meant for use in Visual Studio Code which helps clean up temporary files and stands up a virtual environment for quick development and exploration. Remove this file if you do not want to have Visual Studio Code run the tasks in tasks.json.

Change Log

• Version: 0.7.0 (15SEP2026)
  • Model management and configuration methods

Refer CHANGELOG.md for other changes.

Contact

• Sundaresh Sankaran