How to pass secure_auth to MySQL login via SQLalchemy

Question

I'm working on the front end of a webapp, and my co-developer is using Pyramid and SQAlchemy. We've just moved from SQLite to MySQL. I installed MySQL 5.6.15 (via Homebrew) on my OS X machine to get the Python MySQLdb install to work (via pip in a virtualenv).

Because in MySQL >= 5.6.5 secure_auth is now ON by default I can only connect to the remote database (pre 5.6.5) with the --skip-secure-auth flag, which works fine in a terminal.

However, in the Python Pyramid code, it only seems possible to add this flag as an argument to create_engine(), but I can't find create_engine() in my co-dev's code, only the connection string below in an initialisation config file. He's not available, this isn't my area of expertise, and we launch next week :(

sqlalchemy.url = mysql+mysqldb://gooddeeds:[email protected]/gooddeeds_development?charset=utf8

I've tried appending various "secure auth" strings to the above with no success. Am I looking in the wrong place? Has MySQLdb set secure_auth to ON because I'm running MySQL 5.6.15? If so, how can I change that?

Do you have the exact error message from the MySQL server? I'm not sure that secure_auth has much to do with it. If you still are using insecure, old-passwords (from pre-4.1), you you definitely have change that right now. — geertjanvdk
– geertjanvdk, Commented Feb 10, 2014 at 13:10
It was the well-known "Connection using old (pre-4.1.1) authentication protocol refused..." however, we can't make changes to MySQL on the server. I can log in on the command line with the --skip-secure-auth flag, which I admit should only be a temporary solution. For now, I need to do the same via SQLAlchemy. — Dave Everitt
– Dave Everitt, Commented Feb 11, 2014 at 12:10

geertjanvdk · Accepted Answer · 2014-02-17 12:09:17Z

3

If you are forced to use the old passwords (bah!) when using MySQL 5.6, and using MySQLdb with SQLAlchemy, you'll have to add the --skip-secure-auth to an option file and use URL:

from sqlalchemy.engine.url import URL

..

dialect_options = {
  'read_default_file': '/path/to/your/mysql.cnf',
}

engine = create_engine(URL(
  'mysql',
  username='..', password='..',
  host='..', database='..',
  query=dialect_options
  ))

The mysql.cnf would contain:

[client]
skip-secure-auth

For Pyramid, you can do the following. Add a line in your configuration ini-file that holds the connection arguments:

sqlalchemy.url = mysql://scott:tiger@localhost/test
sqlalchemy.connect_args = { 'read_default_file': '/path/to/foo' }

Now you need to change a bit the way the settings are read and used. In the file that launches your Pyramic app, do the following:

def main(global_config, **settings):
    try:
        settings['sqlalchemy.connect_args'] = eval(settings['sqlalchemy.connect_args'])
    except KeyError:
        settings['sqlalchemy.connect_args'] = {}
    engine = engine_from_config(settings, 'sqlalchemy.')
    # rest of code..

The trick is to evaluate the string in the ini file which contains a dictionary with the extra options for the SQLAlchemy dialect.

edited Feb 17, 2014 at 12:09

answered Feb 11, 2014 at 12:42

geertjanvdk

3,52827 silver badges27 bronze badges

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

2 Comments

Graeme Stuart Over a year ago

How does this work in pyramid where we are using engine = engine_from_config(settings, 'sqlalchemy.')? - (co-developer)

geertjanvdk Over a year ago

I have updated my answer with a solution for Pyramid.

Collectives™ on Stack Overflow

How to pass secure_auth to MySQL login via SQLalchemy

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related