How do I switch the object of my database "MySQL" between two actions?

Rasa Open Source
1

Hi, I am creating my own chatbot that can interface with my internal database, and I want to avoid creating a database connection every time I call an action, instead I would like to pass the MySQL connection object as a parameter.

class ConnectToDatabase(Action):

    def name(self):
        return "action_connect_to_database"

    def run(self, dispatcher, tracker, domain):
        try:
            conn = mysql.connector.connect(
               "Parameters of my db"
            )
            conn_json = json.dumps(conn, default=str)
            return [SlotSet("database_connection", conn_json)]
        except Exception as e:
             dispatcher.utter_message(text=f"Error: {str(e)}")

class ExecuteDatabaseQuery(Action):

    def name(self):
        return "action_execute_database_query"

    def run(self, dispatcher, tracker, domain):
        try:
           

            conn = (tracker.get_slot("database_connection"))
            conn = json.loads(conn)
            print("Conn3:", str(type(conn)))
            ...

But as I understand this method is not good, is there any advice? Thank you!

2

Passing a MySQL connection object as a parameter to your chatbot actions is a good approach to avoid creating a new database connection every time you call an action. This can help improve efficiency and reduce overhead. Here’s a general outline of how you can implement this in your chatbot:

  1. Establish a Database Connection:
  • Before you start interacting with your chatbot, establish a MySQL database connection. You can do this using a database library or connector in your programming language of choice (e.g., pymysql for Python or mysql2 for Node.js).
  1. Create Chatbot Actions:
  • Define the actions or functions that your chatbot can perform. These actions might include retrieving data from the database, updating records, or performing any other relevant tasks.

def retrieve_data_from_database(connection, query): # Use the connection object to execute the query and retrieve data cursor = connection.cursor() cursor.execute(query) result = cursor.fetchall() cursor.close() return result

  1. Handle Errors and Closing the Connection:
  • Be sure to handle errors gracefully and close the database connection when you’re done using it. You can use try-except blocks to handle exceptions, and make sure to close the connection in a finally block or using a context manager, depending on the programming language.

By following this approach, you can efficiently manage your database connections within your chatbot and avoid the overhead of repeatedly establishing connections. It’s important to ensure that your code handles connection errors and exceptions appropriately to maintain the reliability of your chatbot. Additionally, consider using connection pooling if your chatbot needs to handle a high volume of database connections.

1 Like
4

@Lester Hi Lester, thank you for your response. I am exactly with what you said especially the usefulness of separating the two actions. My problem, however, is how I pass this parameter as SlotSet wants in serializable JSON. I have tried Python functions like pickle or json.dumps, but nothing, it doesn’t work. Do you have any ideas?

5

I can tell you what I did: I had the database on MySQL, so I created APIs to give the data into a JSON format, used parameters to filter it and then used the filtered data for information extraction. I think doing that makes it easier and more dynamic, especially if you have APIs you are already using for your website.

1 Like
6

@modesty I can get the data from my database without any problems, the issue is that I wanted to optimize the problem by using one action to connect to the database and the other actions to make the queries I am interested in, keeping the database object always the same for that connection (and not creating a different one for each action, imagining I have multiple actions in my stories)

7

To efficiently handle database connections in your chatbot without creating a new connection each time, you can follow best practices for database connection management. Here’s some advice based on your code and industry best practices:

  1. Use a Connection Pool: Instead of creating a new database connection for every action, consider using a connection pool. A connection pool manages a set of database connections that can be reused when needed. This approach improves performance and resource utilization.
  2. Initialize the Connection Pool: In your chatbot initialization code, create and configure a database connection pool. This should happen once during the chatbot’s startup. Popular Python libraries like SQLAlchemy provide built-in connection pool support.
  3. Pass Connections as Needed: In your action code, you can pass the database connection from the connection pool as a parameter. This eliminates the need to serialize and deserialize the connection object as JSON.

Here’s an example using SQLAlchemy for database connection and pooling:

from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker

Initialize the database connection pool during bot initialization

db_engine = create_engine(“mysql://user:password@localhost/dbname”) Session = sessionmaker(bind=db_engine)

class ConnectToDatabase(Action): def name(self): return “action_connect_to_database”

def run(self, dispatcher, tracker, domain):
    try:
        session = Session()  # Get a connection from the pool
        return [SlotSet("database_session", session)]
    except Exception as e:
        dispatcher.utter_message(text=f"Error: {str(e)}")

class ExecuteDatabaseQuery(Action): def name(self): return “action_execute_database_query”

def run(self, dispatcher, tracker, domain):
    try:
        session = tracker.get_slot("database_session")
        # Use the session for database operations
        # ...
        session.close()  # Release the connection back to the pool
        # ...
    except Exception as e:
        dispatcher.utter_message(text=f"Error: {str(e)}")

This approach efficiently manages database connections and ensures they are properly reused and closed, reducing resource overhead and potential issues.

2 Likes
8

@Flowace Ok, thanks. I’m trying your solution.

ModuleNotFoundError: No module named ‘MySQLdb’ is the first error.

My code new is


db_engine = create_engine("xxxxx")
Session = sessionmaker(bind=db_engine)


class ConnectToDatabase(Action):
    def name(self):
        return "action_connect_to_database"

    def run(self, dispatcher, tracker, domain):
        try:
            # Configura la connessione al database
            session = Session()
            print(session)
            return [SlotSet("database_connection", session)]
        except Exception as e:
            dispatcher.utter_message(text=f"Error: {str(e)}")
        return [SlotSet("database_connection", "Ciao")]
        # Chiudi la connessione


class ExecuteDatabaseQuery(Action):
    def name(self):
        return "action_execute_database_query"

    def run(self, dispatcher, tracker, domain):
        try:
            # Configura la connessione al database
            conn = tracker.get_slot("database_connection")
            cursor = conn.cursor()
            cursor.execute("some queries")
            result = cursor.fetchall()
            result_string = ", ".join([row[0] for row in result])

            # Invia il risultato all'utente
            dispatcher.utter_message(text=f"Ecco i nomi: {result_string}")

            # Chiudi la connessione
            cursor.close()
            conn.close()
            return [SlotSet("query_executed", True)]
        except Exception as e:
            dispatcher.utter_message(text=f"Error: {str(e)}")
        return [SlotSet("query_executed", False)]