{"id":120,"date":"2017-03-08T12:58:28","date_gmt":"2017-03-08T20:58:28","guid":{"rendered":"http:\/\/courses.haigarmen.com\/intd320\/?p=120"},"modified":"2018-03-05T10:39:54","modified_gmt":"2018-03-05T18:39:54","slug":"recording-data","status":"publish","type":"post","link":"https:\/\/courses.haigarmen.com\/intd320\/recording-data\/","title":{"rendered":"Recording Data"},"content":{"rendered":"

To record data from a sensor we will use a Python program that checks the sensor every minute and writes the temperature (with a time stamp) into our MySQL database. First we’ll need to install a package that will help us connect Python to MySQL:<\/p>\n

sudo apt-get install python3-mysqldb<\/p>\n

Database preparation<\/h4>\n

First we will set up our database table that will store our data.<\/p>\n

Using the phpMyAdmin web interface (http:\/\/localhost\/phpmyadmin<\/a>) that we set up, log on using the administrator (root) account and create a database called\u00a0\u2018measurements\u2019.<\/p>\n

\"\"<\/p>\n

Enter in the name of the table and the number of columns that we are going to use for our measured values. In the screenshot above we can see that the name of the table is \u2018temperature\u2019 (how imaginative) and the number of columns is \u20182\u2019.<\/p>\n

We will use two columns so that we can store a temperature reading and the time it was recorded.<\/p>\n

Once we click on \u2018Go\u2019 we are presented with a list of options to configure our table\u2019s columns. Don\u2019t be intimidated by the number of options that are presented, we are going to keep the process as simple as practical.<\/p>\n

For the first column we can enter the name of the \u2018Column\u2019 as \u2018dtg\u2019 (short for d<\/strong>ate t<\/strong>ime g<\/strong>roup) the \u2018Type\u2019 as \u2018TIMESTAMP\u2019 and the \u2018Default\u2019 value as \u2018CURRENT_TIMESTAMP\u2019. For the second column we will enter the name \u2018temperature\u2019 and the \u2018Type\u2019 is \u2018FLOAT\u2019 (we won\u2019t use a default value).<\/p>\n

\"\"<\/p>\n

\"\"
Configure the MySQL Table Columns<\/em><\/figcaption>
<\/figcaption><\/figure>\n

Scroll down a little and click on the \u2018Save\u2019 button and we\u2019re done.<\/p>\n

Why did we choose those particular settings for our table?<\/h5>\n

Our \u2018dtg\u2019 column needs to store a value of time that includes the date and the time, so either of the types \u2018TIMESTAMP\u2019 or \u2018DATETIME\u2019 would be suitable. Either of them stores the time in the format \u2018YYYY-MM-DD HH:MM:SS\u2019. The advantage of selecting TIMESTAMP in this case is that we can select the default value to be the current time which means that when we write our data to the table we only need to write the temperature value and the \u2018dtg\u2019 will be entered automatically for us. The disadvantage of using \u2018TIMESTAMP\u2019 is that it has a more limited range than DATETIME. TIMESTAMP can only have a range between \u20181970-01-01 00:00:01\u2019 to \u20182038-01-19 03:14:07\u2019.<\/p>\n

Our temperature readings are generated (by our sensor) as an integer value that needs to be divided by 1000 to show degrees Centigrade. We could therefore store the value as an integer. However when we were selecting the data or in later processing we would then need to do the math to convert it to the correct value. It could be argued (successfully) that this would be a more efficient solution in terms of the amount of space taken to support the data on the Pi. However, I have a preference for storing the values as they would be used later and as a result we need to use a numerical format that supports numbers with decimal places. There are a range of options for defining the ranges for decimal numbers, but FLOAT allows us to ignore the options (at the expense of efficiency) and rely on our recorded values being somewhere between -3.402823466E+38 and 3.402823466E+38 (if our temperature falls outside those extremes we are in trouble).<\/p>\n

Record the temperature values<\/h4>\n

The following code (which is based on the code that is part of the great temperature sensing tutorial on Adafruit<\/a>) is a Python script which allows us to check the temperature reading from the sensor approximately every 10 seconds and write it to our database.<\/p>\n

The full code can be found here(pi_temp.py<\/a>).<\/p>\n

#!\/usr\/bin\/python\r\n# -*- coding: utf-8 -*-\r\n\r\nimport os\r\nimport glob\r\nimport time\r\nimport MySQLdb as mdb\r\n\r\nos.system('modprobe w1-gpio')\r\nos.system('modprobe w1-therm')\r\n\r\nbase_dir = '\/sys\/bus\/w1\/devices\/'\r\ndevice_folder = glob.glob(base_dir + '28*')[0]\r\ndevice_file = device_folder + '\/w1_slave'\r\n\r\ndef read_temp_raw():\r\nf = open(device_file, 'r')\r\nlines = f.readlines()\r\nf.close()\r\nreturn lines\r\n\r\ndef read_temp():\r\nlines = read_temp_raw()\r\nwhile lines[0].strip()[-3:] != 'YES':\r\ntime.sleep(0.2)\r\nlines = read_temp_raw()\r\nequals_pos = lines[1].find('t=')\r\nif equals_pos != -1:\r\ntemp_string = lines[1][equals_pos+2:]\r\ntemp_c = float(temp_string) \/ 1000.0\r\nreturn temp_c\r\n\r\nwhile True:\r\n\r\ntry:\r\npi_temp = read_temp()\r\ncon = mdb.connect('localhost', \\\r\n'pi_insert', \\\r\n'xxxxxxxxxx', \\\r\n'measurements');\r\ncur = con.cursor()\r\ncur.execute(\"\"\"INSERT INTO temperature(temperature) \\\r\nVALUES(%s)\"\"\", (pi_temp))\r\ncon.commit()\r\n\r\nexcept mdb.Error, e:\r\ncon.rollback()\r\nprint \"Error %d: %s\" % (e.args[0],e.args[1])\r\nsys.exit(1)\r\n\r\nfinally:\r\nif con:\r\ncon.close()\r\n\r\ntime.sleep(10)\r\n<\/pre>\n
Additionally, if you’d like to use the Grove Pi, I’ve altered the grove_sound_sensor.py script<\/a>\u00a0to store the data values to our Measurements database.<\/p>\n
#!\/usr\/bin\/env python\r\nimport time\r\nimport grovepi\r\nimport MySQLdb as mdb\r\n\r\n# Connect the Grove Sound Sensor to analog port A0\r\nsound_sensor = 0\r\n\r\n# Connect the Grove LED to digital port D5\r\nled = 5\r\n\r\ngrovepi.pinMode(sound_sensor,\"INPUT\")\r\ngrovepi.pinMode(led,\"OUTPUT\")\r\n\r\ndef read_sensor():\r\n        # Read the sound level\r\n        sensor_value = grovepi.analogRead(sound_sensor)\r\n        print(\"sensor_value = %d\" %sensor_value)\r\n        return sensor_value\r\n\r\nwhile True:\r\n\r\n    try:\r\n        sensor_value = read_sensor()\r\n        connection = mdb.connect('localhost',\r\n                          'root',\r\n                          'yourPassword',\r\n                          'measurements');\r\n        cursor = connection.cursor()\r\n        cursor.execute(\"\"\"INSERT INTO temperature(temperature)\r\n                       VALUES(%s)\"\"\", (sensor_value))\r\n        connection.commit()\r\n\r\n    except mdb.Error, e:\r\n        connection.rollback()\r\n        print \"Error %d: %s\" % (e.args[0],e.args[1])\r\n        sys.exit(1)\r\n\r\n    finally:\r\n        if connection:\r\n            connection.close()\r\n\r\n\ttime.sleep(10)<\/pre>\n","protected":false},"excerpt":{"rendered":"
To record data from a sensor we will use a Python program that checks the sensor every minute and writes the temperature (with a time stamp) into our MySQL database. First we’ll need to install a package that will help us connect Python to MySQL: sudo apt-get install python3-mysqldb Database preparation First we will set […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-120","post","type-post","status-publish","format-standard","hentry","category-tutorials"],"_links":{"self":[{"href":"https:\/\/courses.haigarmen.com\/intd320\/wp-json\/wp\/v2\/posts\/120","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/courses.haigarmen.com\/intd320\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/courses.haigarmen.com\/intd320\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/courses.haigarmen.com\/intd320\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/courses.haigarmen.com\/intd320\/wp-json\/wp\/v2\/comments?post=120"}],"version-history":[{"count":3,"href":"https:\/\/courses.haigarmen.com\/intd320\/wp-json\/wp\/v2\/posts\/120\/revisions"}],"predecessor-version":[{"id":232,"href":"https:\/\/courses.haigarmen.com\/intd320\/wp-json\/wp\/v2\/posts\/120\/revisions\/232"}],"wp:attachment":[{"href":"https:\/\/courses.haigarmen.com\/intd320\/wp-json\/wp\/v2\/media?parent=120"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/courses.haigarmen.com\/intd320\/wp-json\/wp\/v2\/categories?post=120"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/courses.haigarmen.com\/intd320\/wp-json\/wp\/v2\/tags?post=120"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}