Why can't my code run this code onto python?

Question

I am running this code on Python 3(IDLE) on my raspberry pi 3 whith the latest raspbian software. With this code I am trying to obtain temperature data through ds18b20 sensor and sending that same data towards the mysql database I created.

Help would be very much appreciated!

All that is getting executed out of this code is :

Connected to MySQL database... MySQL Server version on  5.5.5-10.1.37-MariaDB-0+deb9u1
Your connected to -  ('temp_pi',)

Which is saying that the connection has been established between the mysql database. But the goal of this code is to obtain temperature data through the ds18b20 sensor and send that same data into the TAB_CLASSROOM table.

import os
import glob
import time
import MySQLdb
import datetime
import mysql.connector
from mysql.connector import Error

i = datetime.datetime.now()

# Establish the connection to the mysql database.
try:
    connection = mysql.connector.connect(host='127.0.0.1',
                             database='temp_pi',
                             user='root',
                             password='test')

    if connection.is_connected():
       db_Info = connection.get_server_info()
       print("Connected to MySQL database... MySQL Server version on ",db_Info)
       cursor = connection.cursor()
       cursor.execute("select database();")
       record = cursor.fetchone()
       print ("Your connected to - ", record)

except Error as e :
    print ("Error while connecting to MySQL", e)

# Obtain the temperature data through the ds18b20 sensor.           
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'

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  

    # Send the temperature data into a specific table entitled TAB_CLASSROOM.   
    while True:
            print("recording data into database(period = 5s.)....press ctrl+Z to stop!")

            valT = str(read_temp())

            year = str(i.year)
            month = str(i.month)
            day = str(i.day)
            date = day + "-" + month + "-" + year

            hour = str(i.hour)
            minute = str(i.minute)
            second = str(i.second)
            timestr = hour + ":" + minute + ":" + second

            try:
                cur.execute("""INSERT INTO TAB_CLASSROOM(temp_c,T_Date,T_Time) VALUES(%s,%s,%s)""",(valT,date,timestr))
                db.commit()
            except:
                db.rollback()

            time.sleep(10)

    cur.close()  
    db.close()

what error are you getting? What isn't working as expected? You can remove the `phpmyadmin` tag too, doesn't seem relevent to question. — danblack, Jan 10 '19 at 02:09
I'm not getting any error. If you read my description, I had specified what happens when I run this code. Only a part of it runs, the rest does not get executed and I don't know why. I am trying to send temperature data over to a mysql database. @danblack — Miguel Alvarez, Jan 10 '19 at 02:12
And the phpmyadmin is relevant since I insert all this data into a phpmyadmin server. @danblack — Miguel Alvarez, Jan 10 '19 at 02:13
it looks like you're inserting with with python rather than phpmyadmin. If you `SELECT * FROM TAB_CLASSROOM` are you seeing recent data? Try not catching the exception. (and you shouldn't need `db.commit()`). Recommend using a column `creation_time DATETIME DEFAULT CURRENT_TIMESTAMP` and then you don't need to calculate time/date in python. Use [generated columns](https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html) I you want the separate date and time. — danblack, Jan 10 '19 at 02:21

danblack · Accepted Answer · 2019-01-10T02:27:24.717

0

You haven't called read_temp function, you code only defines it.

The while True is indented which means its forming part of the read_temp function. Perhaps you want to remove the indenting from there down to the end.

That way the while True: is executed.

edited Jan 10 '19 at 02:27

answered Jan 10 '19 at 02:22

danblack

12,130
2
22
41

Where and how exactly would I call that function? This is the part I get confused. Sorry I am kind of new to programming with mysql. @danblack – Miguel Alvarez Jan 10 '19 at 02:25
`Traceback (most recent call last): File "/home/pi/Desktop/mysqlfinal1test.py", line 69, in cur.execute("""INSERT INTO TAB_CLASSROOM(temp_c,T_Date,T_Time) VALUES(%s,%s,%s)""",(valT,date,time)) NameError: name 'cur' is not defined During handling of the above exception, another exception occurred: Traceback (most recent call last): File "/home/pi/Desktop/mysqlfinal1test.py", line 72, in db.rollback() NameError: name 'db' is not defined` – Miguel Alvarez Jan 10 '19 at 02:35
thanks for the help! You are helping me get very close to making this code work! – Miguel Alvarez Jan 10 '19 at 02:36
`cur` is mean to be `cursor` which you've created earlier. `db` also isn't defined and you probably mean `connection` – danblack Jan 10 '19 at 02:38
I forgot define cur and db! Now it works!! Thank you so much you are an absolute legend!!! @danblack – Miguel Alvarez Jan 10 '19 at 02:39
FYI - good practice is to use [this form](https://stackoverflow.com/questions/10950362/protecting-against-sql-injection-in-python) for preventing SQL injection. It may not be necessary here however its good practice. Also notice your time is the same for every insert. – danblack Jan 10 '19 at 02:47
The time I am obtaining is always the same when I check every ID in my table... How can this be fixed? – Miguel Alvarez Jan 10 '19 at 03:53
There's limits to the amount of questions for a single codebase. Please ask a new question if another comes up. I suggested before 'creation_time DATETIME DEFAULT CURRENT_TIMESTAMP' as the column definition for the time and then you don't need to insert its value, you can leave it as the default when you insert and it will obtain the current timestamp. – danblack Jan 10 '19 at 04:03

Why can't my code run this code onto python?

1 Answers1