SQL-Agent

SQL-Agent is a powerful command-line tool that converts natural language questions into SQL queries. It understands your database structure through a simple YAML configuration and securely connects to your database to fetch answers.

Installation

There are several ways to install the SQL-Agent.

Option 1: From PyPI (Recommended)

You can install the package directly from PyPI:

pip install SQL-Agent

Option 2: From a Wheel File

If you have a .whl file, you can install it directly using pip:

pip install /path/to/your/file/sql_agent-0.1.0-py3-none-any.whl

Option 3: From GitHub

Once the project is hosted on GitHub, you can also install it directly from the repository:

pip install git+https://github.com/[your-username]/[your-repo-name].git

Quick Setup Guide for Wheel Installation

If you installed SQL-Agent from a .whl file, follow these steps to get started:

1. Install the wheel file:

   pip install /path/to/your/file/sql_agent-0.1.0-py3-none-any.whl
   

2. Create your working directory:

   mkdir my-sql-agent-project
   cd my-sql-agent-project
   

3. Create your config.ini file in this directory:

   touch config.ini
   # Edit config.ini and add your database credentials and API keys
   

4. Create your schema YAML file:

   touch my_schema.yaml
   # Edit my_schema.yaml and define your database structure
   # You can reference the sample file included with the package
   

5. Run the agent:

   sql-agent "Your question here" "my_schema.yaml"
   

Configuration

Before using the agent, you need to configure your database connections and API keys.

1. Create config.ini

Create a file named config.ini in the directory where you will run the agent. This file will hold your credentials.

Important for Wheel Installation: When installing from a .whl file, you have two options for placing your config.ini:

1. Option 1 (Recommended): Place config.ini in your current working directory where you run the sql-agent command.
2. Option 2: Place config.ini in the package installation directory:
   • Find the package location: python -c "import structured_agent; import os; print(os.path.dirname(structured_agent.__file__))"
   • Copy config.ini to: <package-location>/utils/config.ini

The agent first checks for config.ini in your current directory, then falls back to the package's utils folder.

2. Add Your Configurations

Copy the necessary configuration sections into your config.ini and fill in your credentials. Below are some examples.

For Oracle Database:

[database_type]
db_type = oracle

[oracle_dev]
user = <your_oracle_user>
password = <your_oracle_password>
dsn = <your_oracle_dsn>

For Snowflake:

[database_type]
db_type = snowflake

[snowflake_etl_dev]
user = <your_user>
password = <your_password>
account = <your_account>
warehouse = <your_warehouse>
database = <your_database>
schema = <your_schema>
role = <your_role>

Model API Keys: The agent uses a language model to understand questions. Configure your model's API keys like this:

[model_4_o_mini_api_conf]
OAUTH_URL = <your_oauth_url>
OPENAI_API_BASE = <your_api_base>
OPENAI_API_VERSION = <your_api_version>
OPENAI_API_MODEL = <your_model>
OPENAI_API_TYPE = azure

3. Define Your Database Schema

The agent needs a YAML file to understand your database's tables, columns, and relationships.

Create a .yaml file (e.g., my_schema.yaml) and define your database structure as shown in the example below.

Example: school_schema.yaml

db_name: "SchoolDB"
schema_name: "public"
app_name: "Library"
db_type: "snowflake"
name: 
description:

tables:
  - table_name: "authors"
    columns:
      - column_name: "author_id"
        data_type: "INT"
        is_primary_key: true
      - column_name: "author_name"
        data_type: "VARCHAR(100)"
        description: "Name of the author"

  - table_name: "books"
    columns:
      - column_name: "book_id"
        data_type: "INT"
        is_primary_key: true
      - column_name: "title"
        data_type: "VARCHAR(255)"
        description: "Title of the book"
      - column_name: "author_id"
        data_type: "INT"
        is_foreign_key: true
        foreign_key_ref: "authors(author_id)"

foreign_key_relationships:
  - "books.author_id can be joined with authors.author_id"

verified_queries:
  - "Total number of books by each author"

Note on YAML File Location:

• Your schema YAML file (e.g., school_schema.yaml) can be placed anywhere you prefer.
• You'll provide the path to this file when running the sql-agent command.
• For convenience, you can place it in your current working directory and reference it by name only.
• A sample YAML file (sample_school_library_inventory.yaml) is included in the package for reference. You can find it at <package-location>/utils/sample_school_library_inventory.yaml.

Usage

Once your config.ini and schema YAML file are ready, you can run the agent from your terminal.

The command format is:

sql-agent "Your natural language question" "path/to/your_schema.yaml"

Example

From the directory containing your config.ini and school_schema.yaml, run:

sql-agent "How many books are there in the library?" "school_schema.yaml"

The agent will process your question, generate the appropriate SQL query, execute it, and return a natural language answer.

License

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