One Wire Temperature Sensor - DS18B20 to MySQL/MariaDB Database

February 15, 2018

If you followed instructions on how to connect One Wire DS18B20 to Raspberry pi gpio then next step would be how to take temperature readings from DS18B20 One Wire digital temperature sensor and store these readings to MySQL/MariaDB database. As you may know that each DS18B20 One Wire digital temperature sensor has a unique 64-bit serial number wonderful part that we can have more then one DS18B20 One Wire digital temperature sensor connected to same 1-Wire bus.

Check out PiHome Shop for all you need for your Smart Heating and Wireless Temperature Sensors.

Wireless DS18B20 Room Temperature Sensor (Battery Powered)
€20.00 Add to basket

"<yoastmark

MySQL/MariaDB Table Structure

In order to record temperature from DS18B20 1-Wire digital temperature sensor to MySQL/MariaDB database we need database and then one table inside this database, Lets name our database pihome and then 2 x table,first one for records of all DS18B20 sensors list and second where we record all temperature readings. You can create blank database and then run following MySQL script to crate create both tables in pihome database. All table columns are self explained.

Nodes Table for DS18B20 Sensors

CREATE TABLE `nodes` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`node_id` CHAR(50) NOT NULL COLLATE 'utf16_bin',
	`child_id_1` INT(11) NULL DEFAULT NULL,
	`child_id_2` INT(11) NULL DEFAULT NULL,
	`child_id_3` INT(11) NULL DEFAULT NULL,
	`child_id_4` INT(11) NULL DEFAULT NULL,
	`child_id_5` INT(11) NULL DEFAULT NULL,
	`child_id_6` INT(11) NULL DEFAULT NULL,
	`child_id_7` INT(11) NULL DEFAULT NULL,
	`child_id_8` INT(11) NULL DEFAULT NULL,
	`name` CHAR(50) NULL DEFAULT NULL COLLATE 'utf8_bin',
	`last_seen` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
	`status` CHAR(50) NULL DEFAULT NULL COLLATE 'utf8_bin',
	`ms_version` CHAR(50) NULL DEFAULT NULL COLLATE 'utf16_bin',
	`sketch_version` CHAR(50) NULL DEFAULT NULL COLLATE 'utf16_bin',
	PRIMARY KEY (`id`)
)
COLLATE='utf16_bin'
ENGINE=InnoDB
AUTO_INCREMENT=1
;

CREATE TABLE `nodes` (

`id` INT(11) NOT NULL AUTO_INCREMENT,

`node_id` CHAR(50) NOT NULL COLLATE 'utf16_bin',

`child_id_1` INT(11) NULL DEFAULT NULL,

`child_id_2` INT(11) NULL DEFAULT NULL,

`child_id_3` INT(11) NULL DEFAULT NULL,

`child_id_4` INT(11) NULL DEFAULT NULL,

`child_id_5` INT(11) NULL DEFAULT NULL,

`child_id_6` INT(11) NULL DEFAULT NULL,

`child_id_7` INT(11) NULL DEFAULT NULL,

`child_id_8` INT(11) NULL DEFAULT NULL,

`name` CHAR(50) NULL DEFAULT NULL COLLATE 'utf8_bin',

`last_seen` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

`status` CHAR(50) NULL DEFAULT NULL COLLATE 'utf8_bin',

`ms_version` CHAR(50) NULL DEFAULT NULL COLLATE 'utf16_bin',

`sketch_version` CHAR(50) NULL DEFAULT NULL COLLATE 'utf16_bin',

PRIMARY KEY (`id`)

)

COLLATE='utf16_bin'

ENGINE=InnoDB

AUTO_INCREMENT=1

;

Message In Table for Temperature Readings

CREATE TABLE `messages_in` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`node_id` TINYINT(4) NULL DEFAULT NULL,
	`child_id` TINYINT(4) NULL DEFAULT NULL,
	`sub_type` INT(11) NULL DEFAULT NULL,
	`payload` DECIMAL(10,2) NULL DEFAULT NULL,
	`datetime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`)
)
COLLATE='utf16_bin'
ENGINE=InnoDB
AUTO_INCREMENT=1;

CREATE TABLE `messages_in` (

`id` INT(11) NOT NULL AUTO_INCREMENT,

`node_id` TINYINT(4) NULL DEFAULT NULL,

`child_id` TINYINT(4) NULL DEFAULT NULL,

`sub_type` INT(11) NULL DEFAULT NULL,

`payload` DECIMAL(10,2) NULL DEFAULT NULL,

`datetime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY (`id`)

)

COLLATE='utf16_bin'

ENGINE=InnoDB

AUTO_INCREMENT=1;

Python Script for DS18B20 1-Wire Sensor

Once MySQL/MariaDB database and table is ready we can simple create python that can take reading and store these readings to temp_reading table. You can create cron job for this python script to run every 5 minuts or any frequency you like just keep in mind that minimum limit for cron job is 1 minute. if you want to run anything less then one minute then you can create loop and let it run.

#!/usr/bin/python
import time, os, fnmatch, MySQLdb as mdb, logging
from decimal import Decimal
class bc:
	hed = '\033[0;36;40m'
	dtm = '\033[0;36;40m'
	ENDC = '\033[0m'
	SUB = '\033[3;30;45m'
	WARN = '\033[0;31;40m'
	grn = '\033[0;32;40m'
	wht = '\033[0;37;40m'
print bc.hed + " "	
print "  _____    _   _    _                            "
print " |  __ \  (_) | |  | |                           "
print " | |__) |  _  | |__| |   ___    _ __ ___     ___ "
print " |  ___/  | | |  __  |  / _ \  | |_  \_ \   / _ \ "
print " | |      | | | |  | | | (_) | | | | | | | |  __/"
print " |_|      |_| |_|  |_|  \___/  |_| |_| |_|  \___|"
print " "
print "    "+bc.SUB + "S M A R T   H E A T I N G   C O N T R O L "+ bc.ENDC
print bc.WARN +" "
print "***********************************************************"
print "*   PiHome DS18B20 Temperature Sensors Data to MySQL DB   *"
print "* Use this script if you have DS18B20 Temperature sensors *"
print "* Connected directly on Raspberry Pi GPIO.                *"
print "*                                  Build Date: 14/02/2018 *"
print "*                                    Have Fun - PiHome.eu *"
print "***********************************************************"
print " " + bc.ENDC
logging.basicConfig(filename='/var/www/cron/logs/DS18B20_error.log', level=logging.DEBUG, format='%(asctime)s %(levelname)s %(name)s %(message)s')
logger=logging.getLogger(__name__)

#Add in the w1_gpio and w1_therm modules
os.system('modprobe w1-gpio')
os.system('modprobe w1-therm')

#Database Settings Variables 
dbhost = 'localhost'
dbuser = 'root'
dbpass = 'passw0rd'
dbname = 'pihome'

print bc.dtm + time.ctime() + bc.ENDC + ' - DS18B20 Temperature Sensors Script Started'
print "-" * 68

#Function for Storing DS18B20 Temperature Readings into MySQL
def insertDB(IDs, temperature):
	try:
		con = mdb.connect(dbhost, dbuser, dbpass, dbname);
		cur = con.cursor()
		for i in range(0,len(temperature)):
			#Check if Sensors Already Exit in Nodes Table, if no then add Sensors into Nodes Table otherwise just update Temperature Readings. 
			cur.execute('SELECT COUNT(*) FROM `nodes` where node_id = (%s)', (IDs[i]))
			row = cur.fetchone()
			row = int(row[0])
			if (row == 0):
				print bc.dtm + time.ctime() + bc.ENDC + ' - New DS18B20 Sensors Discovered' + bc.grn, IDs[i], bc.ENDC 
				cur.execute('INSERT INTO nodes (node_id, child_id_1, name, last_seen, ms_version) VALUES(%s,%s,%s,%s,%s)', (IDs[i], '0', 'Temperature Sensor', time.strftime("%Y-%m-%d %H:%M:%S"), '0'))
				con.commit()
			#If DS18B20 Sensor record exist: Update Nodes Table with Last seen status. 
			if (row == 1):
				cur.execute('UPDATE `nodes` SET `last_seen`=now() WHERE node_id = %s', [IDs[i]])
				con.commit()
			print bc.dtm + time.ctime() + bc.ENDC + ' - Sensors ID' + bc.grn, IDs[i], bc.ENDC + 'Temperature' + bc.grn, temperature[i], bc.ENDC
			cur.execute('INSERT INTO messages_in(node_id, child_id, sub_type, payload, datetime) VALUES(%s, %s, %s, %s, %s)', (IDs[i], '0', '0', round(temperature[i],2), time.strftime("%Y-%m-%d %H:%M:%S")))
			con.commit()
		con.close()
	except mdb.Error, e:
		logger.error(e)
		print bc.dtm + time.ctime() + bc.ENDC + ' - DB Connection Closed: %s' % e

#Read DS18B20 Sensors and Save Them to MySQL
while True:
	temperature = []
	IDs = []
	for filename in os.listdir("/sys/bus/w1/devices"):
		if fnmatch.fnmatch(filename, '28-*'):
			with open("/sys/bus/w1/devices/" + filename + "/w1_slave") as fileobj:
				lines = fileobj.readlines()
				#print lines
				if lines[0].find("YES"):
					pok = lines[1].find('=')
					temperature.append(float(lines[1][pok+1:pok+6])/1000)
					IDs.append(filename)
				else:
					logger.error("Error reading sensor with ID: %s" % (filename))
	if (len(temperature)>0):
		insertDB(IDs, temperature)
		#time.sleep(300)
		break

#!/usr/bin/python

import time, os, fnmatch, MySQLdb as mdb, logging

from decimal import Decimal

class bc:

hed = '\033[0;36;40m'

dtm = '\033[0;36;40m'

ENDC = '\033[0m'

SUB = '\033[3;30;45m'

WARN = '\033[0;31;40m'

grn = '\033[0;32;40m'

wht = '\033[0;37;40m'

print bc.hed + " "

print " _____ _ _ _ "

print " | __ \ (_) | | | | "

print " | |__) | _ | |__| | ___ _ __ ___ ___ "

print " | ___/ | | | __ | / _ \ | |_ \_ \ / _ \ "

print " | | | | | | | | | (_) | | | | | | | | __/"

print " |_| |_| |_| |_| \___/ |_| |_| |_| \___|"

print " "

print " "+bc.SUB + "S M A R T H E A T I N G C O N T R O L "+ bc.ENDC

print bc.WARN +" "

print "***********************************************************"

print "* PiHome DS18B20 Temperature Sensors Data to MySQL DB *"

print "* Use this script if you have DS18B20 Temperature sensors *"

print "* Connected directly on Raspberry Pi GPIO. *"

print "* Build Date: 14/02/2018 *"

print "* Have Fun - PiHome.eu *"

print "***********************************************************"

print " " + bc.ENDC

logging.basicConfig(filename='/var/www/cron/logs/DS18B20_error.log', level=logging.DEBUG, format='%(asctime)s %(levelname)s %(name)s %(message)s')

logger=logging.getLogger(__name__)

#Add in the w1_gpio and w1_therm modules

os.system('modprobe w1-gpio')

os.system('modprobe w1-therm')

#Database Settings Variables

dbhost = 'localhost'

dbuser = 'root'

dbpass = 'passw0rd'

dbname = 'pihome'

print bc.dtm + time.ctime() + bc.ENDC + ' - DS18B20 Temperature Sensors Script Started'

print "-" * 68

#Function for Storing DS18B20 Temperature Readings into MySQL

def insertDB(IDs, temperature):

try:

con = mdb.connect(dbhost, dbuser, dbpass, dbname);

cur = con.cursor()

for i in range(0,len(temperature)):

#Check if Sensors Already Exit in Nodes Table, if no then add Sensors into Nodes Table otherwise just update Temperature Readings.

cur.execute('SELECT COUNT(*) FROM `nodes` where node_id = (%s)', (IDs[i]))

row = cur.fetchone()

row = int(row[0])

if (row == 0):

print bc.dtm + time.ctime() + bc.ENDC + ' - New DS18B20 Sensors Discovered' + bc.grn, IDs[i], bc.ENDC

cur.execute('INSERT INTO nodes (node_id, child_id_1, name, last_seen, ms_version) VALUES(%s,%s,%s,%s,%s)', (IDs[i], '0', 'Temperature Sensor', time.strftime("%Y-%m-%d %H:%M:%S"), '0'))

con.commit()

#If DS18B20 Sensor record exist: Update Nodes Table with Last seen status.

if (row == 1):

cur.execute('UPDATE `nodes` SET `last_seen`=now() WHERE node_id = %s', [IDs[i]])

con.commit()

print bc.dtm + time.ctime() + bc.ENDC + ' - Sensors ID' + bc.grn, IDs[i], bc.ENDC + 'Temperature' + bc.grn, temperature[i], bc.ENDC

cur.execute('INSERT INTO messages_in(node_id, child_id, sub_type, payload, datetime) VALUES(%s, %s, %s, %s, %s)', (IDs[i], '0', '0', round(temperature[i],2), time.strftime("%Y-%m-%d %H:%M:%S")))

con.commit()

con.close()

except mdb.Error, e:

logger.error(e)

print bc.dtm + time.ctime() + bc.ENDC + ' - DB Connection Closed: %s' % e

#Read DS18B20 Sensors and Save Them to MySQL

while True:

temperature = []

IDs = []

for filename in os.listdir("/sys/bus/w1/devices"):

if fnmatch.fnmatch(filename, '28-*'):

with open("/sys/bus/w1/devices/" + filename + "/w1_slave") as fileobj:

lines = fileobj.readlines()

#print lines

if lines[0].find("YES"):

pok = lines[1].find('=')

temperature.append(float(lines[1][pok+1:pok+6])/1000)

IDs.append(filename)

else:

logger.error("Error reading sensor with ID: %s" % (filename))

if (len(temperature)>0):

insertDB(IDs, temperature)

#time.sleep(300)

break

Ref: if you want to read more about DS18B20 1-Wire Sensor then you can check out this datasheet.

Wireless DS18B20 Hot Water Temperature Sensor (Battery Powered)
€20.00 Add to basket

46 comments

Log in to Reply

Garey

February 23, 2018 at 4:05 pm

Hi,
you changed your python script, i had your old python script to save readings to one table, now you are saving data to two tables, can you please share previous version as well? why you are using two tables now? or can you give second table columns.
thank you.
Log in to Reply

Clumpton

March 22, 2018 at 9:08 am

Hi,
Just trying to set this up with a sensor on the Pi that is running PiHome.
Having problems because your nodes table has node_id as a CHAR where the version installed via git has node_id as an INT.
Which is correct? Do I have to go through all the tables and change node_id column to char?
TIA
- Log in to Reply
  
  Admin
  
  March 22, 2018 at 10:22 am
  
  Hi Clumpton,
  if you are not connecting DS18B20 1-Wire temperature sensor to your raspberry pi GPIO and using wireless temperature sensors then you can change node_id to INT in nodes table and rest is already INT.
  Admin
  - Log in to Reply
    
    Clumpton
    
    March 22, 2018 at 10:39 am
    
    Sorry, I explained badly. node-id is already INT – I am connecting DS18B20 1-Wire temperature sensor to my raspberry pi GPIO. Obviously it tries to insert a new sensor into the table with node_id 28 rather than the full address.
    - Log in to Reply
      
      Admin
      
      March 22, 2018 at 10:50 am
      
      @Clumpton
      i changed all required tables, can you download sql file from github and drop and create database and this should fix your problem, if you need help drop me email and can do remote session with you on teamviewer to install all this.
      Admin
      - Clumpton
        
        March 22, 2018 at 4:44 pm
        
        Yes, problem fixed! Thanks.
        However now the web interface “home” just shows the spinner and please wait message.
        Other pages work, but some are missing information (eg Zones).
        Maybe time for a teamviewer tomorrow please?
        What email should I contact you on?
      - Clumpton
        
        March 22, 2018 at 9:59 pm
        
        OK, Problem solved. I had to re-create the Views in the database. Looks to be working
      - Admin
        
        March 23, 2018 at 12:22 am
        
        @Clumpton glad to hear its working for you, dont forget to create con jobs.
        Admin
    - Log in to Reply
      
      Admin
      
      March 22, 2018 at 11:06 am
      
      one more thing, your zone/boiler relay connected to gpio as well? if they are! make sure you used wiringpi gpio numbers in boiler/zone table.
      Admin
Log in to Reply

raj vora

April 11, 2018 at 5:26 pm

Wed Apr 11 18:19:49 2018 – DS18B20 Temperature Sensors Script Started
——————————————————————–
Traceback (most recent call last):
File “pihome_sensor.py”, line 88, in
insertDB(IDs, temperature)
File “pihome_sensor.py”, line 53, in insertDB
cur.execute(‘SELECT COUNT(*) FROM nodes where node_id = (%s)’, (IDs[i]))
File “/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py”, line 210, in execute
query = query % args
TypeError: not all arguments converted during string formatting

pi@heatingprod:~ $ python
Python 2.7.13 (default, Nov 24 2017, 17:33:09)
[GCC 6.3.0 20170516] on linux2

i have 11 sensors connected to the pi, all are readable.
None have been populated into node table as yet (i was hoping the script would do it!)

I’m guessing a mismatch in versions somewhere? using python 2.7
- Log in to Reply
  
  raj vora
  
  April 11, 2018 at 5:56 pm
  
  figured it out – last few lines need to be:
  
  if (len(temperature)>0):
  print IDs
  insertDB(IDs[0], temperature)
  time.sleep(0.3)
  break
  
  insertDB needs the ID from the array, so need to return the first value.. I think! as my sensors are now not all being inserted
  
  i have 10, but 5 of them have the same sensorid of 0, two have 5, one is – and the other three have different values.
  - Log in to Reply
    
    Admin
    
    April 12, 2018 at 3:35 pm
    
    @raj vora
    my python version is slightly different: but i dont think this will make difference. you might have wrong datatype in your node table, i have char and length 50, this was changed from int to add support for DS18B20 Temperature Sensors.
    
    Node Table
    node_id CHAR(50) NOT NULL COLLATE 'utf16_bin',
    
    Python Version
    root@pihome:~# python Python 2.7.3 (default, Mar 18 2014, 05:13:23)
Log in to Reply

Jason

July 26, 2018 at 11:09 am

I have a house with hydronic heating system with no zones. The system features a Honeywell control panel (two wire, normally open). I have downloaded LAMP and attached a single channel relay and DS18B20 1-Wire temperature sensor to the same Raspberry Pi. I assume that I should delete all the (existing) zones and retain only the boiler? What is the best way to purge (delete) the existing temperature sensors from the system?
- Log in to Reply
  
  Admin
  
  July 27, 2018 at 10:18 am
  
  Hi Jason,
  you can click on settings and then click on zone and delete all Zone leaving on zone. let me know if you need more help.
  - Log in to Reply
    
    Jason
    
    July 27, 2018 at 12:07 pm
    
    Based on my understanding I will require one zone with the relay and temperature sensor to provide both time and temperature control. The boiler option is therefore largely redundant?
    Thanks again for your guidance and assistance.
    - Log in to Reply
      
      Admin
      
      July 27, 2018 at 1:42 pm
      
      Jason,
      Zone is considered area need heating and boiler is source of that heat. at minimum you need one zone and one heat source. do you have boiler ON all the time? if so you can leave boiler as it is but have one zone and relay connected for that zone to open/close heat valve with temperature sensors.
      just wathced this youtube video to understand hydronic heating system
      - Jason
        
        July 28, 2018 at 6:46 am
        
        Noted with thanks!
        
        I have a temperature sensor (“28-031701dc21ff”) wired to the Raspberry Pi. To verify the sensor is working, I cat the w1_slave file and it returns the temperature as expected. When I execute gpio_ds18b20.py I received the following error (I added “Current temperature” to ensure a value was being read from the file read):
        
        Sat Jul 28 16:36:54 2018 – DS18B20 Temperature Sensors Script Started
        ——————————————————————–
        Current temperature: 17.0
        Traceback (most recent call last):
        File “gpio_ds18b20.py”, line 89, in
        insertDB(IDs, temperature)
        File “gpio_ds18b20.py”, line 53, in insertDB
        cur.execute(‘SELECT COUNT(*) FROM nodes where node_id = (%s)’, (IDs[i]))
        File “/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py”, line 210, in execute
        query = query % args
        TypeError: not all arguments converted during string formatting
      - Jason
        
        July 28, 2018 at 10:30 am
        
        TypeError problem solved. I changed line 88 in gpio_ds18b20.py from:
        
        insertDB(IDs, temperature)
        
        to
        
        insertDB(IDs[0], temperature)
        
        Thanks Raj
Log in to Reply

Ian

March 1, 2020 at 6:03 pm

Hello Admin
I am using the latest SDcard image file and have updated everything from github.
I have setup my DS18b20’s and can see they are supplying and saving data to the “w1_slave” files under a folder “28-*” for each sensor.
I have run the file “setup_db.php” in MySQL_Database folder – no errors.
I have run the file “gpio_ds18b20.py” in Cron folder – there is no error report, but it does not insert the sensors nor values into the database and therefore I cannot setup my zones.
What am I missing here please ?
Many thanks again for your much appreciated help.
- Log in to Reply
  
  Admin
  
  March 1, 2020 at 10:57 pm
  
  Hi Ian,
  can you try to run python /var/www/cron/gpio_ds18b20.py and see if it gives you any clue or error.
  - Log in to Reply
    
    Ian
    
    March 2, 2020 at 11:27 am
    
    Hello Admin
    Here is the output ….
    
    Traceback (most recent call last):
    File “/var/www/cron/gpio_ds18b20.py”, line 41, in
    dbhost = config.get(‘db’, ‘hostname’)
    File “/usr/lib/python2.7/ConfigParser.py”, line 607, in get
    raise NoSectionError(section)
    ConfigParser.NoSectionError: No section: ‘db’
    
    Sorry but this message is way beyond my knowledge. Very grateful for any assistance.
    - Log in to Reply
      
      Ian
      
      March 2, 2020 at 12:38 pm
      
      Hello, Ian again.
      I figured out my problem – I had to run the python script from within the MySQL_Database folder – temp sensors now found and Zones can now be succesfully added – yay.
      Two more question though 1) How do I remove the pre-existing 3 zones from the database, is it OK to simply delete those lines from the database, and 2) how do I enable ongoing temperature readings, the initial readings after the python script are not being updated ?
      Many thanks … Ian
      - Admin
        
        March 2, 2020 at 12:43 pm
        
        Hi Ian,
        best to delete zone from settings->Zone and delete each zone. this way all relevant entries gets deleted.
  - Log in to Reply
    
    Petr
    
    March 2, 2020 at 12:27 pm
    
    Hi,
    I have the same problem. In version 1.70, temperature values were no longer written to the database. When I run python /var/www/cron/gpio_ds18b20.py, the values are written correctly. I tried to replace the gpio_ds18b20.py file with the file from version 1.68 and the values are being stored correctly. Is there a problem with database access (changes in version 1.70 and later) ????
    
    Thanks Petr
    - Log in to Reply
      
      Admin
      
      March 2, 2020 at 12:42 pm
      
      Hi Petr,
      database connection settings are saved in single file st_inc/db_config.ini
Log in to Reply

Ian

March 2, 2020 at 2:12 pm

Hello
re “best to delete zone from settings->Zone and delete each zone. this way all relevant entries gets deleted.”

The 3 original, in-built database zones do not show in the settings->Zone window, so they cannot be deleted that way.

I will start from scratch and see if I can delete those 3 in-built zones before I set-up the new ones.

Also – how do you get the temperatures to automaticaly update themselves?

Many thanks, I appreciate your patience.
- Log in to Reply
  
  Admin
  
  March 3, 2020 at 12:57 am
  
  Ian,
  there is cron job commented for this python script, you can uncomment it.
Log in to Reply

Ian

March 2, 2020 at 5:11 pm

UPDATE:

Running setup_db again removed the 2 heating zones but not the Hot Water zone so I deleted that from the database and ran setup_db again – that gave me a clean start to enter my own zones.

To get the temperature readings to update I inserted a change directory command into your gpio_ds18b20.py file and also put a copy of it in the MySQL_Database folder for good measure !! (my cron job via setup.php is also modified to point to that directory)….
#Change to Database directory
os.chdir(“/var/www/MySQL_Database”)

Progress ….
Log in to Reply

Ian

March 6, 2020 at 3:23 pm

Hello Admin
I now have my wired system up and running – and all thanks to you.
May I post my beginners guide to making this happen – here ?
Best wishes … Ian
- Log in to Reply
  
  Admin
  
  March 6, 2020 at 5:17 pm
  
  Hi Ian,
  you are very welcome to write full post, you can do in word document or in any text formate and i can post here for you. With your name being author of this guide (if you agree to do so)
  - Log in to Reply
    
    Ian
    
    March 6, 2020 at 7:02 pm
    
    OK I will produce a MS Word doc – should I send that to you and if yes then how?
Log in to Reply

Frank Lamprecht

April 4, 2020 at 2:24 pm

Hi all
when i execute the script the error messages shows:
not all arguments converted during string formatting
Is anyone to give me a tip whats is wrong?
- Log in to Reply
  
  Admin
  
  April 4, 2020 at 2:54 pm
  
  Hi Frank,
  can you send output please.
Log in to Reply

Kevin

May 14, 2020 at 8:10 am

I have a system with 3 wireless ds18b20 connected but also want to connect a wired ds18b20 to the pi
After initial problems I can now set up another zone using the wired ds18b20. I couldn’t see the new sensor so I enabled 1-wire in the pi configuration and then ran
sudo python /var/www/cron/gpio18b20.py (get errors if I don’t use sudo)
I now have a new sensor and can create a new zone. So everything is working except for the Cron. I checked /etc/crontab with crontab -e and there’s nothing in it.
I edited /etc/crontab and entered
*/1 * * * * python /var/www/cron/gpio_ds18b20.py >/dev/null 2>&1
It didn’t work so I changed it to
*/1 * * * * sudo python /var/www/cron/gpio_ds18b20.py >/dev/null 2>&1
It still doesn’t work
So I still don’t know what to do.
Any help would be gratefully received.
- Log in to Reply
  
  Admin
  
  May 14, 2020 at 1:28 pm
  
  Hi Kevin,
  PiHome Moved to python 3 and now RPi uses python2 by default, so unless you change the default, use following line in your cronjob. and let it run and report back the results.
  
  */1 * * * * python3 /var/www/cron/gpio_ds18b20.py >/dev/null 2>&1
Log in to Reply

Kevin

May 14, 2020 at 6:39 pm

Sorry, maybe I’m being stupid but I copied the line
*/1 * * * * python3 /var/www/cron/gpio_ds18b20.py >/dev/null 2>&1
and inserted it in
/etc/crontab but it didn’t work
/var/www/cron/’CronJobs List’ but it didn’t work
/var/www/cronjob.txt but it didn’t work
If I run
sudo python gpio18b20.py it works and outputs the temperature reading.
If I run
sudo python3 gpio18b20.py it fails withe errors about print.
I hope this helps
- Log in to Reply
  
  Admin
  
  May 14, 2020 at 7:02 pm
  
  to modify crontab you have to type command crontab -e as root user and if you are using PiHome img file for your raspberry pi this is already there for you you just need to un-comment it
  - Log in to Reply
    
    Kevin Agar
    
    May 15, 2020 at 6:45 am
    
    I’ve checked and there is nothing in crontab to un-comment. So I decided to download the latest image from the pihome site and reinstall everything. I can’t get the latest image to work. During boot up it gives errors about apache.
    If I go to raspi-config and try to change the wifi I get the error ‘can’t connect to wpa_supplicant’
    I tried as user pi and user root.
    I then reinstalled the image onto a different sd card and get the same errors.
    - Log in to Reply
      
      Kevin Agar
      
      May 15, 2020 at 7:28 am
      
      To check my installation method is ok, I reinstalled PiHome_173_31_03_2020 and everything is ok
      - Kevin Agar
        
        May 15, 2020 at 8:49 am
        
        Sorry to keep coming back but just thought I should keep you updated.
        Downloaded the PiHome Smart Heating Controller with All dependencies_v1.74.rar again
        Installed on a clean SD card
        Tried in 2 different Rpi zero W
        both gave errors whilst booting
        failed to start Avahi mDNS/DNS-SD Stack
        failed to start dhcpcd on all interfaces
        failed to start D-Bus System Message Bus
        failed to start WPA Supplicant
        failed to start The Apache HTTP Server
        failed to start Bluetooth service
        hope this helps
      - Admin
        
        May 15, 2020 at 10:08 am
        
        Thank you Kevin for update, i will try this img on my rpi zero and let you later during the day.
Log in to Reply

Kevin Agar

May 15, 2020 at 9:41 pm

Having spoken to Waseem, he supplied a new pihome image that installed correctly on my pi zero w.
I then realised that I was trying to edit the crontab as user pi. Changed to root with
su root
now it’s possible to un-comment the line
*/1 * * * * python /var/www/cron/gpio_ds18b20.py >/dev/null 2>&1
Having done that, it now sees and updates the sensor wired directly to the pi.
Thank you Waseem for solving my problem very quickly and helping to increase my knowledge of the system.
Log in to Reply

`Tom

September 21, 2021 at 7:08 pm

Hello,

I would like to ask if Ian has produced the guide, as he stated here:
Ian
March 6, 2020 at 7:02 pm

OK I will produce a MS Word doc – should I send that to you and if yes then how?

If so, can you share where to find it please
- Log in to Reply
  
  Admin
  
  September 21, 2021 at 11:13 pm
  
  Hi Tom,
  you can always update on github or you can join us on discord https://discord.gg/6n72P2VcV6

One Wire Temperature Sensor – DS18B20 to MySQL/MariaDB Database

Wireless DS18B20 Room Temperature Sensor (Battery Powered)

MySQL/MariaDB Table Structure

Nodes Table for DS18B20 Sensors

Message In Table for Temperature Readings

Python Script for DS18B20 1-Wire Sensor

Wireless DS18B20 Hot Water Temperature Sensor (Battery Powered)

46 comments

Leave a Reply Cancel reply