MySQL【进阶篇】02：调用DeepSeek的API实现一个SQL生成器

一、编写简单的SQL生成程序

1、环境信息

MySQL的InnoDB集群

192.168.201.161
192.168.201.162
192.168.201.163

2、查看当前数据库中的数据

在161节点执行

mysql -umgr_user -p'admin' -P6446 -h192.168.201.161

show databases;

use onlinestore;
show tables;

select * from orders;

二、生成代码

1、提示词

使用deepseek的API来输出SQL的生成器，借助gradio实现一个页面，输入SQL的描述，然后生成SQL语句以及这个SQL的查询结果，其中数据库用户为:mgr_user，密码为:admin，库名为：onlinestore，ip为：192.168.201.161，端口为：6446，
deepseek的API key变量名为：DEEPSEEK_API_KEY，值为:sk-dbd3e5790bab4e5ea244f2a535af7665, 其中deepseek的API调用方式如下：
# Please install OpenAI SDK first: `pip3 install openai`

from openai import OpenAI

client = OpenAI(api_key="<DeepSeek API Key>", base_url="https://api.deepseek.com")

response = client.chat.completions.create(
    model="deepseek-chat",
    messages=[
        {"role": "system", "content": "You are a helpful assistant"},
        {"role": "user", "content": "Hello"},
    ],
    stream=False
)

print(response.choices[0].message.content)

2、生成的代码如下

import os
import gradio as gr
import pymysql
from openai import OpenAI

# 配置DeepSeek客户端
DEEPSEEK_API_KEY = "sk-dbd3e5790bab4e5ea244f2a535af7665"
client = OpenAI(
    api_key=DEEPSEEK_API_KEY,
    base_url="https://api.deepseek.com"
)

# 数据库配置
DB_CONFIG = {
    "host": "192.168.201.161",
    "port": 6446,
    "user": "mgr_user",
    "password": "admin",
    "database": "onlinestore",
    "charset": "utf8mb4"
}


def generate_sql(query_desc):
    """使用DeepSeek API生成SQL语句"""
    system_prompt = """你是一个资深SQL专家，根据用户描述生成准确的MySQL查询语句。
数据库信息：
- 数据库名：onlinestore
- 表结构未知，请根据常识推断合理的表名和字段"""

    response = client.chat.completions.create(
        model="deepseek-chat",
        messages=[
            {"role": "system", "content": system_prompt},
            {"role": "user", "content": f"生成SQL查询：{query_desc}"}
        ],
        stream=False
    )

    return response.choices[0].message.content.strip()


def execute_sql(sql_query):
    """执行SQL查询并返回结果"""
    try:
        conn = pymysql.connect(**DB_CONFIG)
        cursor = conn.cursor()
        cursor.execute(sql_query)

        if sql_query.strip().lower().startswith("select"):
            result = cursor.fetchall()
            columns = [desc[0] for desc in cursor.description]
            return {
                "status": "success",
                "data": {
                    "columns": columns,
                    "rows": result
                }
            }
        else:
            conn.commit()
            return {
                "status": "success",
                "data": f"执行成功，影响行数：{cursor.rowcount}"
            }

    except Exception as e:
        return {
            "status": "error",
            "message": f"数据库错误：{str(e)}"
        }
    finally:
        if 'conn' in locals():
            conn.close()


def format_result(result):
    """格式化查询结果"""
    if result["status"] == "error":
        return result["message"]

    if isinstance(result["data"], str):
        return result["data"]

    output = " | ".join(result["data"]["columns"]) + "\n"
    output += "-" * 50 + "\n"
    for row in result["data"]["rows"]:
        output += " | ".join(map(str, row)) + "\n"
    return output


def process_query(query_desc):
    """处理完整流程"""
    # 生成SQL
    try:
        sql_response = generate_sql(query_desc)
        # 提取SQL代码块（处理可能的markdown格式）
        if "```sql" in sql_response:
            sql_query = sql_response.split("```sql")[1].split("```")[0].strip()
        elif "```" in sql_response:
            sql_query = sql_response.split("```")[1].split("```")[0].strip()
        else:
            sql_query = sql_response
    except Exception as e:
        return f"SQL生成错误：{str(e)}", ""

    # 执行查询
    execution_result = execute_sql(sql_query)
    return sql_query, format_result(execution_result)


# 创建Gradio界面
with gr.Blocks(title="SQL 生成器") as demo:
    gr.Markdown("## 自然语言转SQL查询")
    gr.Markdown("输入您的查询需求，自动生成并执行SQL语句")

    with gr.Row():
        with gr.Column():
            input_desc = gr.Textbox(
                label="查询描述",
                placeholder="例如：查询最近一周的订单数据",
                lines=3
            )
            submit_btn = gr.Button("生成并执行", variant="primary")

        with gr.Column():
            sql_output = gr.Textbox(label="生成的SQL语句", interactive=False)
            result_output = gr.Textbox(
                label="查询结果",
                lines=15,
                interactive=False
            )

    submit_btn.click(
        fn=process_query,
        inputs=input_desc,
        outputs=[sql_output, result_output]
    )

if __name__ == "__main__":
    demo.launch(server_port=7860, server_name="127.0.0.1")

3、执行代码

4、进行测试

计算orders表中采用PayPal方式支付的总额

生成的SQL如下：

SELECT SUM(total_amount) AS paypal_payment_total
FROM onlinestore.orders
WHERE payment_method = 'PayPal';

结果为：

35.98

5、到数据库进行查询

SELECT SUM(total_amount) AS paypal_payment_total
FROM onlinestore.orders
WHERE payment_method = 'PayPal';

结果是一样的