Spaces:
Sleeping
Sleeping
File size: 5,672 Bytes
20decc1 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 | 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)
|