Using SSL with SQLAlchemy

Question

I've recently changed my project to use SQLAlchemy and my project runs fine, it used an external MySQL server.

Now I'm trying to work with a different MySQL server with SSL CA, and it doesn't connect.

(It did connect using MySQL Workbench, so the certificate should be fine)

I'm using the following code:

ssl_args = {'ssl': {'ca': ca_path}}
engine = create_engine("mysql+pymysql://<user>:<pass>@<addr>/<schema>",
                        connect_args=ssl_args)

and I get the following error:

Can't connect to MySQL server on '\addr\' ([WinError 10054] An existing connection was forcibly closed by the remote host)

Any suggestions?

Answer 1

I changed the DBAPI to MySQL-Connector, and used the following code:

ssl_args = {'ssl_ca': ca_path}
engine = create_engine("mysql+mysqlconnector://<user>:<pass>@<addr>/<schema>",
                        connect_args=ssl_args)

And now it works.

Answer 2

If you just connect from a client machine with an ssl connection (so you don't have access to the cert and key), you could simple add ssl=true to your uri.

Edit:

For example: mysql_db = "mysql+mysqlconnector://<user>:<pass>@<addr>/<schema>?ssl=true"

Answer 3

The official doc is well documented:

engine = create_engine(
    db_url,
    connect_args={
        "ssl": {
            "ssl_ca": "ca.pem",
            "ssl_cert": "client-cert.pem",
            "ssl_key": "client-key.pem"
        }
    }
)