CP363 : Connecting to MySQL

Connector/Python Library

MySQL provides a number of connector API (Application Programming Interface) to allow languages such as Python, Java, and C to connect to a MySQL database. These connectors are available from MySQL Connectors. In CP363 connect to MySQL databases with Python, and use the Connector/Python library. The latest version of this library as of this writing is 8.0.18.

Installation

Connector/Python can be installed by downloading and installing the appropriate msi file from the link given above.

Another approach is to use pip , which is a Python package installer that should be part of your Python installation. There are two ways to install Connector/Python with pip:

Open a command prompt and enter the command:
pip install mysql-connector-python
From Eclipse, choose Window / Preferences / PyDev / Interpreters / Python Interpreter, click on and enter the command:
install mysql-connector-python

Defining a Connection

The instructor provides Python code you are to use for simple database connections. The Connect class is a class whose only job is to connect to a database and provide a connection to be used for querying a database. This Python code is at Connect.py.

The ConnectTest class is a very simple sample of connecting to the DCRIS database using Connect. It is at ConnectTest.py, and requires an options file: dcris.txt.

Defining Connection Parameters to `hopper`

The dcris.txt options file to connect to the DCRIS database consists of:

      
[database]
user = your Laurier login
password = your database password (cp363_Pass by default)
host = hopper.wlu.ca
database = dcris

[database] is a section header, and the rest are key = value pairs defining the DCRIS connection information. DCRIS is available to you on the department server hopper.wlu.ca.

Using the `Connect` Class

You need only one copy of the Connect class in your CP363 workspace in Eclipse. Put Connect.py into the src folder of a project named login_data_structures, like you did for CP164. All other CP363 projects should refer to this project. (Check out Project References at Using Eclipse with PyDev.

The following code shows a simple program to connect to the DCRIS database and extract the contents of a table:

      
from Connect import Connect

try:
    # Connect to the DCRIS database with an option file
    conn = Connect("dcris.txt")
    # Get the connection cursor object
    cursor = conn.cursor
    # Define a SQL query
    sql = "SELECT * FROM broad"
    # Execute the query from the connection cursor
    cursor.execute(sql)
    # Print the column names from the query result
    print("Columns:")
    print(cursor.column_names)
    # Get and print the contents of the query results (raw results)
    rows = cursor.fetchall()
    print(f"Row count: {cursor.rowcount}")

    print("Data:")
    for row in rows:
        print(row)
    # Close the Connect object
    conn.close()
except Exception as e:
    print(str(e))

and prints the following results:

Columns:
('broadId', 'broadDesc')

Row count: 19

Data:
(7, 'Arms Control and Non-Proliferation Studies')
(13, 'Civil-Military Relations')
(11, 'Defence Management and Policy')
...
(16, 'Weapons Systems')

The Connect object conn provides a connection to a database, and a cursor object.

Python returns the results of the queries as tuples. Knowing the number of columns and their names would allow you to better format this output. Note that the values are returned with an appropriate Python data type. Thus the broadId values are returned as int, while the broadDesc values are returned as str.

Reference

The CP363 web pages provides the basics for connecting to a MySQL database with Python. Complete documentation for the Connector/Python Library can be found at the MySQL Connector/Python Developer Guide.

CP363 : Connecting to MySQL - Python