[SOLVED] AWS Lambda Database Connection works with PyMySQL but not SQLAlchemy

AWS Lambda Database Connection works with PyMySQL but not SQLAlchemy

I am trying to connect to a MySQL RDS Database using Lambda Functions. I have tested this locally through SAM invoke and it works. I am now testing it on the AWS Lambda Console and it does not work. I cannot connect using SQLAlchemy using the PyMYSQL engine, but I can connect using the PyMYSQL Library.

I generate my database_token through:

database_token = rds_client.generate_db_auth_token(
    DBHostname=os.environ["DB_HOST"],
    Port=3306,
    DBUsername=os.environ["DB_USER"],
    Region=os.environ["AWS_REGION"],
)

I then can connect to the database through PyMySQl using:

database_connection = pymysql.connect(
    host=os.environ["DB_HOST"],
    port=3306,
    database=os.environ["DB_NAME"],
    user=os.environ["DB_USER"],
    password=database_token,
    ssl={"any_non_empty_dict": True},
)

But when I use SQLAlchemy using this code block:

DB_URL = f"mysql+pymysql://{os.environ['DB_USER']}:{database_token}@{os.environ['DB_HOST']}:3306/{os.environ['DB_NAME']}"
engine = create_engine(DB_URL, connect_args = {'ssl':{"any_non_empty_dict": True}}, echo=True)
database_connection = engine.connect()

I am returned this error:

(pymysql.err.OperationalError) (1045, "Access denied for user 'DB_USER'@'***.***.***.***' (using password: NO)")

When I replace the HOST name and the PASSWORD with credentials for the database itself and not the Proxy. SQLAlchemy is able to connect. It is only during a connection attempt to the Proxy.

Is there a difference in the configuration of the PyMYSQl engine while using SQLAlchemy?

Solution

I think the passowrd and username need to be url encoded not sure about username but i can confirm the password need to be encoded from this issue.