Metadata-Version: 2.4
Name: duckdb-llm-udf
Version: 0.1.1
Summary: LLM UDF extension for DuckDB (Python interface)
Home-page: https://github.com/yourusername/duckdb_llm_udf
Author: Your Name
Author-email: Your Name <your@email.com>
License: MIT License
        
        Copyright (c) 2025 
        
        Permission is hereby granted, free of charge, to any person obtaining a copy
        of this software and associated documentation files (the "Software"), to deal
        in the Software without restriction, including without limitation the rights
        to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
        copies of the Software, and to permit persons to whom the Software is
        furnished to do so, subject to the following conditions:
        
        The above copyright notice and this permission notice shall be included in all
        copies or substantial portions of the Software.
        
        THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
        IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
        FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
        AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
        LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
        OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
        SOFTWARE.
        
Requires-Python: >=3.8
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: duckdb>=0.8.0
Requires-Dist: python-dotenv>=0.19.0
Requires-Dist: numpy>=1.21.0
Provides-Extra: openai
Requires-Dist: openai>=1.0.0; extra == "openai"
Provides-Extra: anthropic
Requires-Dist: anthropic>=0.5.0; extra == "anthropic"
Provides-Extra: all
Requires-Dist: openai>=1.0.0; extra == "all"
Requires-Dist: anthropic>=0.5.0; extra == "all"
Dynamic: author
Dynamic: home-page
Dynamic: license-file
Dynamic: requires-python

# DuckDB LLM UDF

![Python 3.8+](https://img.shields.io/badge/Python-3.8+-blue.svg)
![DuckDB 0.8.0+](https://img.shields.io/badge/DuckDB-0.8.0+-yellow.svg)
![License: MIT](https://img.shields.io/badge/License-MIT-green.svg)
[![PRs Welcome](https://img.shields.io/badge/PRs-welcome-brightgreen.svg)](CONTRIBUTING.md)

> Query your database in plain English—no SQL required. DuckDB LLM UDF translates natural language to SQL using large language models like OpenAI GPT-4 and Anthropic Claude.

## ✨ Overview

<p align="center">
  <img src="https://i.imgur.com/waxVImv.png" alt="Divider" width="600">
</p>

DuckDB LLM UDF bridges the gap between natural language and database queries. It's perfect for:

- **Data analysts** who need quick answers without writing complex SQL
- **Application developers** looking to add natural language query capabilities
- **SQL learners** who want to see how their questions translate to SQL
- **DuckDB users** who want to leverage the power of modern LLMs

This Python-based extension creates User-Defined Functions (UDFs) that let you query your database in plain English:

1. 📚 **Schema Analysis**: Automatically extracts your database schema metadata
2. 🤖 **LLM Integration**: Sends properly formatted prompts to OpenAI or Anthropic
3. 🔍 **SQL Generation**: Converts natural language to accurate SQL
4. ✅ **Safety First**: Asks for confirmation before executing any generated SQL
5. 📊 **Results Delivery**: Returns query results in standard DuckDB format

## 🚀 Installation

**Core features only:**
```bash
pip install duckdb-llm-udf
```

**With OpenAI support:**
```bash
pip install duckdb-llm-udf[openai]
```

**With Anthropic support:**
```bash
pip install duckdb-llm-udf[anthropic]
```

**With all providers:**
```bash
pip install duckdb-llm-udf[all]
```

Or install from source:
```bash
git clone https://github.com/yourusername/duckdb_llm_udf.git
cd duckdb_llm_udf
pip install -e .
```

### Dependencies

The package will automatically install the required dependencies:

- **DuckDB** ≥ 0.8.0
- **python-dotenv** ≥ 0.19.0 (for .env file support)
- **numpy** ≥ 1.21.0 (required for DuckDB UDFs)

Optional dependencies (installed with extras):
- **OpenAI**: for GPT model integration
- **Anthropic**: for Claude model integration

To use OpenAI or Anthropic, install the corresponding extra as shown above.
## 🔍 Usage

### Python API

```python
import os
from dotenv import load_dotenv
import duckdb
from duckdb_llm_udf import register_llm_functions

# Sample database creation for demonstration
def create_sample_database(conn):
    ...

# Usage example
if __name__ == "__main__":
    # Load .env file from current working directory
    load_dotenv()
    conn = duckdb.connect()
    register_llm_functions(conn)
    create_sample_database(conn)
    # Set API key in DuckDB (required for LLM queries)
    api_key = os.environ.get('OPENAI_API_KEY', '')
    if not api_key:
        print("Warning: No OpenAI API key found. Please set the OPENAI_API_KEY environment variable.")
        print("Alternatively, you can set it with: conn.execute(\"SELECT llm_configure('api_key', 'your-api-key')\")")
    else:
        conn.execute(f"SELECT llm_configure('api_key', '{api_key}')")

    # Ask a question in natural language
    query = "Show me the top 5 customers by total order amount"

    # Option 1: Generate SQL without executing (for review)
    sql = conn.execute(f"SELECT ask_llm('{query}', 'execute', 'false')").fetchone()[0]
    print(f"Generated SQL:\n{sql}")

    # Option 2: Execute directly with user confirmation
    result = conn.execute(f"SELECT ask_llm('{query}')").fetchall()
    print(result)

    # Direct Python function usage
    from duckdb_llm_udf.llm_udf import ask_llm
result = conn.execute(f"SELECT ask_llm('{query}')").fetchall()
print(result)

# Direct Python function usage
from duckdb_llm_udf.llm_udf import ask_llm

# Generate SQL without executing
sql = ask_llm(query, conn, execute=False)
print(f"Generated SQL:\n{sql}")

# Execute with confirmation
results = ask_llm(query, conn)
print(results)
```


## ⚙️ Configuration

You can configure DuckDB LLM UDF in three ways:

### 1. Environment Variables

```bash
# API Keys (required for corresponding provider)
OPENAI_API_KEY=sk-your-openai-key-here
ANTHROPIC_API_KEY=sk-ant-your-anthropic-key-here

# Configuration (all optional)
LLM_PROVIDER=openai        # Default: "openai", Alternatives: "anthropic"
LLM_MODEL=gpt-4o           # Default: "gpt-3.5-turbo" or "claude-3-sonnet-20240229"
LLM_TEMPERATURE=0.7        # Controls randomness (0.0-1.0)
LLM_MAX_TOKENS=4096        # Maximum tokens for LLM response
```

### 2. .env File (recommended for security)

Create a `.env` file in your project with the same variables as above:

```bash
# API Keys
OPENAI_API_KEY=sk-your-openai-key-here

# Configuration 
LLM_PROVIDER=openai
LLM_MODEL=gpt-4o
LLM_TEMPERATURE=0.3
```

Variables will automatically load when the package is imported. See `examples/.env.example` for a template.

### 3. Runtime Configuration

Python interface:
```python
conn.execute("SELECT llm_configure('api_key', 'your-api-key')")
conn.execute("SELECT llm_configure('model', 'gpt-4-turbo')")
```

## 🧪 Examples

The `examples/` directory contains ready-to-use examples:

- `basic_usage.py` - Core functionality demonstration
- `dotenv_usage.py` - Using environment variables with a .env file
- `test_extraction.py` - Test schema extraction without LLM API calls

### Example Questions

Here are some example questions you can ask your database:

- "Show me the top 5 customers by total order amount"
- "How many orders were placed in each month of 2023?"
- "What's the average order value by product category?"
- "Find customers who haven't made a purchase in the last 30 days"
- "What product has generated the most revenue?"

## 🔧 How It Works

<p align="center">
  <img src="https://i.imgur.com/waxVImv.png" alt="Divider" width="600">
</p>

1. **Schema Analysis**: When you call `ask_llm()`, the function extracts your database schema metadata (tables, columns, types, foreign keys, etc.)

2. **Prompt Engineering**: Your natural language question is combined with the schema into a carefully crafted prompt that helps the LLM understand the context

3. **LLM Query**: The prompt is sent to the configured LLM (OpenAI or Anthropic) with instructions to generate valid SQL

4. **SQL Generation**: The LLM produces SQL based on your schema and question

5. **Safety Check**: The generated SQL is presented to you for review and confirmation

6. **Execution**: If approved, the SQL is executed and the results are returned in standard DuckDB format

## 📝 License

This project is licensed under the MIT License - see the LICENSE file for details.

## 👥 Contributing

Contributions are welcome and appreciated! Here's how you can help:

- 🐛 **Report bugs** by opening an issue
- 💡 **Suggest features** or improvements
- 🧪 **Improve tests** or add new test cases
- 📚 **Improve documentation** to make it clearer or more complete
- 🧑‍💻 **Submit pull requests** with bug fixes or new features

Please see [CONTRIBUTING.md](CONTRIBUTING.md) for more details.

## 🙏 Credits

This project was built with:

- [DuckDB](https://duckdb.org/) - The in-process SQL OLAP database management system
- [OpenAI API](https://openai.com/api/) - For GPT model integration
- [Anthropic API](https://anthropic.com/) - For Claude model integration

## 📨 Contact

If you have any questions or need help, please open an issue on GitHub.
