MSSQL Database Connectivity

Getting Started with Rasa Rasa Certification Workshops
1

Hi,

I am trying to establish database connectivity with MSSQL from Rasa. The requirement is to pass userid (entered by user) to a table in MSSQL and retrieve the records from table. Use these as response to user by Chatbot.

Thanks, Pooja

2

Can someone please respond here?

Below are my files and I am getting error

(rasa-init-demo) C:\Users\DELL\RASASQL>rasa shell
2020-10-05 19:16:45 INFO     root  - Connecting to channel 'cmdline' which was specified by the '--connector' argument. Any other channels will be ignored. To connect to all given channels, omit the '--connector' argument.
2020-10-05 19:16:45 INFO     root  - Starting Rasa server on http://localhost:5005
2020-10-05 19:16:56 INFO     root  - Rasa server is up and running.
Bot loaded. Type a message and press enter (use '/stop' to exit):
Your input ->  hi
Please enter your name.
Your input ->  my name is Pooja
2020-10-05 19:17:33 ERROR    rasa.core.actions.action  - The model predicted the custom action 'action_get_name', but you didn't configure an endpoint to run this custom action. Please take a look at the docs and set an endpoint configuration via the --endpoints flag. https://rasa.com/docs/rasa/core/actions
2020-10-05 19:17:33 ERROR    rasa.core.processor  - Encountered an exception while running action 'action_get_name'. Bot will continue, but the actions events are lost. Please check the logs of your action server for more information.
2020-10-05 19:17:33 ERROR    rasa.core.nlg.interpolator  - Failed to fill utterance template 'Your name is {result}'. Tried to replace 'result' but could not find a value for it. There is no slot with this name nor did you pass the value explicitly when calling the template. Return template without filling the template.
Traceback (most recent call last):
  File "c:\users\dell\.conda\envs\rasa-init-demo\lib\site-packages\rasa\core\nlg\interpolator.py", line 28, in interpolate_text
    text = text.format(values)
KeyError: 'result'
Your name is {result}
Bye
Your input ->

NLU.md

intent:greet

  • hey

  • hello

  • hi

  • good morning

  • good evening

  • hey there

intent:goodbye

  • bye

  • goodbye

  • see you around

  • see you later

intent:name_entry

Stories.md

happy path

  • greet

    • utter_greet

  • name_entry

    • action_get_name

    • utter_show_name

    • utter_goodbye

domain.yml intents:

  • greet

  • goodbye

  • name_entry

entities:

  • name

slots:

name:

type: unfeaturized

actions:

  • utter_greet

  • utter_show_name

  • utter_goodbye

  • action_get_name

responses:

utter_greet:

  • text: “Please enter your name.”

utter_show_name:

  • text: “Your name is {result}”

utter_goodbye:

  • text: “Bye”

session_config:

session_expiration_time: 60

carry_over_slots_to_new_session: true

endpoints.yml #action_endpoint:

url: “http://localhost:5057/webhook”

actions.py

This files contains your custom actions which can be used to run

custom Python code.

See this guide on how to implement these action:

https://rasa.com/docs/rasa/core/actions/#custom-actions/

This is a simple example for a custom action which utters “Hello World!”

from typing import Any, Text, Dict, List

from rasa_sdk import Action, Tracker

from rasa_sdk.executor import CollectingDispatcher

import sqlite3, pyodbc

class ActionHelloWorld(Action):

 def name(self) -> Text:

     return "action_get_name"

 def submit(self,dispatcher,tracker,domain):

    try:

        conn = pyodbc.connect('Driver = {SQL Server};'

                        'Server=*;'

                        'Database=chatbot;'

                        'UID=*;'

                        'PWD=*;')

        name = tracker.get_slot('name')

        cursor = conn.cursor()

        msg = 'select fullname from chatbottest where name=?'

        curson.execute(msg,(name))

        for row in cursor:

            dispatcher.uttermessage([row.fullname+row.name])

    except:

        dispatcher.uttermessage("failed")

        return[]
3

Hi Pooji,

Please post Rasa technical questions in the Rasa OSS forum or Rasa X forum (if they aren’t related to the topics covered by the workshop).

The error indicates that you don’t have an action server configured correctly in the endpoints.yml.

Greg

4

Hi, pooja you can connect database directly from custom actions you can visit w3schools there you can find there how to do with examples

def read(id_person: int) -> Person: db = pymysql.connect(SERVER, USER, PASSWORD, DB) cursor = db.cursor() sql = “SELECT * FROM PERSON WHERE ID = ‘%d’” % (id_person) try: cursor.execute(sql) results = cursor.fetchall() for row in results: first_name = row[1] last_name = row[2] age = row[3] sex = row[4] income = row[5] person = Person(id_person, first_name, last_name, age, sex, income) except: print(“Error fetching data.”) finally: db.close() return person

If the ID is in a slot, you can easily pass it to the read function using the tracker.get_slot function and then get the person object in return. Then using the dispatcher you could simply pass person.first_name in the response.

1 Like
5

Thanks @Rajendra9 Will try this