Fetching data from an oracle database

Rasa Open Source
1

Hello everyone,

I am trying to connect an Oracle database to my rasa chatbot, I want to fetch data from that database which shall be used to answer the questions of a user. For example, if a user asks: “Who is the developer of Application Number 123?”. Then I want the chatbot to go into the application_info table (columns: application_id, developer_name) and answer the user with something like “The name of the developer of the Application Number 123 is John Taylor”. Can someone please walk me through the steps on how to go about with this?

Any ideas/suggestions/advice would be huge help!

Thanks!

2

Hello, you can use the MySQL connector module and this Python class to query your database:

import mysql.connector

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

    def __init__(self, hostname, database, username, password):
        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, query):
        result = []

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

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

        return result

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

Then you can use it like so:

application_id = tracker.get_slot('application_id')

db = DatabaseConnection('localhost', 'my_database', 'druhmil', 'passw0rd')
results = db.query("SELECT developer_name FROM `application_info` "
                   f"WHERE application_id = '{application_id}'")
developer_name = results[0]
db.disconnect()
1 Like
3

Certainly! To connect your Rasa chatbot to an Oracle database and fetch data from it, you can follow these steps:

  1. Install Required Libraries: Make sure you have the necessary Python libraries installed. You’ll need cx_Oracle library to connect to Oracle database. You can install it using pip: pip install cx_Oracle

  2. Configure Database Connection: In your Rasa project, create a new Python file (e.g., database.py) to handle the database connection and queries. In this file, you need to establish a connection to your Oracle database using the appropriate credentials. Here’s an example of how you can establish a connection: import cx_Oracle

Oracle database connection settings

username = “<your_username>” password = “<your_password>” host = “<your_host>” port = “<your_port>” service_name = “<your_service_name>”

dsn = cx_Oracle.makedsn(host, port, service_name=service_name) connection = cx_Oracle.connect(username, password, dsn)

  1. Query the Database: Once the connection is established, you can execute SQL queries to fetch the desired data enrichment tools. In your case, you can construct a query to retrieve the developer’s name based on the application number. Here’s an example of how to execute a query and retrieve the result: def get_developer_name(application_number): query = “SELECT developer_name FROM application_info WHERE application_id = :app_id” cursor = connection.cursor() result = cursor.execute(query, app_id=application_number).fetchone() cursor.close() return result[0] if result else None
  2. Integrate with Rasa Chatbot: Now, you need to integrate the database functionality with your Rasa chatbot. In your Rasa custom action file (e.g., actions.py), you can define a custom action that will handle the user’s question and fetch the required information from the database. Here’s an example: from rasa_sdk import Action, Tracker from rasa_sdk.events import SlotSet from database import get_developer_name

class QueryDeveloperAction(Action): def name(self) → Text: return “action_query_developer”

def run(self, dispatcher: CollectingDispatcher,
        tracker: Tracker,
        domain: Dict[Text, Any]) -> List[Dict[Text, Any]]:
    application_number = tracker.get_slot("application_number")
    developer_name = get_developer_name(application_number)

    if developer_name:
        response = f"The name of the developer of Application Number {application_number} is {developer_name}."
    else:
        response = f"Sorry, I couldn't find any information for Application Number {application_number}."

    dispatcher.utter_message(text=response)
    return []
4

in the credintials file what i should write