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 fakerdjango-admin startproject core .python manage.py startapp sim
- Add our app
sim
to theINSTALLED_APPS
insettings.py
:
# settings.pyINSTALLED_APPS = ['sim',...]
- Create a file called
.env
atcore/.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.pyfrom pathlib import Pathimport osfrom dotenv import load_dotenvload_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.pyfrom django.db import modelsclass 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 makemigrationspython 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 renderfrom .models import Persondef 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 thesim
directory with the below code:
# urls.pyfrom django.urls import pathfrom . import viewsurlpatterns = [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.pyfrom django.contrib import adminfrom django.urls import include, pathurlpatterns = [path('admin/', admin.site.urls),path('', include('sim.urls')),]
Create a template to display the people
- Create a
templates
directory in thesim
directory - Create a
people.html
file in thetemplates
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 thetemplates
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 }}" /><inputtype="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 thetemplates
directory
<div>{% for text_message in text_messages %}<p>{{ text_message }}</p>{% endfor %}</div>
Update sim/views.py
to the below:
# views.pyfrom time import sleepfrom django.core.serializers import serializefrom django.http import HttpResponsefrom django.shortcuts import renderfrom .models import Personfrom openai import OpenAIimport osclient = 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 completionwhile 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 thesim
directory with the below code:
import randomfrom faker import Fakerfrom sim.models import Person, Organizationfake = 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 org2sex = '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_datagenerate_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