Connect to MySQL 5.7 from Python using SSL

In the previous blog we saw how to connect to MySQL Server 5.7 from PHP using SSL. In this entry I will describe the steps I took to connect to MySQL Server 5.7 from Python code. I used mysql-connector as the Python library. The default install (version 2.2.3) failed, so I had to install the previous version (v. 2.1.6).

sudo pip install mysql-connector==2.1.6

The code to connect using Python is similar to PHP with similar parameters which have to be set. I did run into a few issues with my query not getting parsed if I passed query parameters as parameters. So I had to prepare the whole query and send it as a single complete query with no parameters in the 'execute' function. Below is my piece of working code.

As I went further and tried to put data fetched from WoW API, I faced further problems with text encoding. I also had problems with creating multi line queries. I could not find a lot of discussion material on Stackoverflow. I found out though that Python 2.x has a default text encoding of 'ascii' whereas Python 3.x has a default encoding of 'utf-8'. My EC2 instance came with Python 2.7. When I was sending SQL statements to the mysql-connector library, it was assuming that the text was encoded in default 'ascii' and decoding it based on that before encoding it in 'utf-8'. Below is the error which comes because of this problem.

Traceback (most recent call last):
  File "conn.py", line 78, in 
    db_insert_auctions(conn, cursor, auctions, region, realm_id)
  File "conn.py", line 27, in db_insert_auctions
    for result in cursor.execute(query, multi=True):
  File "/usr/local/lib/python2.7/site-packages/mysql/connector/cursor.py", line 494, in _execute_iter
    result = next(query_iter)
  File "/usr/local/lib/python2.7/site-packages/mysql/connector/connection.py", line 521, in cmd_query_iter
    statements = bytearray(statements.encode('utf-8'))
UnicodeDecodeError: 'ascii' codec can't decode byte 0xc3 in position 200: ordinal not in range(128)

I decided to install Python 3.x. I anyway had a preference for it over 2.x, and was just delaying installing it. This did the trick and there were no more text encoding issues. Below are the steps to install Python 3.x and related dependencies for my code. Note that the make phase of the installation takes a few minutes and it will be available as python3 on the command line. Make sure openssl-devel is installed, if not then the error will show up only on pip install and in that case Python has to be built again after installing openssl-devel. Also note that these commands are for Amazon Linux, so for other distributions the commands or library names could be different.

# Install prerequisites if required
sudo yum groupinstall development
sudo yum install zlib-devel
sudo yum install openssl-devel

# Download, configure, make and install
wget https://www.python.org/ftp/python/3.6.2/Python-3.6.2.tar.xz
tar xf Python-3.6.2.tar.xz
cd Python-3.6.2
./configure --enable-optimizations
make
sudo make install

# Install dependencies using pip3
sudo /usr/local/bin/pip3 install mysql-connector==2.1.6
sudo /usr/local/bin/pip3 install requests
sudo /usr/local/bin/pip3 install boto3

Below is the code which worked for me to insert 'utf-8' encoded input text using multi line SQL insert statements.