Design & Development of IoT Application

Using Raspberry Pi2 and ThingSpeak

A Practical Approach
(Experimental Manual for B.Tech & M.Tech students)

For System on Chip and Embedded Systems

In association with Microsoft

Designed & Developed By: Mrs. Ritu Gupta, Project Associate, CSE

Under the Guidance of:Prof. SRN Reddy, CSE

Computer Science & Engineering Department

Indra Gandhi Delhi Technical University for Women

Kashmere Gate, Delhi - 110006

 LIST OF EXPERIMENTS

Exp No. Description of Experiment

To understand what is cloud, its importance, usage, services and types of Cloud.
Exp. 1
To familiarize with ThingSpeak and understand the procedure of creation of a
Exp. 2
Channel over ThingSpeak.
Exp. 3 To understand the procedure of MATLAB analysis of a ThingSpeak Channel.
Exp. 4 To understand the procedure of MATLAB visualization of a ThingSpeak Channel.
To understand the procedure of scheduling the MATLAB analysis code of a
Exp. 5
ThingSpeak Channel.
To understand the integrated procedure of creation, analysis, scheduling and
Exp. 6
visualization of a Channel.
Exp. 7 To upload DHT11 sensor data to ThingSpeak channel through Raspberry pi2.
Exp. 8 To upload Light sensor (TSL) data to ThingSpeak channel through Raspberry pi2
To read Light Sensor data from ThingSpeak channel and store it into database
Exp. 9
through Raspberry pi2.

2|Page
 Experiment 1

Objective: To understand what is cloud, its importance, usage, services and types of Cloud.

Introduction:

1. What is cloud computing?

Cloud computing is the delivery of computing services – servers, storage, databases , networking,
software, analytics and more—over the Internet.

Cloud computing may also be referred to as simply “the cloud,” is the delivery of on-demand
computing resources—everything from applications to data centers—over the internet on a pay-for-
use basis.

· Elastic resources—Scale up or down quickly and easily to meet demand

· Metered service so you only pay for what you use
· Self service—All the IT resources you need with self-service access.

2. Why we need cloud?

There are six common reasons why we need cloud:

1. Cost – Cloud computing is very cost effective. By using it we can easily eliminate the huge
expenses of buying hardware and software and setting up and running on – site data
centersi.e, rack s full of servers, 24 x 7 electricity for power and cooling , IT experts for
managing all the infrastructure.

2. Speed – Most cloud computing services are on demand and self service provided. Vast
amount of computing services provisioned with in minutes or with just few mouse clicks.

3. Global Scale – It includes ability to scale elastically, it means delivering the right amount of IT
resources and from the right geographic location.
4. Productivity – Cloud computing removes the need of many time consuming tasks like
hardware setup , software patchingand other time- consuming IT management tasks.

5. Performance - The biggest cloud computing services run the world wide network of secure
data centers that are regularly upgraded to the fastest and efficient computing hardware.

6. Reliability- Cloud computing makes it easy to backup the data, disaster recovery, maintain
business continuity as data can be easily transfered to multiple redundant sites on the cloud
provider’s network.

3. Usage of cloud computing

Following things can be done with a cloud:

3|Page
 · Create new apps and services
· Store, back up and recover data
· Host websites and blogs
· Stream audio and video
· Deliver software on demand
· Analyse data for patterns and make predictions

4. Type of cloud services:

Cloud services fall into three broad categories:

1. IaaS (Infrastructure-as-a-service) – IaaS provides companies with IT infrastructure like

servers, virtual machines, storage, networking, operating system on pay-per-use-basis.
Benefits of using IaaS:
a. No need of investing in hardware.
b. On demand scaling of infrastructure to support dynamic work load.
c. Flexible and innovative services are available as per the requirement.
2. Paas (Plateform-as-a-service) – These cloud computing services provides environment for
developing , testing, delivering and managing software applications as per developer’s
requirement.
Benefits of using PaaS:
a. Easy and fast development of applications.
b. Easy deployment of new web applications to the cloud.
c. Reducing complexity with middleware as service.
3. SaaS (Software-as-a-service) – Software applications can be easily delivered, managed and
run on the cloud from distant computers.
Benefits of using SaaS:
a. Easy signup and rapid start of services via innovative apps.
b. Apps and data are accessible from any connected device.
c. Data is stored in the cloud so, no loss of data if any of device breaks
d. Services are scalable as per the usage.

Figure 1- Types of cloud services and examples

4|Page
 4. Types of cloud:

There are three different ways to deploy the cloud resources:

1. Public Cloud: Public clouds are owned and operated by a third party service provider. These
are the public networks that use shared resources. Easily available to general public.
Inexpensive to set-up as no need to buy IT infrastructure and maintain it.
2. Private Cloud: Private clouds are owned and operated by a single company. It is not shared
with other users. It is either internally managed or managed by a third party.
3. Hybrid Cloud: Combining public and private cloud results into hybrid cloud. Organisations
use public cloud to deal with the customers and private cloud for internal usage.

5|Page
 Experiment 2

Objective: To familiarize with ThingSpeak and understand the procedure of creation of a Channel
over ThingSpeak.

Introduction:

What is ThingSpeak?

ThingSpeak is an open source platform that provides various services exclusively targeted for
development of IoT (Internet of Things) applications. It enables various services like real time
data collection, analysis and visualisation of collected data via charts. It enables the creation
of various plugins, apps collaborating with various web services, social networking and other
APIs.

Thingspeak Channel:
‘Thingspeak channel’ is the core element of the thingspeak platform. This channel is used to
store the real-time data or the data transferred through various sensors and embedded
systems. Data stored at the channel is further used for analysis and visualisation.

Software Requirement: Internet .

Hardware Requirement: Computer System.

Procedure:

1. Creating a channel :

Before creating a channel you need to sign in to things speak. You can easily sign in either
using your either thingspeak account or mathswork account, or create a new mathswork
account via following link:

https://thingspeak.com/users/sign_up

6|Page
 1. Click on the menu bar ‘Channels > My Channels.’

2. Now on the channels page click on the button ‘New Channel’.

7|Page
 3. New channel page have various text box fields showing the settings of the channel.
a. Name – provide a unique name to your channel.
b. Fields – Click the check boxes next to the field and then enter the field name.

c. To make your channel public check the ‘Make Public‘check box.

d. Similarly, you can also add the location to your channel by clicking the ‘Show
Location’ check box.
e. Check the ‘Show video’ check box to make the video visible uploaded by you.
f. Now click the ‘Save channel’ button to save your channel.

8|Page
 4. Now , the channel page opens with the following tabs:
a. Private View- It displays the information about your channel that is only visible to
you.

b. Public View- If you have chosen to make your channel publically visible then it will
display the selected fields and information.

9|Page
 c. Channel Settings- It will show all the options that are available during the channel
creation.

d. API Keys- In this tab you will have two API Keys – Read API key (to read from your
channel), write API Key (to write to your channel).

10 | P a g e
 e. Data Import/export- It enables you to import and export the channel data.

5. In future your channel will be available to you just by clicking ‘Channels >My Channels’.

11 | P a g e
 Experiment 3

Objective: To understand the procedure of MATLAB analysis of a ThingSpeak Channel.

Software Requirement: Internet.

Hardware Requirement:Computer System.

Procedure:

a. Sign in to your ThingSpeak account.

b. Click on ‘Channels>My Channels’ option of menu bar.
c. Select the channel for which you want to do analysis.
d. Click on the tab ‘Apps’ of the menubar and choose the option ‘MATLAB Analysis’or
You can also go directly by clicking the button ‘MATLAB Analysis’ available on your
channel window.

e. Click on the ‘New button’.

12 | P a g e
 f. The page display with the following options:

g. Click on the option ‘Custom (no starter code)’. Then click the ‘Create’ button.

13 | P a g e
 a. In the ‘Name’ field type the name for your analysis.
b. Now write the MATLAB Code in below textbox for analysis using the following steps:
a. First mention the Channel ID of the Channel from which you are getting or
reading the data using the following syntax:
readChannelID=’ ’;
b. Then mention the Id of the channel to which you want to write the
analysed results.
writeChannelID = ‘ ’;
c. Now mention the write API key of your channel. You may get the write
API key by just clicking on the tab ‘API Keys’ of your channel.
writeAPIKey = ‘ ’;
d. Read the data from the channel using following code:
[data, time] = thingSpeakRead(readChannelID);
e. Now add the data analysis code at this step.
f. Store the result in the analyzed data variable.
analyzedData= data;
g. Now write the data to your channel using the following code:
thingSpeakWrite(writeChannelID, analyzedData, 'WriteKey',
writeAPIKey);
h. The complete code will look like as follows:
readChId= 60903;
writeChId = 281300;
writeKey = '80ORTDZMU2HF8PXL';
[temp,time] = thingSpeakRead(readChId,'Fields',4,'NumPoints',20);
humidity = thingSpeakRead(readChId,'Fields',3,'NumPoints',20);
tempC = (5/9)*(temp-32);
b = 17.62;
c = 243.5;

14 | P a g e
 gamma = log(humidity/100) + b*tempC./(c+tempC);
dewPoint = c*gamma./(b-gamma)
dewPointF = (dewPoint*1.8) + 32;
thingSpeakWrite(writeChId,[temp,humidity,dewPointF],'Fields',[1,2,3],...
'TimeStamps',time,'Writekey',writeKey);
i. Finally click on the ‘Save and Run’ button to get output.
j. You will get the analysis chart like as follows:

15 | P a g e
 Experiment 4

Objective: To understand the procedure of MATLAB visualization of a ThingSpeak Channel.

Software Requirement:

Hardware Requirement:

Procedure:

a. Sign in to your ThingSpeak account.

b. Click on ‘Channels>My Channels’ option of menu bar.
c. Select the channel for which you want to do analysis.
d. Click on the tab ‘Apps’ of the menubar and choose the option ‘MATLAB
Visualizations’or you can also go directly by clicking the button ‘MATLAB
Visualizations’ available on your channel window.

e. The following page will get opened:

16 | P a g e
 f. Click on the radio button ‘Custom (no starter code)’. Then click the ‘Create’
button.
g. Enter the ‘Name’ field.
h. Now enter the MATLAB code in the field using below instructions:
· Enter your channel ID and read API key. Use the following syntax:
readChId = ‘ ‘;
readKey = ‘ ‘;
· Read the data from the channel using the following syntax:
[data, time] = thingSpeakRead(readChannelID, 'Field', fieldID1,
'NumPoints', 30, 'ReadKey', readAPIKey);
· Use the following to visualize the data:
thingSpeakPlot(time, data);
i. The complete code will look like as follows:

readChId = 281300;

readKey = 'VDI33L79J7XFM00F';

[dewPointData,timeStamps] = thingSpeakRead(readChId,'fields',[1,2,3],...

'NumPoints',100,'ReadKey',readKey);

thingSpeakPlot(timeStamps,dewPointData,'xlabel','TimeStamps',...

'ylabel','Measured Values','title','Dew Point Measurement',...

'Legend',{'Temperature','Humidity','Dew Point'},'grid','on');

j. Then click on the button ‘Save and Run’.

k. The visualization chart will look like as follows:

17 | P a g e
 Experiment 5

Objective: To understand the procedure of scheduling the MATLAB analysis code of a ThingSpeak
Channel.

Software Requirement:

Hardware Requirement:

Procedure:

1. Click on the ‘TimeControl’ tab at the bottom of your MATLAB Analysis page.

2. TimeControl page will look as follows:

3. Edit the settings of the page:

I. Name: Enter a unique name .
II. Time Zone : edit the time zone based on your location.

18 | P a g e
 III. Frequency: how many times you want to make the TimeControl to
run .
· Choose ‘One Time’ , if you want it to run a single
time only.
· Else choose ‘Recurring’.
· Now edit your recurrence settings to schedule the
recurrence of analysis.
o Recurrence – choose a recurrence pattern
‘week, day, hour or minute’.
· Edit the time settings accordingly.
· Set the ‘Fuzzy Time’. Fuzzy time is nothing but a
random number of minutes that make the
TimeControl to run.
4. At the end click the ‘Save TimeControl’ button to make it run.

19 | P a g e
 Experiment 6

Objective: To understand the integrated procedure of creation, analysis, scheduling and visualization
of a Channel.

Software Requirement:

Hardware Requirement:

Procedure:

1.Creating a channel :
1. Before creating a channel you need to sign in to things speak. You can easilysign in
either using your either thingspeak account or mathswork account, or create a new
mathswork account via following link:

https://thingspeak.com/users/sign_up

1. Click on the menu bar ‘Channels > My Channels.’

20 | P a g e
21 | P a g e
 2. Now on the channels page click on the button ‘New Channel’.

3. New channel page have various text box fields showing the settings of the channel.
a. Name – provide a unique name to your channel.
b. Fields – Click the check boxes next to the field and then enter the field name.

c. To make your channel public check the ‘Make Public‘check box.

d. Similarly, you can also add the location to your channel by clicking the ‘Show
Location’ check box.
e. Check the ‘Show video ’ check box to make the video visible uploaded by you.
f. Now click the ‘Save channel’ button to save your channel.

22 | P a g e
 4. Now , the channel page opens with the following tabs:
a. Private View- It displays the information about your channel that is only visible to
you .

b. Public View- If you have choose to make your channel publically visible then it will
display the selected fields and information.

23 | P a g e
 c. Channel Settings- It will show all the options that are available during the channel
creation.

d. API Keys- In this tab you will have two API Keys – Read API key ( to read from your
channel), write API Key (to write to your channel).

24 | P a g e
 e. Data Import/export- It enables you to import and export the channel data.

5. In future your channel will be available to you just by clicking ‘Channels >My Channels’.
2. MATLAB Analysis:
a. Sign in to your ThingSpeak account.
b. Click on ‘Channels>My Channels’ option of menu bar.
c. Select the channel for which you want to do analysis.
d. Click on the tab ‘Apps’ of the menubar and choose the option ‘MATLAB Analysis ‘or
You can also go directly by clicking the button ‘MATLAB Analysis’ available on your
channel window.

25 | P a g e
 e. Click on the ‘New button’.

f. The page display with the following options:

26 | P a g e
 g. Click on the option ‘Custom (no starter code)’. Then click the ‘Create’ button.

h. In the ‘Name’ field type the name for your analysis.

i. Now write the MATLAB Code in below textbox for analysis using the following steps:
i. First mention the Channel ID of the Channel from which you are getting or
reading the data using the following syntax:
readChannelID=’ ’;
ii. Then mention the Id of the channel to which you want to write the analysed
results.
writeChannelID = ‘ ’;
iii. Now mention the write API key of your channel. You may get the write API
key by just clicking on the tab ‘API Keys’ of your channel.
writeAPIKey = ‘ ’;
iv. Read the data from the channel using following code:
[data, time] = thingSpeakRead(readChannelID);
v. Now add the data analysis code at this step.
vi. Store the result in the analyzed data variable.

27 | P a g e
 analyzedData= data;
vii. Now write the data to your channel using the following code:
thingSpeakWrite(writeChannelID, analyzedData, 'WriteKey',
writeAPIKey);
viii. Finally click on the ‘Save and Run’ button to get output.
ix. You will get the analysis chart like as follows:

3. Scheduling the MATLAB Analysis code:

a. Click on the ‘TimeControl’ tab at the bottom of your MATLAB Analysis page.

b. TimeControl page will look as follows:

28 | P a g e
 c. Edit the settings of the page:
IV. Name: Enter a unique name .
V. Time Zone : edit the time zone based on your location.
VI. Frequency: how many times you want to make the TimeControl to
run .
· Choose ‘One Time’ , if you want it to run a single
time only.
· Else choose ‘Recurring’.
· Now edit your recurrence settings to schedule the
recurrence of analysis.
o Recurrence – choose a recurrence pattern
‘week, day, hour or minute’.
· Edit the time settings accordingly.
· Set the ‘Fuzzy Time’. Fuzzy time is nothing but a
random number of minutes that make the
TimeControl to run.

d. At the end click the ‘Save TimeControl’ button to make it run.

4. MATLAB Visualization of the Channel:
a. Click on the tab ‘MATLAB Visualization’. The following page will get opened:

29 | P a g e
 b. Click on the radio button ‘Custom (no starter code)’. Then click the ‘Create’ button.
c. Enter the ‘Name’ field.
d. Now enter the MATLAB code in the field using below instructions:
· Enter your channel ID and read API key. Use the following syntax:
readChId = ‘ ‘;
readKey = ‘ ‘;
· Read the data from the channel using the following syntax:
[data, time] = thingSpeakRead(readChannelID, 'Field', fieldID1,
'NumPoints', 30, 'ReadKey', readAPIKey);
· Use the following to visualize the data:
thingSpeakPlot(time, data);
l. Then click on the button ‘Save and Run’.
m. The visualization chart will look like as follows:

30 | P a g e
 Experiment 7

Objective:To upload DHT11 sensor data to ThingSpeak channel through Raspberry pi2.

Software Requirement: Python , Web browser.

Hardware Requirement: Raspberry Pi 2, DHT11 sensor, connectors, sensor connecting wires,

computer system.

Procedure:

1. Creating a Channel:
a. Create a Channel over ThingSpeak.
b. Edit the channel settings, add two fields first one for temperature and second for
humidity .
c. Save the channel.
2. Establishing hardware infrastructure:
a. Take a Raspberry Pi 2 board, connect it with the power of 12V approx..
b. Make the connections with monitor, keyboard and mouse.

3. Connecting sensor with Raspberry Pi 2:

a. The DHT11 sensor consists of three connecting pins :
1. VCC: This pin used to connect with the voltage. Connect this pin with the
pin number 2 at raspberry pi board via connecting wire.
2. GND: This is called as ‘ground’. Connect it at Pin number 6 of pi board.
3. DATA: It is used as an output data port connect this at pin named GPIO 23
at pi board.
b. The connections of sensor with the raspberry Pi is shown the following diagram:

31 | P a g e
 4. Coding for DHT11 Sensor:
a. The python code for reading the sensor data is as follows:

importsys
importRPi.GPIOasGPIO
fromtimeimportsleep
importAdafruit_DHT
importurllib2

defgetSensorData():
RH,T=Adafruit_DHT.read_retry(Adafruit_DHT.DHT11,23)
# return dict
return(str(RH),str(T))

# main() function
defmain():
# use sys.argv if needed
iflen(sys.argv)<2:
print('Usage: python tstest.py PRIVATE_KEY')
exit(0)
print'starting...'

baseURL='https://api.thingspeak.com/update?api_key=%s'%sys.argv[1]

whileTrue:
try:
RH,T=getSensorData()
f=urllib2.urlopen(baseURL+
"&field1=%s&field2=%s"%(RH,T))
printf.read()
f.close()
sleep(15)
except:
print'exiting.'
break

# call main

32 | P a g e
 if__name__=='__main__':
main()

snapshot of the coding

5. Save the code as ‘dht_thingspeak.py’.

6. Use Adafruit DHT Library to make this code run.
7. Now run the following command at the terminal to execute the code:
sudopython dht_thingspeak.py ‘writeAPI Key’.
Here, mention the write API key of your thingspeak channel at theplace ’writeAPIKey’.
8. The snapshot of running the python code is as follows:

5. Result:

The data at your ThingSpeak channel will be visible as follows:

33 | P a g e
34 | P a g e
 Experiment 8

Objective:To upload Light sensor (TSL) data to ThingSpeak channel through Raspberry pi2.

Software Requirement: Python , Web browser.

Hardware Requirement: Raspberry Pi 2, Light sensor (TSL), connectors, sensor connecting wires,
computer system.

Procedure:

1. Creating a Channel:
a. Create a Channel over ThingSpeak.
b. Edit the channel settings, add single field for light intensity .
c. Save the channel.
2. Establishing hardware infrastructure:
a. Take a Raspberry Pi 2 board, connect it with the power of 12V approx.
b. Make the connections with monitor, keyboard and mouse.
3. Connecting sensor with Raspberry Pi 2:
a. The Light sensor consists of three connecting pins :
1. VCC: This pin used to connect with the voltage. Connect this pin with the
pin number 2 at raspberry pi board via connecting wire.
2. GND: This is called as ‘ground’. Connect it at Pin number 6 of pi board.
3. SCL: Connect this pin with the SCL pin at the PI board.
4. SDA: Connect this pin with the SDA pin at the PI board.
4. Coding for Light Sensor:
a. The python code for reading the sensor data is as follows:
#!/usr/bin/python
import sys
import smbus
import time
import urllib2
from time import sleep
from Adafruit_I2C import Adafruit_I2C
import RPi.GPIO as GPIO
GPIO.setmode(GPIO.BCM)
GPIO.setup(25,GPIO.OUT)
GPIO.setwarnings(False)
class Luxmeter:
i2c = None
gain = 0
def __init__(self, address=0x39, debug=0, pause=0.8):
self.i2c = Adafruit_I2C(address)
self.address = address
self.pause = pause
self.debug = debug

35 | P a g e
 self.gain = 0
self.i2c.write8(0x80, 0x03)
def setGain(self,gain=1):
"""set the gain"""
if(gain != self.gain):
if(gain==1):
self.i2c.write8(0x81,0x02)
if (self.debug):
print"Setting low gain"
else:
self.i2c.write8(0x81, 0x12)
if(self.debug):
print"Setting high gain"
self.gain=gain;
time.sleep(self.pause)
def readWord(self, reg):
"""Reads a word from I2C device"""
try:
wordval = self.i2c.readU16(reg)
newval = self.i2c.reverseByteOrder(wordval)
if (self.debug):
print("I2C: Device 0x%02X returned 0x%04X from reg
0x%02X"%(self.address, wordval& 0xFFFF, reg))
return newval
except IOError:
print("Error accessing 0x%02X: Check your I2C address" %
self.address)
return -1
def readFull(self, reg=0x8C):
"""Reads visible+IR diode from the I2C device"""
return self.readWord(reg);

def readIR(self, reg=0x8E):

"""Reads IR only diode from the I2C device"""
return self.readWord(reg);

def getLux(self, gain = 0):

"""Grabs a lux reading either with autoranging (gain=0) or with a specified
gain (1, 16)"""
if (gain == 1 or gain == 16):
self.setGain(gain)
ambient = self.readFull()
IR = self.readIR()
elif (gain==0):
self.setGain(16)

36 | P a g e
 ambient = self.readFull()
if (ambient < 65535):
IR = self.readIR()
if (ambient >= 65535 or IR >= 65535):
self.setGain(1)
ambient = self.readFull()
IR = self.readIR()

if(self.gain==1):
ambient *= 16
IR *= 16

if (IR <= 0):

print"LIGHT ON"
GPIO.output(25,GPIO.HIGH)
elif (IR >= 16):
GPIO.output(25,GPIO.LOW)

ratio = (IR / float(ambient))

if (self.debug):
print "IR Result", IR
print "Ambient Result", ambient
if ((ratio >= 0) & (ratio <= 0.52)):
lux = (0.0315 * ambient) - (0.0593 * ambient * (ratio**1.4))
elif (ratio <= 0.65):
lux = (0.0229 * ambient) - (0.0291 * IR)
elif (ratio <= 0.80):
lux = (0.0157 * ambient) - (0.018 * IR)
elif (ratio <= 1.3):
lux = (0.00338 * ambient) - (0.0026 * IR)
elif (ratio > 1.3):
lux = 0

return lux

# main() function
def main():
# use sys.argv if needed
if len(sys.argv) < 2:
print('Usage: python tstest.py PRIVATE_KEY')
exit(0)
print 'starting...'

baseURL = 'https://api.thingspeak.com/update?api_key=%s' % sys.argv[1]

37 | P a g e
 while True:
try:
oLuxmeter = Luxmeter()
print "Light intensity in term of LUX ", oLuxmeter.getLux(1)
print
"====================================================================="

f = urllib2.urlopen(baseURL +
"&field1=%s" % (oLuxmeter.getLux(1)))

print f.read()
f.close()
sleep(2)
except:
print 'exiting.'
break
# call main
if __name__ == '__main__':
main()
9. Save the code as ‘light_thingspeak.py’.
10. Use Adafruit Library to make this code run.
11. Now run the following command at the terminal to execute the code:
sudo python light_thingspeak.py ‘writeAPI Key’.
Here, mention the write API key of your thingspeak channel at the place ’writeAPIKey’.
5. Result:

The Light intensity data will be visible at your ThingSpeak channel.

38 | P a g e
 Experiment 9

Objective:To read Light Sensordata from ThingSpeak channel and store it into database through
Raspberry pi2.

Software Requirement: Python, Web browser, SQL Server.

Hardware Requirement: Raspberry Pi 2, Light sensor (TSL), connectors, sensor connecting wires,
computer system.

Procedure:

1. Setting up MySQL on a Raspberry pi:

Install the MySQL Server by using the following command:

sudo apt-get install mysql-server python-mysqldb

2. Creating Database:
1. Use MySQL shell to create database.
2. Use the following commands to create the database:

$ mysql -u root -p
Enter password:
mysql> create database database_name;
mysql> use database_name;

3. Creating Table:

39 | P a g e
 Use the following create table command for table creation:
mysql> create table table_name (column_namecolumn_type,
column_namecolumn_type, ……);

4. Python code:
Use the following code to read the data from ThingSpeak channel and store the values
to data base:
import urllib2,json
import MySQLdb

READ_API_KEY='1WWH97J6W6BFF7F6'
CHANNEL_ID=305731
def main():
while True:
conn =
urllib2.urlopen("http://api.thingspeak.com/channels/%s/feeds/last.json?api_key=%s" \
% (CHANNEL_ID,READ_API_KEY))
response = conn.read()
print "http status code=%s" % (conn.getcode())
data=json.loads(response)

db=MySQLdb.connect("localhost","root","root","sensor")
curs=db.cursor()

Light_intensity= data['field1']
Date_time= data['created_at']
print Light_intensity
print Date_time

curs.execute("insert into lightsensor (Light_intensity,Date_time)

values('"+str(Light_intensity)+"','"+str(Date_time)+"')")

db.commit()
conn.close()
if __name__ == '__main__':
main()

40 | P a g e