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

[Flowace (Shivani)]

@alebudetti

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!

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.