Easy way to integrate a database into a chatbot

Rasa Open Source
1

Hi,

I want to integrate a database and look up values based on an entity the user gave me. How is this possible? Is there a detailed guide anywhere to follow?

I am pretty new in the whole chatbot space. Sorry if this might seem like a naive question.

Thank you so much!

2

Hi, I’m actually looking at the same thing. I didn’t find any detailed guides. You have to do it using custom actions. Make APIs calls to access your database. I found some links which people have implemented. Try looking at them:

3

Hey, thank you for your reply. Yeah I am having a bit troubles finding the correct API commands in order to retrieve Info from the database. I will have a look at the links maybe I can find something to find some orientation. Thank you :slight_smile:

4

I don’t think this is really Rasa-related since you can use pymysql (for MySQL obviously :smiley:) & others, or an ORM such as SQLAlchemy to do this. For example, for returning the first name of a person in a database from an “ID” entity, things could look like this:

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.

5

Hi @nico, I have used APIs to access my database. My data is in MongoDB and I used Flask and PyMongo to make my APIs. I am successfully able to access my database from the custom actions. Like @alecigne said, you can directly access your database using pymysql or pymongo or any other. For small scale projects this menthod is fine, but if you plan to scale the it up, it is always better to use APIs to access your database.

6

I am a complete newbie, when programming with python. I am trying to implement a basic code to access the database like @alecigne suggested. I am having some troubles with the whole person = Person(…) part. I need to create a object for that somewhere right?

7

Indeed @nico, you would need a Person class to instantiate, something like this:

class Person():

    def __init__(self, id_person, first_name, last_name, age, sex, income):
        self.id_person = id_person
        self.first_name = first_name
        self.last_name = last_name
        self.age = age
        self.sex = sex
        self.income = income

    def __str__(self):
        return (f'ID: {self.id_person}, '
                f'First name: {self.first_name}, '
                f'Last name: {self.last_name}, '
                f'Age: {self.age}, '
                f'Sex: {self.sex}, '
                f'Income: {self.income}')
8

Thanks, I managed to figure it out after a while :slight_smile:

9

HI @nico, I am trying to build a demo chatbot using MySQL server. But, I am getting connectivity issues. Could you please help me out if you have already implemented it.

Below is error I am getting with RASA:

2019-03-11 11:50:13 DEBUG rasa_core.processor - Predicted next action ‘action_db’ with prob 1.00. 2019-03-11 11:50:13 DEBUG rasa_core.actions.action - Calling action endpoint to run action ‘action_db’. 2019-03-11 11:50:13 ERROR rasa_core.actions.action - Failed to run custom action ‘action_db’. Action server responded with a non 200 status code of 404. Make sure your action server properly runs actions and returns a 200 once the action is executed. Error: 404 Client Error: NOT FOUND for url: http://localhost:5055/webhook/ 2019-03-11 11:50:13 ERROR rasa_core.processor - Encountered an exception while running action ‘action_db’. Bot will continue, but the actions events are lost. Make sure to fix the exception in your custom code. 2019-03-11 11:50:13 DEBUG rasa_core.processor - Failed to execute custom action.

Please let me know if you have any ideas.

10

Hi @nico, I believe i’m in the same shoes as you were. I am working on a college project and want to integrate the database with a chatbot i am working on. Will you please share your experience it would be a great help.

Thanks in advance!

11

@alecigne do you have the same code for MongoDb, i am having problems in fetching the input, that will be of great help.

12

@srikar_1996 Could you please provide steps to do it?

13

Hi,

If you want to connect using pymongo, it would be something like this:

import pymongo

myclient = pymongo.MongoClient("mongodb://localhost:27017/")
mydb = myclient["database name"]
mycol = mydb["table name"]
mycol2=mydb["table name"]

Once you have your tables, you can directly use pymongo for CRUD operations. For example

mycol.insert_one(record)
mycol.insert(records)

where record = {"col1": value, "col2": value, "col3": value}

14

Hii @alecigne i am using sql alachemy to store it in mysql db import sys import pymysql from sqlalchemy import create_engine user = ‘root’ password = ‘root’ host = ‘localhost’ database = ‘dab’

def connects(email): my_conn = create_engine(‘mysql+pymysql://{0}:{1}@{2}/{3}’.format(user, password, host, database)) conn = my_conn.connect() try:

    id = "INSERT INTO  `dab`.`user_data` (`email`) VALUES ('%s')"
    conn.execute(id, email)
except:
    print(sys.exc_info()[0])
    print("an error occured in connection")

but i am getting error that no module import error on pymysql but it is present in the environment