Display fetch data from MySQL Database as an output

Rasa Open Source
1

Hi everyone, I have below code to fetch data from MySQL database in rasa, which is working fine. I want to show fetched data to user. e.g. after fetching the data if found, it should show user that your details are:

else It should show except message : “Error : Unable to fetch data”.

db_connect.py

def DataFetch(phone):
        mydb = mysql.connector.connect(
                   host="localhost", 
                   user="root", 
                   passwd="password", 
                   database="db"
            ) 
        mycursor = mydb.cursor() 
        sql = 'SELECT UserName, UserPhone, UserEmail from rasa_db.Users where UserPhone="{0}";'.format(phone)

        try:
            #Execute the SQL Query
            mycursor.execute(sql) 

            results = mycursor.fetchall()

            for row in results:
                UserName = row[0]
                UserEmail = row[2]

                #Now print gethced data
                print("User Name: %s,User Email: %s" % (UserName,UserEmail))
        except:
            print("Error : Unable to fetch data.")

action.py

class ActionFetchData(Action):
    def name(self) -> Text:
        return "action_response"
    
    def run(self, dispatcher: CollectingDispatcher,
            tracker: Tracker,
            domain: Dict[Text, Any]) -> List[Dict[Text, Any]]:

        user_phone = tracker.get_slot("user_phone")

        DataFetch(tracker.get_slot("user_phone"))

        return []

Thanks in advance!!

2

Hello and welcome to the forum :slight_smile:

What’s your question? Everything seems correct for me - except for the fact that you are not showing the message to the user.

If this is your error, then you should know print() prints to the Action Server console, while dispatcher.utter_message() prints the text (or others, check the docs) in the chat.

db_connect.py

def DataFetch(phone):
    mydb = mysql.connector.connect(
        host="localhost", 
        user="root", 
        passwd="password", 
        database="db"
    )

    mycursor = mydb.cursor() 
    sql = 'SELECT UserName, UserPhone, UserEmail from rasa_db.Users where UserPhone="{0}";'.format(phone)

    try:
        #Execute the SQL Query
        mycursor.execute(sql) 
        results = mycursor.fetchall()

        UserName = results[0][0]
        UserEmail = results[0][2]

        #Now return fetched data
        return f"User Name: {UserName}, User Email: {UserEmail}"

    except:
        return "Error : Unable to fetch data."

actions.py

class ActionFetchData(Action):
    def name(self):
        return "action_response" # Be careful, did you mean action_fetch_data?
    
    def run(self, dispatcher, tracker, domain):
        message = DataFetch(tracker.get_slot("user_phone"))
        dispatcher.utter_message(message) # or dispatcher.utter_message(text = message)

        return []
3

@Bobby Welcome to the Rasa community and family :slight_smile: Happy Learning!!

add this in action.py

dispatcher.utter_message(template="utter_response")

utter_response:
 - text: "Your User Name :  **{UserName}**" and Email: **{UserEmail}**"

@Bobby If you are able to see these values printout in action server then you can render it in domain.yml

Please do check the code again.

1 Like
4

Thanks for your replies @nik202 and @ChrisRahme .

@ChrisRahme , I actually wanted to display name and email.

@nik202 , I had already tried your option but I’m getting below output: Your Name : None, Email: None.

Also please suggest How can I also display the except part of message i.e. “Error : Unable to fetch data ” if record is not found in the output.

**both the try and except part are working fine in rasa run actions output.

1 Like
5

@Bobby Right then what is the significance of this code?

class ActionFetchData(Action):
    def name(self) -> Text:
        return "action_response"
    
    def run(self, dispatcher: CollectingDispatcher,
            tracker: Tracker,
            domain: Dict[Text, Any]) -> List[Dict[Text, Any]]:

        user_phone = tracker.get_slot("user_phone")

        DataFetch(tracker.get_slot("user_phone"))

        return []
6

@Bobby doesn’t my code work? Just use dispatcher.utter_message() instead of print().

To not have to pass dispatcher as argument to the DataFetch() function, I made it return the message instead, to be printed in the run() method. But you can also do this:

def DataFetch(phone, dispatcher):
    mydb = mysql.connector.connect(
        host="localhost", 
        user="root", 
        passwd="password", 
        database="db"
    )

    mycursor = mydb.cursor() 
    sql = 'SELECT UserName, UserPhone, UserEmail from rasa_db.Users where UserPhone="{0}";'.format(phone)

    try:
        #Execute the SQL Query
        mycursor.execute(sql) 
        results = mycursor.fetchall()

        UserName = results[0][0]
        UserEmail = results[0][2]

        #Now print fetched data
        dispatcher.utter_message(f"User Name: {UserName}, User Email: {UserEmail}")

    except:
        dispatcher.utter_message("Error : Unable to fetch data.")



class ActionFetchData(Action):
    def name(self):
        return "action_response" # Be careful, did you mean action_fetch_data?
    
    def run(self, dispatcher, tracker, domain):
        DataFetch(tracker.get_slot("user_phone"), dispatcher)

        return []

Querying the database for the phone number through the DataFetch() function. The first line (user_phone = ...) is useless though.

1 Like
7

I suggest you check if the user has given their phone number as well, otherwise the query won’t work. Of course, it will print the error message, but I think being a bit more precise is better for the end user :slight_smile:

class ActionFetchData(Action):
    def name(self):
        return "action_response" # Be careful, did you mean action_fetch_data?
    
    def run(self, dispatcher, tracker, domain):
        user_phone = tracker.get_slot("user_phone")

        if user_phone: # user_phone is not None
            dispatcher.utter_message(DataFetch(user_phone))
        else: # user_phone is None
            dispatcher.utter_message("Please give me your phone number.")

        return []
8

@ChrisRahme you are awesome!! thank you for your help!!

1 Like
9

@nik202 this code helps to trigger DataFetch action and search db using phone number entered by user. But I was unable to print the fetched data here.

10

Hey @ChrisRahme I don’t know why but the “No record found” part is not working. any suggestions?

11

Which part are you talking about? This one?

Can you develop more what you mean by “not working”? What’s happening?

12

This part…

except:
                dispatcher.utter_message("No record found.")
                #dispatcher.utter_message(response = "utter_want_appointment") ##ask user it he wants to book a new appointment

If number is not found, the bot is not saying that No record found.

13

Please ignore…it suddenly started working :face_with_thermometer:

14

This part isn’t in the above code but I assume it’s this one:

Are you using Rasa X or Rasa Shell to talk to the bot?

  • If Rasa X, try Rasa Shell. Rasa X has difficulties showing the output sometimes, especially after queries I noticed.
  • If Rasa Shell, try to see if the code even enters the except with a print() inside of it. The print() will output to the Action Server logs/terminal.
    except Exception as e:
    print("\n\n>>> I am here! <<<")
    print("Error:", e, "\n\n")
    dispatcher.utter_message("Error : Unable to fetch data.")
15

will try this part if it happens again…thanks again!! :slight_smile:

1 Like