CHAPTER 5
IMPLEMENTATION AND RESULT
5.1.1 REQUIREMENTS
The following packages were installed by using command
!pip install -r [Link]
● gunicorn
● Django>=4.2
● openai
● langchain>=0.1.13
● langchain-community>=0.0.26
● langchain-core>=0.1.25
● sqlalchemy>=2.0
● pymysql
● djangorestframework
● django-cors-headers
5.1.2 BACKEND IMPLEMENTATION CODE
Langchain_core.py
import os
import re
from functools import lru_cache
from [Link] import settings
from langchain_community.chat_models import ChatOpenAI
from langchain_community.utilities import SQLDatabase
from langchain_community.chat_message_histories import ChatMessageHistory
from [Link] import PromptTemplate
from [Link] import RunnableSequence
from langchain_core.[Link] import RunnableWithMessageHistory
from langchain_core.output_parsers import StrOutputParser
# === Setup ===
[Link]["OPENAI_API_KEY"] = settings.OPENAI_API_KEY
# === Model ===
llm = ChatOpenAI(model_name="gpt-4", temperature=0)
20
# === Session Memory ===
store = {}
def get_memory(session_id):
if session_id not in store:
store[session_id] = ChatMessageHistory()
return store[session_id]
# === DB Connection ===
db_user = settings.DB_USER
db_password = settings.DB_PASSWORD
db_host = settings.DB_HOST
db_name = settings.DB_NAME
db_port = settings.DB_PORT
db_url =
f"mysql+pymysql://{db_user}:{db_password}@{db_host}:{db_port}/{db_name}"
db = SQLDatabase.from_uri(db_url)
# === Schema Description Caching ===
@lru_cache()
def get_schema_description():
return db.get_table_info()
# === Few-shot SQL Examples ===
EXAMPLES = """
Examples:
Q: List all customers from France.
A:
SELECT customerName FROM customers WHERE country = 'France';
Q: Which product has the highest price?
A:
SELECT productName FROM products ORDER BY buyPrice DESC LIMIT 1;
Q: What is the total payment amount received?
A:
SELECT SUM(amount) FROM payments;
Q: How many orders were placed by each customer?
A:
SELECT customerNumber, COUNT(*) as order_count FROM orders GROUP BY
customerNumber;
"""
21
# === SQL Prompt Builder ===
def build_sql_prompt():
schema = get_schema_description()
return PromptTemplate.from_template(
f"""You are an expert SQL assistant.
Use the schema and examples below to write a valid MySQL query for the user's
question.
Only return the raw SQL query without explanation or markdown formatting.
Schema:
{schema}
{EXAMPLES}
Chat History:
{{chat_history}}
User: {{question}}
SQL:"""
)
# === Strip markdown or extra explanation from SQL ===
def clean_sql_output(sql):
sql = [Link]()
sql = [Link](r"```sql|```", "", sql).strip()
sql = [Link](r'\n(?=SELECT|WITH|INSERT|UPDATE|DELETE)', sql,
maxsplit=1)[-1]
return [Link]()
# === Correct Common SQL Mistakes ===
def correct_common_sql_errors(query):
corrections = {
"customer_id": "customerNumber",
"order_id": "orderNumber",
"order_details": "orderdetails",
"product_id": "productCode",
"[Link]": "[Link]"
}
for wrong, right in [Link]():
query = [Link](wrong, right)
return query
# === Execute SQL ===
def execute_query(query):
22
try:
print(f"\nExecuting SQL Query:\n{query}")
result = [Link](query)
return result if result else {"info": "No results found."}
except Exception as e:
print(f"\nSQL Execution Error: {e}")
return {"error": f"Query failed: {str(e)}"}
# === Rephrase Results ===
answer_prompt = PromptTemplate.from_template(
"""Given the user question, SQL query, and result, return a helpful,
user-friendly answer.
Question: {question}
SQL Query: {query}
SQL Result: {result}
Answer:"""
)
rephrase_chain = answer_prompt | llm | StrOutputParser()
# === Final Processor ===
def process_question(question, session_id="user-1"):
sql_prompt = build_sql_prompt()
memory_chain = RunnableWithMessageHistory(
RunnableSequence(sql_prompt | llm | StrOutputParser()),
get_session_history=get_memory,
input_messages_key="question",
history_messages_key="chat_history"
)
# 1. Generate SQL
raw_sql = memory_chain.invoke(
{"question": question},
config={"configurable": {"session_id": session_id}}
)
clean_query = clean_sql_output(raw_sql)
clean_query = correct_common_sql_errors(clean_query)
# 2. Execute SQL
sql_result = execute_query(clean_query)
# 3. Rephrase answer
return rephrase_chain.invoke({
"question": question,
23
"query": clean_query,
"result": str(sql_result)
})
[Link]
from [Link] import path
from .views import AskQuestion
urlpatterns = [
path("ask/", AskQuestion.as_view(), name="ask-question"),
]
[Link]
from rest_framework.views import APIView
from rest_framework.response import Response
from .langchain_logic import process_question
class AskQuestion(APIView):
def post(self, request):
question = [Link]("question")
session_id = [Link]("session_id", "default-session")
answer = process_question(question, session_id)
return Response({"answer": answer})
5.1.3 FRONTEND IMPLEMENTATION CODE
[Link]
import { useState, useEffect, useRef } from "react";
import axios from "axios";
import {
Send,
Trash2,
Database,
Code,
MessageSquare,
ChevronRight,
Search,
AlertCircle,
24
AlertTriangle,
CheckCircle,
HelpCircle,
Loader,
} from "lucide-react";
type Message = {
role: "user" | "assistant";
text: string;
timestamp: Date;
status?: "success" | "error" | "pending";
};
export default function Home() {
const [messages, setMessages] = useState<Message[]>([]);
const [question, setQuestion] = useState("");
const [loading, setLoading] = useState(false);
const [showWelcome, setShowWelcome] = useState(true);
const messagesEndRef = useRef<HTMLDivElement>(null);
const textareaRef = useRef<HTMLTextAreaElement>(null);
const [isExpanded, setIsExpanded] = useState(false);
const [theme, setTheme] = useState<"light" | "dark">("light");
// Sample suggestions for the welcome screen with categories
const suggestions = [
{ text: "Show all users who signed up last week", category: "Users" },
{
text: "Find products with inventory below 10 units",
category: "Inventory",
},
{
text: "What are the top 5 most ordered products?",
category: "Analytics",
},
{ text: "Show transactions over $1000", category: "Transactions" },
{ text: "List tables in the database", category: "Schema" },
{ text: "Find customers with no orders", category: "Relationships" },
];
// Auto-resize textarea as user types
useEffect(() => {
if ([Link]) {
[Link] = "56px";
[Link] = `${[Link](
25
[Link],
150
)}px`;
}
}, [question]);
// Scroll to bottom whenever messages change
useEffect(() => {
scrollToBottom();
}, [messages]);
const scrollToBottom = () => {
[Link]?.scrollIntoView({ behavior: "smooth" });
};
const askQuestion = async (text = question) => {
if (![Link]()) return;
const newMessage: Message = {
role: "user",
text,
timestamp: new Date(),
};
const updatedMessages = [...messages, newMessage];
setMessages(updatedMessages);
setLoading(true);
setQuestion("");
setShowWelcome(false);
try {
const res = await [Link](
"[Link]
{
question: text,
session_id: "frontend-user",
}
);
setMessages([
...updatedMessages,
{
role: "assistant",
text: [Link],
26
timestamp: new Date(),
status: "success",
},
]);
} catch (err) {
setMessages([
...updatedMessages,
{
role: "assistant",
text: "Something went wrong. Please try again.",
timestamp: new Date(),
status: "error",
},
]);
} finally {
setLoading(false);
}
};
const handleKeyDown = (e: [Link]) => {
if ([Link] === "Enter" && ![Link]) {
[Link]();
askQuestion();
}
};
const useSuggestion = (suggestion: string) => {
askQuestion(suggestion);
};
const formatTime = (date: Date) => {
return [Link]([], { hour: "2-digit", minute: "2-digit" });
};
const clearConversation = () => {
setMessages([]);
setShowWelcome(true);
};
const toggleTheme = () => {
setTheme(theme === "light" ? "dark" : "light");
};
return (
27
<div
className={`flex flex-col h-screen ${
theme === "dark" ? "bg-gray-900 text-white" : "bg-gray-50 text-gray-900"
}`}
>
{/* Header with better branding */}
<header
className={`${
theme === "dark"
? "bg-gray-800 border-gray-700"
: "bg-white border-gray-200"
} border-b px-6 py-4 flex items-center justify-between shadow-sm`}
>
<div className="flex items-center space-x-3">
<div className="bg-gradient-to-r from-blue-600 to-purple-600 text-white
p-2 rounded-lg">
<Database size={24} />
</div>
<h1 className="text-2xl font-bold bg-gradient-to-r from-blue-600
to-purple-600 text-transparent bg-clip-text">
SQL Assistant
</h1>
</div>
<div className="flex items-center space-x-4">
<button
onClick={toggleTheme}
className={`${
theme === "dark"
? "text-gray-300 hover:text-white"
: "text-gray-500 hover:text-gray-700"
} flex items-center gap-1 text-sm px-3 py-1 rounded-md
hover:bg-opacity-10 hover:bg-gray-500`}
☀️ 🌙
>
{theme === "dark" ? " Light" : " Dark"}
</button>
<button
onClick={clearConversation}
className={`${
theme === "dark"
? "text-gray-300 hover:text-white"
: "text-gray-500 hover:text-gray-700"
} flex items-center gap-1 text-sm px-3 py-1 rounded-md
hover:bg-opacity-10 hover:bg-gray-500`}
>
28
<Trash2 size={16} />
Clear chat
</button>
</div>
</header>
{/* Main chat area with improved styling */}
<main
className={`flex-1 overflow-y-auto p-6 ${
theme === "dark" ? "bg-gray-900" : "bg-gray-50"
}`}
>
<div className="max-w-4xl mx-auto space-y-6">
{showWelcome ? (
<div
className={`${
theme === "dark"
? "bg-gray-800 border-gray-700"
: "bg-white border-gray-200"
} rounded-xl shadow-md p-6 mb-6 border`}
>
<h2
className={`text-2xl font-bold ${
theme === "dark" ? "text-white" : "text-gray-800"
} mb-2`}
>
Welcome to SQL Assistant!
</h2>
<p
className={`${
theme === "dark" ? "text-gray-300" : "text-gray-600"
} mb-6`}
>
Ask me anything about your database or try one of these
examples:
</p>
<div className="grid grid-cols-1 md:grid-cols-2 gap-3">
{[Link]((suggestion, index) => (
<button
key={index}
className={`p-3 ${
theme === "dark"
? "border-gray-700 hover:bg-gray-700 text-gray-200"
: "border-gray-200 hover:bg-gray-50 text-gray-700"
29
} border rounded-lg text-left hover:border-blue-300 transition-all flex
items-start space-x-3`}
onClick={() => useSuggestion([Link])}
>
<div
className={`mt-1 ${
theme === "dark" ? "text-blue-400" : "text-blue-500"
}`}
>
<ChevronRight size={16} />
</div>
<div>
<div
className={`text-sm font-medium ${
theme === "dark" ? "text-blue-400" : "text-blue-600"
}`}
>
{[Link]}
</div>
<div>{[Link]}</div>
</div>
</button>
))}
</div>
</div>
) : null}
{[Link] === 0 && !showWelcome ? (
<div className="flex items-center justify-center h-64">
<div
className={`text-center ${
theme === "dark" ? "text-gray-400" : "text-gray-500"
}`}
>
<MessageSquare size={48} className="mx-auto mb-4 opacity-50" />
<p>No messages yet. Ask something about your database!</p>
</div>
</div>
) : null}
{[Link]((msg, i) => (
<div
key={i}
className={`flex ${
30
[Link] === "user" ? "justify-end" : "justify-start"
}`}
>
<div
className={`rounded-2xl p-4 max-w-3xl whitespace-pre-wrap
shadow-sm
${
[Link] === "user"
? "bg-gradient-to-r from-blue-500 to-blue-600 text-white"
: theme === "dark"
? "bg-gray-800 border-gray-700 text-gray-100"
: "bg-white border border-gray-200 text-gray-800"
}
`}
>
<div className="flex justify-between items-center mb-2">
<div className="flex items-center">
{[Link] === "user" ? (
<span className="font-semibold flex items-center">
You
</span>
):(
<span className="font-semibold flex items-center">
<Database size={16} className="mr-1" /> SQL Assistant
</span>
)}
</div>
<span
className={`text-xs ${
[Link] === "user"
? "opacity-75"
: theme === "dark"
? "text-gray-400"
: "text-gray-500"
}`}
>
{formatTime([Link])}
</span>
</div>
<div
className={`${[Link] === "assistant" ? "prose" : ""} ${
theme === "dark" && [Link] === "assistant"
? "prose-invert"
: ""
31
}`}
>
{[Link]}
</div>
{[Link] === "error" && (
<div className="flex items-center text-red-500 text-sm mt-2">
<AlertTriangle size={14} className="mr-1" /> Error: Unable
to process request
</div>
)}
</div>
</div>
))}
{loading && (
<div className="flex justify-start">
<div
className={`${
theme === "dark"
? "bg-gray-800 border-gray-700 text-gray-300"
: "bg-white border-gray-200 text-gray-600"
} rounded-2xl p-4 shadow-sm border flex items-center space-x-3`}
>
<Loader size={18} className="animate-spin" />
<span>Generating response...</span>
</div>
</div>
)}
<div ref={messagesEndRef} />
</div>
</main>
{/* Footer with better input design */}
<footer
className={`${
theme === "dark"
? "bg-gray-800 border-gray-700"
: "bg-white border-gray-200"
} border-t p-4`}
>
<div className="max-w-4xl mx-auto">
<div
className={`relative ${
32
theme === "dark" ? "bg-gray-700" : "bg-white"
} rounded-xl border ${
isExpanded
? "border-blue-400 shadow-md"
: theme === "dark"
? "border-gray-600 shadow-sm"
: "border-gray-300 shadow-sm"
} transition-all duration-200`}
>
<textarea
ref={textareaRef}
className={`w-full p-4 pr-24 resize-none focus:outline-none rounded-xl
max-h-36 ${
theme === "dark"
? "bg-gray-700 text-white placeholder-gray-400"
: "bg-white text-gray-700 placeholder-gray-500"
}`}
placeholder="Ask about your database..."
value={question}
onChange={(e) => {
setQuestion([Link]);
setIsExpanded([Link] > 0);
}}
onKeyDown={handleKeyDown}
onFocus={() => setIsExpanded(true)}
onBlur={() => setIsExpanded([Link] > 0)}
rows={1}
/>
<button
onClick={() => askQuestion()}
disabled={loading || ![Link]()}
className={`absolute bottom-3 right-3 p-2 rounded-lg transition-all ${
loading || ![Link]()
? theme === "dark"
? "bg-gray-600 text-gray-400"
: "bg-gray-100 text-gray-400"
: "bg-gradient-to-r from-blue-500 to-purple-600 text-white shadow-md
hover:shadow-lg"
}`}
>
<Send size={20} />
</button>
</div>
<div className="flex justify-between items-center mt-2">
33
<p
className={`text-xs ${
theme === "dark" ? "text-gray-400" : "text-gray-500"
}`}
>
Press Enter to send • Shift+Enter for new line
</p>
<div className="flex items-center">
<span
className={`text-xs mr-2 ${
theme === "dark" ? "text-gray-400" : "text-gray-500"
}`}
>
Powered by AI
</span>
<Code
size={14}
className={theme === "dark" ? "text-gray-400" : "text-gray-500"}
/>
</div>
</div>
</div>
</footer>
</div>
);}
34
5.2 Output
35
36
