pip install openai

# For macOS/Linux
export OPENAI_API_KEY='your-api-key-here'

# For Windows (Command Prompt)
set OPENAI_API_KEY=your-api-key-here

Our Python script will automatically detect and use this variable.

Step 2: The Art of the Prompt

The single most important factor for success with Codex is the prompt. A well-crafted prompt guides the AI to the exact output you need. For our text-to-SQL app, a great prompt has three key ingredients:

1. Context: Tell the AI about the database structure. This is crucial for it to know what tables and columns are available.
2. Instruction: Clearly state the task. We want it to generate a SQL query.
3. The User's Request: The natural language question we want to translate.

Here’s what our prompt structure will look like:

Given the following SQL tables, your job is to write queries given a user's request.

CREATE TABLE Orders (

OrderID int,

CustomerID int,

OrderDate datetime,

TotalAmount decimal

);

CREATE TABLE Customers (

CustomerID int,

CustomerName varchar(255),

Country varchar(255)

);

-- User request: [The user's question will go here]

SELECT

Notice how we end the prompt with `SELECT`. This is a powerful technique called "priming." It gives the model a strong hint about what should come next, nudging it to start writing a SQL query immediately.

Step 3: Writing the Python Code to Call Codex

Now, let's bring it all together in a Python script. Create a file named `sql_generator.py`.

import os
from openai import OpenAI

# The client will automatically pick up the OPENAI_API_KEY environment variable.
client = OpenAI()

def create_sql_prompt(user_request):
    """Dynamically creates the prompt for the Codex model."""
    schema_definition = """
Given the following SQL tables, your job is to write queries given a user's request.

CREATE TABLE Orders (
  OrderID int, 
  CustomerID int, 
  OrderDate datetime, 
  TotalAmount decimal
);

CREATE TABLE Customers (
  CustomerID int, 
  CustomerName varchar(255), 
  Country varchar(255)
);
"""
    # The final prompt includes the schema, the user's request, and a priming token.
    prompt = f"{schema_definition}\n-- User request: {user_request}\nSELECT"
    return prompt

def generate_sql_query(prompt):
    """Sends the prompt to the OpenAI API and gets the SQL query."""
    try:
        response = client.completions.create(
            # While 'gpt-3.5-turbo-instruct' is a great modern choice, 
            # for pure code-gen, older models like 'code-davinci-002' were specifically tuned.
            # Let's use a modern, capable instruct model.
            model="gpt-3.5-turbo-instruct", 
            prompt=prompt,
            temperature=0, # We want deterministic, accurate SQL, not creative SQL.
            max_tokens=200, # Max length of the generated query.
            stop=["\n\n", ";"] # Stop generating when it encounters a double newline or a semicolon.
        )
        # The actual query is in the 'text' field of the first choice.
        # We also prepend the 'SELECT' we used for priming.
        return "SELECT" + response.choices[0].text
    except Exception as e:
        return f"An error occurred: {e}"

if __name__ == "__main__":
    # Example usage
    user_question = "Show me the names of customers from Canada who have an order total over 1000"
    print(f"User Request: {user_question}\n")

    # 1. Create the full prompt
    full_prompt = create_sql_prompt(user_question)

    # 2. Generate the SQL query
    sql_query = generate_sql_query(full_prompt)

    # 3. Print the result
    print("Generated SQL Query:")
    print(sql_query)

Step 4: Running Your App and Using the Output

Save the file and run it from your terminal:

python sql_generator.py

You should see an output similar to this:

User Request: Show me the names of customers from Canada who have an order total over 1000

Generated SQL Query:
SELECT c.CustomerName 
FROM Customers c 
JOIN Orders o ON c.CustomerID = o.CustomerID 
WHERE c.Country = 'Canada' AND o.TotalAmount > 1000

Look at that! Codex correctly understood the need to join two tables, filter by country and total amount, and select only the customer's name. This is a non-trivial query that it generated perfectly from a single English sentence.

Level Up: Tips for Mastering Codex

You've built your first app, but this is just the beginning. To truly master Codex, you need to understand how to control its behavior. The most important parameter is `temperature`.

Controlling Creativity with Temperature

Temperature Value	Behavior	Best Use Case
0.0 - 0.2	Highly deterministic and focused. The model will choose the most likely, common, and safe output.	Generating precise code, SQL queries, API calls, or data translation where correctness is paramount.
0.3 - 0.7	A balance between deterministic and creative. It can generate slightly different but still correct variations.	Writing documentation, code comments, or generating boilerplate code where some variety is acceptable.
0.8 - 1.0	Highly creative and exploratory. The model will take more risks and can produce novel or unexpected results.	Brainstorming code ideas, writing creative code examples, or generating multiple different approaches to a problem.

More Pro Tips:

• Be Specific: Vague prompts lead to vague code. Instead of "make a button," try "create a blue HTML button with a white border that says 'Click Me'."
• Provide Examples (Few-Shot Learning): In your prompt, you can include one or two examples of an input and the desired output before your final request. This teaches the model the exact format you want.
• Iterate: Your first prompt might not be perfect. If the output isn't right, tweak the prompt, adjust the temperature, and try again. Prompt engineering is an iterative process.