SQL 查询

让 Claude 生成和执行 SQL

🌐 查看英文原文 | 源码 Notebook

---

如何使用 Claude 进行 SQL 查询

在本教程中，我们将探索如何使用 Claude 根据自然语言问题生成 SQL 查询。我们将设置一个测试数据库，将 schema 提供给 Claude，并演示它如何理解人类语言并将其翻译为 SQL 查询。

环境配置

首先，让我们安装必要的库，并使用 API key 设置 Anthropic 客户端。

# Install the necessary libraries
%pip install anthropic

# Import the required libraries
import sqlite3

from anthropic import Anthropic

# Set up the Claude API client
client = Anthropic()
MODEL_NAME = "claude-opus-4-1"

创建测试数据库

我们将使用 SQLite 创建一个测试数据库，并填充示例数据：

# Connect to the test database (or create it if it doesn't exist)
conn = sqlite3.connect("test_db.db")
cursor = conn.cursor()

# Create a sample table
cursor.execute("""
    CREATE TABLE IF NOT EXISTS employees (
        id INTEGER PRIMARY KEY,
        name TEXT,
        department TEXT,
        salary INTEGER
    )
""")

# Insert sample data
sample_data = [
    (1, "John Doe", "Sales", 50000),
    (2, "Jane Smith", "Engineering", 75000),
    (3, "Mike Johnson", "Sales", 60000),
    (4, "Emily Brown", "Engineering", 80000),
    (5, "David Lee", "Marketing", 55000),
]
cursor.executemany("INSERT INTO employees VALUES (?, ?, ?, ?)", sample_data)
conn.commit()

使用 Claude 生成 SQL 查询

现在，让我们定义一个函数，将自然语言问题发送给 Claude 并获取生成的 SQL 查询：

# Define a function to send a query to Claude and get the response
def ask_claude(query, schema):
    prompt = f"""Here is the schema for a database:

{schema}

Given this schema, can you output a SQL query to answer the following question? Only output the SQL query and nothing else.

Question: {query}
"""

    response = client.messages.create(
        model=MODEL_NAME, max_tokens=2048, messages=[{"role": "user", "content": prompt}]
    )
    return response.content[0].text

我们将检索数据库 schema 并将其格式化为字符串：

# Get the database schema
schema = cursor.execute("PRAGMA table_info(employees)").fetchall()
schema_str = (
    "CREATE TABLE EMPLOYEES (\n" + "\n".join([f"{col[1]} {col[2]}" for col in schema]) + "\n)"
)
print(schema_str)

CREATE TABLE EMPLOYEES (
id INTEGER
name TEXT
department TEXT
salary INTEGER
)

现在，让我们提供一个自然语言问题示例并将其发送给 Claude：

# Example natural language question
question = "What are the names and salaries of employees in the Engineering department?"
# Send the question to Claude and get the SQL query
sql_query = ask_claude(question, schema_str)
print(sql_query)

SELECT name, salary
FROM EMPLOYEES
WHERE department = 'Engineering';

执行生成的 SQL 查询

最后，我们将在测试数据库上执行生成的 SQL 查询并打印结果：

# Execute the SQL query and print the results
results = cursor.execute(sql_query).fetchall()

for row in results:
    print(row)

('Jane Smith', 75000)
('Emily Brown', 80000)

完成后别忘了关闭数据库连接：

# Close the database connection
conn.close()

---

Tip

中文读者提示：本章节的代码和输出为英文原文。如需理解具体实现细节，可参考上方中文导读和代码注释。如有疑问，欢迎在 GitHub Issues 讨论。