LangChain 08 - Query SQL DB with GPT

Feature Overview

This feature allows users to describe query requirements in natural language. GPT automatically converts them to SQL statements and executes queries without manually writing SQL code. The system supports multiple mainstream databases including MySQL, PostgreSQL, SQL Server, etc.

Detailed Feature Description

1. Natural Language to SQL

  • Users can describe query requirements in everyday language
  • GPT analyzes semantics and automatically generates corresponding SQL queries
  • Supports complex query conditions including multi-table joins, aggregate functions, subqueries, etc.

2. Database Connection Management

  • Supports configuring multiple database connections
  • Can save commonly used connection configurations

3. Query Execution and Result Display

  • Automatically executes generated SQL statements
  • Displays query results in table format

4. Query History

  • Automatically saves query history
  • Can search historical queries by time, database, keywords, etc.

Usage Steps

  1. Select Database Connection - Choose target database from configured connections, or create new connection
  2. Input Query Requirement - Describe query requirement in natural language
  3. Generate SQL - System automatically generates SQL statement, user can view and confirm
  4. Execute Query - Click execute button to run query
  5. Save/Export Result - Can save query as template or export result data

Application Scenarios

  • Business Analysis: Sales data analysis, customer behavior analysis, inventory status queries
  • Daily Reports: Automatically generate daily reports, weekly reports, performance statistics reports
  • Technical Support: Fault diagnosis data analysis, system log queries

Security Features

  • All database connection information encrypted storage
  • Query permission control, prevent unauthorized access
  • SQL injection protection mechanism
  • Sensitive data automatic desensitization processing

Technical Advantages

  • No SQL expertise needed to query databases
  • Greatly improve data analysis efficiency
  • Reduce risk of human-written SQL errors

Install Dependencies

pip install --upgrade --quiet langchain-core langchain-community langchain-openai

Write Code

from langchain_core.prompts import ChatPromptTemplate
from langchain_community.utilities import SQLDatabase
from langchain_core.output_parsers import StrOutputParser
from langchain_core.runnables import RunnablePassthrough
from langchain_openai import ChatOpenAI

template = """Based on the table schema below, write a SQL query that would answer the user's question:
{schema}

Question: {question}
SQL Query:"""
prompt = ChatPromptTemplate.from_template(template)

db = SQLDatabase.from_uri("sqlite:///./Chinook.db")

def get_schema(_):
    return db.get_table_info()

def run_query(query):
    return db.run(query)

model = ChatOpenAI(model="gpt-3.5-turbo")

sql_response = (
    RunnablePassthrough.assign(schema=get_schema)
    | prompt
    | model.bind(stop=["\nSQLResult:"])
    | StrOutputParser()
)

message = sql_response.invoke({"question": "How many employees are there?"})
print(f"message: {message}")

Running Result

message: SELECT COUNT(*) AS totalEmployees
FROM Employee;