Access https://conda.io/miniconda.html and download the Windows Installer.
Run the following commands on the Anaconda command prompt:
conda install numpy, pandas, matplotlib conda update conda
Sometimes data analysis requires previous versions of Python or other tools for a project.
Next we will setup three environments that can be used with various project requirements.
To create a Python 2 enviroment run the following from the Anaconda command prompt:
conda update conda -y conda create -n py2 python=2 anaconda jupyter notebook -y
To activate the environment:
source activate py2On MacOS or Linux:
source activate py2
To deactivate the environment:
source deactivate py2On MacOS or Linux:
source deactivate py2
To create the Python 3 environment run the following from the Anaconda command prompt:
conda create -n py3 python=3 anaconda jupyter notebook -y
To activate the environment:
activate py3On MacOS or Linux:
source activate py3
To deactivate the enviroment:
deactivate py3On MacOS or Linux:
source deactivate py3
In [178]:
%load_ext sql
In [179]:
%config SqlMagic.autopandas=True
In [180]:
import pandas as pd
import numpy as np
In [181]:
import MySQLdb
The connection to the MySQL database uses the following format:
mysql://username:password@hostname/database
To start a sql command block type:
%%sql
Note: Make sure the %%sql is on the top of the cell
Then the remaining lines can contain SQL code.
Example: to connect to pidata database and select records from the temps table:
In [182]:
%%sql mysql://pilogger:foobar@172.20.101.81/pidata
SELECT * FROM temps LIMIT 10;
Out[182]:
Example to create a pandas dataframe using the results of a mysql query
In [183]:
df = %sql SELECT * FROM temps WHERE datetime > date(now());
In [184]:
df
Out[184]:
Note the data type of the dataframe df:
In [185]:
type(df)
Out[185]:
In [186]:
%%sql
use pidata;
show tables;
Out[186]:
Connect using the mysqldb python library:
In [187]:
#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 = "foobar" # e.g. "Password123"
In [188]:
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:
In [189]:
new_dataframe = pd.read_sql("SELECT * \
FROM temps",
con=conn)
conn.close()
In [190]:
new_dataframe
Out[190]:
Logon using an admin account and create a table called temps3 to hold sensor data: 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
In [191]:
%%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[191]:
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
In [143]:
%%sql mysql://admin:admin@172.20.101.81
CREATE USER 'user1'@'%' IDENTIFIED BY 'logger';
GRANT SELECT, INSERT, DELETE, UPDATE ON pidata.temps3 TO 'user1'@'%';
FLUSH PRIVILEGES;
Out[143]:
In [144]:
%sql select * from mysql.user;
Out[144]:
Next we will test access to the newly created table using the new user Start a new connection using the new user
In [146]:
%%sql mysql://user1:logger@172.20.101.81/pidata
select * from temps3;
Out[146]:
Let's add some test data to make sure we can insert using the new user
In [174]:
for x in range(10):
%sql INSERT INTO temps3 (device,datetime,temp,hum) VALUES('pi222',date(now()),73.2,22.0);
In [175]:
%sql SELECT * FROM temps3;
Out[175]:
Now we will delete the rows in the database
In [166]:
%sql DELETE FROM temps3;
Out[166]:
In [168]:
%sql SELECT * FROM temps3;
Out[168]:
In [133]:
%%sql mysql://admin:admin@172.20.101.81
drop user if exists 'user1'@'%';
Out[133]:
In [134]:
%sql select * from mysql.user;
Out[134]: