Connecting Python to MySQL using an encrypted option file

Question

I used mysql_config_editor to create a .mylogin.cnf file with a password. I know it worked correctly because I can use it to connect through both the command line utility mysql and the R package RMySQL without a problem.

However, when trying to connect using Mysql-Connector/Python:

# using mysql-connector-python-rf
import os
import mysql.connector
con = mysql.connector.connect(option_files=os.path.expanduser('~/.mylogin.cnf'))

or with PyMySQL:

# using pymysql
import os
import pymysql
con = pymysql.connect(option_files=os.path.expanduser('~/.mylogin.cnf'))

I get the same error:

---------------------------------------------------------------------------
UnicodeDecodeError                        Traceback (most recent call last)
<ipython-input-64-d17e56ef7010> in <module>()
----> 1 con = mysql.connector.connect(option_files=os.path.expanduser('~/.mylogin.cnf'))

/usr/local/lib/python3.5/site-packages/mysql/connector/__init__.py in connect(*args, **kwargs)
    140     # Option files
    141     if 'option_files' in kwargs:
--> 142         new_config = read_option_files(**kwargs)
    143         return connect(**new_config)
    144 

/usr/local/lib/python3.5/site-packages/mysql/connector/optionfiles.py in read_option_files(**config)
     66             config['option_files'] = [config['option_files']]
     67         option_parser = MySQLOptionsParser(list(config['option_files']),
---> 68                                            keep_dashes=False)
     69         del config['option_files']
     70 

/usr/local/lib/python3.5/site-packages/mysql/connector/optionfiles.py in __init__(self, files, keep_dashes)
    162             self.files = files
    163 
--> 164         self._parse_options(list(self.files))
    165         self._sections = self.get_groups_as_dict()
    166 

/usr/local/lib/python3.5/site-packages/mysql/connector/optionfiles.py in _parse_options(self, files)
    193                                      "than once in the list".format(file_))
    194                 with open(file_, 'r') as op_file:
--> 195                     for line in op_file.readlines():
    196                         if line.startswith('!includedir'):
    197                             _, dir_path = line.split(None, 1)

/usr/local/Cellar/python3/3.5.1/Frameworks/Python.framework/Versions/3.5/lib/python3.5/encodings/ascii.py in decode(self, input, final)
     24 class IncrementalDecoder(codecs.IncrementalDecoder):
     25     def decode(self, input, final=False):
---> 26         return codecs.ascii_decode(input, self.errors)[0]
     27 
     28 class StreamWriter(Codec,codecs.StreamWriter):

UnicodeDecodeError: 'ascii' codec can't decode byte 0x96 in position 28: ordinal not in range(128)

Skimming over the source code, it looks like these are trying to read the files in cleartext. However, mysql_config_editor encrypts the login file it generates. Both modules work fine when entering the password manually in the code.

How can I connect to Python using one of these generated config files? I'm using Python 3, so MySQLdb isn't an option.

update: for now, I'm using RPy2 to run queries in R and pipe the results back into Python. The code is a little ugly but the workflow isn't so bad.

Likely you already know this, but the cleartext ~/.my.cnf option file will work with Mysql-Connector/Python > 2.0. — Ian
– Ian, Commented May 12, 2016 at 19:16
@Ian yup, the whole point is not to use cleartext. but i switched over to it anyway out of frustration... — shadowtalker
– shadowtalker, Commented May 12, 2016 at 19:31
@Ian in my understanding the encryption used is pretty weak. so if someone really wanted to get at my stuff it wouldn't be much of an obstacle. — shadowtalker
– shadowtalker, Commented May 12, 2016 at 20:28
The .mylogin.cnf file is not really encrypted anyway, since it contains the key to decrypt it. It's just obfuscated. There are various scripts online to extract the plain text, among which myloginpath.py from the module in exic's answer. Or even just the command my_print_defaults -s root — mivk
– mivk, Commented Feb 8, 2020 at 23:32

exic · Accepted Answer · 2019-01-22 08:07:11Z

3

pip install myloginpath worked for me, then:

import myloginpath
import pymysql
conf = myloginpath.parse('client')
db = pymysql.connect(**conf, host='mydbhost', db='whatever')

answered Jan 22, 2019 at 8:07

exic

2,7282 gold badges26 silver badges31 bronze badges

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

2 Comments

shadowtalker Over a year ago

Nice find, I don't think this existed back then.

exic Over a year ago

True, it probably didn't :-)

Collectives™ on Stack Overflow

Connecting Python to MySQL using an encrypted option file

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