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)