import json from tools.sql import get_db_metadata import pandas as pd import re from langchain_core.prompts import PromptTemplate from langchain_core.output_parsers import JsonOutputParser, StrOutputParser class SQLDatabaseAgent: def __init__(self, llm, conn, smart_filtering=True): self.llm = llm self.conn = conn self.smart_filtering = smart_filtering self.raw_metadata = get_db_metadata(conn) self.filtered_metadata = None def smart_filter_schema(self, user_query): """ Filters the full SQL metadata based on the user's query using LLM. """ if not self.smart_filtering: self.filtered_metadata = self.raw_metadata return self.filtered_metadata print("\nraw_metadata = ", self.raw_metadata) filter_prompt = PromptTemplate( template=""" You are an expert database assistant. Based on the user's question: "{user_query}" And the complete database metadata (in JSON): {metadata_json} Return ONLY the relevant parts of this metadata needed to answer the question. - Keep the structure: "schemas" -> "tables" -> "columns". - Drop schemas or tables if they aren't needed. - Keep only the most relevant columns, unless unsure. - Output should be a **valid JSON object** only, without any explanation or extra text. """, input_variables=["user_query", "metadata_json"] ) chain = filter_prompt | self.llm | JsonOutputParser() filtered = chain.invoke({ "user_query": user_query, "metadata_json": json.dumps(self.raw_metadata) }) self.filtered_metadata = filtered return filtered def get_filtered_metadata(self): return self.filtered_metadata or self.raw_metadata def generate_sql_query(self, user_query): """ Generates a SQL query using the LLM based on filtered schema and user query. """ self.metadata = self.get_filtered_metadata() self.user_query = user_query print("\get_filtered_metadata = ", self.metadata) sql_prompt = PromptTemplate( input_variables=["user_query", "metadata_json"], template=""" You are a SQL expert. Given the following database metadata and user question, write a SQL query. USER QUESTION: {user_query} DATABASE METADATA (JSON): {metadata_json} GUIDELINES: - Please correct any typos if there are any in {user_query} including column names, upto best of your knowledge. - Generate queries which are compatiable with sqlite. - Return only the SQL query in ```sql ... ``` block. - Do NOT include explanations or comments. - Use only the columns and tables present in the metadata. - Make sure you use the right table name. - Do NOT modify the schema or create/drop tables. Respond with only the query in a single ```sql block. """ ) chain = sql_prompt | self.llm | StrOutputParser() sql_query = chain.invoke({ "user_query": self.user_query, "metadata_json": json.dumps(self.metadata) }) return sql_query def fix_sql_database_code(self, sql_query, error): fix_prompt = PromptTemplate( input_variables=["sql_query", "error"], template=""" You are an expert SQL query fixer designed to clean and optimize SQL queries for SQLite databases. Your job is to fix the following SQL query so it can run on a SQLite database. BROKEN SQL QUERY: {sql_query} LAST KNOWN ERROR: {error} USER QUESTION: {user_query} DATABASE METADATA (JSON): {metadata_json} GUIDELINES: - Looking at the last error, try to return the correct the sql query. - Remove unnecessary code blocks or delimiters like ```sql, ```, or backticks (`). - Provide only the corrected SQL query without any extra text or explanation. """ ) chain_fixer = fix_prompt | self.llm | StrOutputParser() fixed_sql_query = chain_fixer.invoke({ "sql_query": sql_query, "error": str(error), "user_query": self.user_query, "metadata_json": json.dumps(self.metadata) }) return fixed_sql_query def run_sql_query(self, sql_query): """ Executes the SQL query on the current database and returns a pandas DataFrame. """ # Extract SQL code from the ```sql ... ``` block #print("\nsql_query = ", sql_query) match = re.search(r"```sql\s*(.*?)\s*```", sql_query, re.DOTALL) #print("\nmatch = ",match) if match: query = match.group(1).strip() else: query = sql_query.strip() # fallback if ```sql block isn't used #print("\nquery = ", query) try: return pd.read_sql_query(query, self.conn) except Exception as e: print("yes i am here") attempt = 0 current_query = query while attempt < 3 or e: try: res = pd.read_sql_query(self.fix_sql_database_code(query, e), self.conn) e=None return res except Exception as e: attempt=+1 print("attempt = ", attempt=+1) pass return pd.read_sql_query(self.fix_sql_database_code(query, e), self.conn)