Creating SQL Queries from Natural Language

!pip install orq-ai-sdk datasets huggingface_hub

import os
from orq_ai_sdk import Orq

client = Orq(
    api_key=os.environ.get("ORQ_API_KEY", "your_orq_api_key"),
    contact_id="your_contact_id"  # Optional: add contact ID from Studio for tracking
)

from datasets import load_dataset

ds = load_dataset("Clinton/Text-to-sql-v1")

# Convert to a pandas DataFrame (selecting the "train" split as an example)
df = ds["train"].to_pandas()

# Select the top 300 rows
df = df.head(50)

# Display the DataFrame or save it
print(df)

Below are sql tables schemas paired with instruction that describes a task. Using valid SQLite, write a response that appropriately completes the request for the provided tables:

Here is the instruction: {{instruction}}

Here is the table: {{table}}

Here are some output examples, adhere to this output form:

SELECT home_team FROM table_name_77 WHERE away_team = "carlton"

SELECT "Yellow jersey" FROM table_3791 WHERE "Distance (km)" = '125'

SELECT COUNT(DISTINCT demographic.subject_id) FROM demographic INNER JOIN diagnoses ON demographic.hadm_id = diagnoses.hadm_id INNER JOIN lab ON demographic.hadm_id = lab.hadm_id WHERE diagnoses.icd9_code = "45620" AND lab.fluid = "Blood"

NO NEED TO INCLUDE   ```sql```  AROUND THE OUTPUT:

 ```sql SQL
SELECT Sex, COUNT(*) AS FacultyCount
FROM Faculty
GROUP BY Sex
ORDER BY FacultyCount DESC;

**Step 5: Generating SQL Queries**  
This step involves invoking the Orq deployment to generate SQL queries for each row in the dataset. The instruction column provides the natural language task, while the input column contains the table schema. The results are stored in a new column named output.

# Initialize the outputs list
outputs = []

# Iterate through each row in the DataFrame
for _, row in df.iterrows():
    # Extract the 'instruction' and 'input' columns for each row
    instruction = row["instruction"]
    table = row["input"]
    
    # Invoke the deployment for each row
    generation = client.deployments.invoke(
        key="text_to_SQL",  # Replace with your actual deployment key
        context={
            "environments": []
        },
        inputs={
            "table": table,
            "instruction": instruction
        },
        metadata={
            "custom-field-name": "custom-metadata-value"
        }
    )
    
    # Append the model's output to the outputs list
    outputs.append(generation.choices[0].message.content)

# Add the outputs as a new column in the DataFrame
df["output"] = outputs

# Save the results to a CSV file
df.to_csv("generated_sql_queries.csv", index=False)
print("Generated SQL queries saved to 'generated_sql_queries.csv'.")