第1-2章：使用 OpenAI 创建简单的自然语言到 SQL-大模型应用实践教程

!pip install -q openai==1.1.1
!pip install -q panel

import openai
import panel as pn
openai.api_key="your-openai-key"
#model = "gpt-3.5-turbo"
model = "gpt-4o-mini"

def continue_conversation(messages, temperature=0):
    response = openai.chat.completions.create(
        model=model,
        messages=messages,
        temperature=temperature,
    )
    #print(str(response.choices[0].message["content"]))
    return response.choices[0].message.content

def add_prompts_conversation(_):
    #Get the value introduced by the user
    prompt = client_prompt.value_input
    client_prompt.value = ''

    #Append to the context the User promnopt.
    context.append({'role':'user', 'content':f"{prompt}."})
    context.append({'role':'system', 'content':f"""Only return SQL Orders.
    If you can't return and SQL order, say sorry, and ask, politely but concisely, for a new question."""})

    #Get the response.
    response = continue_conversation(context)

    #Add the response to the context.
    context.append({'role':'assistant', 'content':f"{response}"})

    #Undate the panels to shjow the conversation.
    panels.append(
        pn.Row('User:', pn.pane.Markdown(prompt, width=600)))
    panels.append(
        pn.Row('Assistant:', pn.pane.Markdown(response, width=600, styles={'background-color': '#F6F6F6'})))

    return pn.Column(*panels)

context = [ {'role':'system', 'content':"""
you are a bot to assist in create SQL commands, all your answers should start with
this is your SQL, and after that an SQL that can do what the user request.

Your SQL Database is composed by some tables.
Try to Maintain the SQL order simple.
Just after the SQL add a simple and concise text explaining how it works.
If the user ask for something that can not be solved with an SQL Order
just answer something nice and simple, maximum 10 words, asking him for something that
can be solved with SQL.
"""} ]

context.append( {'role':'system', 'content':"""
first table:
{
  "tableName": "employees",
  "fields": [
    {
      "nombre": "ID_usr",
      "tipo": "int"
    },
    {
      "nombre": "name",
      "tipo": "string"
    }
  ]
}
"""
})

context.append( {'role':'system', 'content':"""
second table:
{
  "tableName": "salary",
  "fields": [
    {
      "nombre": "ID_usr",
      "type": "int"
    },
    {
      "name": "year",
      "type": "date"
    },
    {
      "name": "salary",
      "type": "float"
    }
  ]
}
"""
})

context.append( {'role':'system', 'content':"""
third table:
{
  "tablename": "studies",
  "fields": [
    {
      "name": "ID",
      "type": "int"
    },
    {
      "name": "ID_usr",
      "type": "int"
    },
    {
      "name": "educational level",
      "type": "int"
    },
    {
      "name": "Institution",
      "type": "string"
    },
    {
      "name": "Years",
      "type": "date"
    }
    {
      "name": "Speciality",
      "type": "string"
    }
  ]
}
"""
})

#Creating the panel.
pn.extension()

panels = []

client_prompt = pn.widgets.TextInput(value="Hi", placeholder='Order your data…')
button_conversation = pn.widgets.Button(name="generate SQL")

interactive_conversation = pn.bind(add_prompts_conversation, button_conversation)

dashboard = pn.Column(
    client_prompt,
    pn.Row(button_conversation),
    pn.panel(interactive_conversation, loading_indicator=True),
)