QueryInsight

QueryInsight is an AI-powered SQL agent that converts natural language questions into SQL queries, executes them against a PostgreSQL database, generates human-readable answers, and dynamically renders visualizations using Plotly charts in a web interface. The visualizations are created via an LLM-generated JavaScript renderChart function, tailored to the user's query and answer context.

---

🔍 Features

• Connects to a PostgreSQL database with schema awareness.
• Accepts natural language questions (e.g., "Show sales by region as a pie chart").
• Uses OpenAI LLM to:
  • Convert questions to SQL queries.
  • Generate human-readable answers from query results.
  • Create a JavaScript renderChart function for Plotly visualizations.
• Supports user-specified chart types (e.g., bar, pie, line, scatter, table) or infers them from the query and answer.
• Displays results in a web interface with SQL, answers, charts, and raw data tables.

---

🛠 Tech Stack

• Backend: Python (Flask)
• Database: PostgreSQL
• LLM: OpenAI API
• Visualization: Plotly (with dynamically generated JavaScript)
• Frontend: HTML, JavaScript, jQuery

---

⚙️ Setup

1. Clone the repository:

git clone https://github.com/your-repo/queryinsight.git
cd queryinsight

2. Create and activate a virtual environment:

python -m venv venv
source venv/bin/activate  # On Windows: venv\Scripts\activate

3. Install dependencies:

pip install -r requirements.txt

4. Create a .env file in the root directory:

DATABASE_URL=postgresql://user:password@localhost:5432/dbname
OPENAI_API_KEY=your_openai_api_key

5. Update the database schema:

Edit app/utils.py to match your PostgreSQL database schema.

6. Run the application:

python main.py

Access the web interface at: http://localhost:5000

---

💡 Usage

Enter a natural language question in the web form (e.g.,

• "Show total sales by region as a bar chart"
• "What is the trend of sales over time?")

The app will display:

• The generated SQL query.
• A human-readable answer summarizing the results.
• A dynamically rendered Plotly chart (based on the user query and answer).
• Raw query results in a table.

You can specify a chart type (e.g., "as a pie chart") or let the LLM infer the best visualization.

---

📁 Folder Structure

queryinsight/
├── app/
│   ├── sql_generator.py      # LLM to generate SQL queries
│   ├── query_runner.py       # Executes SQL against PostgreSQL
│   ├── answer_builder.py     # LLM to generate human-readable answers
│   ├── visualizer.py         # LLM to generate JavaScript renderChart function
│   └── utils.py              # Helper functions (e.g., schema loading)
├── templates/
│   └── index.html            # Web interface for queries and results
├── static/
│   └── chart.js              # JavaScript for rendering Plotly charts
├── main.py                   # Flask app entry point
├── requirements.txt          # Dependencies
├── .env                      # Environment variables (not tracked)
├── .gitignore                # Git ignore file
└── README.md                 # Project documentation

---

🔎 Example Queries

• "Show sales by region as a bar chart" → Generates a bar chart with sales per region.
• "What is the distribution of sales by product?" → Infers a pie chart for proportions.
• "Show sales trends over time" → Generates a line chart for temporal data.
• "List all products" → Displays a table if no clear chart is inferred.

---

📝 Notes

• Ensure your PostgreSQL database is running and accessible with the credentials in .env.
• The OpenAI API key requires a paid account.
• Update the schema in app/utils.py to match your database structure for accurate SQL generation.
• The renderChart function is dynamically generated and executed in the browser.
• To extend chart types, modify the LLM prompt in visualizer.py to support additional Plotly configurations (e.g., histograms, box plots).

---