ollama部署文字转sql,并使用fastapi提供外部接口访问

根据你提供的官方文档中的调用方法，可以使用 Ollama 的 chat 方法来与模型进行交互。我们将调整 FastAPI 应用代码以使用 ollama 模块的 chat 方法。

1. 安装必要的库

确保你已经安装了 FastAPI 和 Ollama Python 客户端库：

pip install fastapi uvicorn ollama

2. 创建 FastAPI 应用

创建一个新的 Python 文件（例如 main.py），并编写 FastAPI 应用代码：

from fastapi import FastAPI, HTTPException
from pydantic import BaseModel
import ollama

app = FastAPI()

class SQLRequest(BaseModel):
    question: str

@app.post("/generate_sql")
async def generate_sql(request: SQLRequest):
    prompt = f"""
    ### Instructions:
    Your task is to convert a question into a SQL query, given a Postgres database schema.
    Adhere to these rules:
    - **Deliberately go through the question and database schema word by word** to appropriately answer the question
    - **Use Table Aliases** to prevent ambiguity. For example, `SELECT table1.col1, table2.col1 FROM table1 JOIN table2 ON table1.id = table2.id`.
    - When creating a ratio, always cast the numerator as float

    ### Input:
    Generate a SQL query that answers the question `{request.question}`.
    This query will run on a database whose schema is represented in this string:
    CREATE TABLE products (
      product_id INTEGER PRIMARY KEY, -- Unique ID for each product
      name VARCHAR(50), -- Name of the product
      price DECIMAL(10,2), -- Price of each unit of the product
      quantity INTEGER  -- Current quantity in stock
    );

    CREATE TABLE customers (
       customer_id INTEGER PRIMARY KEY, -- Unique ID for each customer
       name VARCHAR(50), -- Name of the customer
       address VARCHAR(100) -- Mailing address of the customer
    );

    CREATE TABLE salespeople (
      salesperson_id INTEGER PRIMARY KEY, -- Unique ID for each salesperson
      name VARCHAR(50), -- Name of the salesperson
      region VARCHAR(50) -- Geographic sales region
    );

    CREATE TABLE sales (
      sale_id INTEGER PRIMARY KEY, -- Unique ID for each sale
      product_id INTEGER, -- ID of product sold
      customer_id INTEGER,  -- ID of customer who made purchase
      salesperson_id INTEGER, -- ID of salesperson who made the sale
      sale_date DATE, -- Date the sale occurred
      quantity INTEGER -- Quantity of product sold
    );

    CREATE TABLE product_suppliers (
      supplier_id INTEGER PRIMARY KEY, -- Unique ID for each supplier
      product_id INTEGER, -- Product ID supplied
      supply_price DECIMAL(10,2) -- Unit price charged by supplier
    );

    -- sales.product_id can be joined with products.product_id
    -- sales.customer_id can be joined with customers.customer_id
    -- sales.salesperson_id can be joined with salespeople.salesperson_id
    -- product_suppliers.product_id can be joined with products.product_id

    ### Response:
    Based on your instructions, here is the SQL query I have generated to answer the question `{request.question}`:
    """

    try:
        response = ollama.chat(model='sqlcoder:7B', messages=[{'role': 'user', 'content': prompt}])
        return {"sql_query": response['message']['content']}
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))

if __name__ == "__main__":
    import uvicorn
    uvicorn.run(app, host="0.0.0.0", port=8000)

3. 启动 Ollama 服务

确保 Ollama 服务正在本地运行。你可以使用以下命令启动 Ollama 服务：

ollama serve

4. 启动 FastAPI 应用

在终端中运行以下命令启动 FastAPI 应用：

uvicorn main:app --reload

5. 测试 API

你可以使用 curl 或 Postman 测试 API。例如，使用 curl：

curl -X POST "http://localhost:8000/generate_sql" -H "Content-Type: application/json" -d '{"question": "哪种产品的销售额最高？"}'

这应该会返回一个包含生成的 SQL 查询的 JSON 响应。

详细解释

1. FastAPI：创建一个简单的 Web API 服务。@app.post("/generate_sql") 定义了一个 POST 端点，用于接收用户的问题并调用 Ollama 模型生成 SQL 查询。
2. Pydantic：用于数据验证和解析。SQLRequest 类定义了请求体的数据结构。
3. Ollama：使用 ollama.chat 方法与模型 sqlcoder:7B 进行交互并生成响应。

通过这些步骤，你应该能够正确配置和运行 FastAPI 应用，并通过调用 Ollama 服务来生成 SQL 查询。