AttributeError: 'Engine' object has no attribute 'character_set_name'

[AtherBaig]

I am running a python app on GCP app engine which connects to cloud SQL database (mySQL). But there is an error in GCP logs that I am unable to resolve,

File "/layers/google.python.pip/pip/lib/python3.9/site-packages/sqlalchemy/dialects/mysql/mysqldb.py", line 161, in on_connect charset_name = conn.character_set_name() AttributeError: 'Engine' object has no attribute 'character_set_name'

Here is the code I am using to establish connection:

def connect_with_connector() -> sqlalchemy.engine.base.Engine:
    """
    Initializes a connection pool for a Cloud SQL instance of MySQL.

    Uses the Cloud SQL Python Connector package.
    """
    # Note: Saving credentials in environment variables is convenient, but not
    # secure - consider a more secure solution such as
    # Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
    # keep secrets safe.

        instance_connection_name = cinstance_connection_name
        db_user = cdb_user
        db_pass = cdb_pass
        db_name = cdb_name
        ip_type = cip_type

        connector = Connector(ip_type)

        def getconn() -> pymysql.connections.Connection:
            conn: pymysql.connections.Connection = connector.connect(
                instance_connection_name,
                "pymysql",
                user=db_user,
                password=db_pass,
                db=db_name,
            )
            return conn

        pool = sqlalchemy.create_engine(
            "mysql+pymysql://",
            creator=getconn,
            # ...
        )
        return pool

    # Use the custom engine for SQLAlchemy
    app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://'
    app.config['SQLALCHEMY_ENGINE_OPTIONS'] = {
        'creator': connect_with_connector
    }
    db = SQLAlchemy(app)

Am I missing an argument to the method?

[ms4446]

The error indicates that SQLAlchemy is attempting to access the character_set_name attribute on an Engine object, which is not present. This typically occurs when SQLAlchemy expects a database connection object but instead receives an Engine object, leading to a mismatch in expected attributes.

Troubleshooting Steps:

1. Check for Missing Arguments:

   • Review the sqlalchemy.create_engine documentation to ensure all necessary arguments are included, particularly those related to character set encoding.
   • Add any missing arguments to the create_engine call to ensure proper configuration.

2. Address SQLAlchemy Version 2.0 Compatibility:

   • If you're using SQLAlchemy 2.0, adapt the on_connect event handling to align with the new API changes.
   • As a temporary measure, consider downgrading to SQLAlchemy 1.4, but keep in mind this is not a long-term solution.

3. Inspect the getconn Function:

   • Verify that getconn returns a valid connection object that includes character set information and is compatible with SQLAlchemy.

4. Review Connector Configuration:

   • Double-check the configuration of the Cloud SQL Connector, ensuring it's set up correctly with the appropriate character set and other connection parameters.

5. Consult Documentation and Community Resources:

   • Refer to the SQLAlchemy documentation for create_engine and dialect-specific options.
   • Seek advice from the SQLAlchemy and Cloud SQL communities for any known issues or alternative solutions.

6. Test with a Simplified Connection:

   • Try establishing a connection using a more straightforward setup, bypassing the custom getconn function. This can help isolate whether the issue is with the custom connection logic or another aspect of the setup.

7. Implement Detailed Logging:

   • Enhance logging around the connection process to gain more insight into where and why the failure occurs. This can provide valuable clues for troubleshooting.