# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 2
#     language: python
#     name: python2
# ---

# ### Lesson 2: Setup Jupyter Notebook for Data Analysis
#
# #### Learning Objectives:
# <ol>
#     <li>Create Python tools for data analysis using Jupyter Notebooks</li>
#     <li>Learn how to access data from MySQL databases for data analysis</li>
#     
# </ol>

# #### Exercise 1: Install Anaconda
# Access https://conda.io/miniconda.html and download the Windows Installer.<br>
#
# Run the following commands on the Anaconda command prompt:<br>
# <pre>
# conda install numpy, pandas, matplotlib
# conda update conda
# </pre>
#
# Sometimes data analysis requires previous versions of Python or other tools for a project.<br>
#
# Next we will setup three environments that can be used with various project requirements.

# #### Exercise 2: Configure conda environments for Python 2 and Python 3 data analysis
#
# To create a <b>Python 2</b> enviroment run the following from the Anaconda command prompt:<br>
#     
# <pre>
# conda update conda -y
# conda create -n py2 python=2 anaconda jupyter notebook -y
# </pre>
#
# To activate the environment:<br>
# <pre>source activate py2</pre>
# On MacOS or Linux:
# <pre>source activate py2</pre>
#
# To deactivate the environment:<pr>
# <pre>source deactivate py2</pre>
# On MacOS or Linux:
# <pre>source deactivate py2</pre>

# To create the <b>Python 3</b> environment run the following from the Anaconda command prompt:
#
# <pre>
# conda create -n py3 python=3 anaconda jupyter notebook -y 
# </pre>
#
# To activate the environment: 
# <pre>activate py3</pre>
# On MacOS or Linux:
# <pre>source activate py3</pre>
#
# To deactivate the enviroment:
# <pre>deactivate py3</pre>
# On MacOS or Linux:
# <pre>source deactivate py3</pre>

# ### Setup Jupyter Notebook to access data from MySQL databases

# #### Exercise 3: Load the mysql libraries into the environment and access data from MySQL database
#
# Run the following commands from the Anaconda command line:<br>
# <pre>
# pip install ipython-sql
# conda install mysql-python
# </pre>
#
# This will install sql magic capabilities to Jupyter Notebook

# #### Load the sql magic jupyter notebook extension:

# %load_ext sql

# #### Configure sql magic to output queries as pandas dataframes:

# %config SqlMagic.autopandas=True

# #### Import the data analysis libraries:

import pandas as pd
import numpy as np

# #### Import the MySQLdb library

import MySQLdb

# #### Connect to the MySQL database using sql magic commands
#
# The connection to the MySQL database uses the following format:
#
# <pre>
# mysql://username:password@hostname/database
# </pre>
#
# To start a sql command block type:
# <pre>%%sql</pre>
#
# Note: Make sure the %%sql is on the top of the cell<br>
#
# Then the remaining lines can contain SQL code.<br>
#
# Example: to connect to <b>pidata</b> database and select records from the <b>temps</b> table:
#

# + magic_args="mysql://pilogger:foobar@172.20.101.81/pidata" language="sql"
# SELECT * FROM temps LIMIT 10;
# -

# Example to create a pandas dataframe using the results of a mysql query

# df = %sql SELECT * FROM temps WHERE datetime > date(now());


df

# Note the data type of the dataframe df:

type(df)

# #### Use %%sql to start a block of sql statements
# Example: Show tables in the pidata database

# + language="sql"
# use pidata;
# show tables;
# -

# #### Exercise 4: Another way to access mysql data and load into a pandas dataframe

# Connect using the mysqldb python library:

#Enter the values for you database connection
database = "pidata"                # e.g. "pidata"
hostname = "172.20.101.81"         # e.g.: "mydbinstance.xyz.us-east-1.rds.amazonaws.com"
port = 3306                        # e.g. 3306 
uid = "pilogger"                   # e.g. "user1"
pwd = "<PASSWORD>"                     # e.g. "<PASSWORD>"

conn = MySQLdb.connect( host=hostname, user=uid, passwd=pwd, db=database )
cur = conn.cursor()

# Create a dataframe from the results of a sql query from the pandas object:

new_dataframe = pd.read_sql("SELECT * \
                 FROM temps", 
                 con=conn)
conn.close()

new_dataframe


# ### Now let's create the tables to hold the sensor data from our Raspberry Pi

# <pre>
# <b>Logon using an admin account and create a table called temps3 to hold sensor data:</b>
#
# The table contains the following fields:
# device       -- VARCHAR, Name of the device that logged the data
# datetime     -- DATETIME, Date time in ISO 8601 format YYYY-MM-DD HH:MM:SS
# temp         -- FLOAT, temperature data
# hum          -- FLOAT, humidity data
# </pre>

# + magic_args="mysql://admin:admin@172.20.101.81/pidata" language="sql"
#         
# DROP TABLE if exists temps3;
#
# CREATE TABLE temps3 (
#     device varchar(20) DEFAULT NULL, 
#     datetime datetime DEFAULT NULL,
#     temp float DEFAULT NULL,
#     hum float DEFAULT NULL
# ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
# -

# ### Next we will create a user to access the newly created table that will be used by the Raspberry Pi program

# Example:
# Start a connection using an admin account, create a new user called user1.
# Grant limited privileges to the pidata.temps3 table
#
# Note: Creating a user with @'%' allows the user to access the database from any host

# + magic_args="mysql://admin:admin@172.20.101.81" language="sql"
# CREATE USER 'user1'@'%' IDENTIFIED BY 'logger';
# GRANT SELECT, INSERT, DELETE, UPDATE ON pidata.temps3 TO 'user1'@'%';
# FLUSH PRIVILEGES;
#
# -

# %sql select * from mysql.user;
        

# Next we will test access to the newly created table using the new user
# Start a new connection using the new user

# + magic_args="mysql://user1:logger@172.20.101.81/pidata" language="sql"
# select * from temps3;
# -

# Let's add some test data to make sure we can insert using the new user

for x in range(10):
    # %sql INSERT INTO temps3 (device,datetime,temp,hum) VALUES('pi222',date(now()),73.2,22.0);

# %sql SELECT * FROM temps3;

# Now we will delete the rows in the database

# %sql DELETE FROM temps3;

# %sql SELECT * FROM temps3;

# + magic_args="mysql://admin:admin@172.20.101.81" language="sql"
# drop user if exists 'user1'@'%';
# -

# %sql select * from mysql.user;

StarcoderdataJupyterScriptsDedupFiltered