Rasa with MYSQL

Rasa Open Source
1

Can someone help me send the result of this query to the frontend !!! please

This is a simple module to fetch data from MySQL db.

python -m pip install mysql-connector //run this command if you face import error

references: Python MySQL

-- coding: utf-8 --

import logging

import json

import requests

from datetime import datetime

from typing import Any, Dict, List, Text, Optional

import webbrowser

from rasa_sdk import Action, Tracker

from rasa_sdk.forms import FormValidationAction

from rasa_sdk.executor import CollectingDispatcher

from rasa_sdk.events import (

SlotSet,

UserUtteranceReverted,

ConversationPaused,

EventType,

FollowupAction,

)

logger = logging.getLogger(name)

import mysql.connector

import traceback

import mysql.connector

import traceback

class ActionGetInscricao(Action):

def name(self) -> Text:

    return "action_get_inscricao"

def run(

        self,

        dispatcher: CollectingDispatcher,

        tracker: Tracker,

        domain: Dict[Text, Any],

) -> List[EventType]:

    print("iniciando custom action: " + self.name())

    query_original = "SELECT * FROM teste.usuario where id = "

    inscricao = tracker.get_slot("inscricao")

    query = query_original + inscricao

    return []

def getData(query: str):

    try:

        mydb = mysql.connector.connect(

            host="localhost",

            user="root",

            passwd="",

            database="teste"

        )

        cursor = mydb.cursor()

        cursor.execute(query)

        results = cursor.fetchall()

        return results

    except:

        print("Error occured while connecting to database or fetching data from database. Error Trace: {}".format(

            traceback.format_exc()))

        return []
2

Everything seems fine except that you are not calling getData(query) in your run() method.

NB: To properly format code and make it readable, please put 3 backticks (```) a line before and a line after the code

```
like so
```
3

Do you have a working simple example of a bot project with mysql? I dont found any, thats still working.

4

I wrote a Class for that

class DatabaseConnection:
    hostname = None
    database = None
    username = None
    password = None
    connection = None
    cursor = None
    query = None


    def __init__(self, hostname = None, database = None, username = None, password = None):
        if not self.connection:
            self.hostname = hostname
            self.database = database
            self.username = username
            self.password = password
            self.connect()


    def connect(self):
        self.connection = mysql.connector.connect(
            host     = self.hostname,
            user     = self.username,
            password = self.password,
            database = self.database)
        
        return self.connection


    def disconnect(self):
        self.cursor.close()
        self.connection.close()
    

    def query(self, sql):
        result = []

        self.cursor = self.connection.cursor()
        self.cursor.execute(sql)

        for row in self.cursor:
            result.append(row)

        return result


    def simple_query(self, table, columns = '*', condition = None):
        result = []

        sql = f"SELECT {columns} FROM {table}"
        if condition:
            sql += f" WHERE {condition}"

        return self.query(sql)


    def count(self, table, condition = None):
        return len(self.simple_query(table, '*', condition))

and integrated it in a Custom Action

class ActionFetchQuota(Action):
    def name(self) -> Text:
        return 'action_fetch_quota'


    def run(self, dispatcher, tracker, domain):
        username = tracker.get_slot('username')

        if username:
            results = None

            try:
                db = DatabaseConnection('localhost', 'rasa', 'root', 'root')
                results = db.query("SELECT Quota, Consumption, Speed "
                    "FROM `user_info` INNER JOIN `consumption` "
                    "ON `user_info`.`ID` = `consumption`.`UserID` "
                    f"WHERE username = '{username}'")
                db.disconnect()
            except Exception as e:
                dispatcher.utter_message('Sorry, I couldn\'t connect to the database.')
                return [SlotSet('username', None)]

            if len(results) != 1:
                dispatcher.utter_message(f'Sorry, {username} is not a registered username.')
                return [SlotSet('username', None)]

            try:
                quota, consumption, speed = results[0]
                ratio = consumption/quota * 100
                dispatcher.utter_message(f'You spent {consumption} GB ({ratio}%) of your {quota} GB quota for this month.')
            except Exception as e:
                dispatcher.utter_message('Sorry, there was an error. Please try again')
                return [SlotSet('username', None)]
        
        else: # Not logged in
            dispatcher.utter_message('You are not logged in. Do you want to log in?')
        
        return []
5

Its works! thanks for help!

1 Like