🧰 API: Generate a SQL Query
You can call our SQL generation endpoint in order to generate SQL based on a user request.
Method
Method: POST
URL: https://ai-query2.p.rapidapi.com/generate_sql
Headers:
X-RapidAPI-Key: Your Rapid API Key, received once subscribed to a plan
X-RapidAPI-Host: ai-query2.p.rapidapi.com
Params:
prompt: A description of the data you're looking for
model: One of oa_v3, oa_v3_16k, oa_v4, va_v1 or va_v1_32k. Defaults to oa_v3_16k.
data_source_type: One of postgresql, mysql, mariadb, sqlserver, redshift, snowflake, bigquery or oracle
data_schema: A list of dictionaries representing your data schema
Example
Request
import requests
url = "https://ai-query2.p.rapidapi.com/generate_sql/"
payload = {
"prompt": "Show me all customer rentals and related payments over $100 for the past 6 months",
"model": "oa_v3_16k",
"data_source_type": "postgresql",
"data_schema": [{"table_name": "customer", "columns": [{"name": "customer_id", "type": "integer"}, {"name": "store_id", "type": "smallint"}, {"name": "first_name", "type": "character varying(45)"}, {"name": "last_name", "type": "character varying(45)"}, {"name": "email", "type": "character varying(50)"}, {"name": "address_id", "type": "smallint"}, {"name": "activebool", "type": "boolean"}, {"name": "create_date", "type": "date"}, {"name": "last_update", "type": "timestamp"}, {"name": "active", "type": "integer" } ]}, {"table_name": "actor", "columns": [{"name": "actor_id", "type": "integer"}, {"name": "first_name", "type": "character varying(45)"}, {"name": "last_name", "type": "character varying(45)"}, {"name": "last_update", "type": "timestamp" } ]}, {"table_name": "category", "columns": [{"name": "category_id", "type": "integer"}, {"name": "name", "type": "character varying(25)"}, {"name": "last_update", "type": "timestamp" } ]}, {"table_name": "film", "columns": [{"name": "film_id", "type": "integer"}, {"name": "title", "type": "character varying(255)"}, {"name": "description", "type": "text"}, {"name": "release_year", "type": "date"}, {"name": "language_id", "type": "smallint"}, {"name": "rental_duration", "type": "smallint"}, {"name": "rental_rate", "type": "numeric(4,2)"}, {"name": "length", "type": "smallint"}, {"name": "replacement_cost", "type": "numeric(5,2)"}, {"name": "rating", "type": "character varying(5)"}, {"name": "last_update", "type": "timestamp"}, {"name": "special_features", "type": "text[]"}, {"name": "fulltext", "type": "tsvector" } ]}, {"table_name": "film_actor", "columns": [{"name": "actor_id", "type": "smallint"}, {"name": "film_id", "type": "smallint"}, {"name": "last_update", "type": "timestamp" } ]}, {"table_name": "film_category", "columns": [{"name": "film_id", "type": "smallint"}, {"name": "category_id", "type": "smallint"}, {"name": "last_update", "type": "timestamp" } ]}, {"table_name": "address", "columns": [{"name": "address_id", "type": "integer"}, {"name": "address", "type": "character varying(50)"}, {"name": "address2", "type": "character varying(50)"}, {"name": "district", "type": "character varying(20)"}, {"name": "city_id", "type": "smallint"}, {"name": "postal_code", "type": "character varying(10)"}, {"name": "phone", "type": "character varying(20)"}, {"name": "last_update", "type": "timestamp" } ]}, {"table_name": "city", "columns": [{"name": "city_id", "type": "integer"}, {"name": "city", "type": "character varying(50)"}, {"name": "country_id", "type": "smallint"}, {"name": "last_update", "type": "timestamp" } ]}, {"table_name": "country", "columns": [{"name": "country_id", "type": "integer"}, {"name": "country", "type": "character varying(50)"}, {"name": "last_update", "type": "timestamp" } ]}, {"table_name": "inventory", "columns": [{"name": "inventory_id", "type": "integer"}, {"name": "film_id", "type": "smallint"}, {"name": "store_id", "type": "smallint"}, {"name": "last_update", "type": "timestamp" } ]}, {"table_name": "language", "columns": [{"name": "language_id", "type": "integer"}, {"name": "name", "type": "character(20)"}, {"name": "last_update", "type": "timestamp" } ]}, {"table_name": "payment", "columns": [{"name": "payment_id", "type": "integer"}, {"name": "customer_id", "type": "smallint"}, {"name": "staff_id", "type": "smallint"}, {"name": "rental_id", "type": "integer"}, {"name": "amount", "type": "numeric(5,2)"}, {"name": "payment_date", "type": "timestamp" } ]}, {"table_name": "rental", "columns": [{"name": "rental_id", "type": "integer"}, {"name": "rental_date", "type": "timestamp"}, {"name": "inventory_id", "type": "integer"}, {"name": "customer_id", "type": "smallint"}, {"name": "return_date", "type": "timestamp"}, {"name": "staff_id", "type": "smallint"}, {"name": "last_update", "type": "timestamp" } ]}, {"table_name": "staff", "columns": [{"name": "staff_id", "type": "integer"}, {"name": "first_name", "type": "character varying(45)"}, {"name": "last_name", "type": "character varying(45)"}, {"name": "address_id", "type": "smallint"}, {"name": "email", "type": "character varying(50)"}, {"name": "store_id", "type": "smallint"}, {"name": "active", "type": "boolean"}, {"name": "username", "type": "character varying(16)"}, {"name": "password", "type": "character varying(40)"}, {"name": "last_update", "type": "timestamp"}, {"name": "picture", "type": "bytea" } ]}, {"table_name": "store", "columns": [{"name": "store_id", "type": "integer"}, {"name": "manager_staff_id", "type": "smallint"}, {"name": "address_id", "type": "smallint"}, {"name": "last_update", "type": "timestamp"}]}]
}
headers = {
"content-type": "application/json",
"X-RapidAPI-Key": "Your Rapid API Key, received once subscribed to a plan",
"X-RapidAPI-Host": "ai-query2.p.rapidapi.com"
}
response = requests.post(url, json=payload, headers=headers)
print(response.json())
Response
{
"response": "SELECT c.customer_id, c.first_name, c.last_name, r.rental_id, r.rental_date, r.return_date, p.amount FROM customer c JOIN rental r ON c.customer_id = r.customer_id JOIN payment p ON r.rental_id = p.rental_id WHERE p.amount > 100 AND r.rental_date > NOW() - INTERVAL 6 MONTH;"
}