How to insert data from Python program to MySQL database at intervals

Question

I have created a temperature sensor (using DS18B20 temperature sensor) and wrote the python program to read and display the temperature every 10 seconds. It worked fine. Later, I modified the code to save the recordings of each 10 sec to a MySQL database. Now, the problem is that it records AND uploads the data to the databse for the first time it reads. Then, I get an error message. So basically, the program reads and uploads to the database for once and then quits after the error. Please tell me how to fix this! Thanks a lot!

Here is the code:

import os
import time
import MySQLdb
import datetime

i = datetime.datetime.now()

db = MySQLdb.connect(host = "localhost", user = "root", passwd = "bb9125ap", db = "PY1")
cur = db.cursor()

os.system('modprobe w1-gpio')
os.system('modprobe w1-therm')

temp_sensor = '/sys/bus/w1/devices/28-00042d6745ff/w1_slave'

def temp_raw():
    f = open(temp_sensor,'r')
    lines = f.readlines()
    f.close
    return lines

def read_temp():
    lines = temp_raw()
    while lines[0].strip()[-3:] != 'YES':
          time.sleep(0.2)
          lines = temp_raw()
    temp_output = lines[1].find('t=')
    if temp_output != -1:
          temp_string = lines[1].strip()[temp_output+2:]
          temp_c = float(temp_string) / 1000.0
          return temp_c

    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)
          time = hour + ":" + minute + ":" + second

          try:
             cur.execute("""INSERT INTO PY1.DUMP1(temp_c,rec_time,rec_date) VALUES(%s,%s,%s)""",(valT,time,date))
             db.commit()
          except:
             db.rollback()

          time.sleep(10)

     cur.close()
     db.close()

Program name is temp1.py and line 54 according to my editor is db.rollback() Here is the error message I get

Traceback (most recent call last): File "temp1.py" , line 54 , in time.sleep(10) Attribute:'str' object has no attribute 'sleep'

Traceback (most recent call last): File "temp1.py", line 54, in <module> time.sleep(10) AttributeError: 'str' object has no attribute 'sleep' — uknowho
– uknowho, Commented Apr 4, 2015 at 18:18
The above is the error message I get. The program however saves the first temperature recording along with date and time readings to the database, but after that it fails. And my apologies for not posting the error message!! — uknowho
– uknowho, Commented Apr 4, 2015 at 18:22

rockerBOO · Accepted Answer · 2015-04-04 18:45:17Z

1

You're overwriting your imported time with a local variable time, converting it to a string.

time = hour + ":" + minute + ":" + second

answered Apr 4, 2015 at 18:45

rockerBOO

3,0402 gold badges18 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

rockerBOO Over a year ago

Rename your time = to something like timestr =

uknowho Over a year ago

Thanks a lot! It worked perfectly!! :) However, there is something wrong in my code because all the newly updated values in the database are of the same time. I mean if I am giving a sleep of 10sec then the updated time values in the database should show the recorded data at intervals of 10sec. However, it records data for the same time and keeps repeating this!!

rockerBOO Over a year ago

You need to get the time when you make a reading the temp. You are getting it when the program starts.

Collectives™ on Stack Overflow

How to insert data from Python program to MySQL database at intervals

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related