mistergrumpy
Posts: 37
Joined: Fri Dec 16, 2016 7:37 pm

Database and Mysql

Sun Apr 23, 2017 10:50 am

Hello. Moving on from my rpi house thermostat in which I had an early version running, I've decided to try the latest version as below which includes Mysql. I don't truly know what Mysql is/does in laymans terms however I have managed to get the thing running after doing lots of research.
My problem is this. The script gives the house room temperature and then a set temperature of 19.1 degrees which I can see it takes from line 74 in the loop. I'm not able to send a gmail though and have it read by the script to change the 19.1 temperature.
I gather it either takes my gmail details from the db_chris.py file or else from a db_chris database in the mysql but I don't know which.
I have attached what's in my db_chris.py file under the main code if that helps. There's nothing in the Mysql database at the minute as I'm still working out how to add things to it.

Code: Select all

#imports for thermometer reading
import os
import glob
import time
import datetime
#imports for gmail reading
import imaplib
import email
from db_chris import *     # reads the database and gmail information from db_chris.py
import MySQLdb

# wiringpi numbers  
import wiringpi
wiringpi.wiringPiSetup()
wiringpi.pinMode(0, 1) # sets WP pin 0 to output 

#Find temperature from thermometer
os.system('modprobe w1-gpio')
os.system('modprobe w1-therm')

base_dir = '/sys/bus/w1/devices/'
device_folder = glob.glob(base_dir + '28*')[0]
device_file = device_folder + '/w1_slave'

dbConnection = MySQLdb.connect(host=dbHost, user=dbUser, passwd= dbPass, db=dbName)

def reconnect():
    dbConnection = MySQLdb.connect(host=dbHost, user=dbUser, passwd= dbPass, db=dbName)

def selectTemperture():
    nowTime = datetime.datetime.now().strftime('%H:%M:%S')
    sqlq = "SELECT temp FROM setTemp WHERE startTime < '%s' ORDER BY startTime DESC LIMIT 1;" % nowTime
    cursor = dbConnection.cursor()
    cursor.execute(sqlq)
    targetTemp = ("%s" % cursor.fetchone())
    cursor.close()
    return float(targetTemp)

def updateDatabase(setTemperature, room1Temperature):
    sqlq = "INSERT INTO temperature(setPoint, room1) \
            VALUES ('" + setTemperature + "', '" + room1Temperature + "');"
    cursor = dbConnection.cursor()
    cursor.execute(sqlq)
    dbConnection.commit()
    cursor.close()

def read_temp_raw():
    f = open(device_file, 'r')
    lines = f.readlines()
    f.close()
    return lines

def read_temp():
    lines = read_temp_raw()
    while lines[0].strip()[-3:] != 'YES':
        time.sleep(0.2)
        lines = read_temp_raw()
    equals_pos = lines[1].find('t=')
    if equals_pos != -1:
        temp_string = lines[1][equals_pos+2:]
        temp_c = float(temp_string) / 1000.0
        temp_f = temp_c * 9.0 / 5.0 + 32.0
        return temp_c #, temp_f

loopi = 0
while True:
        loopi += 1
        room1_temp =read_temp()
        print "Max room"
        print room1_temp
        try:
            set_temp = selectTemperture()
        except:
            set_temp = 19.1
            reconnect()
        print "Set temp"
        print set_temp
        if (loopi > 6):
            loopi = 0
            try:
                updateDatabase(str(set_temp), str(room1_temp))
            except:
                pass
        if (set_temp  > room1_temp):#Compare varSubject to temp
            wiringpi.digitalWrite(0, 0) # sets port 0 to 0 (3.3V, off) inverted from original - this is how my boiler works.
            print "HEATING ON\n"
        # Allow for a 1 degree window
        elif (room1_temp > set_temp + 1):
            wiringpi.digitalWrite(0, 1) # sets port 0 to 1 (3.3V, on)
            print "HEATING OFF\n"
        time.sleep(5)

Code: Select all

dbHost="127.0.0.1"
dbUser="raspberrypi"
dbPass="XXXXX"
dbName="db_chris"

mistergrumpy
Posts: 37
Joined: Fri Dec 16, 2016 7:37 pm

Re: Database and Mysql

Mon Apr 24, 2017 10:09 am

Not sure that I explained that too well. I want to send an email and have it read and compared with the house room temperature but the script isn't picking up my emails. I've deleted the db_chris.py file that I had and it doesn't affect things. Where would I put my gmail address and password?

mistergrumpy
Posts: 37
Joined: Fri Dec 16, 2016 7:37 pm

Re: Database and Mysql

Tue Apr 25, 2017 10:23 am

Anyone? Please. I'm going mad.
It centres around

Code: Select all

from db_chris import *     # reads the database and gmail information from db_chris.py
I'm thinking I install MYsql connector then the db_chris.py can interact with the MYsql db_chris database?
What exactly would I write in the database?
Is it just a table with headings Gmail logon, Gmail password?

Thanks in advance. I know people are busy.

scotty101
Posts: 3598
Joined: Fri Jun 08, 2012 6:03 pm

Re: Database and Mysql

Tue Apr 25, 2017 10:35 am

Based on my own experience, SQLite is much easier to use than MySQL. I'd suggest you use that instead.
Can I suggest you try a few SQL tutorials first before trying to implement something blindly. I'd suggest https://www.tutorialspoint.com/sqlite/ and http://zetcode.com/db/sqlitepythontutorial/

This will give you the basics of what you can store in a database and how to run queries on it.

You are the person that needs to decide what you want to store in the database.
Settings, config and passwords are probably better suited to being stored in a configuration file or separate code file like what you have done so far.
If you want to record the temperature every 5 minutes and store this, then a database would be more suitable.
Last edited by scotty101 on Tue Apr 25, 2017 10:41 am, edited 1 time in total.
Electronic and Computer Engineer
Pi Interests: Home Automation, IOT, Python and Tkinter

scotty101
Posts: 3598
Joined: Fri Jun 08, 2012 6:03 pm

Re: Database and Mysql

Tue Apr 25, 2017 10:37 am

Additionally, with a recent Raspbian version that uses the device tree, you shouldn't need to use the following lines.

Code: Select all

#Find temperature from thermometer
os.system('modprobe w1-gpio')
os.system('modprobe w1-therm')
Electronic and Computer Engineer
Pi Interests: Home Automation, IOT, Python and Tkinter

mistergrumpy
Posts: 37
Joined: Fri Dec 16, 2016 7:37 pm

Re: Database and Mysql

Tue Apr 25, 2017 3:25 pm

I'll definitely have a look thanks. As you can probably tell, I have a habit of jumping in head first and starting from there but it's just easier for me to learn that way.

vijaydeveloper
Posts: 3
Joined: Tue Nov 20, 2018 9:20 am
Location: India
Contact: Website

Re: Database and Mysql

Wed Feb 13, 2019 6:45 am

scotty101 wrote:
Tue Apr 25, 2017 10:35 am
Based on my own experience, SQLite is much easier to use than MySQL. I'd suggest you use that instead.
Can I suggest you try a few SQL tutorials first before trying to implement something blindly. I'd suggest http://zetcode.com/db/sqlitepythontutorial/

This will give you the basics of what you can store in a database and how to run queries on it.

You are the person that needs to decide what you want to store in the database.
Settings, config and passwords are probably better suited to being stored in a configuration file or separate code file like what you have done so far.
If you want to record the temperature every 5 minutes and store this, then a database would be more suitable.
According to me, the same will follow but the user can find a lot of tutorials list from here and can select one of the best tutorials for learning.

Return to “Troubleshooting”