Python Webhook for integrating Dialogflow CX chatbot with CloudSQL database

This article is about Building a Hotel booking Chatbot in Dialogflow CX and integrating it with Mysql backend database which is hosted on Google Cloud SQL using Python webhooks. This article gives you an overview of how to build Entities, Intents, Route groups, python webhooks, Flows, and Pages.

Dialogflow CX

Dialogflow CX provides a new way of designing agents, taking a state machine approach to agent design. This gives you clear and explicit control over a conversation, a better end-user experience, and a better development workflow.

CX is firmly embedded as an extension of the Google Cloud Platform offering.

Chatbot Overview: The Chatbot is build to demonstrate following tasks related to the hotel booking:

1. Booking a room in hotel
2. Checking for Booking Status
3. Cancel the booking

You can build an end to end chatbot by following the below steps. Also, agent export is available on github, you can simply import the blob file in Dialogflow CX.

Step 1: Creating A Project

The first step in creating a CX application is to create a new project within Google Cloud Platform. This is the first indication that CX is strongly aligned with the Google Cloud Platform.

Within CX you can have various projects. Projects are the highest order of organising your assistants.

New GCP Project creation

Step 2: Enable the Dialogflow API

1. In the console search field, type in “Dialogflow API”.
2. Select the Dialogflow API tile.
3. Click the Enable button.

Step 3: Create your agent

1. Visit the Dialogflow CX console, then select your Cloud Project name, which was created at earlier step. Click Check my progress to verify the objective.

2. Click Create agent. If you do not see this page, refresh your browser.

Create Dialogflow CX agent

3. Name your agent Hotel Booking.

4. Pick global from the Location drop-down.

5. Click Create.

Agent creation

6. After creating the agent, navigate to Agent Settings > General > Logging settings and check the boxes next to Enable Cloud Logging and Enable interaction logging option. It will generate logs for this agent.

7. Click Save.

Enable Logging

Step 4: Create Entities

• Entities define the type of information you wish to extract from an end-user, ex: city you want to fly to. Use Dialogflow’s built-in “ system entities’’ for matching dates, times, colors, email addresses, and so on.
• System entities can also be “extended” to include values that are not part of the default system values. If you need to create a fully customized entity, you can do so by creating a Custom Entity type for matching data that is custom to your business and not found as a system entity.

In this use case we need to create two custom entities- Room_type and phone_number. Follow the given steps to create custom entities:

Procedure to create room_type entity:

1. Click on Manage option in the top left corner
2. Click on “Entity types”
3. Click the Create option to create a new entity
4. Enter the name of the entity as “room_type”
5. Enter the reference values and synonyms as shown in the below table.

room_type entity

Procedure to create phone_number entity:

1. Click the Create option to create a new entity
2. Enter the name of the entity as “phone_number”
3. Check “Regex entities” Option
4. Enter the reference regex expression:
   ^(\+\d{1,2}\s?)?1?\-?\.?\s?\(?\d{3}\)?[\s.-]?\d{3}[\s.-]?\d{4}$

This regex validates various phone number formats such as: Only number, Country code and phone number, 10 digit, etc.

phone_number entity

Note: As a best practice create required entities before creating intents so that entities can be mapped automatically in the training examples.

Step 5: Create Intents

​​Intents are the reasons an end-user has for interacting with the agent, for example, ordering something. You can create an intent for every topic they may want to navigate.

Intents can be reused across Pages and Flows. Each intent is defined by training phrases end-users typically ask. These can be annotated or “labeled” to collect specific parameters, such as arrival city or departure date.

Dialogflow CX will suggest annotations as you include training phrases for the intent; they can also be manually annotated to collect the parameter values you want to extract from the end-user’s interaction with your agent.

In this case, we need to create three intents- “book_room”, “Booking Status” and “Cancel Booking”.

Create a intent — book_room

1. Click Manage > Intents > Create :

2. Display Name: book_room

3. Under the Training Phrases header, add each of the following phrases into Dialogflow, click Enter after each phrase:

• Book a Room
• book a room for tomorrow
• Book a room on 4th march of room type suite
• Book a Deluxe room on 4th march 2023

Note: For higher model accuracy, using 20–50 training phrases with short and long response options is recommended.

4. Click Save.

5. Some words are highlighted because Dialogflow has automatically labeled the entities, such as a date, place, or number.

book_room intent

Note: You can also add training phrases in bulk by creating a training phrase CSV file and uploading it to Dialogflow.

Create an intent — booking_status

1. Create an intent named booking_status by following the same steps mentioned in the previous section.
2. Under the Training Phrases header, add each of the following phrases into Dialogflow, click Enter after each phrase:

• get booking details
• Booking status for phone number 1234 for the date 4th march
• Booking status

booking_status intent

Create an intent — cancel_booking

1. Create an intent named cancel_booking by following the same steps mentioned in the previous section.
2. Under the Training Phrases header, add each of the following phrases into Dialogflow, click Enter after each phrase:

• cancel booking with booking date 4th march
• cancel booking with phone number 12345 on booking date tomorrow
• Cancel my Booking with registered phone number 12345
• Cancel my Booking

cancel_booking intent

Step 6: Grouping FAQs

Frequently Asked Questions (FAQs) may happen at any stage of a conversation. For instance, FAQs include questions about store opening hours, refund policies, etc. In an airline scenario, some FAQs are about carry-on baggage limits and flight upgrades.

In this section, you will create a few FAQs and explore the use of route groups to group these FAQs. This will enable your virtual agent to deviate from a conversational flow, quickly answer these side queries, and then return to the main flow of the conversation.

Create some FAQ intents

Create three new intents with training phrases as follows:

Create a general route group

1. Select Route Groups under the Manage tab.
2. Click + Create.
3. Enter General FAQs for the Display name.
4. Click Continue.
5. Click into the newly created General FAQs route group.
6. Next, you’ll add all three of the FAQ intents you created.
7. For each intent in the list, click Add route, select the intent, and add the following for the fulfillment messages and click Save:

Step 7: Create a mysql db hosted in Cloud SQL

1. Go to the Cloud SQL overview page.
2. Spin a new mysql db hosted on cloud sql or use existing db
3. Create below two sample tables for this demo and create entries into room_type table as shown in the screenshot.

Booking table structure

room_type table structure

DDL’s and DML ‘s for the above tables are available in Github.

Step 8: Create a webhook

Create the Cloud Function

1. Go to the Cloud Functions overview page.
2. Click Create Function, and set the following fields:

• Environment: 1st gen
• Function name: hotel_booking
• Region: If you specified a region for your agent, use the same region.
• HTTP Trigger type: HTTP
• URL: Click the copy button here and save the value. You will need this URL when configuring the webhook.
• Authentication: Allow unauthenticated invocations
• Require HTTPS: checked

3. Click Save.

4. Click Next (You do not need special runtime, build, connections, or security settings).

5. Set the following fields:

• Runtime: Python 3.7.
• Source code: Inline Editor
• Entry point: handle_webhook

6. Replace the following code in main.py. Please note to review and change which are within <>.

import pymysql


def handle_webhook(request):


   connection = pymysql.connect(unix_socket='/cloudsql/<project name>:<region>:<cloudsql instance name>',
                               user='<userid>',
                               password='<password>',
                               database='<db name>',
                               cursorclass=pymysql.cursors.DictCursor)


   req = request.get_json()
   tag = req["fulfillmentInfo"]["tag"]
   phone_number = str(int(req["sessionInfo"]["parameters"]["phone_number"]))


   if tag == "get_room_type":
       booking_date_year = str(int(req["sessionInfo"]["parameters"]["booking_date"]["year"]))
       booking_date_month = str(int(req["sessionInfo"]["parameters"]["booking_date"]["month"]))
       booking_date_day = str(int(req["sessionInfo"]["parameters"]["booking_date"]["day"]))
       booking_date = str(booking_date_year)+'-'+str(booking_date_month)+'-'+str(booking_date_day)
       with connection:
           with connection.cursor() as cursor:
               cursor.execute("SELECT A.room_type FROM room_type A left join (select room_type,count(*) as no_of_room_booked from booking where booking_date ='%s' group by room_type) B on A.room_type=B.room_type where COALESCE(num_of_rooms-no_of_room_booked,num_of_rooms) >0;" % booking_date)
               result = cursor.fetchall()
               result = ", ".join([str(row["room_type"]) for row in result])
       if result== "":
           msg="Sorry! No room is available for the booking date."
       else:
           msg= "Select the preferred room type. Below are the available Room types on the date {} \n {}".format(booking_date,result)
       res = {"fulfillment_response": {"messages": [{"text": {"text": [msg]}}]}}
       return res
   elif tag == "confirm_booking":
       room_type = req["sessionInfo"]["parameters"]["room_type"]
       booking_date_year = str(int(req["sessionInfo"]["parameters"]["booking_date"]["year"]))
       booking_date_month = str(int(req["sessionInfo"]["parameters"]["booking_date"]["month"]))
       booking_date_day = str(int(req["sessionInfo"]["parameters"]["booking_date"]["day"]))
       booking_date = str(booking_date_year)+'-'+str(booking_date_month)+'-'+str(booking_date_day)
       with connection:
           with connection.cursor() as cursor:
               cursor.execute("SELECT A.room_type FROM room_type A left join (select room_type,count(*) as no_of_room_booked from booking where booking_date ='%s' group by room_type) B on A.room_type=B.room_type where COALESCE(num_of_rooms-no_of_room_booked,num_of_rooms) >0;" % booking_date)
               result = cursor.fetchall()
               result = ", ".join([str(row["room_type"]) for row in result])
               if room_type in result:
                   sql_insert_query = """insert into booking values (%s,%s,%s)"""
                   cursor.execute(sql_insert_query, (phone_number,booking_date,room_type,))
                   connection.commit()
                   return None
               else:
                   res = {"sessionInfo": {"parameters": {"room_type": None,},},}
                   return res
   elif tag == "cancel_booking":
       booking_date_year = str(int(req["sessionInfo"]["parameters"]["booking_date"]["year"]))
       booking_date_month = str(int(req["sessionInfo"]["parameters"]["booking_date"]["month"]))
       booking_date_day = str(int(req["sessionInfo"]["parameters"]["booking_date"]["day"]))
       booking_date = str(booking_date_year)+'-'+str(booking_date_month)+'-'+str(booking_date_day)
       with connection:
           with connection.cursor() as cursor:
               cursor.execute("Delete FROM booking where booking_date =%s and phone_number = %s;", (booking_date,phone_number,))
               connection.commit()
               return None
   elif tag == "get_booking":
       with connection:
           with connection.cursor() as cursor:
               cursor.execute("select booking_date,room_type FROM booking where phone_number = %s;", (phone_number,))
               result = cursor.fetchall()
               result = ", ".join([f'{row["booking_date"]} with room type {row["room_type"]}' for row in result])
               len_result = len(result)
               if len(result) > 0:
                 result = f"You have Confirmed bookings on {result}"
               else:
                 result = f"You do not have any bookings with this number"


       res = {"fulfillment_response": {"messages": [{"text": {"text": [result]}}]}}
       return res
   else:
       result = f"There are no fulfillment responses defined for {tag} tag"
       res = {"fulfillment_response": {"messages": [{"text": {"text": [result]}}]}}
       return res

7. Replace the following code in requirements.txt.

pymysql

8. Click Deploy.

9. Wait until the status indicator shows that the function has successfully deployed. While waiting, examine the code you just deployed. Code comments describe important details.

Create the webhook

Now that the webhook exists as a Cloud function, you will associate this webhook with your agent. To create the webhook for your agent:

1. Open the Dialogflow CX Console.
2. Choose your Google Cloud project.
3. Select your agent.
4. Select the Manage tab.
5. Click Webhooks.
6. Click Create.
7. Complete the following fields:

• Display name: room_booking
• Webhook URL: Provide the webhook URL you saved when creating the function.
• All other fields use default values.

8. Click Save.

Step 9: Create Flows and Pages

Flows are used to define topics and the associated conversational paths. Every agent has one flow called the Default Start Flow. This single flow may be all you need for a simple agent.

More complicated agents may require additional flows, and different development team members can be responsible for building and maintaining these flows.

Every flow starts with a Page, and is made of one or multiple different pages thereafter to handle the conversation within a particular flow. The current page an end-user is on is considered the “active page”. Each page can be configured to collect any required information from the end-user.

1. At the top left corner of the screen, you will find the Build and manage tab. Click on Build tab and you will see a window with “Default start flow” as shown in the screenshot below.

2. Click on the “+” button and create three flows named: Book a room , Booking Status and Cancel Booking.

3. After creating three flows you will see flows created, as shown in the screenshot given below:

Default Start Flow

The page your agent starts from is called the Default Start Flow. Pages store routing logic, responses (known as Fulfillment), specific actions to take if an intent cannot be matched (known as no-match) or receives no-input (which is when the agent does not receive a response in time).

1. Click Build.
2. Click Start to open the page.

3. From the expanded options on the Start page, select the + icon next to Routes.

4. Select the intent book_room from the drop-down, scroll down to the transition section and select flow and select “Book Room” from the drop-down then click Save.

5. Repeat the same steps for adding routes for the intent booking_status and select transition as Booking Status flow.

6. Repeat the same steps for adding routes for the intent cancel_booking and select transition as Cancel Booking flow.

7. Next, you need to add the route group to the Start page in the Default Start Flow so that Dialogflow knows you want the intents/routes defined in the route group triggered any time it finds a match during the flow. Click on Add state handler.

8. Click on the checkbox to enable Route groups then click on Apply.

9. Click + to the right of Route groups to add a new route group.

10. Select General FAQs from the route group dropdown.

11. Click Save.

12. Notice the General FAQs route group shows (3) next to it indicating that it contains three routes.

Book Room Flow

1. Click on Book Room Flow.
2. Click Start to open the page.

3. From the expanded options on the Start page, select the + icon next to Routes.

4. Select the intent book_room from the drop-down, scroll down to the transition section and select page and choose ‘+ new Page’ from the drop-down.

5. Name the page ‘get details’ and click Save

6. Now go to the main window of the flow and click on the page ‘get details’ , then the + by Parameters to collect booking data.

7. Enter phone_number in the Display name field.

8. Choose @phone_number from the Entity type drop-down.

9. Scroll down to Initial prompt fulfillment > Agent Says and add ‘Please provide your phone number’ and Click Save.

10. Exit this window to make another parameter.

11. Click the + by Parameters again to create 2 additional parameters one by one with the following name, entity type, and how the agent will prompt the end-user.

12. Click on room_type parameters again, under webhook settings, enable the webhook and select room_booking from the drop-down and update Tag field as get_room_type and Save.

13. Click the + by Routes and select the condition “Match AT LEAST ONE rule (OR)” and update parameter as “$page.params.status”

14. Scroll down to locate Routes and click the + sign next to it.

15. Scroll down to Condition select “Match AT LEAST ONE rule (OR)”

16. In the Parameter field enter $page.params.status

17. Choose the = sign in the Operand drop-down.

18. In the Value field enter: “FINAL” (ensure you include the double quotes)

19. Scroll down to the transition section and select page and choose ‘+ new Page’ from the drop-down.

20. Name the page ‘confirm booking’ and click Save

When finished they are listed like this:

Note: The order in which the parameters are listed affects the order in which the booking agent will ask for each. You can easily change the order by dragging parameters up or down.

21. Now go to the main window of the flow and click on the page ‘confirm booking.

22.Scroll down to locate Routes and click the + by Routes and create the following routes one by one by following details given below

When finished they are listed like this:

Booking Status Flow

1. Click on Booking Status Flow.
2. Click Start to open the page.
3. From the expanded options on the Start page, select the + icon next to Routes.
4. Select the intent booking_status from the drop-down, scroll down to the transition section and select page and choose ‘+ new Page’ from the drop-down.
5. Name the page ‘get details’ and click Save
6. Now go to the main window of the flow and click on the page ‘get details’ , then the + by Parameters to collect booking data.
7. Enter phone_number in the Display name field.
8. Choose @phone_number from the Entity type drop-down.
9. Scroll down to Initial prompt fulfillment > Agent Says and add ‘Please provide your phone number’ and Click Save.

10. Scroll down to locate Routes and click the + by Routes and create the following routes one by one by following details given below

When finished they are listed like this:

Cancel Booking Flow

1. Click on Cancel Booking Flow.
2. Click Start to open the page.
3. From the expanded options on the Start page, select the + icon next to Routes.
4. Select the intent cancel_booking from the drop-down, scroll down to the transition section and select page and choose ‘+ new Page’ from the drop-down.
5. Name the page ‘get details’ and click Save
6. Now go to the main window of the flow and click on the page ‘get details’ , then the + by Parameters to collect booking data.
7. Enter phone_number in the Display name field.
8. Choose @phone_number from the Entity type drop-down.
9. Scroll down to Initial prompt fulfillment > Agent Says and add ‘Please provide your phone number’ and Click Save.

10. Exit this window to make another parameter.

11. Click the + by Parameters again to create 1 additional parameter with the following name, entity type, and how the agent will prompt the end-user.

12. Scroll down to locate Routes and click the + by Routes and create the following routes one by one by following details given below

When finished they are listed like this:

Step 10: Create No Match and No Input event handlers

1. Next, configure the agent to re-prompt users when their inputs are not recognized (or matched) by the virtual agent.
   In the Default Start Flow, you can find examples of Event handlers: sys.no-match-default and sys.no-input-default.
2. Set up similar event handlers for all the parameters present under all the flows.
3. Create one No Match and one No Input handler for each of the parameters under “Reprompt event handlers”.

Step 11: Testing

1. To test that your agent works as intended, click on Test Agent in the upper right corner of the screen.
2. Interact with the agent as if you were the end-user. As you move through the main flow, notice the pages, intents, and transitions you created.

Here is an example of the above agent testing in the Test Agent console:

Conclusion

This article explains on a high level, how to use different features in Dialogflow CX. You can extend Intents, Entities, webhook functions based on the use case, and enhance the functionality of end to end hotel booking chatbot.