Natural-Language-to-SQL System with the OpenAI API

A data-analysis prototype that turns natural-language questions into SQL and executes them against a relational database

Overview

This project is a Natural Language to SQL prototype that lets people query data without writing SQL themselves. When a user asks a question such as “Show the total sales for each state,” the OpenAI API generates a SQLite query from the supplied database schema, and the application executes it and returns the result.

I used the Adidas US Sales Dataset for the experiment. The pipeline preprocesses an Excel worksheet with Pandas, loads it into an in-memory SQLite database through SQLAlchemy, and connects the user’s question to query generation and execution. Beyond saving a few lines of SQL, the goal was to explore whether a conversational interface could make data retrieval more accessible to non-developers.

Pipeline

Excel data → Pandas preprocessing → SQLite → schema-aware prompt → OpenAI API → SQL extraction → database execution → results

  1. Prepare the data: Load the Excel sales data into a Pandas DataFrame and normalize column names containing spaces.
  2. Build the database: Write the DataFrame to a Sales table in an in-memory SQLite database with SQLAlchemy.
  3. Provide schema context: Combine the table name and complete column list with the user’s question so the model can work within the real data structure.
  4. Generate SQL: Instruct the model to return a SQLite query that begins with SELECT and ends with a semicolon.
  5. Process and execute: Extract the SQL statement from the model response, execute it through SQLAlchemy’s text() interface, and inspect the returned rows.

Schema-Aware Prompting

A model that does not know the table structure may invent columns or mix SQL dialects. Instead of sending only the question, I included the schema in the prompt:

### SQLite table and its columns
Sales(
  Retailer, Retailer_ID, Invoice_Date, Region, State, City,
  Product, Price_per_Unit, Units_Sold, Total_Sales,
  Operating_Profit, Operating_Margin, Sales_Method
)

Request: Show the sum of total sales for each State.

The model generated the following query:

SELECT State, SUM(Total_Sales) AS Total_Sales_Sum
FROM Sales
GROUP BY State;
Executing SQL generated from a natural-language request through a SQLAlchemy connection.

Running this query against the Sales table returned aggregated sales for Alabama, California, Florida, and the other states, validating the complete path from a natural-language question to actual database results.

Problems Solved During Development

  • Excel dependencies: Added openpyxl, which Pandas requires to read .xlsx workbooks in this environment.
  • Date aggregation errors: A groupby().sum() call attempted to aggregate a datetime64 column. Restricting the operation with numeric_only=True resolved the issue.
  • Lightweight SQL environment: Used SQLAlchemy with in-memory SQLite to validate the workflow without provisioning a separate database server.
  • API request failure: An initial 429 insufficient_quota response highlighted that API access depends on project billing and usage-limit configuration as well as a valid key.
  • Response formatting: Added a response handler that isolates the executable SQL statement instead of passing the full assistant message to the database.

Outcome and Lessons Learned

The prototype demonstrated that a natural-language interface can lower the entry barrier to relational data analysis. It also made clear that prompt instructions alone are not a sufficient safety boundary when model-generated text is executed against a database. Detailed schema context improves relevance, but the application must still validate the syntax and permissions of every generated query.

For a production version, I would add a read-only database account, allow only one SELECT statement, restrict accessible tables and columns with an allowlist, parse and validate SQL before execution, require confirmation for sensitive queries, and cap execution time and result size. Constrained or structured model outputs can make generation more predictable, but server-side validation remains essential.

Tech Stack

Python · Pandas · SQLAlchemy · SQLite · OpenAI API · Jupyter Notebook · openpyxl

Development Note