<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Lesson 3
Step1:  <b>Connect to the MySQL database instance using and account that has admin access and run SQL to drop/create table
Step2:  Check to see that the table was created
Step3:  Exercise 2
Step4:  <b>Check to be sure the new user has access to the new table
Step5:  <b>Exercise 3
Step6:  Now we can check to see if the data was inserted as expected
Step7:  Next we will empty the table so it will be ready for live sensor data
<ASSISTANT_TASK:>
Python Code:
%load_ext sql
Explanation: Lesson 3: Create MySQL Database Table for Sensor Data
Learning Objectives:
<ol>
    <li>Learn how to access MySQL from Jupyter Notebook to create tables and uses</li>
    <li>Prepare location to store data collected from IoT devices</li>    
</ol>
Exercise 1: Create a MySQL table to hold the sensor data from our Raspberry Pi
Note: This exercise will use an existing MySQL database server to expedite the process.<br>
You can use these notes to create the setup in your own server.
<b>Logon using an admin account and create a table called temps3 to hold sensor data:</b>
Table Design Schema:
<pre>
<b>Field         Description</b>
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>
Load the sql notebook extension:
End of explanation
%%sql mysql://admin:admin@172.20.101.81/pidata
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;
Explanation: <b>Connect to the MySQL database instance using and account that has admin access and run SQL to drop/create table:</b>
End of explanation
%sql show tables;
Explanation: Check to see that the table was created:
End of explanation
%%sql mysql://admin:admin@172.20.101.81
DROP USER IF EXISTS piuser3;
CREATE USER 'piuser3'@'%' IDENTIFIED BY 'logger';
GRANT SELECT, INSERT, DELETE, UPDATE ON pidata.temps3 TO 'piuser3'@'%';
FLUSH PRIVILEGES;
%sql select * from mysql.user;
Explanation: Exercise 2: Create a MySQL user that has access to the new table:
We will create a user called <b>piuser</b> that will have limited access to the <b>temps3</b> table on the <b>pidata</b> database.<br>
We will create a user account called <pre>'piuser'@'%'</pre>
This means the <b>piuser</b> account can access the MySQL server from any hostname
Login to the MySQL database using an account that has admin access, then run the code to drop/create the new user:
<br><br>
Note: We will create a new user called <b>piuser3</b> with a password <b>logger</b> that will have access to the <b>temps3</b>
End of explanation
%%sql mysql://piuser3:logger@172.20.101.81/pidata
select * from temps3;
Explanation: <b>Check to be sure the new user has access to the new table:</b>
End of explanation
for x in range(10):
    %sql INSERT INTO temps3 (device,datetime,temp,hum) VALUES('pi-003',date(now()),73.2,22.0);
Explanation: <b>Exercise 3: Add test data to new table to confirm the new piuser account can add records to the table</b>
Note: We are still connected to the pidata database using the piuser3@'%' account
End of explanation
%sql SELECT * from temps3;
Explanation: Now we can check to see if the data was inserted as expected:
End of explanation
%sql DELETE FROM temps3;
%sql SELECT * FROM temps3;
Explanation: Next we will empty the table so it will be ready for live sensor data:
End of explanation
<END_TASK>