Spaces:
Sleeping
Sleeping
Download agents/sql_database_agent.py from Rishe7/Autodatabot: direct link, hf CLI and curl.
- Browser
- Download file 5.67 kB
-
https://huggingface.co/spaces/Rishe7/Autodatabot/resolve/main/agents/sql_database_agent.py
- Command line
-
hf download hf://spaces/Rishe7/Autodatabot/agents/sql_database_agent.py
-
curl -L -o sql_database_agent.py https://huggingface.co/spaces/Rishe7/Autodatabot/resolve/main/agents/sql_database_agent.py
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) | |