mysqlclient

These notes explain how to use mysqlclient in place of the PEP249 compliant PyMySQL.

Install a local copy of the mysqlclient interface code:

pip3 install --user mysqlclient

Your code should then be of the general form:

#!/usr/bin/env python3
import _mysql

# Set parameters
dbname   = "biodb"
dbhost   = "pandora"
dbuser   = "xxx"     # Ask a demonstrator
dbpass   = "xxx"     # Ask a demonstrator

# Create SQL statement to find information for proteins from Leishmania
sql = "select pdb_code, resolution, name from protein where source = 'LEISHMANIA MAJOR'"

# Connect to the database
db = _mysql.connect(host=dbhost, user=dbuser, passwd=dbpass, db=dbname)

# Execute the query
db.query(sql)

# Grab the results back from the database into a Python object that we
# can access
results = db.store_result()

# Fetch the first row of the results. Note that we cannot use the
# 'results' object as an iterator so we have to iterate manually
# through the results using the '.fetch_row()' method.
#
# The default ('how=0') gets the results as a tuple of tuples (i.e. a
# list of lists). The outer list only contains one item.
# 'how=1' gets it back as a list of dictionaries and again the (outer)
# list contains only one item.
result  = results.fetch_row(how=1)

# Having got the one result row back, we iterate around, doing
# something with that row and then getting the next row. Note that
# strings are returned as Python 'bytestrings' and need to be
# converted to normal strings with the '.decode()' method before
# printing them. Floating point numbers appear as normal strings so
# should not be decoded
while result:
   print (result[0]['colname1'].decode(), ' ', result[0]['colname2'].decode())
   result = results.fetch_row(how=1)