Help With The PEP249 Standard

This information is no longer needed as the version of python3 installed on pandora is now configured to support PyMySQL.

It is retained only so you can install PyMySQL on your own computer, or in case of problems.

Installing PyMySQL

Install the PyMySQL library locally by using the command:

  pip3 install --user PyMySQL

See the Notes page for more information on why and whether you need to do this.

Starting Your Script

#!/usr/bin/env python3
import pymysql.cursors

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

# 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 = pymysql.connect(host=dbhost, port=port, user=dbuser, passwd=dbpass, db=dbname)

# Create a cursor and execute the SQL on it
cursor = db.cursor()
nrows  = cursor.execute(sql)

If you have problems with not being able to load the module...

Some people have reported problems with loading the pymysql module. If this affects you, use a different install of python.

i.e. if you are running your script by specifying python3 on the command line, use:

/l_mnt/python/envs/teaching/bin/python3  myscript.py

or, if you have made the script executable and are running it as ./myscript.py, then change the shebang line to:

#!/l_mnt/python/envs/teaching/bin/python3

Also, change your imports to:

import pymysql.cursors
import mysql

Retrieving a single row

When you know there is only one row being returned, this is the simplest way to retrieve it:

data   = cursor.fetchone()

data is now a list containing the fields returned by the query. Access them with data[0], etc.

Retrieving multiple rows

When multiple rows are returned there are three ways to retrieve them.

The first method is to use cursor.fetchone(), as with a single row and then iterate until it returns nothing:

data = cursor.fetchone()
while data is not None:

    ... do something with data ...

    data = cursor.fetchone()

The second, and by far the easiest, method is to use cursor as an iterator:

for data in cursor:

    ... do something with data ...

The third method (which will not be described here) is to use cursor.fetchmany()

Continue