-
-
Save minesh1291/f7a5c10a9d88097cc17a to your computer and use it in GitHub Desktop.
Demos of ScienceOps that reads from a remote MySQL server
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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()) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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() |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment