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

Drew · Accepted Answer · 2013-02-20 23:53:12Z

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, 2013 at 23:53

Drew

6,7275 gold badges48 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

after-ephemera Over a year ago

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'}

Boz Over a year ago

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'}

Ben Over a year ago

The approach noted in this answer appears to be correct if you're using PyMySQL

Denis Otkidach · Accepted Answer · 2011-11-07 09:34:26Z

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 7, 2011 at 9:34

Denis Otkidach

33.5k8 gold badges86 silver badges102 bronze badges

3 Comments

cfpete Over a year ago

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?

Denis Otkidach Over a year ago

They are passed directly to DB API's connect(), i.e. MySQLdb.connect() in your case.

cfpete Over a year ago

got it, so it's identical. Thanks!

phd · Accepted Answer · 2017-05-07 21:21:49Z

1

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,
)

answered May 7, 2017 at 21:21

phd

97.2k14 gold badges165 silver badges223 bronze badges

Comments

Cabrera · Accepted Answer · 2018-05-13 23:06:19Z

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, 2018 at 23:06

Cabrera

2,0401 gold badge21 silver badges19 bronze badges

3 Comments

dfrankow Over a year ago

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()

Cabrera Over a year ago

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 ?

dfrankow Over a year ago

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.

Collectives™ on Stack Overflow

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

4 Answers 4

3 Comments

3 Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

3 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related