Demos of ScienceOps that reads from a remote MySQL server

demo-iris-scienceops.py

from mysql import connector
from sklearn import linear_model
from yhat import Yhat, YhatModel, preprocess


class MySQLIrisClassifier(YhatModel):
    REQUIREMENTS = ['scikit-learn']

    def select_data(self, cursor):
        # Returns the full dataset of features and outputs
        # from the iris table.
        cursor.execute('''
            SELECT
                id,
                sepal_length,
                sepal_width,
                petal_length,
                petal_width,
                target_observed
            FROM
                iris
        ''')
        ids, X, Y = [], [], []
        for row in cursor.fetchall():
            ids.append(row[0])
            X.append([float(z) for z in row[1:-1]])
            Y.append(float(row[-1]))
        return ids, X, Y


    def update_data(self, cursor, ids, predictions):
        records = zip([int(p) for p in predictions], ids)
        cursor.executemany(
            'UPDATE iris SET target_predicted = %s WHERE id = %s',
            records
        )
        return dict(zip(ids, [int(p) for p in predictions]))

    @preprocess(in_type=dict, out_type=dict)
    def execute(self, data):
        if 'run' not in data or not data['run']:
            return {}

        connection = connector.connect(
            host='mysql-demo.yhathq.com',
            database='yhatdemo',
            user='yhat',
            password='yhattest'
        )
        cursor = connection.cursor(buffered=True)

        # Get iris data and fit a model to it.
        ids, X, Y = self.select_data(cursor)
        logreg = linear_model.LogisticRegression(C=1e5)
        logreg.fit(X, Y)

        # Add predictions to the table.
        predictions = {}
        try:
            predictions = self.update_data(cursor, ids, logreg.predict(X))
        except:
            raise
        else:
            connection.commit()
        finally:
            connection.rollback()

        cursor.close()
        connection.close()

        return predictions

yh = Yhat('USERNAME', 'API_KEY', 'SCIENCEOPS_URL')
yh.deploy('MySQLIrisClassifier', MySQLIrisClassifier, globals())

iris-query.sql

SELECT 
    i.sepal_length,
    i.sepal_width,
    i.petal_length,
    i.petal_width,
    ico.class as class_observed,
    icp.class as class_predicted
FROM
    iris i
INNER JOIN 
    iris_class ico
ON
    ico.target = i.target_observed
LEFT OUTER JOIN
    iris_class icp
ON
    icp.target = i.target_predicted

setup-table.py

# This script creates tables for the iris dataset
# and inserts the iris data into them. See README.md.

from mysql import connector
from sklearn import datasets


def create_tables(cursor):
    # Step 1: Create tables on the remote MySQL database
    cursor.execute('DROP TABLE IF EXISTS iris')
    cursor.execute('DROP TABLE IF EXISTS iris_class')
    cursor.execute('''
        CREATE TABLE iris_class (
            target INT NOT NULL PRIMARY KEY,
            class VARCHAR(25) NOT NULL UNIQUE
        )
    ''')
    cursor.execute('''
        CREATE TABLE iris (
            id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
            sepal_length NUMERIC NOT NULL,
            sepal_width NUMERIC NOT NULL,
            petal_length NUMERIC NOT NULL,
            petal_width NUMERIC NOT NULL,
            target_observed INT NOT NULL,
            target_predicted INT,
            FOREIGN KEY (target_observed) REFERENCES iris_class (target),
            FOREIGN KEY (target_predicted) REFERENCES iris_class (target)
        )
    ''')


def insert_data(cursor):
    # Step 2: Insert iris dataset
    iris = datasets.load_iris()

    for target_val, target_name in enumerate(iris['target_names']):
        cursor.execute('''
            INSERT INTO iris_class (target, class) VALUES (%s, %s)
        ''', [target_val, str(target_name)])

    records = []
    for x, y in zip(iris.data, iris.target):
        records.append([str(z) for z in list(x) + [y]])

    cursor.executemany('''
        INSERT INTO iris (
            sepal_length,
            sepal_width,
            petal_length,
            petal_width,
            target_observed
        ) VALUES (
            %s, %s, %s, %s, %s
        )
    ''', records)


if __name__ == '__main__':
    # NOTE: this must be the same as in the `demo-iris-scienceops.py` script
    connection = connector.connect(
        host='<hostname or ip>',
        database='<database name>',
        user='<username>',
        password='<password>'
    )
    cursor = connection.cursor(buffered=True)

    # Create tables and insert data in a single transaction.
    try:
        create_tables(cursor)
        insert_data(cursor)
    except:
        raise
    else:
        connection.commit()
    finally:
        connection.rollback()

    cursor.close()
    connection.close()

mysql-demos

These scripts show Yhat products used with a relational database. Specifically,
they read from and write to a remote MySQL server.
In order to use these demos:

1. Grab a MySQL server. Update the scripts with the appropriate credentials.
2. Re-create the yhatdemo tables. These will contain the iris dataset
   before classification.

python setup-tables.py

1. Connect to MySQL on mysql-demo.yhathq.com and run the iris-query.sql
   query. This will give you the features and two output fields. class-observed
   is the known class of each iris. class-predicted should be null and will
   contain the fitted classifications after we train our model.
2. Re-run the iris-query-sql query. It should now have fitted classifications.