Accessing the MySQL Database with Python

MySQL database schemas can be accessed from Python programs through Connector/Python. Connector/Python is a native driver for Python that can communicate with a MySQL server.

Connector/Python comes as a Python library that needs to be installed on the workstation. To check whether it has been installed, run the following in Python3.

import mysql.connector

cnx = mysql.connector.connect(user='exampleuser', password='passabc',
                              host='csmysql.cs.cf.ac.uk',
                              database='sample')
cnx.close()

If you don't get an error message when you run this, then Connector/Python is installed. If you get a message such as

ImportError: No module named 'mysql'

then it's not installed. Contact the School's computing support through .

For information about the School's MySQL database server and to learn about your MySQL account and database schema, see MySQL in the School of Computer Science & Informatics

MySQL from Python

Please look at the examples of using Connector/Python in Python at https://dev.mysql.com/doc/connector-python/en/connector-python-examples.html.

The following program, which uses the School's MySQL Sample Database, shows how to issue an SQL SELECT command to retrieve data from a database, and how to collect and print the retrieved data.

import mysql.connector
cnx = mysql.connector.connect(user='exampleuser', password='passabc',
                              host='csmysql.cs.cf.ac.uk',
                              database='sample')
cursor = cnx.cursor()

query = ("SELECT s.name, o.osname, p.pname "
         "FROM systems s, opsystem o, people p "
         "WHERE s.oscode=o.oscode and s.ownercode = p.pcode")

cursor.execute(query)

print ("Name\tOwner\tOperating System")
for (name, osname, pname) in cursor:
  print("{}\t{}\t{}".format(name, pname, osname))

cursor.close()
cnx.close()

When this program is run, it produces the following output:

Name    Owner   Operating System
green   John    MacOSX
blue    Jane    Windows
red     Jane    Linux
brown   Arthur  Linux
pink    Sally   Linux