Function translate_nlp_to_sql

bmtools/tools/database/api.py:58–101

(description : str)

    @tool.get("/translate_nlp_to_sql")
    def translate_nlp_to_sql(description : str):
        global schema, query
        """translate_nlp_to_sql(description: str) translates the input nlp string into sql query based on the database schema, and the sql query is the input of rewrite_sql and select_database_data API.
            description is a string that represents the description of the result data.
            schema is a string that represents the database schema.
            Final answer should be complete.

            This is an example:
            Thoughts: Now that I have the database schema, I will use the \\\'translate_nlp_to_sql\\\' command to generate the SQL query based on the given description and schema, and take the SQL query as the input of the \\\'rewrite_sql\\\' and  \\\'select_database_data\\\' commands.
            Reasoning: I need to generate the SQL query accurately based on the given description. I will use the \\\'translate_nlp_to_sql\\\' command to obtain the SQL query based on the given description and schema, and take the SQL query as the input of the \\\'select_database_data\\\' command.
            Plan: - Use the \\\'translate_nlp_to_sql\\\' command to generate the SQL query. \\\\n- Use the \\\'finish\\\' command to signal that I have completed all my objectives.
            Command: {"name": "translate_nlp_to_sql", "args": {"description": "Retrieve the comments of suppliers . The results should be sorted in descending order based on the comments of the suppliers."}}
            Result: Command translate_nlp_to_sql returned: "SELECT s_comment FROM supplier BY s_comment DESC"
        """
        
        openai.api_key = os.environ["OPENAI_API_KEY"]
        # schema = db.compute_table_schema()

        prompt = """Translate the natural language description into an semantic equivalent SQL query.
        The table and column names used in the sql must exactly appear in the schema. Any other table and column names are unacceptable.
        The schema is:\n
        {}
        
        The description is:\n
        {}

        The SQL query is:
        """.format(schema, description)

        # Set up the OpenAI GPT-3 model
        model_engine = "gpt-3.5-turbo"
        
        prompt_response = openai.ChatCompletion.create(
            engine=model_engine,
            messages=[
                {"role": "assistant", "content": "The table schema is as follows: " + schema},
                {"role": "user", "content": prompt}
                ]
        )
        output_text = prompt_response['choices'][0]['message']['content']

        query = output_text

        return output_text

    @tool.get("/select_database_data")
    def select_database_data(query : str):