Autodatabot / agents /sql_database_agent.py
Rishe7's picture
Push project_final_demo contents
20decc1
Raw History Blame Contribute Delete
5.67 kB
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)