Use English to query your Django data 🏴󠁧󠁢󠁥󠁮󠁧󠁿

LLMs have produced the hottest new programming language: English.

In this guide, we'll build a Django app that lets non-technical users ask questions in English about your data in your database.

We'll use OpenAI's Assistant API (Docs) to power the app.

🏁 Your finished product will look like this (sped up) 🏴󠁧󠁢󠁥󠁮󠁧󠁿:

I've also made a simple video guide (featuring me 🏇🏿) that goes along with the step-by-step instructions. Here's the video:

Let's begin 💫

Setup our Django app

Install packages and create our Django app

pip install django openai python-dotenv faker
django-admin startproject core .
python manage.py startapp sim

Add our app sim to the INSTALLED_APPS in settings.py:

# settings.py
INSTALLED_APPS = [
    'sim',
    ...
]

Create a file called .env at core/.env and add the below to it. We'll use this to add our OpenAI API key as an environment variable.

OPENAI_API_KEY=<your open ai api key>

It's very quick to get your OpenAI API key: go here, click "Create New Secret Key", and copy the key into the .env file. No need to add quotes around the key.

Add the following to the top of core/settings.py to use our environment variables:

# settings.py
from pathlib import Path
import os
from dotenv import load_dotenv

load_dotenv()

if not os.getenv('OPENAI_API_KEY'):
    raise Exception('OPEN_AI_API_KEY not found in environment variables. Please add it to your .env file.')

Create our database models

We'll create a simple database with two models. We'll then query the database using English.

# sim/models.py
from django.db import models

class Organization(models.Model):
    name = models.CharField(max_length=100)

class Person(models.Model):
    organization = models.ForeignKey(Organization, on_delete=models.CASCADE)
    first_name = models.CharField(max_length=100)
    last_name = models.CharField(max_length=100)
    age = models.IntegerField()
    sex = models.CharField(max_length=1, choices=[('M', 'Male'), ('F', 'Female')])
    work_country = models.CharField(
        max_length=2, choices=[('US', 'United States'), ('UK', 'United Kingdom'), ('DE', 'Germany')]
    )
    salary = models.IntegerField()
    email = models.EmailField()

    def __str__(self):
        return f'{self.first_name} {self.last_name}'

Run the migrations

python manage.py makemigrations
python manage.py migrate

Create a simple view

Create a view to display all the people in the database in sim/views.py:

from django.shortcuts import render
from .models import Person

def people(request):
    people = Person.objects.all()
    return render(request, 'people.html', {'people': people})

Add a URL for the view

Create a urls.py file in the sim directory with the below code:

# urls.py
from django.urls import path
from . import views

urlpatterns = [
    path('', views.people, name='people'),
    path('query', views.query, name='query'),
    path('start-conversation', views.start_conversation, name='start_conversation'),
]

Include the app's URLs in the core/urls.py file:

# project/urls.py
from django.contrib import admin
from django.urls import include, path

urlpatterns = [
    path('admin/', admin.site.urls),
    path('', include('sim.urls')),
]

Create a template to display the people

Create a templates directory in the sim directory
Create a people.html file in the templates directory
Add the following code to the people.html file

<!doctype html>
<html>
  <head>
    <title>Let users talk to your database in English</title>
    <script src="https://unpkg.com/htmx.org"></script>
    <style>
      body {
        font-family: Arial, sans-serif;
        background-color: #f4f4f4;
        color: #333;
        margin: 0;
        padding: 20px;
      }

      h1 {
        color: #444;
      }

      table {
        width: 100%;
        border-collapse: collapse;
      }

      th,
      td {
        padding: 10px;
        text-align: left;
        border-bottom: 1px solid #ddd;
      }

      th {
        background-color: #f8f8f8;
      }

      tr:nth-child(even) {
        background-color: #f2f2f2;
      }

      form {
        margin-top: 20px;
      }

      input[type='text'] {
        padding: 8px;
        width: 70%;
        margin-right: 10px;
        border: 1px solid #ddd;
        border-radius: 4px;
      }

      button {
        padding: 10px 15px;
        background-color: #5cb85c;
        color: white;
        border: none;
        border-radius: 4px;
        cursor: pointer;
      }

      button:hover {
        background-color: #4cae4c;
      }

      #result {
        margin-top: 20px;
      }
    </style>
  </head>

  <body>
    <h1>Talk to your database in English</h1>
    <table>
      <tr>
        <th>First</th>
        <th>Last</th>
        <th>Email</th>
        <th>Org</th>
        <th>Country</th>
        <th>Age</th>
        <th>Sex</th>
        <th>Salary</th>
      </tr>
      {% for person in people %}
      <tr>
        <td>{{ person.first_name }}</td>
        <td>{{ person.last_name }}</td>
        <td>{{ person.email }}</td>
        <td>{{ person.organisation }}</td>
        <td>{{ person.work_country }}</td>
        <td>{{ person.age }}</td>
        <td>{{ person.sex }}</td>
        <td>$ {{ person.salary|floatformat:"2g" }}</td>
      </tr>
      {% endfor %}
    </table>

    <form method="post" hx-post="/start-conversation">
      {% csrf_token %}
      <button type="submit">Start a query</button>
    </form>
  </body>
</html>

Create a conversation.html file in the templates directory

<form method="post" hx-post="/query" hx-target="#result">
  {% csrf_token %}
  <input type="hidden" name="assistant_id" value="{{ assistant_id }}" />
  <input type="hidden" name="thread_id" value="{{ thread_id }}" />
  <input
    type="text"
    name="query"
    placeholder="Enter your query in English"
    oninput="clearResult()"
  />
  <button type="submit">Run</button>
</form>
<p id="result">{{ result }}</p>

Create an answer.html file in the templates directory

<div>
  {% for text_message in text_messages %}
  <p>{{ text_message }}</p>
  {% endfor %}
</div>

Update sim/views.py to the below:

# views.py
from time import sleep
from django.core.serializers import serialize
from django.http import HttpResponse
from django.shortcuts import render
from .models import Person
from openai import OpenAI
import os

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

def people(request):
    people = Person.objects.all()
    return render(request, 'people.html', {'people': people})

def start_conversation(request):
    people_dataset = serialize('json', Person.objects.all())
    instructions = f"You are a personal data analyst. Answer questions clearly about the below dataset:\n {people_dataset}"

    assistant = client.beta.assistants.create(
        instructions=instructions,
        name="Data analyst",
        tools=[{"type": "code_interpreter"}],
        model="gpt-3.5-turbo-16k",
    )
    thread = client.beta.threads.create()

    return render(request, 'conversation.html', {"thread_id": thread.id, "assistant_id": assistant.id})

def query(request):
    if request.method == 'POST':
        assistant_id = request.POST['assistant_id']
        thread_id = request.POST['thread_id']
        query = request.POST['query']
        print(f'{query = }')

        message = client.beta.threads.messages.create(
            thread_id=thread_id,
            role="user",
            content=query
        )
        run = client.beta.threads.runs.create(
            thread_id=thread_id,
            assistant_id=assistant_id
        )

        # Poll for completion
        while run.completed_at is None:
            sleep(1)
            run = client.beta.threads.runs.retrieve(
                thread_id=thread_id,
                run_id=run.id
            )

        messages = client.beta.threads.messages.list(thread_id=thread_id)

        text_messages = reversed([message.content[0].text.value for message in messages.data])
        return render(request, 'answer.html', {'text_messages': text_messages })
    else:
        return HttpResponse(400)

Add sample data script

Create a one-off script to add sample data to the database. Create a file called generate.py in the sim directory with the below code:

import random
from faker import Faker
from sim.models import Person, Organization

fake = Faker()

def generate_person(org, sex):
    first_name = fake.first_name_male() if sex == 'M' else fake.first_name_female()
    last_name = fake.last_name()
    email = f"{first_name.lower()}.{last_name.lower()}@example.com"

    Person.objects.create(
        first_name=first_name,
        last_name=last_name,
        age=random.randint(20, 60),
        sex=sex,
        work_country=random.choice(['US', 'UK', 'DE']),
        salary=random.randint(30000, 100000),
        email=email,
        organization=org
    )

def generate_sample_data():
    _, org1 = Organization.objects.get_or_create(name='Org 3')
    _, org2 = Organization.objects.get_or_create(name='Org 4')
    for i in range(20):
        org = org1 if i % 2 == 0 else org2
        sex = 'F' if i < 10 else 'M'
        generate_person(org, sex)

Run the below code in the Django shell to add sample data to the database:

Open the Django shell:

python manage.py shell

And then run the below code in the shell:

from sim.generate import generate_sample_data
generate_sample_data()

Run the app

Run the app

python manage.py runserver

Enter a query in English and click "Run". You should see the answer to your query after a 5-10 seconds.

Congratulations 🎉

You've built a simple app that lets users query your database in English, using OpenAI's Assistant API.

To enhance the app, particularly involving speeding responses, some things to add would be:

Add a streaming response to get the data from the assistant as soon as the assistant starts responding
Only create the assistant once, and then reuse it for all queries. Currently, we create a new assistant for each user, which needs more time.
Use a faster model in the assistant to get faster responses

Tom Dekan