Integrating GPT with Databases to Execute SQL Queries: A Step-by-Step Guide

Curious about making GPT query SQL databases to reveal key insights effortlessly? Imagine it pinpointing bestsellers or tracking sales trends as simply as asking where to get the best coffee. Today, we're guiding you through connecting GPT to any database, unlocking a wealth of data insights with ease. 

Step 1: Connecting to the Database & Schema Overview

Alright, let's kick things off by getting our database connection up and running. This is where we make sure GPT can peek into our database and start working its magic. We're using the Chinook database for this adventure—a real-world database you can easily download from GitHub. It's like a playground for what we're about to do.

Creating a Safe Space for Our Project

First things first, we're setting up a virtual environment. It's like creating a little bubble where our project can live without bumping into anything it shouldn't. If you've never set up one before, no worries, it's just a couple of lines in your terminal:

python -m venv venv
source venv/bin/activate

Making the Connection

Next up, we're connecting to our database. Here’s how we do it:

• We start by importing SQLite3 in our Python script. SQLite is super handy for projects like this because it's lightweight and doesn't require setting up a separate database server.
• Then, we use the connect method to hook up to our Chinook database.
  
  

import sqlite3
conn = sqlite3.connect('data/Chinook.db')

If you haven’t already, move the Chinook database file into a data directory in your project folder. That way, it’s easy for our script to find and connect to it.

Getting to Know the Database

Now that we're connected, we need to introduce GPT to the structure of our database. We'll write some code to fetch all the table names:

def get_table_names():
  table_names = []
  tables = conn.execute('Select name from sqlite_master where type="table"')
  for table in tables.fetchall():
    table_names.append(table[0])

  return table_names

Next, we'll get even more detailed by fetching the column names for each table:

def get_column_names(table_name):
  """Return a list of column names."""
  column_names = []
  columns = conn.execute(f"PRAGMA table_info('{table_name}');").fetchall()
  for col in columns:
    column_names.append(col[1])
  return column_names

The next function compiles all the necessary details about our database into a neat package, making it easy for GPT to grasp the structure at a glance.

def get_database_info():
  """Return a list of dicts containing the table name and columns for each table in the database."""
  table_dicts = []
  for table_name in get_table_names():
  columns_names = get_column_names(table_name)
  table_dicts.append({"table_name": table_name, "column_names": columns_names})
  return table_dicts

We can already preview the output of the method get_database_info():

Step 2: Generating SQL Queries with GPT

Next, we need to let GPT know that this database exists and how it's structured. And we need a function that can be used by GPT to do the actual queries.

We do this by defining a function in our code that GPT can call to interact to execute the queries. This function takes an SQL query as input, executes it against our database, and returns the results.

def ask_database(query):
 results = str(conn.execute(query).fetchall())
 return results

Later, we'll use OpenAI's API to enable GPT to generate SQL queries based on user prompts. But first, we need to ensure OpenAI knows about our ask_database function and how to use it.

The following configuration tells OpenAI about our custom ask_database function, what it expects as input (an SQL query string), and what it outputs (the query results).

tools = [
 {
   "type": "function",
   "function": {
     "name": "ask_database",
     "description": "Use this function to answer user questions about music. Input should be a fully formed SQL query.",
     "parameters": {
       "type": "object",
       "properties": {
       "query": {
         "type": "string",
         "description": f"""
           SQL query extracting info to answer the user's question.
           SQL should be written using this database schema:
           {database_schema_string}
           The query should be returned in plain text, not in JSON.
         """,
       }
     },
     "required": ["query"],
   },
  }
 }
] 

Crafting the GPT-Prompt

Finally, we craft prompts that instruct GPT to generate SQL queries based on user questions. For example, if a user wants to know the top five selling albums, we'd ask GPT to generate an SQL query that can retrieve this information from our database.

client = OpenAI(
  api_key=os.environ.get("OPENAPI_API_KEY")
)

def generate_sql_query(query):
   chat_completion = client.chat.completions.create(
   messages=[
     {
       "role": "system",
       "content": "Answer user questions by generating SQL queries"
     },
     {
     "role": "user",
     "content": query
     }
 ], 
 model="gpt-3.5-turbo",
 tools=tools
 )

tool_call = chat_completion.choices[0].message.tool_calls[0]

This instructs GPT to use its understanding of our database (thanks to the schema information we provided) to craft an SQL query that answers the user's question.

if tool_call.function.name == 'ask_database':
 query = json.loads(tool_call.function.arguments)["query"]
 result = ask_database(query)
 print(result)