CP363: Connecting to MySQL - Wirelessly

NOTE: Other than giving you access to our databases wirelessly, this should have no affect on your Assignments 1 through 4. Since the actual connections to the database should be done outside of the various functions (the parameter conn is an already open database connection), any functions that you have (correctly) written should be unchanged by this document.

SSH Tunneling

Laurier's IT department does not allow direct wireless access to the databases on bohr and hopper for security reasons. They do, however, allow SSH (Secure SHell) access to hopper through Putty for courses such as CP367. We can use this in order to access databases on hopper through something called SSH Tunneling. SSH Tunneling allows us to connect to a server on an open port (22 for SSH), and then connect to that remote server's other externally closed ports from within the remote server. This requires logging into the remote server using SSH. Students do not have accounts on bohr but do have accounts on hopper. I have placed a copy of the DCRIS database on hopper in order to give you access to it.

Instructions

You will require a copy of sshtunnel for Python. If Python is correctly installed on your laptop, you may install sshtunnel by executing the following line from your command prompt:

pip install sshtunnel

If this does not work, you can install sshtunnel from within Eclipse. Go to Window / Preferences / PyDev / Interpreters / Python Interpreter. This brings up the following dialog:

Press the Manage with pip button to bring up the Manage pip dialog. Replace <package> with sshtunnel and press Run :

sshtunnel for Python should now be installed on your laptop.

The Connect class code provided to you earlier is unchanged. However, the call to Connect requires a connection to the remote server with ssh first. You'll need the following Tunnel class (from Tunnel.py):

      
# Imports
from configparser import ConfigParser
from sshtunnel import SSHTunnelForwarder

class Tunnel:

    tunnel = None

    def __init__(self, option_file):
        try:
            # Read the contents of the option file
            config = ConfigParser()
            config.read_file(open(option_file))
            # Extract the ssh section into a dictionary
            params = dict(config['ssh'])
            params['remote_bind_address'] = (
                params['remote_bind_address'], int(params['remote_port']))
            # Remove the remote_port entry
            del params['remote_port']
            # Create the tunnel
            self.tunnel = SSHTunnelForwarder(**params)
        except BaseException as e:
            print(str(e))

    

A Tunnel requires an options file in the same way that Connect does. The options file (name it hopper.txt) should look like:

      
[ssh]
ssh_address_or_host = hopper.wlu.ca
ssh_username = barn4520
ssh_password = …
remote_bind_address = hopper.wlu.ca
remote_port = 3306

(and of course replace barn4520 with your own login and add your password) and the dcris options file for Connect is:

      
[database]
user = barn4520
password = cp363_Pass
host = hopper.wlu.ca
database = dcris

    

If you have changed your database password so that it is no longer cp363_Pass, use that instead.

The connection test program now becomes:

      
# Imports
from Connect import Connect
from Tunnel import Tunnel

try:
    tunnel = Tunnel("hopper.txt")

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

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

    

The with tunnel.tunnel: simply tells Python to perform all database connections within the ssh tunnel connection.

To connect to your own database on hopper (for Assignment 5) create an options file for it similar to dcris.txt.

The ConnectTest.py file provides the above test code.

Tunneling with Heidi

Heidi supports using ssh tunneling. In Heidi's Session Manager choose Network Type MySQL (SSH Tunnel), and fill in the required fields in the SSH Tunnel tab. (You do not need to provide a private key.) MySQL Workbench also supports SSH Tunneling.