How to make mysql connection that requires CA-CERT with sqlalchemy or SQLObject

Question

I would like to connect to a MySQL database that requires ca-cert. I can do it with MySQLdb like below:

MySQLdb.connect(host = self.host,
                port = self.port,
                unix_socket = self.unix_socket,
                user = self.user,                                
                passwd = self.passwd,
                db = self.db,
                ssl = { 'cert': self.sslcert,
                        'key': self.sslkey,
                         'ca': self.sslca }

How do I do the same think in SQLAlchemy or SQLObject?

Thanks, peter

score 43 · Answer 1 · answered Feb 20 '13 at 23:53

43

To use SSL certs with SQLAlchemy and MySQLdb, use the following python code:

db_connect_string='mysql://<user>:<pswd>@<db server>:3306/<database>'
ssl_args = {'ssl': {'cert':'/path/to/client-cert', 
                     'key':'/path/to/client-key', 
                      'ca':'/path/to/ca-cert'}}

create_engine(db_connect_string, connect_args=ssl_args)

answered Feb 20 '13 at 23:53

Drew

6,311
4
44
44

2

With the latest versions, it should be ```ssl_args = {'ssl_cert':'/path/to/client-cert', 'ssl_key':'/path/to/client-key', 'ssl_ca':'/path/to/ca-cert'}``` – Jk Jensen Jan 23 '20 at 18:52
With the latest version, make sure your ssl_args is in this format `ssl_args = {'sslrootcert':'server-ca.pem', 'sslcert':'client-cert.pem', 'sslkey':'client-key.pem'}` – Boz Sep 07 '20 at 12:51
The approach noted in this answer appears to be correct if you're using PyMySQL – Ben May 05 '21 at 19:12

score 8 · Accepted Answer · answered Nov 07 '11 at 09:34

8

create_engine() in SQLAlchemy has a connect_args parameter:

connect_args – a dictionary of options which will be passed directly to the DBAPI’s connect() method as additional keyword arguments.

answered Nov 07 '11 at 09:34

Denis Otkidach

32,032
8
79
100

Thanks for the answer and the link. The documentation doesn't seem to have examples of what those options are? Are they identical to the MySQLdb library? – cfpete Nov 08 '11 at 22:47
They are passed directly to DB API's `connect()`, i.e. `MySQLdb.connect()` in your case. – Denis Otkidach Nov 10 '11 at 10:28
got it, so it's identical. Thanks! – cfpete Nov 10 '11 at 19:02

score 1 · Answer 3 · answered May 07 '17 at 21:21

SQLObject (untested):

from sqlobject.mysql import MySQLConnection
connection = MySQLConnection(
    db=self.db,
    user=self.user,
    password=self.password,
    host=self.host,
    ssl_key=self.sslkey,
    ssl_cert=self.sslcert,
    ssl_ca=self.sslca,
)

score 1 · Answer 4 · answered May 13 '18 at 23:06

1

According to their docs, SQLAlchemy's create_engine function takes a db url with the following format: dialect[+driver]://user:password@host/dbname[?key=value..] meaning you could pass the ssl key, cert, and ca as key value pairs.

answered May 13 '18 at 23:06

Cabrera

1,670
1
16
16

Yes, I want this. But what are the key names? if I try 'sslca' I get an exception `TypeError: 'sslca' is an invalid keyword argument for connect()` – dfrankow Mar 20 '19 at 19:52
I would assume the keys are dialect specific and would be found in their corresponding docs. I know that for mysql, you have to a pass a `--ssl-ca` arg though the command line to connect using SSL; Maybe try `ssl-ca=foo` ? – Cabrera Mar 22 '19 at 16:22
Thanks for the suggestion. I couldn't find anything in docs. I tried all kinds of variations ('ssl-ca', 'ssl.ca', 'ssl_ca', 'sslca', 'ssl', 'ca', etc.). Since I am using Django, I ended up passing this parameter in Django setup. Hack, but works. – dfrankow Mar 22 '19 at 20:15

How to make mysql connection that requires CA-CERT with sqlalchemy or SQLObject

4 Answers4

Linked