Note: This exercise will use an existing MySQL database server to expedite the process.
You can use these notes to create the setup in your own server.
Logon using an admin account and create a table called temps3 to hold sensor data:
Table Design Schema:
Field Description 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
Load the sql notebook extension:
In [3]:
%load_ext sql
Connect to the MySQL database instance using and account that has admin access and run SQL to drop/create table:
In [6]:
%%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;
Out[6]:
Check to see that the table was created:
In [7]:
%sql show tables;
Out[7]:
We will create a user called piuser that will have limited access to the temps3 table on the pidata database.
We will create a user account called
'piuser'@'%'
This means the piuser 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:
Note: We will create a new user called piuser3 with a password logger that will have access to the temps3
In [29]:
%%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;
Out[29]:
In [30]:
%sql select * from mysql.user;
Out[30]:
Check to be sure the new user has access to the new table:
In [32]:
%%sql mysql://piuser3:logger@172.20.101.81/pidata
select * from temps3;
Out[32]:
Exercise 3: Add test data to new table to confirm the new piuser account can add records to the table
Note: We are still connected to the pidata database using the piuser3@'%' account
In [34]:
for x in range(10):
%sql INSERT INTO temps3 (device,datetime,temp,hum) VALUES('pi-003',date(now()),73.2,22.0);
Now we can check to see if the data was inserted as expected:
In [35]:
%sql SELECT * from temps3;
Out[35]:
Next we will empty the table so it will be ready for live sensor data:
In [37]:
%sql DELETE FROM temps3;
Out[37]:
In [38]:
%sql SELECT * FROM temps3;
Out[38]: