SQLAlchemy - disable SSL for MySQL

I'm running some test code with a MySQL database using SQLAlchemy. I'm unable to connect to the server using the database URL 'mysql://poopoo:peepee@localhost:32913/test', as it refuses me authentication.

When I try to connect to the database using DBeaver, I get an error informing me that "Public key Retrieval is not allowed". This lead me to this post, suggesting I add ?allowPublicKeyRetrieval=true&useSSL=false to the end of the URL. I can confirm that this method works in DBeaver.

However, adding these to the URL, either in a string or with connect_args, a TypeError is raised, informing me that these are invalid keyword arguments for connect.

How do I add these options to my SQLAlchemy connection?

I'm using testcontainers to generate the MySQL test server, but I don't think that's relevant.

Solution

To fix this issue with SQLAlchemy, you need to use the correct driver syntax. For MySQL connections with public key retrieval, use mysql+pymysql:// instead of mysql:// and add the parameters to the query string:

engine = create_engine('mysql+pymysql://poopoo:peepee@localhost:32913/test?allowPublicKeyRetrieval=true&useSSL=false')

If you're still getting errors, you can also try passing these parameters via connect_args with the correct format:

engine = create_engine(
    'mysql+pymysql://poopoo:peepee@localhost:32913/test',
    connect_args={
        "ssl": {"ssl_mode": "DISABLED"},
        "allow_public_key_retrieval": True
    }
)

Make sure you have the pymysql driver installed: pip install pymysql.