Chat with Databases using LangChain

Copy# Install required packages pip install langchain langchain-community langchain-openai langgraph # Import necessary modules import os from langchain_openai import ChatOpenAI from langchain_community.utilities import SQLDatabase from langchain import hub from langchain_community.tools.sql_database.tool import QuerySQLDatabaseTool from langgraph.prebuilt import create_react_agent # Set your OpenAI API key os.environ["OPENAI_API_KEY"] = "your-api-key-here"

Copy# Connect to an SQLite Database from sqlalchemy import create_engine from sqlalchemy.pool import StaticPool # For an existing SQLite database db_path = "chinook.db" # Path to your SQLite database engine = create_engine(f"sqlite:///{db_path}") db = SQLDatabase(engine) # Alternatively, for in-memory testing with example data def get_in_memory_db(): import sqlite3 import requests url = "https://raw.githubusercontent.com/lerocha/chinook-database/master/ChinookDatabase/DataSources/Chinook_Sqlite.sql" response = requests.get(url) sql_script = response.text connection = sqlite3.connect(":memory:", check_same_thread=False) connection.executescript(sql_script) return create_engine( "sqlite://", creator=lambda: connection, poolclass=StaticPool, connect_args={"check_same_thread": False}, ) engine = get_in_memory_db() db = SQLDatabase(engine)

Copy# Initialize Language Model llm = ChatOpenAI(model="gpt-4") # You can also use gpt-3.5-turbo for lower cost # Initialize SQL Database Toolkit from langchain_community.agent_toolkits.sql.toolkit import SQLDatabaseToolkit toolkit = SQLDatabaseToolkit(db=db, llm=llm) # Get the tools from the toolkit tools = toolkit.get_tools() # Get a prompt from LangChain Hub prompt_template = hub.pull("langchain-ai/sql-agent-system-prompt") system_message = prompt_template.format(dialect="SQLite", top_k=5) # Create the agent agent_executor = create_react_agent( llm, tools, state_modifier=system_message )

Copy# views.py for Django integration from django.http import JsonResponse from django.views.decorators.csrf import csrf_exempt import json @csrf_exempt def chat_with_db(request): if request.method == 'POST': try: data = json.loads(request.body) user_message = data.get('message', '') # Process the user message with our agent response = agent_executor.invoke( {"messages": [("user", user_message)]} ) # Extract the final AI message ai_message = response["messages"][-1].content return JsonResponse({ 'success': True, 'message': ai_message }) except Exception as e: return JsonResponse({ 'success': False, 'error': str(e) }) return JsonResponse({ 'success': False, 'error': 'Only POST requests are supported' })

Copy// JavaScript for the chat interface document.addEventListener('DOMContentLoaded', function() { const messageInput = document.querySelector('.chat-input input'); const sendButton = document.querySelector('.chat-input button'); const messagesContainer = document.querySelector('.chat-messages'); const sampleQueries = document.querySelectorAll('.sample-query'); // Function to add a message to the chat function addMessage(message, isUser = false) { const messageDiv = document.createElement('div'); messageDiv.className = isUser ? 'user-message' : 'bot-message'; messageDiv.innerHTML = message; messagesContainer.appendChild(messageDiv); messagesContainer.scrollTop = messagesContainer.scrollHeight; } // Function to send a message to the backend async function sendMessage(message) { addMessage(message, true); // Show loading indicator const loadingDiv = document.createElement('div'); loadingDiv.className = 'bot-message'; loadingDiv.innerHTML = '<i class="fas fa-spinner fa-spin"></i> Thinking...'; messagesContainer.appendChild(loadingDiv); try { const response = await fetch('/chat_with_db/', { method: 'POST', headers: { 'Content-Type': 'application/json', }, body: JSON.stringify({ message: message }), }); const data = await response.json(); // Remove loading indicator messagesContainer.removeChild(loadingDiv); if (data.success) { addMessage(data.message); } else { addMessage('Error: ' + data.error); } } catch (error) { // Remove loading indicator messagesContainer.removeChild(loadingDiv); addMessage('An error occurred while connecting to the server.'); } } // Event listener for send button sendButton.addEventListener('click', function() { const message = messageInput.value.trim(); if (message) { sendMessage(message); messageInput.value = ''; } }); // Event listener for enter key messageInput.addEventListener('keypress', function(e) { if (e.key === 'Enter') { const message = messageInput.value.trim(); if (message) { sendMessage(message); messageInput.value = ''; } } }); // Event listeners for sample queries sampleQueries.forEach(query => { query.addEventListener('click', function() { const questionText = this.querySelector('p').textContent; messageInput.value = questionText; messageInput.focus(); }); }); });