ramhemanth580/NL_2_SQL_Data_Analysis_Chatbot

1examples = [2  {3      "input": "List all customers in France with a credit limit over 20,000.",4      "query": "SELECT * FROM customers WHERE country = 'France' AND creditLimit > 20000;"5  },6  {7      "input": "Get the highest payment amount made by any customer.",8      "query": "SELECT MAX(amount) FROM payments;"9  },10  {11      "input": "Show product details for products in the 'Motorcycles' product line.",12      "query": "SELECT * FROM products WHERE productLine = 'Motorcycles';"13  },14  {15      "input": "Retrieve the names of employees who report to employee number 1002.",16      "query": "SELECT firstName, lastName FROM employees WHERE reportsTo = 1002;"17  },18  {19      "input": "List all products with a stock quantity less than 7000.",20      "query": "SELECT productName, quantityInStock FROM products WHERE quantityInStock < 7000;"21  },22  {23    'input':"what is price of `1968 Ford Mustang`",24    "query": "SELECT `buyPrice`, `MSRP` FROM products  WHERE `productName` = '1968 Ford Mustang' LIMIT 1;"   25  }, 26  {27    "input": "List products sold by order date.",28    "query": "SELECT productName , orderDate , DAYNAME(orderDate) AS 'DayName' FROM products INNER JOIN orderdetails ON products.productCode = orderdetails.productCode INNER JOIN Orders ON orderdetails.orderNumber = orders.orderNumber WHERE DAYNAME(Orders.orderDate) = 'MONDAY';"29  },30  {31    "input": "List the order dates in descending order for orders for the 1940 Ford Pickup Truck.",32    "query": "SELECT DISTINCT(products.productName), orders.orderDate FROM orders JOIN orderdetails ON orderdetails.orderNumber = orders.orderNumber JOIN products ON orderdetails.productCode = products.productCode WHERE productName = '1940 Ford Pickup Truck' ORDER BY orderDate DESC;"33  },34  {35    "input": "List the names of customers and their corresponding order number where a particular order from that customer has a value greater than $25,000.",36    "query": "SELECT customers.customerName, orders.orderNumber, SUM(orderdetails.priceEach * orderdetails.quantityOrdered) AS tot_value FROM customers JOIN orders ON customers.customerNumber = orders.customerNumber JOIN orderdetails ON orders.orderNumber = orderdetails.orderNumber GROUP BY customers.customerName, orders.orderNumber HAVING tot_value > 25000 ORDER BY customers.customerName;"37  },38  {39    "input": "For orders containing more than two products, report those products that constitute more than 50% of the value of the order.",40    "query": "SELECT orderNumber, productName, ProductsCount ,contribution FROM (SELECT orderNumber, productCode, (SELECT Count(*) FROM orderdetails WHERE OrderNumber = Main.orderNumber) As 'ProductsCount', quantityOrdered*priceEach As 'Product Value', (quantityOrdered*priceEach / (SELECT SUM(quantityOrdered*priceEach) FROM orderdetails WHERE orderNumber = Main.orderNumber ))*100 As 'Contribution' FROM orderdetails Main ORDER BY orderNumber) DataTable INNER JOIN Products ON Products.productCode = DataTable.productCode WHERE ProductsCount > 2 AND Contribution > 50;"41  },42  {43    "input": "List all the products purchased by Herkku Gifts.",44    "query": "SELECT productName FROM products INNER JOIN orderdetails od on products.productCode = od.productCode INNER JOIN orders o on od.orderNumber = o.orderNumber INNER JOIN customers c on o.customerNumber = c.customerNumber WHERE c.customerName = 'Herkku Gifts';"45  },46  {47    "input": "Find products containing the name 'Ford'.",48    "query": "SELECT productName AS 'Products' FROM Products WHERE productName LIKE '%Ford%';"49  },50  {51    "input": "List products ending in 'ship'.",52    "query": "SELECT productName FROM products WHERE productName LIKE '%ship';"53  },54  {55    "input": "Report the number of customers in Denmark, Norway, and Sweden.",56    "query": "SELECT customerName FROM Customers WHERE country IN ('Denmark','Norway','Sweden');"57  },58  {59    "input": "What are the products with a product code in the range S700_1000 to S700_1499",60    "query": "SELECT productCode,productName FROM Products WHERE RIGHT(productCode,4) BETWEEN 1000 AND 1499 ORDER BY RIGHT(productCode,4);"61  },62  {63    "input": "Which customers have a digit in their name?",64    "query": "SELECT customerName FROM Customers WHERE customerName RLIKE '[0-9]';"65  },66  {67    "input": "List the names of employees called Dianne or Diane.",68    "query": "SELECT CONCAT(firstName,' ',lastName) AS 'Employee Name' FROM Employees WHERE lastName RLIKE 'Dianne|Diane' OR firstName RLIKE 'Dianne|Diane';"69  },70  {71    "input": "List the products containing ship or boat in their product name.",72    "query": "SELECT productName FROM Products WHERE productName RLIKE 'ship|boat';"73  },74  {75    "input": "List the products with a product code beginning with S700.",76    "query": "SELECT productCode, productName FROM Products WHERE productCode LIKE 'S700%';"77  },78  {79    "input": "Find products containing the name 'Ford'.",80    "query": "SELECT productName As 'Products' FROM Products WHERE productName LIKE '%Ford%';"81  },82  {83    "input": "List products ending in 'ship'.",84    "query": "SELECT productName FROM products WHERE productName LIKE '%ship';"85  },86  {87    "input": "Report the number of customers in Denmark, Norway, and Sweden.",88    "query": "SELECT customerName FROM Customers WHERE country IN ('Denmark','Norway','Sweden');"89  },90  {91    "input": "what is the minimum payment received ?",92    "query": "SELECT min(amount) As 'Minimum Payment' FROM payments;"93  }94]95 96from langchain_community.vectorstores import Chroma97from langchain_core.example_selectors import SemanticSimilarityExampleSelector98from langchain.embeddings import HuggingFaceEmbeddings99import google.generativeai as genai100import streamlit as st101import os102from dotenv import load_dotenv103 104load_dotenv()105load_dotenv()106genai.configure(api_key=os.environ["GOOGLE_API_KEY"])107# Access the value of Huggingface_API_KEY108HF_API_TOKEN = os.getenv("HF_API_TOKEN")109#embeddings = HuggingFaceEmbeddings(huggingfacehub_api_token=HF_API_TOKEN,model_name="sentence-transformers/all-MiniLM-L6-v2")110#embeddings = HuggingFaceEmbeddings(model_name="sentence-transformers/all-MiniLM-L6-v2")111 112from langchain_google_genai import GoogleGenerativeAIEmbeddings113 114class CustomGoogleGenerativeAIEmbeddings:115    def __init__(self, model, task_type=None):116        # Initialize the GoogleGenerativeAIEmbeddings with the model and task type117        self.embeddings = GoogleGenerativeAIEmbeddings(model=model, task_type=task_type)118        119    def __call__(self, input):120        # Use the embed_query method for single inputs121        return self.embeddings.embed_query(input)122 123    def embed_query(self, text):124        # Use the embed_query method to generate an embedding for a single piece of text125        return self.embeddings.embed_query(text)126 127    def embed_documents(self, documents):128        # Use the embed_documents method to generate embeddings for multiple pieces of text129        return self.embeddings.embed_documents(documents)130 131# Usage132model = "models/embedding-001"  # Replace with your actual model name133task_type = "retrieval_document"  # Replace with your actual task type if needembeddings = CustomGoogleGenerativeAIEmbeddings(model=model, task_type=task_type)134 135embeddings = CustomGoogleGenerativeAIEmbeddings(model=model, task_type=task_type)136 137vectorstore = Chroma()138vectorstore.delete_collection()139 140@st.cache_resource141def get_example_selector():142    example_selector = SemanticSimilarityExampleSelector.from_examples(143        examples,144        embeddings,145        vectorstore,146        k=4,147        input_keys=["input"],148    )149    return example_selector