NLPeXperts’: A Tabular Data Analysis Tool
Chatbot and Graph Plotter

Application Purpose
Bridge the gap between data and users by making the tool user-friendly and intuitive, minimizing the need for extensive knowledge of database structures or SQL.
Integrated data visualization tools to translate complex data into clear charts and graphs, facilitating exploration and understanding.
The tool can handle and analyze large datasets efficiently without performance degradation ensuring privacy.
Challenges faced while using Tabular data
As data volumes grow, querying complex tabular (SQL) databases can become slow and challenging. Writing efficient SQL queries and interpreting large datasets can be cryptic.
Tabular databases often lack a user-friendly interface, requiring users to understand the table structure and write complex queries to access data.
Complex queries, large datasets, and unfriendly interfaces delay and hinder decision-making. A streamlined solution is needed to provide timely insights through a user-friendly interface.

Architecture and Planning

CHATBOT
Steps involved when a user sends a natural language query in the chatbot:

1. Chatbot sends the user query as a string to the backend( flask based python app).
2. In the python backend, I have created a function that can convert this string into a prompt, like this: “Convert to SQL” + string + “existing tables are:” + table_col_combo + “last chat from the user is:” + history.
3. The table_col_combo is a python function that can accumulate all the table names and the colulmns of all the tables.
4. The history contains the last message sent by the user as a String.
5. This prompt, is then sent to Gemini api, A LLM, for generating a SQL query which is extracted from the output of the gemini function.
6. This extracted output, a SQL query is then passed to a SQL Server which is running in the backend.
7. After running the SQL query in the backend, the backend responds to a success message or an output of the query- which is returned as output from the chatbot.

GRAPH PLOTTER
Steps involved when a user sends a natural language query in the graph plotter:

1. Graph Plotter sends the user query as a string to the backend( flask based python app) along with the table selected for generating graph.
2. In the python backend, I have created a function that can convert this string into a prompt, like this: “Convert to Python code” + string + “existing tables are:” + table_col_combo.
3. The table_col_combo is a python function that can accumulate all the table names and the colulmns of all the tables.
4. This prompt, is then sent to Gemini api, A LLM, for generating a python code which is extracted from the output of the gemini function.
5. This extracted output, that is the Python code is then saved as a temp.py file in the backend.
6. Then a function is called to start a subprocess that can run a python code as a command in a terminal.
7. After running the python code in the backend, the backend responds a success message and a graph image is saved in the backend- which is returned as an image object on the graph plotter interface.

Features of the Tool
Table images can be uploaded which can also be handwritten.
Tables can also be uploaded in CSV format.
Tables can be downloaded from the table modal to the device.
Users can also input messages using their voices.
User-friendly interface with dark mode feature.
The existing table list can be viewed by clicking on the button.
Sample questions are listed to assist users with a feature to copy them.
Can perform statistical analysis like calculating averages.
The tabular Data support history mechanism.
The tabular Data has multilingual support.
The tool can answer complex queries relating to more than one table
Select the table and specify the parameters to plot the graph or chart of any type
Download the plot that has been generated by the tool to the device.
A guide to aid users about the type of graphs.
Tables can be uploaded as images or as CSV file and can be viewed using show existing tables.
The chatbot and graph plotter is made responsive.

Creation Process
Purpose and Vision: The core idea behind the development of NLPeXperts was to create a comprehensive tool that bridges the gap between users and complex datasets by simplifying the process of accessing and analyzing tabular data. The goal was to make data analysis intuitive for users with little to no knowledge of SQL or database structures, while still offering advanced visualization features to aid in decision-making.
Challenges Identified: The primary challenge with traditional tabular databases was the steep learning curve associated with writing complex SQL queries. This issue was compounded by the large datasets users often needed to work with, leading to slow query processing and cryptic data outputs that hindered timely insights. A streamlined solution was necessary to simplify database access, automate query generation, and provide a user-friendly interface.
Solution Overview:
Chatbot for Querying: A natural language chatbot was implemented to allow users to input queries in plain language. This chatbot converts user queries into SQL commands, significantly lowering the barrier for interacting with large datasets. The chatbot works by sending user queries to an AI-powered backend, which interprets the query, converts it into SQL, and retrieves the corresponding data.
Graph Plotter for Data Visualization: Alongside the chatbot, a graph plotter was developed to provide data visualization capabilities. Users can generate graphs by describing the type of graph or the data they want to visualize. The tool converts the user’s input into Python code, executes it in the backend, and returns the plotted graph as an image, making it easy to understand complex datasets.
Development Stages:
Backend Infrastructure: The backend was built using Flask to handle natural language inputs and interface with the database. A crucial component was the integration of the Gemini API, a large language model (LLM) used for generating SQL queries and Python graphing code based on user input. This allowed the tool to automate the query and visualization process while maintaining accuracy and performance.
Query and Visualization Automation:
For SQL Queries: The chatbot automatically generates SQL queries by combining the user’s natural language input with a pre-programmed database schema.
For Graph Generation: The graph plotter dynamically generates Python code to plot the graph based on the structure of the data tables. This approach eliminated the need for users to write code themselves.
Database Connectivity: An SQLite database was chosen due to its lightweight nature and ease of integration. The database connection was managed through a series of functions that retrieved table names, column details, and sample data, ensuring that users could interact with the data without understanding its underlying structure.
User Interface and Features: The tool was designed with a user-friendly interface, including features such as file uploads, voice input, dark mode, and the ability to download tables and graphs. Multilingual support and a history mechanism were implemented to enhance user interaction.
Key Components added for enhanced performance:
Table and Column Recognition: Functions were created to dynamically retrieve table and column data from the SQLite database, allowing for real-time query generation.
Code Extraction: Python and SQL code blocks were extracted from the LLM’s responses and executed in real-time, providing immediate feedback to the user.
History Management: A history feature was implemented to store user interactions, enabling the tool to handle follow-up questions effectively.
Deployment and User Experience: The final tool was deployed with responsive web pages, allowing users to interact with the chatbot and graph plotter from any device. Routes were created to handle chatbot queries, graph plotting, file uploads, and data downloads, making the tool versatile and accessible.

CODE BLOCKS and their purpose

1. combo():
   Connects to the SQLite database mydatabase.db and retrieves the names of all the tables except the SQLite-specific sqlite_sequence.
   It then fetches column names for each table using the PRAGMA table_info() SQL command and forms a string indicating which columns belong to which table.
   Returns a list of these table and column combinations.
2. table_data(selected_table_graphy):
   Checks whether the table selected_table_graphy exists in the SQLite database.
   If the table exists, it retrieves the column names and a sample of the first row of data to provide a reference.
   If the table doesn’t exist, it returns a message indicating that the table isn’t present.
   Returns a combination of column names and a sample of data or an error message if the table is missing.
3. question_generator():
   Uses Google Generative AI (through the gemini-1.5-flash model) to generate 10–15 natural language questions about the tables and columns present in the database.
   The prompt is structured to ensure the AI uses actual tables and columns, rather than making up new ones.
   The function returns a list of questions generated by the AI.
4. collector(chat):
   Inserts the given chat input into the history list at the beginning (index 0).
   Used to maintain a history of chats or queries.
5. extract_code_block(text):
   Extracts Python code from the input text by searching for specific indicators (python, Python, PYTHON, or block markers like \n).
   Returns the cleaned code block or None if no valid block is found.
6. chatbot(input):
   Processes user input to generate SQLite commands based on the structure of the database tables.
   Uses Google Generative AI (via the gemini-pro model) to generate SQL commands. The prompt asks for the most suitable SQL query based on the input and database structure.
   The generated SQL commands are executed via the execute_sql() function, and the responses are collected and returned.
7. graphplot(input):
   Uses Google Generative AI to generate Python code that plots graphs based on the structure of the data.
   The prompt instructs the AI to use columns with the highest correlation for graph generation and restricts the AI to certain libraries (Matplotlib, Pandas, and Numpy).
   The generated Python code is executed using the subproc() function, and the result (plot image) is saved as a file.
8. process_table_download(table_name):
   Downloads the content of the specified table (table_name) from the SQLite database and saves it as a CSV file (output.csv).
   Returns a success message after completing the download.
9. get_db_connection():
   Establishes and returns a connection to the SQLite database.
10. execute_sql(sql_command):
    Takes an SQL command as input and executes it using the SQLite cursor.
    Handles multiple SQL commands by splitting the input string and processing them individually.
    If the SQL command is a SELECT query, it fetches and returns the query result. For other commands, it returns the list of available tables.
    Commits the changes and closes the connection.
11. subproc(new_file_content):
    Saves the given new_file_content (which is Python code) into a dynamically created Python file (dynamic_script.py).
    Runs this script using the Python interpreter in a subprocess and captures the output or errors generated during the script's execution.
    Prints the result of the execution (stdout and stderr).
12. upload():
    Handles file uploads, primarily images, from the user. The image is read and passed to Google Generative AI to extract SQL commands.
    The AI generates an SQLite command to add table headers and input data from the image, which is then executed using execute_sql().
    Returns the response from executing the generated SQL command.
13. browse_file():
    Handles CSV file uploads, strips any leading/trailing spaces from the column headers, and stores the CSV data as a table in the SQLite database.
    Calls question_generator() after the upload to generate new questions based on the updated database structure.
    Returns a success message after uploading the file.
14. show_file():
    Retrieves the list of all available tables in the SQLite database, then fetches metadata about each table's structure (columns, etc.).
    Stores the fetched data for further use.

Routes on Website
/: Renders the homepage (index.html) and passes the AI-generated questions.
/graphs: Renders a page displaying available graphs (graphy.html).
/about: Renders an about page (about.html).
/get: Receives user input and responds with generated chatbot responses.
/graphy: Processes graph requests from the user and generates the required plot.
/listTable: Lists available tables in the SQLite database.
/selectTable: Allows users to select a table for downloading or further interaction.
/downloadTable: Provides the CSV download of a selected table.
/downloadGraph: Downloads the generated graph as a PNG image.
/addimg: Uploads an image and generates corresponding SQL commands to add data.
/addtable: Uploads a CSV file, processes it into the database, and updates the question generator.
/show: Fetches metadata about all tables in the SQLite database.