如何使用GPT作为SQL查询引擎的自然语言( 三 ) _查询

<- read.csv("states.csv") |>dplyr::filter(!is.na(Region) & Region != "")?states_schema <- sqldf::sqldf("PRAGMA table_info(states)")states_schema_string <- paste(apply(states_schema, 1, paste, collapse = "\t"), collapse = "\n")?states_sample <- dplyr::sample_n(states, 3)states_sample_string <- paste(apply(states_sample, 1, paste, collapse = "\t"), collapse = "\n")?# Function to process user inputget_prompt <- function(query, schema = states_schema_string, rows_sample = states_sample_string, table_name = "states") {my_prompt <- glue::glue("Act as if you're a data scientist. You have a SQLite table named {table_name} with the following schema:?```{schema}```?The first rows look like this: ?```{rows_sample}```?Based on this data, write a SQL query to answer the following question: {query}Return the SQL query ONLY. Do not include any additional explanation.")print(my_prompt)return(my_prompt)?}?ui <- fluidPage(titlePanel("Query state database"),sidebarLayout(sidebarPanel(textInput("query", "Enter your query", placeholder = "e.g., What is the total 2020 population by Region?"),actionButton("submit_btn", "Submit")),mainPanel(uiOutput("the_sql"),br(),br(),verbatimTextOutput("results"))))?server <- function(input, output) {?# Create the prompt from the user query to send to GPTthe_prompt <- eventReactive(input$submit_btn, {req(input$query, states_schema_string, states_sample_string)my_prompt <- get_prompt(query = input$query)})?# send prompt to GPT, get SQL, run SQL, print resultsobserveEvent(input$submit_btn, {req(the_prompt()) # text to send to GPT?# Send results to GPT and get response# withProgress adds a Shiny progress bar. Commas now needed after each statementwithProgress(message = 'Getting results from GPT', value = http://www.kingceram.com/post/0, {# Add Shiny progress messagemy_results <- openai::create_chat_completion(model ="gpt-3.5-turbo", temperature = 0, messages =list(list(role = "user", content = the_prompt()))) the_gpt_sql <- my_results$choices$message.content ?# print the SQLsql_html <- gsub("\n", "
", the_gpt_sql) sql_html <- paste0("【如何使用GPT作为SQL查询引擎的自然语言】", sql_html, "
") ?# Run SQL on data to get resultsgpt_answer <- sqldf(the_gpt_sql) setProgress(value = http://www.kingceram.com/post/1, message = 'GPT results received') # Send msg to user that })# Print SQL and resultsoutput$the_sql <- renderUI(HTML(sql_html)) ?if (is.vector(gpt_answer) ) {output$results <- renderPrint(gpt_answer) } else {output$results <- renderPrint({ print(gpt_answer) }) } })}shinyApp(ui = ui, server = server)