Raspberry pi sensor input to sqlite3 database using python

Question

I'm using a DS18B20 temperature sensor and raspberry pi 3. I want to take the Date/Time and Temperature data and put it into a sql database (later to be published to a website hosted by the pi... Haven't got that far yet)

I found a python script to read the sensor data and it was working fine. I then added the SQL parts and while it looks like it works (and no errors come up...) it doesn't seem to be changing the database at all. I can't tell if it is actually changing the table (and I'm just not looking in the right place) or if it isn't (and where I went wrong)

import os                                               # import os module
import glob                                             # import glob module
import time                                             # import time module
import sqlite3

conn = sqlite3.connect('Temperature.db')
c = conn.cursor()

os.system('modprobe w1-gpio')                          # load one wire comm$
os.system('modprobe w1-therm')
base_dir = '/sys/bus/w1/devices/'                      # point to the addre$
device_folder = glob.glob(base_dir + '28*')[0]         # find device with a$
device_file = device_folder + '/w1_slave'              # store the details
def read_temp_raw():
   f = open(device_file, 'r')
   lines = f.readlines()                               # read the device de$
   f.close()
   return lines

def read_temp():
   lines = read_temp_raw()
   while lines[0].strip()[-3:] != 'YES':              # ignore first line
      time.sleep(0.2)
      lines = read_temp_raw()
   equals_pos = lines[1].find('t=')                   # find temperature i$
   if equals_pos != -1:
      temp_string = lines[1][equals_pos+2:]
      temp_c = float(temp_string) / 1000.0            # convert to Celsius
      return temp_c

while True:
   date= (time.strftime("%Y-%m-%d ") + time.strftime("%H:%M:%S")) 
   temp=(read_temp())                               
   c.execute("INSERT INTO readings (date,temp) VALUES(?,?)", (date,temp))

   conn.close()
   break

The sqlite3 database is called Temperature.db and only has the one table "readings"

I'm really new at this so any advice is greatly appreciated.

cmpgamer · Answer 1 · 2017-05-23T13:03:59.350

You need to commit your changes to the database whenever you use an Insert or Update statement. You just call commit() on your server connection.

Also, another issue is that you close your database connection. You don't want to close the database connection in the while loop since you will not be able to insert new entries into the database since you closed your connection to it. Instead, close the cursor connection and create a new one any time you need to do a new transaction.

while True:
   c = conn.cursor()
   date= (time.strftime("%Y-%m-%d ") + time.strftime("%H:%M:%S")) 
   temp=(read_temp())                               
   c.execute("INSERT INTO readings (date,temp) VALUES(?,?)", (date,temp))
   conn.commit()
   c.close()
   break

If you read the documentation here, the commit() function commits the current database transaction. When you call close() on your database connection, it doesn't commit any changes since the last transaction.

A better practice to use as well is to use a Try-Except clause that calls rollback() if there is a problem with the database.

conn = sqlite3.connect('test.db')
try:
    cur = conn.cursor()
    cur.execute(query, data)
    conn.commit()
except Exception as e:
    print(e)
    conn.rollback()
conn.close()

Raspberry pi sensor input to sqlite3 database using python

1 Answers1