Using pymysql with Python 3.5

So instead of moving forward with anything I mentioned before... Ta Da!

This bit of Python code makes it very easy to connect to a MySQL database using pymysql.

Anyone who has decided to move to Python 3.5 and also developing scripts for MySQL will have discovered that many libraries aren't yet compatible. MySQL connector is at the time of this writing (4-21-2016) only compatible with Python 3.4.

I imagine most libraries will have moved to 3.5 soon enough but there are plenty of reasons to use pymysql as a base library -- for one it is very Pythonic.

Problems that seemed common when I did a quick search on this topic:

Authentication Errors
While there are plenty of reasons for this the most common are:

1. The user in MySQL aren't set up correctly -- either because 'user'@'localhost' or 'user'@'%' aren't set up or the user doesn't have the expected access in the GRANT tables
2. There's some script-related problem -- I cover one possibility below
3. All the other obvious reasons why anything can't connect to anything

Anyway, in case you were looking for this here it is:

import pymysql as mdb
import sys
import base64

class MySQLUtility:
 def __init__(self,**db_params):
  self.connection_parameters = db_params
 
 def QueryAndReturnRows(self,query):
  with mdb.connect( **self.connection_parameters ) as db:
   db.execute(query)
   rows = db.fetchall()
   return rows

def testUtility():
 password = base64.b64decode(b'HsKeTbb0=').decode('utf-8')
 connect_params = {
   'user': "planetman",
   'password': password,
   'host': '10.1.15.25',
   'port' : 4308,
   'db': 'planetdb',
   'charset': 'utf8mb4',
   'cursorclass' : mdb.cursors.DictCursor
 }
 msqltool = MySQLUtility(**connect_params)
 port_rows = msqltool.QueryAndReturnRows("SELECT PortName,Location,IPAddress,MACAddress FROM ports")
 for row in port_rows:
  print(row)

As you can see this is a very simple class--but this is the basic skeleton I'd start with and expand on.

The pymysql (which is imported as "mdb") stuff happens within the MySQLUtility class. There are a few interesting things that happen in the script as a whole:

• Many people encode a password just so it isn't easy to automatically read as plain-text. Config files often contain passwords and while encryption would be best encoding is better than plain-text. In this case I'm assuming you've encoded a password with the base64 method. In Python 3 you need to really treat the encoding as bytes -- and when decoding tell base64.b64decode() what flavor of string you expect. In this case we want a 'utf-8' string decoded from the b64 byte string. Otherwise base64.b64decode(b'some encoded text') will return literal b'some encoded text'. And then MySQL will reject with a failed password error.
• Use the **params method of passing data structures. It's just easy and looks good.
• Connection methods in all libraries look almost exactly the same. The underlying code connecting to MySQL supports and expects the things that MySQL supports and expects (like cursors).

OK that's it for today. I've got several larger examples of things for later when I can get time to make put them into a readable form.