A Guide to Building an NLP-Based Food Chatbot with Dialogflow.

There are countless online tutorials on AI concepts like Machine Learning, Natural Language Processing (NLP), and Large Language Models (LLMs). Many of them are project-based learning. However, most tutorials only cover fragments of a project pipeline, overlooking key (often minute) details that can leave beginners stuck or frustrated.

This tutorial takes a different, comprehensive and step-by-step approach to building a chatbot, guiding you from scratch to deployment. You’ll learn to configure Dialogflow, set up a MySQL database, write backend code with FastAPI, and deploy your chatbot as a web demo.

Best of all, you’ll work on a project that addresses a real-world need. Skills like these are in high demand, and tools like these can make a real impact.

Let’s get started!

Prerequisites

To get the best out of this tutorial, below are few requirements and a list of packages and tools you’ll need to install.

Basic Requirements

Beginner level knowledge in Python.
Basic familiarity on how file-paths work in computers.
A Google account.

Tools and Software To Install

Signup for a Dialogflow account.
Download and install Pycharm (or any code editor of your choice).
Download and install MySQL workbench.
Signup for an ngrok account.

Understanding The Workflow

For you to have a strong grasp on how building chatbots on Dialogflow works and how each component interacts to facilitate the entire process, we will discuss the workflow below by referencing this particular project.

What is Dialogflow and How Does it Work?

Dialogflow is a cloud-based conversational AI platform owned by Google. It provides a framework for developers to build chatbots, voice assistants, and other conversational interfaces for applications.

One of the cool things about Dialogflow is you don’t need to have technical knowledge to build a simple chatbot on it, (building more complex chatbots may require coding skills).

Dialogflow's framework is built on key components that enable it to interpret user input, and generate appropriate responses. These components are Intents, Entities, Fulfillment, Context, Text Responses, Actions and Parameters, Events and more. Let’s explain each below:

Intent

An intent is a mapping between what the user says and the action that will be taken by the chat bot. In Dialogflow, you can define multiple intents, each handling a specific goal. For example, one intent might handle 'placing new orders,' while another manages 'tracking existing orders.' Dialogflow ensures that the chatbot can correctly identify the user's goal and provide the appropriate response for each intent.

When you interact with a chatbot, Dialogflow analyzes your input and matches it to the most relevant intent using predefined training phrases, which are examples of what a user might say. So if a user says, "I want to order pizza," Dialogflow will match it to the OrderPizzaIntent. It’ll do the same matching process if a user says “i want to track my pizza order” but it’ll match it to a separate intent specifically created to handle user inputs on tracking orders, like trackPizzaIntent.

Fulfillment

The Fulfillment component is what allows Dialogflow to connect with external systems, such as backend servers, APIs, or databases. Here’s how it works: when an intent is triggered, Dialogflow sends a request to a webhook (your server). The webhook processes the request, carries out the required action (like retrieving data from a database or an API), and sends the response back to Dialogflow. Dialogflow then delivers the final response to the user.

An example of this scenario is if a customer asks, "What do you have on your menu?", the chatbot triggers the CheckMenuIntent. Fulfillment then connects to the backend through the API, fetches real-time data from the database, and responds with, “On our menu, we have Brownies, Cake Parfaits, Samosas….”

Entities

Entities are specific pieces of information in the customer's input that represent key parameters Dialogflow uses to understand and tailor its responses. They’re usually values like numbers, dates, items, or names—important details that provide the primary meaning to a sentence or input.

There are system entities and custom entities. System entities are pre-built for dates, numbers, or locations. For example, @sys.date automatically extracts dates, while @sys.number identifies numbers. Custom entities, on the other hand, are user-defined and suited for domain-specific terms. Another example referencing our food chatbot is, for instance, a pizza-ordering bot, where you might create an @pizza-size entity to capture sizes like "small," "medium," or "large."

Context

Contexts are important for understanding the relationship between users input in conversations. In Dialogflow, there are input and output contexts, both under the “Context” column. An input context sets the stage for which intents can be triggered based on the current conversation state, while an output context activates after an intent is triggered. Let's say you're building a bot for a clothing store. If someone just types "blue," the bot wouldn’t know what they mean. Blue what? Shirts? Pants? Hats? But if the user first says "I'm looking for a shirt," that's like setting the context. Now, when they say "blue," the bot understands they mean a blue shirt.

Input contexts are like the "I'm looking for a shirt" part. It tells the bot what kind of responses to expect next. Output contexts are what the bot "sets" after it responds, like the bot now knowing you're looking for shirts. This way, the bot remembers the flow of the conversation and can understand later inputs in that same context.

Getting Started

We will start by downloading Pycharm code editor and installing all the necessary frameworks for this project (you can use any code editor of your choice, but for this tutorial we’ll be using Pycharm).

Downloading and Installing Pycharm

To download and install Pycharm on your computer, follow these steps:

Search the term “download Pycharm” on your browser.
Click on the “JetBrains” download link.
Scroll down until you come across the “Pycharm Community Edition” download Link.
Download and Install Pycharm.
For Mac or Linux users, select your version from the links just above the “Pycharm Professional” download link.
After installing Pycharm, click on “New Project” at the top right corner.
This action will open up an interface with several sections including a name, location of the project, interpreter type and python version. I advise setting your interpreter type to “venv” (virtual environment), it ensures that your project’s dependencies are isolated from the system’s global Python environment.
To start coding, right-click on the project folder, click on “New”, select “Python file” and give the file a name.
Here’s a full Youtube video on setting up Pycharm if you get stuck.

Pip Installing Dependencies

We have to install FastAPI and Uvicorn to be able to link our chatbot to a live server and backend. I’ll walk you through the steps below:

Click on the icon below in your Pycharm editor to open a terminal specific to your current directory.
Pip install FastAPI by inputting this code:
```
 pip install Fastapi
```

Pip install MySQL connector:

 pip install mysql-connector-pythonPip install Uvicorn

Building The Chat bot With Dialogflow

Imagine Dialogflow as a house that's already built. It has the basic structure: walls, a roof, rooms—but it's missing all the details that make it a home. It's like a bare-bones house without paint, furniture, or decorations. It's functional, but it doesn't have a specific purpose yet.

The platform provides the basic framework for creating a chatbot. You can then customize it to do specific things, like build a bot that takes food orders from customers or a virtual assistant that can answer questions about a document.

There are many features that can be added to a chat bot built on Dialogflow, but for this tutorial, our chatbot will focus on facilitating 2 major functions:

Placing new orders
Tracking orders

Dialogflow has two tiers:

Dialogflow ES (Essentials)
Dialogflow CX (Customer Experience)

Dialogflow ES is the simpler and more popular option of the two. You can use Dialogflow ES to build chatbots for a variety of applications from basic FAQs to more complex customer service bots. It offers a free tier for experimentation and learning, as well as a paid tier. Dialogflow CX, however, is the more advanced tier, designed for building complex conversational AI agents with sophisticated conversational flows. Dialogflow CX is a paid tier with no free option.

Setting Up A Dialogflow Project

Search ‘google dialogflow” in your browser.

Click on “Conversational Agents and Dialogflow - Google Cloud”, scroll down until you can see both Dialogflow CX and Dialogflow ES, side by side. Click on “Go to console” on the Dialogflow ES.

It will open up a page that prompts you to input your Google login details, after that you’ll be ushered into Dialogflow proper. I already built a chatbot below, but we’ll create a new agent for yours.

Click on the downward facing arrow at the top-left corner, just beside the wheel icon.

Click on “Create new agent”, give it a name and click “Create”. I decided to name mine “Yummy”.

Setting up Intent

Let’s create our first intent by first understanding how the default welcome intent works in Dialogflow.

When you click on the Default intent, you’ll find the “Event” is set to “Welcome” with the dialogflow logo attached. The "Welcome" event serves as a predefined trigger that automatically activates the intent when a conversation starts, regardless of any specific user input.

If you scroll just below it, you’ll see the “Training Phrases” section and all the training prompts you can use to kickstart the agent. Let’s try one.

In the example above, we typed 'heya,' which is one of the training phrases used to train the agent to recognize user input texts. It replied with a response: “Greetings! How can i assist?”. This reply stems from another section within the Default Welcome intent, below the “Training Phrases” section. It’s called the “Text Response” component.

The text response component defines how and what the agent replies to the user. It is completely customizable, so you can write in whatever response you like. You can even add emojis 🙂.

Let’s say we want to change the already provided text responses to something more inviting and streamlined to our chatbot’s services. To do that, first we have to delete the current text responses and input new ones.

If you hover around a text response, a bin will pop up by the side, that is the delete icon. When you’re done deleting old responses, simply type back into the columns to input new ones. After that, ensure you click on the “Save” button above and wait until it’s done saving.

As you can see, we’ve replaced the old text responses with more appropriate ones (and even added emojis 😁). The input “Heya” now returns a different response.

Let’s go back to the “Intents” page and carry out a similar exercise for the “Default Fallback Intent”. This intent is like a fallback switch for the model, whenever it doesn’t understand an input given. This time around, our new text response will contain guidance on how and what a customer can order.

In the example above i typed in a completely unrelated and unconventional input (Whoopsie!) that didn’t make sense to the model, and so it replied with the new text response i gave it for such situations. For now, this text response triggers automatically, so it doesn’t matter what you type in, it’s still going to return the built-in text response.

We’ll learn how to write code that routes the agent to relevant responses based on the meaning behind user input, rather than displaying a generic message regardless of what is typed. Don’t forget to hit “Save” after changing the text response or making any modification to a value in Dialogflow.

Defining Intents and Entities

For this tutorial we will define 6 intents and 1 entity. Like we learned earlier, intents are the building blocks from which the model derives its functions. Each intent is unique and handles a particular service for the user.

new.order: The new.order intent initiates the order process, similar to a default welcome intent. To maintain conversational context and ensure the bot understands that the user is actively placing an order, we will configure the context section to ongoing-order with a lifespan of five turns (user-bot interactions) when the new.order intent is triggered. This means that for five subsequent interactions, Dialogflow will prioritize matching intents specifically associated with the ongoing-order context. This’ll prevent the bot from straying off-topic and ensures a smooth, focused ordering experience. You can go on to fill the appropriate training phrases and text responses for this particular intent. You don’t have to configure “Event”, “Action and Parameters”, Fulfillment, or any other value within the new.order intent. Again, always click “Save” after configuring context or any other values.
order.add - context: ongoing-order: This intent handles conversational flows where the customer is adding more items to their order list. The context is also set to ongoing-order in both the input and output context windows.

Under the “Training Phrases” section, enter example phrases that a customer might use when adding items to an ongoing order list. Highlight food items and ascribe them to their respective entities, which, for this chatbot, is @food-item would or @sys-number. I’ll explain further below.

The next section is Action and Parameters. In Dialogflow ES, Parameters are how you extract key information from user input, like dates, locations, or product names. You have to define these parameters within an intent, linking them to entities that define the type of data they should capture. In our case, we want to match words like “Strawberry cake” as a food item entity, and three as a number entity. Therefore, we must’ve already defined them in the Action and Parameters table.

We'll add a response from our backend for this intent. Since we're using a database, the chatbot will be able to calculate and display a customer's order in a simple, engaging way—showing what they ordered, the total quantity, and the price. Leave the “Text response” section blank, but enable ‘Webhook’ from the “Fulfillment” section.
order.complete - context: ongoing-order: The purpose of the order.complete intent is synonymous with its name. The intent handles the end of a conversational flow after a customer must have finished ordering. The context is also set to ongoing-order, but solely as an input context.

For the “Training Phrases” section you can use words and sentences a customer would use to signify an end to the conversation. Here are some examples in the snippet below.

There’s no need including “Actions and Parameters” for this intent, you may input a text response, just to test the conversation cycle within Dialogflow. Enable the Webhook under the “Fulfillment” value because we will be connecting this intent to our backend.
order-remove - context: ongoing-order: This intent handles situations where the customer wants to remove an item from their order list. We want our chatbot to be as flexible as possible to accommodate human nature when it comes to ordering stuff online. Especially food. The context for the order.remove intent is also set to ongoing-order in both the input and output context windows.

The “Training Phrase” section will also feature words and sentences a customer would normally input when removing an item from their order list. Don’t worry so much about being very detailed, Dialogflow learns from the example phrases you give and can easily understand variations of it from customers, no matter how differently they are structured.

You do have to highlight some parameters for this intent, so the model can recognize them. You have to highlight numbers as @sys-number and @food-item. Do this after indicating these parameters on your “Action and Parameters” table.

A creative way to include a text response for this intent is to input the value used in the “Action and Parameters” table as a response. This way you don’t have to type in different text responses for each food item on your database. Instead, that specified food item value will automatically fill in whatever food item the customer ordered. In my case you can see i used @food-item as the value.

Lastly, enable the webhook underneath the “Fulfilment” for this intent.
track.order: This intent tracks the customer’s order through an assigned ID, from your database. For this intent, we will set the context to ongoing-tracking and not ongoing-order. This is because, this intent serves a different purpose, therefore we can help the agent understand its duties better.

For the “Training Phrases”, use words and sentences that mimic a customer asking for the tracking status of their order.

There is no need to input “Action and Parameters” or Text responses for this intent, we will handle all that from our backend. However, do enable the webhook under the fulfillment column.
track.order - context: ongoing-tracking: This tracking order intent is similar but serves a different purpose to the former. The context column will contain the “ongoing-tracking” function in both the input and output context windows. The ongoing-tracking context serves two purposes in this intent. As an input context, it ensures that the user's query is relevant to the ongoing conversation about order tracking, acting as a prerequisite for matching the intent. As an output context, it maintains the conversational state by extending the context for subsequent interactions. This allows the chatbot to handle follow-up queries seamlessly, keeping the conversation focused on the "order tracking" topic.

The “Training Phrases” will be tracking IDs in the respective format you want them to be in. Either as numbers, alphabets, etc. For my chatbot i used numbers.

You would’ve guessed we need to define an input in our “Action and Parameters” section for this intent, since we’re using numbers.

You do not need to input a “Text Responses” under this intent. You should, however, enable the webhook, under the “Fulfillment” section.

MySQL Setup and Integration

Instead of dealing with the complexities of setting up the MySQL server and installing multiple dependencies, we’ll take a more portable and efficient approach by downloading and using MySQL Workbench.

Search for “MySQL Workbench” on your browser, then download and install whatever version is compatible with your PC’s operating system.
After installing MySQL Workbench, you’ll create an SQL file of separate tables containing food items, order tracking status and orders made. Here’s a detailed step on how to make one directly from MySQL Workbench:
- Create a table such as the one below on your MS Excel and save it as “Comma delimited” (CSV) format.
- Open your workbench and create a new Schema by clicking on the drum icon at the top-left corner.
- Give the new schema a name and click “apply” at the bottom.
- Click on “Apply” again, then “Finish”. You’ll see the newly created schema on the left hand side where your “Schemas” are highlighted.
- Click on the new schema and right-click on “Tables”.
- Select “Table Data import Wizard”.
- Set the file path to the directory where your already created the excel table in csv format.
- Click “Next” throughout, and “Drop table if exists” which will appear on the second page prompt.
- Finally select “Finish”, right-click on the “Tables” sub-folder within the new schema and select “Refresh all”. You will see the excel table appear as a SQL file.
- Repeat the same process to create a tracking order, and orders executed table. They should be in the format below:
- If you’re interested in learning more about MySQL, here’s a great YouTube video resource.

Backend and FastAPI Integration

Dialogflow is excellent at understanding language and figuring out what users intend to do. But it doesn't store data or connect to external systems on its own. That's where a developer-built backend comes in. It uses 'fulfillment' to connect Dialogflow to your databases and API. So, when a user asks to see their order history, Dialogflow understands the request, but the backend actually fetches that information from a database and sends it back to the user through Dialogflow.

The backend code for this project is collectively in 3 python files: backend.py, generic_helper.py and db_helpmate.py. backend.py is the main FastAPI application that serves as the entry point for handling chatbot requests. generic_helper provides reusable utility functions to simplify and standardize common operations. While db_helpmate.py handles all database-related operations using MySQL, such as inserting orders, tracking statuses, and retrieving prices.

Let’s see the code in detail while i explain:

backend.py

       from fastapi import FastAPI, Request
       from fastapi.responses import JSONResponse
       import db_helpmate
       import generic_helper

       app = FastAPI()

       # initialize global variable
       inprogress_orders = {}

       @app.get("/")
       async def root():
           return {"message": "Welcome to the FastAPI chatbot backend!"}

       @app.post("/")
       async def handle_request(request: Request):
           # Retrieve the JSON data from the request
           payload = await request.json()

           # Extract the necessary information from the payload
           intent = payload['queryResult']['intent']['displayName'].split(" - context")[0]
           parameters = payload['queryResult']['parameters']
           output_contexts = payload['queryResult'].get('outputContexts', [])
           session_id = generic_helper.extract_session_id(output_contexts[0]["name"]) if output_contexts else "unknown_session"

           # Log extracted data for debugging
           print(f"Intent: {intent}")
           print(f"Parameters: {parameters}")
           print(f"Output Contexts: {output_contexts}")
           print(f"Session ID: {session_id}")

           # Updated intent handler dictionary
           # Updated intent handler dictionary to handle 'order.add' correctly
           # Updated intent handler dictionary
           # Updated intent handler dictionary
           intent_handler_dict = {
               'order.add': add_to_order,  # Adding items to order
               'order.add - context: ongoing-order': add_to_order,
               'order.complete': complete_order,  # Completing order
               'order.complete - context: ongoing-order': complete_order,
               'order.remove': remove_from_order,  # Adding 'order.remove' for cases without context
               'order.remove - context: ongoing-order': remove_from_order,  # Removing items with context
               'track.order': track_order,  # Track order without context
               'track.order - context: ongoing-tracking': track_order  # Track order with context
           }

           # Handle the intent or return a default response if the intent isn't mapped
           if intent in intent_handler_dict:
               return intent_handler_dict[intent](parameters, session_id)
           else:
               return JSONResponse(content={
                   "fulfillmentText": f"Sorry, I don't know how to handle the intent '{intent}'."
               })

For the backend.py file we will first start by importing all dependencies. Here, we’re using FastAPI to handle user intents related to food orders. It defines a POST endpoint that processes JSON payloads from Dialogflow, extracting the intent, parameters, and session details.

The chatbot backend uses an intent_handler_dict to map intents (order.add, order.complete) to specific functions like add_to_order or complete_order. These functions manage the in-memory order dictionary (inprogress_orders), saving orders to the database via the db_helpmate module.

The backend interacts with a database for operations like inserting order items, tracking order statuses, and calculating totals. If an unrecognized intent is received, it responds with a default message.

        def save_to_db(order: dict):
            next_order_id = db_helpmate.get_next_order_id()

            # Insert individual items along with quantity in orders table
            for food_item, quantity in order.items():
                rcode = db_helpmate.insert_order_item(
                    food_item,
                    quantity,
                    next_order_id
                )

                if rcode == -1:
                    return -1

            # Now insert order tracking status
            db_helpmate.insert_order_tracking(next_order_id, "in progress")

            return next_order_id

The save_to_db function handles the process of saving a user's order to the database. It first retrieves the next available order_id using db_helpmate.get_next_order_id(). Then, it iterates through the order dictionary, inserting each food item and its quantity into the database by calling the db_helpmate.insert_order_item method. If any insertion fails (returns -1), the function immediately halts and returns -1 to indicate an error.

Once all items are successfully added, the function updates the order_tracking table to mark the order's status as "in progress" using db_helpmate.insert_order_tracking. Finally, it returns the newly generated order_id.

        def complete_order(parameters: dict, session_id: str):
            if session_id not in inprogress_orders:
                fulfillment_text = "I'm having trouble finding your order. Sorry! Can you place a new order please?"
            else:
                order = inprogress_orders[session_id]
                order_id = save_to_db(order)
                if order_id == -1:
                    fulfillment_text = "Sorry, I couldn't process your order due to a backend error. " \
                                       "Please place a new order again."
                else:
                    order_total = db_helpmate.get_total_order_price(order_id)

                    fulfillment_text = f"Awesome. We have placed your order. " \
                                       f"Here is your order id # {order_id}. " \
                                       f"Your order total is {order_total} which you can pay at the time of delivery!"

                del inprogress_orders[session_id]

            return JSONResponse(content={
                "fulfillmentText": fulfillment_text
            })

The complete_order function finalizes an ongoing-order by processing it and providing feedback to the user. It begins by checking if the user's session ID exists in the inprogress_orders dictionary. If the session is not found, it responds with a message asking the user to place a new order.

If the session is valid, the function retrieves the user's order and saves it to the database using the save_to_db function. If saving to the database fails (returns -1), an error message is sent back to the user, prompting them to start over.

When the database operation succeeds, the function calculates the total price of the order using db_helpmate.get_total_order_price. It then constructs a confirmation message containing the order ID and total amount. The session's order data is deleted from inprogress_orders to mark the order as completed. The function returns the appropriate fulfillment response as a JSON object to Dialogflow, ensuring the user is informed of the order status.

        def add_to_order(parameters: dict, session_id: str):
            food_items = parameters["food-item"]
            quantities = parameters["number"]

            if len(food_items) != len(quantities):
                fulfillment_text = "Sorry I didn't understand. Can you please specify food items and quantities clearly?"
            else:
                new_food_dict = dict(zip(food_items, quantities))

                if session_id in inprogress_orders:
                    current_food_dict = inprogress_orders[session_id]
                    current_food_dict.update(new_food_dict)
                    inprogress_orders[session_id] = current_food_dict
                else:
                    inprogress_orders[session_id] = new_food_dict

                order_str = generic_helper.get_str_from_food_dict(inprogress_orders[session_id])
                fulfillment_text = f"So far you have: {order_str}. Do you need anything else?"

            return JSONResponse(content={
                "fulfillmentText": fulfillment_text
            })

Here, the add_to_order function handles adding food items to a user's ongoing-order. It starts by extracting the food-item and number parameters from the request, which represent the items and their respective quantities. If the number of items and quantities do not match, the function responds with an error message asking the user to clarify their request.

If the inputs are valid, the function combines the items and quantities into a dictionary using zip. It checks if the session ID already exists in the inprogress_orders dictionary. If it does, the current order is updated with the new items. Otherwise, a new entry is created for the session ID with the given order. The function then uses generic_helper.get_str_from_food_dict to generate a string representation of the updated order. This string is included in a fulfillment message that informs the user about their current order and asks if they want to add more items. After all this, the function returns the fulfillment message as a JSON response, allowing the chatbot to keep the conversation contextually relevant while updating the user's order.

        def remove_from_order(parameters: dict, session_id: str):
            if session_id not in inprogress_orders:
                return JSONResponse(content={
                    "fulfillmentText": "I'm having a trouble finding your order. Sorry! Can you place a new order please?"
                })

            food_items = parameters["food-item"]
            current_order = inprogress_orders[session_id]

            removed_items = []
            no_such_items = []

            for item in food_items:
                if item not in current_order:
                    no_such_items.append(item)
                else:
                    removed_items.append(item)
                    del current_order[item]

            if len(removed_items) > 0:
                fulfillment_text = f'Removed {",".join(removed_items)} from your order!'

            if len(no_such_items) > 0:
                fulfillment_text = f' Your current order does not have {",".join(no_such_items)}'

            if len(current_order.keys()) == 0:
                fulfillment_text += " Your order is empty!"
            else:
                order_str = generic_helper.get_str_from_food_dict(current_order)
                fulfillment_text += f" Here is what is left in your order: {order_str}"

            return JSONResponse(content={
                "fulfillmentText": fulfillment_text
            })

The remove_from_order function manages the removal of food items from a user's ongoing order. It first checks if the session ID exists in the inprogress_orders dictionary; if not, it returns a message indicating the order is missing. If valid, it retrieves the current order and iterates through the provided food-item list, removing items found in the order and noting items not present. It then constructs a response, informing the user of items removed, items not found, and the current state of the order. If the order is empty, it states this. The function ensures the order data stays updated and provides feedback to the chatbot for the user.

        def track_order(parameters: dict, session_id: str):
            order_id = int(parameters['order_id'])
            order_status = db_helpmate.get_order_status(order_id)
            if order_status:
                fulfillment_text = f"The order status for order id: {order_id} is: {order_status}"
            else:
                fulfillment_text = f"No order found with order id: {order_id}"

            return JSONResponse(content={
                "fulfillmentText": fulfillment_text
            })

The track_order function retrieves the status of an order based on the provided order_id. It calls a database function to fetch the current status of the order. If the status is found, it responds with the order's status. If no order is found with the given ID, it returns a message indicating that the order could not be located. It provides this information to the chatbot for the user’s query on order tracking.

generic_helper.py

import re

def get_str_from_food_dict(food_dict: dict):
    result = ", ".join([f"{int(value)} {key}" for key, value in food_dict.items()])
    return result


def extract_session_id(session_str: str):
    match = re.search(r"/sessions/(.*?)/contexts/", session_str)
    if match:
        extracted_string = match.group(1)
        return extracted_string

    return ""

The import re statement in the generic_helper.py file imports Python's regular expression module, re. This module allows the use of regular expressions to search, match, and manipulate strings.

In the extract_session_id function, re.search is used to search for a specific pattern within the session_str. The pattern being searched for is /sessions/(.*?)/contexts/, which matches the portion of the string between "/sessions/" and "/contexts/" (i.e., the session ID). The re.search function helps find and extract this session ID from the input string.

The get_str_from_food_dict function converts a dictionary of food items and their quantities into a formatted string. It combines the quantity and food item into a readable format, separating each item with a comma. The extract_session_id function extracts the session ID from a given session string using a regular expression. It looks for a pattern in the string that matches the session ID and returns it. If no match is found, it returns an empty string.

db_helpmate.py

```
       import mysql.connector
       global cnx

       cnx = mysql.connector.connect(
           host="localhost",
           user="root",
           password="soot",
           database="pearl_bot"
       )
```
The import mysql.connector statement imports the MySQL connector module, which enables Python to interact with a MySQL database.

In the code, a global connection object cnx is created by calling mysql.connector.connect(). This method connects to a MySQL database using the provided parameters:
- host="localhost" specifies that the database is hosted locally.
- user="root" defines the username for authentication.
- password="soot" is the password for the specified user.
- database="pearl_bot" specifies the name of the database to connect to.

The cnx object is used for subsequent interactions with the database, such as executing queries and retrieving data.

        # Function to call the MySQL stored procedure and insert an order item
        def insert_order_item(food_item, quantity, order_id):
            try:
                cursor = cnx.cursor()

                # Calling the stored procedure
                cursor.callproc('insert_order_item', (food_item, quantity, order_id))

                # Committing the changes
                cnx.commit()

                # Closing the cursor
                cursor.close()

                print("Order item inserted successfully!")

                return 1

            except mysql.connector.Error as err:
                print(f"Error inserting order item: {err}")

                # Rollback changes if necessary
                cnx.rollback()

                return -1

            except Exception as e:
                print(f"An error occurred: {e}")
                # Rollback changes if necessary
                cnx.rollback()

                return -1

The insert_order_item function adds an item to the database by calling a stored procedure in MySQL. It first creates a database cursor (cnx.cursor()) for executing the procedure. The procedure insert_order_item is called with the parameters food_item, quantity, and order_id to store an item's details in the database. After a successful execution, the changes are committed with cnx.commit(), and the cursor is closed. The function prints a success message and returns 1 if the operation is successful.

If a mysql.connector.Error occurs during the process, the error is logged, the transaction is rolled back with cnx.rollback(), and the function returns -1. Similarly, any other exceptions are caught, logged, and handled with a rollback to ensure the database remains consistent.

        # Function to insert a record into the order_tracking table
        def insert_order_tracking(order_id, status):
            cursor = cnx.cursor()

            # Inserting the record into the order_tracking table
            insert_query = "INSERT INTO order_tracking (order_id, status) VALUES (%s, %s)"
            cursor.execute(insert_query, (order_id, status))

            # Committing the changes
            cnx.commit()

            # Closing the cursor
            cursor.close()

The insert_order_tracking function adds a record to the order_tracking table in the database. It begins by creating a database cursor (cnx.cursor()) to execute SQL queries. Using the query INSERT INTO order_tracking (order_id, status) VALUES (%s, %s), it inserts the provided order_id and status into the table. After successfully executing the query, the changes are saved to the database with cnx.commit(), and the cursor is closed to free resources. This function helps track the status of orders in the system.

        def get_total_order_price(order_id):
            cursor = cnx.cursor()

            # Executing the SQL query to get the total order price
            query = f"SELECT get_total_order_price({order_id})"
            cursor.execute(query)

            # Fetching the result
            result = cursor.fetchone()[0]

            # Closing the cursor
            cursor.close()

            return result

This function is essential for calculating the cost of a customer's order. The get_total_order_price function retrieves the total price of an order from the database using its order_id. It creates a database cursor (cnx.cursor()), executes an SQL query (SELECT get_total_order_price({order_id})) that calls a stored function named get_total_order_price, and fetches the result with cursor.fetchone()[0]. The cursor is then closed, and the total price is returned.

        # Function to get the next available order_id
        def get_next_order_id():
            cursor = cnx.cursor()

            # Executing the SQL query to get the next available order_id
            query = "SELECT MAX(order_id) FROM orders"
            cursor.execute(query)

            # Fetching the result
            result = cursor.fetchone()[0]

            # Closing the cursor
            cursor.close()

            # Returning the next available order_id
            if result is None:
                return 1
            else:
                return result + 1

To ensure unique and sequential order IDs for new entries, the get_next_order_id function determines the next available order_id for new orders in the database. It creates a database cursor (cnx.cursor()), executes an SQL query (SELECT MAX(order_id) FROM orders) to find the highest current order_id, and fetches the result with cursor.fetchone()[0]. After closing the cursor, it checks if the result is None (indicating no existing orders) and returns 1 in that case. Otherwise, it returns the next available order_id by adding 1 to the current maximum.

Integrating FastAPI

Asides writing code for our backend, we need a working server that can fetch information from our database to the customer. Hence, the reason why we need FastAPI. Below, i will work you through how to get started with FastAPI and Uvicorn, and how to integrate them successfully with the chatbot.

First we’ll download ngrok, it’s a tool that helps convert “http’ protocol to a much secure “https” URL link. Dialoglow only accepts “https” protocol for its webhook integration. Here’s how to download and setup an ngrok account:
- For macOS:
  1. Go to ngrok's download page and select the macOS version.
  2. Unzip the downloaded .zip file.
  3. Create a folder for this project if you haven’t already and move the extracted ngrok file there for global access.
  4. Open an account with ngrok and add your ngrok authentication token.

For Windows:
1. Download: Visit ngrok's download page and select the Windows version.
2. Extract: Unzip the downloaded .zip file.
3. Move the ngrok.exe file to the same directory PATH where for chatbot folder is stored.
4. Open an account with ngrok and add your ngrok authentication token.

Since we already have FastAPI and Uvicorn installed, it’s time to setup our server. Go to your Pycharm terminal, after you must have installed FastAPI and Uvicorn, type in the following command:
```
 uvicorn backend:app --reload
```
the reason why we’re using “backend” is because that is the name of the file our FastAPI application is in. It may be “main.py” or something different for yours.
Copy paste the "http 8000” url link provided into your browser and you should see the following message, indicating that the server is live:

Putting It All Together

The first thing we need to do is convert the “http” protocol to a secure “https” URL acceptable by Dialogflow.

Open the command prompt in your computer.
Change the directory to the exact folder location the ngrok.exe file is in your PC and type in the following comand: “ngrok http 8000”. (Note for the image below, the change directory command is “cd” and the rest of the file path links to my ngrok.exe folder. )
The ngrok server should immediately come online and a https server link will be issued to you for that address:
Copy the url link:
Paste it into the Fulfillment section of your Dialogflow chatbot:
Now go to your Pycharm terminal and activate FastAPI using the Uvicorn command:
```
 uvicorn:backend --reload
```
The server will automatically become live.
Next, copy the URL link from your chatbot’s fulfillment section and paste on your browser. If you see the message in the image below, that means your server is live and working properly:

Testing Our Chatbot’s Workflow

Let’s test the workflow as a live web demo so we see how it functions when we deploy it.

In Dialogflow, navigate to “Integrations”
Scroll down and select “Web Demo”
Click on the URL link highlighted. It’ll take you to an interface where you can chat with the bot you just created.
Prompt your chatbot by sending “Hi”
Make a new order
Test the “order.add” intent by prompting the chatbot to add an order to your existing one:
Test the “order.remove” intent by prompting it to remove some items from the current order:
When you’re done tell the bot.
Test the “track.order” feature by requesting to track your order.

Our Chatbot is working correctly and is ready for deployment. To deploy, all you have to do is copy the code highlighted in the web demo page into the body section of your index.html file (as shown below).

Deployment

In this section we’ll build a simple but visually appealing Web UI interface that we can deploy our built chatbot to. We’ll be using simple HTML and CSS for styling. If you are not conversant with HTML or CSS you can just prompt ChatGPT to generate a base code for you to build on. You don’t have to copy paste the code blocks below verbatim onto your editor. You can add a different styling, color, typography, etc, whatever suits your tastes. Now, let’s get started building our interface.

Create a separate frontend folder inside Pycharm for this project on a separate window.
Right-click on the project folder, select “New” and create an HTML file (Not a python file), you can name it “index.html”.

Input the following HTML code

 <!DOCTYPE html>
 <html lang="en">
 <head>
     <meta charset="UTF-8">
     <meta name="viewport" content="width=device-width, initial-scale=1.0">
     <title>Pearl's Treat Chatbot</title>

     <!-- Reference to the external CSS file -->
     <link rel="stylesheet" href="styles.css">
 </head>
 <body>

     <!-- Hero Section -->
     <section id="hero">
         <div>
             <h1>Welcome to Pearl's Treat Chatbot</h1>
             <p>Talk to us about your favorite treats!</p>
         </div>
     </section>

     <!-- Chatbot fixed on the side -->
     <div id="chatbot-container">
         <iframe width="350" height="430" allow="microphone;"
             src="https://console.dialogflow.com/api-client/demo/embedded/510ab3ee-444e-4ba7-9e44-9953a1d22be7">
         </iframe>
     </div>

     <!-- Footer Section -->
     <footer>
         <p>&copy; 2025 Pearl's Treat - Ada George, Port Harcourt</p>
     </footer>

 </body>
 </html>

The HTML code sets up a simple webpage for "Pearl's Treat Chatbot." The <head> section handles the page title, linking an external styles.css file for styling, and ensuring the layout works well on all devices.

The <body> includes three main parts: the Hero Section, which welcomes users with a headline and brief description; the Chatbot Section, where our Dialogflow chatbot is embedded in an <iframe>; and the Footer, provides copyright and location information.

Right-click again on the project folder, select “New” and “File”. This action will create a CSS file, you can name it styles.css.

Input the following CSS code within the newly created file:

 * Body and general page styling */
 body {
     font-family: 'Arial', sans-serif;
     background-color: #f8d7f7; /* Light pink background */
     color: #333;
     margin: 0;
     padding: 0;
 }

 /* Hero section styling */
 #hero {
     position: relative;
     background-color: #9b6fb0; /* Light purple color */
     color: white;
     height: 400px;
     display: flex;
     justify-content: center;
     align-items: center;
     text-align: center;
 }

 #hero h1 {
     font-size: 3rem;
     color: white;
 }

 #hero p {
     font-size: 1.2rem;
     color: white;
 }

 /* Chatbot fixed sidebar styling */
 #chatbot-container {
     position: fixed;
     right: 20px;
     bottom: 20px;
     z-index: 1000;
     border-radius: 8px;
     box-shadow: 0 4px 8px rgba(0, 0, 0, 0.2);
 }

 iframe {
     border: none;
     border-radius: 8px;
 }

 /* Footer styling */
 footer {
     margin-top: 20px;
     text-align: center;
     font-size: 14px;
     color: #777;
 }

 /* Responsive adjustments for smaller screens */
 @media (max-width: 768px) {
     #hero h1 {
         font-size: 2rem;
     }

     #chatbot-container {
         bottom: 10px;
         right: 10px;
     }
 }

 /* Footer styling */
 footer {
     margin-top: 40px;
     font-size: 14px;
     color: #777;
 }

 /* Add responsiveness for small screens */
 @media screen and (max-width: 768px) {
     h1 {
         font-size: 2rem;
     }

     iframe {
         width: 90%;
         height: 400px;
     }

     p {
         font-size: 1rem;
     }
 }

The CSS code styles the chatbot interface with an appealing design. The body has a light pink background and clean typography, while the hero section features a light purple background with centered, white text. The chatbot is fixed in the bottom-right corner with a rounded, shadowed container, ensuring it stays accessible as the page scrolls. The footer is minimal and centered for additional details. Responsive adjustments ensure the layout adapts smoothly to smaller screens, resizing elements for a user-friendly experience across devices.

To be able to view your newly built web UI, go back to the frontend folder you created in your computer files and click on the Chrome icon that should be displayed as the “index.html” file. It may appear differently if you use a different browser.
Here’s how mine looks like:

Conclusion

Congratulations, you just learned how to build a chatbot, end-to-end, from scratch using Dialogflow for Natural language processing, MySQl as a database and FastAPI for backend integration. As a bonus, we also learned how to deploy our chatbot onto a website. It is my hope that with this knowledge you can get started easily on building more powerful and creative chatbots for your own various use cases. Here’s a link to my Github repository where you’ll find all the codes for this project available.

How to Build an End-to-End NLP Food Chatbot in Dialogflow with MySQL and FastAPI

Table of contents