Connecting Rasa to a database to store responses

I’m trying to connect my rasa open source chatbot to a local database and store response from the user. But I couldn’t succeed in the process of integrating database to my chatbot. I tried using google but nothing seems to be working. I hope I’ll get a solution from community members. Attaching the data for your reference.

nlu.yml nlu: - intent: painting_request examples: | - im interested in buying a painting - i’m interested in buying a painting - I’m interested in buying a painting - interested in buying canvas - id like to request a painting - id like to buy a painting - im looking for a painting that have a beautuful background - i would like to get a sketch of my photograph - i would like to get a sketch of my photo

- intent: art_type_entry
  examples: |
    - im looking for a [graphite](art_type) painting 
    - i'm looking for a [graphite](art_type) sketch
    - i want to buy a [graphite](art_type) painting
    - i want to buy a [graphite](art_type) sketch
    - can i able to get a [graphite](art_type) art
    - i want my picture to be drawn in [graphite](art_type)
    - im looking for a [charcoal](art_type) painting 
    - i'm looking for a [charcoal](art_type) sketch
    - i want to buy a [charcoal](art_type) painting
    - i want to buy a [charcoal](art_type) sketch
    - can i able to get a [charcoal](art_type) art
    - i want my picture to be drawn in [charcoal](art_type)
    - im looking for a [coloured pencil art](art_type) painting 
    - i'm looking for a [coloured pencil art](art_type) sketch
    - i want to buy a [coloured pencil art](art_type) painting
    - i want to buy a [coloured pencil art](art_type) sketch
    - can i able to get a [coloured pencil art](art_type) art
    - i want my picture to be drawn in [coloured pencil art](art_type)
    - i want my picture to be drawn in [oil painting](art_type)
    - im looking for a [oil painting](art_type) painting 
    - i'm looking for a [oil painting](art_type) sketch
    - i want to buy a [oil painting](art_type) painting
    - i want to buy a [oil painting](art_type) sketch
    - can i able to get a [oil painting](art_type) art
    - i want my picture to be drawn in [oil painting](art_type)
    
- intent: size_entry
  examples: |
    - size should be of [a4](size)
    - size should be of [a3](size)
    - size should be of [a2](size)
    - size should be of [a1](size)
    - painting should be of [a4](size)
    - painting should be of [a3](size)
    - painting should be of [a2](size)
    - painting should be of [a1](size)

- intent: frame_entry
  examples: |
    - my frame should be of [standard](frame)
    - my frame should be of [wooden](frame)
    - my frame should be of [designer](frame)
    - my canvas should be fitted with a [standard](frame)
    - my canvas should be fitted with a [wooden](frame)
    - my canvas should be fitted with a [designer](frame)
    - frame should be of [standard](frame)
    - frame should be of [Wooden](frame)
    - frame should be of [designer](frame)

- intent: finishing_entry
  examples: |
    - the outer finishing should be done with [maple](finishing)
    - the outer finishing should be done with [walnut](finishing)
    - the outer finishing should be done with [black](finishing)
    - the outer finishing should be done with [white](finishing)
    - the outer finishing should be done with [glass](finishing)
    - outer finishing should be done with [brass](finishing)
    - outer finishing should be done with [maple](finishing)
    - outer finishing should be done with [walnut](finishing)
    - outer finishing should be done with [black](finishing)
    - outer finishing should be done with [white](finishing)
    - outer finishing should be done with [glass](finishing)
    - outer finishing should be done with [brass](finishing)

- intent: orientation_entry
  examples: |
    - orientation of canvas should be in [landscape](orientation)
    - orientation of canvas should be in [potrait](orientation)
    - orientation of canvas should be in [designer choice](orientation)

rules.yml rules: - rule: activate painting form steps: - intent: painting_request - action: painting_form - active_loop: painting_form

  - rule: submit form
    condition:
    - active_loop: painting_form
    steps:
    - action: painting_form
    - active_loop: null
    - action: utter_submit
    - action: utter_slots_values

stories.yml stories: - story: stop form + continue steps: - intent: painting_request - action: painting_form - active_loop: painting_form - intent: out_of_scope - action: utter_ask_continue - intent: affirm - action: painting_form - active_loop: null - action: utter_submit - action: utter_slots_values

  - story: stop form + stop
    steps:
      - intent: painting_request
      - action: painting_form
      - active_loop: painting_form
      - intent: out_of_scope
      - action: utter_ask_continue
      - intent: deny
      - action: action_deactivate_loop
      - active_loop: null

domain.yml intents: - greet - goodbye - affirm - reject - mood_great - mood_unhappy - bot_challenge - askname - help - out_of_scope - art_type_entry - size_entry - frame_entry - finishing_entry - orientation_entry - painting_request: use_entities: []

entities:
  - art_type
  - size
  - frame
  - finishing
  - orientation

actions:
- validate_painting_form # From actions.py page

slots:
  model:
    type: text
    influence_conversation: false
    auto_fill: false
  frame_size:
    type: text
    influence_conversation: false
    auto_fill: false
  frame_type:
    type: text
    influence_conversation: false
    auto_fill: false
  frame_orientation:
    type: text
    influence_conversation: false
    auto_fill: false
  frame_finishing:
    type: text
    influence_conversation: false
    auto_fill: false
  requested_slot:
    type: text
    influence_conversation: false

forms:
  painting_form:
    required_slots:
      model:
      - type: from_entity
        entity: art_type
      frame_size:
      - type: from_entity
        entity: size
      frame_type:
      - type: from_entity
        entity: frame
      frame_orientation:
      - type: from_entity
        entity: orientation
      frame_finishing:
      - type: from_entity
        entity: finishing

responses:
  utter_ask_continue:
  - text: "Do you want to continue?"
  
  utter_wrong_art_type:
  - text: "Sorry we are unable to proceed with your art type, please try again"

  utter_wrong_size_type:
  - text: "Sorry we are unable to proceed with your size type, please try again"

  utter_wrong_frame_type:
  - text: "Sorry we are unable to proceed with your frame type, please try again"

  utter_wrong_finishing_type:
  - text: "Sorry we are unable to proceed with your finishing type, please try again"

  utter_wrong_orientation_type:
  - text: "Sorry we are unable to proceed with your orientation type, please try again"
  
  utter_ask_model:
  - text: "What is your desired art model"
  
  utter_ask_frame_size:
  - text: "Please enter your required size"
  
  utter_ask_frame_type:
  - text: "Please enter your desired frame type"
  
  utter_ask_frame_finishing:
  - text: "Please enter your deisired finishing"
  
  utter_ask_frame_orientation:
  - text: "Please enter orientation"
  
  utter_submit:
  - text: "Thanks for the information provided."

  utter_slots_values:
  - text: "I am going to run a search using the following parameters:\n
            - art_type: {model}\n
            - size: {frame_size}\n
            - frame: {frame_type}\n
            - finishing: {frame_finishing}\n
            - orientation: {frame_orientation}"


session_config:
  session_expiration_time: 60
  carry_over_slots_to_new_session: true

actions.py from database_connector import DataUpdate from typing import Any, Text, Dict, List, Union

from rasa_sdk import Tracker 
from rasa_sdk.executor import CollectingDispatcher
from rasa_sdk.forms import FormValidationAction

class PaintingFormValidation(FormValidationAction):

    """Example of a form validation action."""

    def name(self) -> Text:
        return "validate_painting_form"

    def slot_mappings(self) -> Dict[Text, Union[Dict, List[Dict]]]:
        return{
            "model": [self.from_entity(entity='art_type', intent='art_type_entry')],
            "frame_size": [self.from_entity(entity='size', intent='size_entry')],
            "frame_type": [self.from_entity(entity='frame', intent='frame_entry')],
            "frame_finishing": [self.from_entity(entity='finishing', intent='finishing_entry')],
            "frame_orientation": [self.from_entity(entity='orientation', intent='orientation_entry')]
        }

    @staticmethod
    def art_type_db() -> List[Text]:
        """Database of supported cuisines."""

        return [
            "graphite",
            "charcoal",
            "sketching",
            "oilPainting",
            "colored pencil"
        ]

    @staticmethod
    def size_db() -> List[Text]:
        """Database of supported sizes"""

        return [
            "a1",
            "a2",
            "a3",
            "a4"
        ]

    @staticmethod
    def frame_db() -> List[Text]:
        """Database of supported frame types"""

        return [
            "no frame",
            "standard",
            "designer"
            ]

    @staticmethod
    def finishing_db() -> List[Text]:
        """Database of supported finishing"""

        return [
            "maple",
            "wood",
            "metal",
            "glass",
            "walnut"
        ]

    @staticmethod
    def orientation_db() -> List[Text]:
        """Database of supported orientations."""

        return [
            "landscape",
            "potrait",
            "designer choice"
            ]

    @staticmethod
    def is_int(string: Text) -> bool:
        """Check if a string is an integer."""

        try:
            int(string)
            return True
        except ValueError:
            return False

    def validate_model(
        self,
        value: Text,
        dispatcher: CollectingDispatcher,
        tracker: Tracker,
        domain: Dict[Text, Any],
    ) -> Dict[Text, Any]:
        """Validate art type value."""

        if value.lower() in self.art_type_db():
            # validation succeeded, set the value of the "art_type" slot to value
            return {"model": value}
        else:
            dispatcher.utter_message(response="utter_wrong_art_type")
            # validation failed, set this slot to None, meaning the
            # user will be asked for the slot again
            return {"model": None}

    def validate_frame_size(
        self,
        value: Text,
        dispatcher: CollectingDispatcher,
        tracker: Tracker,
        domain: Dict[Text, Any],
    ) -> Dict[Text, Any]:
        """Validate size value."""

        if value.lower() in self.size_db():
            # validation succeeded, set the value of the "size" slot to value
            return {"frame_size": value}
        else:
            dispatcher.utter_message(response="utter_wrong_size_type")
            # validation failed, set this slot to None, meaning the
            # user will be asked for the slot again
            return {"frame_size": None}

    def validate_frame_type(
        self,
        value: Text,
        dispatcher: CollectingDispatcher,
        tracker: Tracker,
        domain: Dict[Text, Any],
    ) -> Dict[Text, Any]:
        """Validate frame type value."""

        if value.lower() in self.frame_db():
            # validation succeeded, set the value of the "frame" slot to value
            return {"frame_type": value}
        else:
            dispatcher.utter_message(response="utter_wrong_frame_type")
            # validation failed, set this slot to None, meaning the
            # user will be asked for the slot again
            return {"frame_type": None}

    def validate_frame_finishing(
        self,
        value: Text,
        dispatcher: CollectingDispatcher,
        tracker: Tracker,
        domain: Dict[Text, Any],
    ) -> Dict[Text, Any]:
        """Validate finishing value."""

        if value.lower() in self.finishing_db():
            # validation succeeded, set the value of the "finishing" slot to value
            return {"frame_finishing": value}
        else:
            dispatcher.utter_message(response="utter_wrong_finishing_type")
            # validation failed, set this slot to None, meaning the
            # user will be asked for the slot again
            return {"frame_finishing": None}

    def validate_frame_orientation(
        self,
        value: Text,
        dispatcher: CollectingDispatcher,
        tracker: Tracker,
        domain: Dict[Text, Any],
    ) -> Dict[Text, Any]:
        """Validate orientation value."""

        if value.lower() in self.orientation_db():
            # validation succeeded, set the value of the "orientation" slot to value
            return {"frame_orientation": value}
        else:
            dispatcher.utter_message(response="utter_wrong_orientation_type")
            # validation failed, set this slot to None, meaning the
            # user will be asked for the slot again
            return {"frame_orientation": None}

    def submit(
        self,
        dispatcher:CollectingDispatcher,
        tracker: Tracker,
        domain: Dict[Text, Any]
     ) -> List[Dict]:

        dispatcher.utter_message(template="utter_submit")

        DataUpdate(tracker.get_slot("model"), tracker.get_slot("frame_size"), tracker.get_slot("frame_type"), tracker.get_slot("frame_finishing"), tracker.get_slot("frame_orientation"))
        dispatcher.utter_message("Your response has been loaded.")
        
        return []

database_connector.py import psycopg2

def DataUpdate(art_type_entry, size_entry, frame_entry, finishing_entry, orientation_entry):
    '''
    Pushes Descriptive Analytics Data to the Database
    '''
    db = psycopg2.connect(
                host="localhost",
                database="Rasa_Chatbot",
                user="postgres",
                password="V@run1995"
                )

    mycursor = db.connect()
    
    postgres_insert_query = """INSERT INTO rasainfo(model,size,type,orientation,finishing) VALUES (%s,%s,%s,%s,%s);""".format(art_type_entry,size_entry, frame_entry, finishing_entry, orientation_entry)
    
    mycursor.execute(postgres_insert_query)
    
    db.commit()

    print("Record inserted successfully into table")

@VarunRachamalla Heya! You want to store the user/bot conversation in a database? right?

I want to store the user input. Once the rasa server is up and running in local environment, the input provided by the user as per the questions from rasa chatbot. If you can see the forms in domain.yml you can see the list of list of questions which will be asked by chatbot, the responses from the user need to be stored in database.

@VarunRachamalla you only want to store the response of the user in the database? or the whole conversation between user and bot? the same question before also I asked.

Only the user response in the database.

@VarunRachamalla What is the relevance of only user response? It does not make any sense if it’s not with the question and its relevant answer.

The questions from bot are in the forms under domain.yml which runs as custom action in actions.py. If a person types the input as “I want to buy a painting” then the activate painting form rule under rules.yml will start running and next to it forms will run in the shell and ask for the inputs like size, model and others. The inputs provided for model, size and others should be stored in relational database under specific column listed to it. This is what I want to achieve. If possible we can connect over via google meet and discuss to solve this problem.

@VarunRachamalla Are you aware of Tracker Stores ? Yes or No?

@nik202 No

@VarunRachamalla update all the field of PostgresSQL as per your settings in endpoint.yml after create server and connection whilst running pg4admin

tracker_store:
   type: SQL
   dialect: "postgresql"  # the dialect used to interact with the db
   url: "localhost"  # (optional) host of the sql db, e.g. "localhost"
   db: "chatbot"  # path to your db
   username: "postgres"  # username used for authentication
   password: "123"  # password used for authentication
   # query: # optional dictionary to be added as a query string to the connection URL
   #   driver: my-driver

check the log of 5005 it will ask for some dependencies, install that and you good to go. You will automatically see the log of conversation in postgress. Do let me know if you have any issues.