BarnyardBarnyard
Posts: 7
Joined: Wed Jan 08, 2020 8:28 pm

Trouble writing BME280 sensor data to mariadb

Fri Jan 24, 2020 7:05 pm

I've been working on the Raspberry Pi . orgs "build your own weather station" project and have decided that their guide for writing to a db is missing too much to cobble together a solution as a python beginner. I've now started googling around for what you need to write to mariadb and know that a connector is needed. I've added this, but now I'm having an issue with taking the python file that reads the sensor bme280_sensor and actually getting it to write to the db.

Code: Select all

import mysql.connector as mariadb
mariadb_connection = mariadb.connect(user='pi', password='password', database='weather')
cursor = mariadb_connection.cursor()
import bme280_sensor
import time
interval = 1
start_time = time.time()
while True:
      if time.time() - start_time >= interval:
        cursor.execute("INSERT INTO weather(AMBIENT_TEMPERATURE, AIR_PRESSURE, HUMIDITY),VALUES (%s, %s,%s))", (AMBIENT_TEMPERATURE, AIR_PRESSURE, HUMIDITY))

When I run the above code, I get this error: Traceback (most recent call last):
File "/home/pi/weather-station/PiForum Help weather_station_BYO.py", line 10, in <module>
cursor.execute("INSERT INTO weather(AMBIENT_TEMPERATURE, AIR_PRESSURE, HUMIDITY),VALUES (%s, %s,%s))", (AMBIENT_TEMPERATURE, AIR_PRESSURE, HUMIDITY))
NameError: name 'AMBIENT_TEMPERATURE' is not defined

Here is the python file that is supposedly reading the sensor in a way that can then be written to the db:

Code: Select all

import bme280
import smbus2
from time import sleep

port = 1
address = 0x77
bus = smbus2.SMBus(port)

bme280.load_calibration_params(bus,address)

def read_all():
    bme280_data = bme.sample(bus,address)
    return bme280_data.humidity, bme280_data.pressure, bme280_data.temperature



I'm hoping for a simple solution that will allow a continuous temperature measurement that writes to the mariadb that I've built out. My interests in python and raspberry pi are budding and I've got a python book on the way. This is just an early attempt for me to get something done to prove to myself that I can solution this out. I'm a little frustrated at this point but have one bit of code to be proud of... this reads the data:

Code: Select all

import bme280
import smbus2
from time import sleep

port = 1
address = 0x77
bus = smbus2.SMBus(port)

bme280.load_calibration_params(bus,address)

while True:
    bme280_data = bme280.sample(bus,address)
    humidity = bme280_data.humidity
    pressure = bme280_data.pressure
    ambient_temperature = bme280_data.temperature
    print(humidity, pressure, ambient_temperature)
    sleep(1)
    

ghp
Posts: 2077
Joined: Wed Jun 12, 2013 12:41 pm
Location: Stuttgart Germany
Contact: Website

Re: Trouble writing BME280 sensor data to mariadb

Fri Jan 24, 2020 9:12 pm

There is a document available on python and mariadb https://mariadb.com/resources/blog/how- ... o-mariadb/
Related to your code, change it like (untested)

Code: Select all

ambient_temperature_value = 43.2
air_pressure_value = 32.4
humidity_value = 24.3
c.execute("INSERT INTO weather(AMBIENT_TEMPERATURE, AIR_PRESSURE, HUMIDITY) VALUES (%s, %s, %s)", 
          (ambient_temperature_value, air_pressure_value, humidity_value))
The problem in your code are:
remove the ',' in INSERT INTO weather(the colums) >>>,<<< VALUES (the values)"
And you have to provide initialized variables to be inserted.

BarnyardBarnyard
Posts: 7
Joined: Wed Jan 08, 2020 8:28 pm

Re: Trouble writing BME280 sensor data to mariadb

Mon Jan 27, 2020 3:17 pm

Thanks for the tips. I'm getting hung up on referring my code to a different bit of python saved elsewhere, so I'm trying to merge the two. Feels a little more logical but now I get the following error:

Code: Select all

Traceback (most recent call last):
  File "/home/pi/weather-station/Pi forum v2.py", line 30, in <module>
    cursor.execute("INSERT INTO weather(AMBIENT_TEMPERATURE, AIR_PRESSURE, HUMIDITY) VALUES (%s,%s,%s);" (humidity, pressure, temperature))
TypeError: 'str' object is not callable
[/color][/b]

Here is the state of my code now:

Code: Select all

import bme280
import smbus2
from time import sleep

port = 1
address = 0x77
bus = smbus2.SMBus(port)

bme280.load_calibration_params(bus,address)

import mysql.connector as mariadb
mariadb_connection = mariadb.connect(user='pi', password='password', database='weather')
cursor = mariadb_connection.cursor()
import time
interval = 1

start_time = time.time()


bme280_data = bme280.sample(bus,address)
humidity = bme280_data.humidity
pressure = bme280_data.pressure
temperature = bme280_data.temperature
#print(humidity, pressure, ambient_temperature)
sleep(1)


while True:
      if time.time() - start_time >= interval:
          cursor.execute("INSERT INTO weather(AMBIENT_TEMPERATURE, AIR_PRESSURE, HUMIDITY) VALUES (%s,%s,%s);" (humidity, pressure, temperature))
      commit()

markkuk
Posts: 290
Joined: Thu Mar 22, 2018 1:02 pm
Location: Finland

Re: Trouble writing BME280 sensor data to mariadb

Mon Jan 27, 2020 7:03 pm

There's a comma missing between the query string and the parameters. The statement should be:

Code: Select all

cursor.execute("INSERT INTO weather(AMBIENT_TEMPERATURE, AIR_PRESSURE, HUMIDITY) VALUES (%s,%s,%s);", (humidity, pressure, temperature))

BarnyardBarnyard
Posts: 7
Joined: Wed Jan 08, 2020 8:28 pm

Re: Trouble writing BME280 sensor data to mariadb

Tue Jan 28, 2020 5:25 am

Awesome! Thanks for calling that out. This code "works" but I've got a couple lingering questions/issues.

1) my temperature readings are set to once an hour:
sleep(3600)
This is working, so why do I need:
interval = 5? This was a remnant from some bit of code I was referring to but seems makes no difference when present or commented out.

2) I've just pulled data from the db and see that my values over the last 4-5 hours are all the same. The time stamp is once an hour but values are the same. Does this code prevent a "fresh" sensor read? It appears I'm missing something?

3) my next steps are to clean this up and begin feeding data to wunderground. So pumped for this part. If you have an ideas/tips etc on this I'd love to hear them.

Thanks again for helping out. Much appreciated.

Code: Select all

import mysql.connector as mariadb
mariadb_connection = mariadb.connect(user='pi', password='password', database='weather')
cursor = mariadb_connection.cursor()
import time
interval = 5
start_time = time.time()
import bme280
import smbus2
from time import sleep

port = 1
address = 0x77
bus = smbus2.SMBus(port)

bme280.load_calibration_params(bus,address)
#!/usr/bin/python

bme280_data = bme280.sample(bus,address)
temperature = bme280_data.temperature
pressure = bme280_data.pressure
humidity = bme280_data.humidity
#print(humidity, pressure, ambient_temperature)


while True:
      if time.time() - start_time >= interval:
          cursor.execute("insert into WEATHER_MEASUREMENT1(GROUND_TEMPERATURE, AIR_PRESSURE, HUMIDITY) values (%s, %s, %s);",(temperature, pressure, humidity))
          sleep(3600)
          mariadb_connection.commit() 

markkuk
Posts: 290
Joined: Thu Mar 22, 2018 1:02 pm
Location: Finland

Re: Trouble writing BME280 sensor data to mariadb

Tue Jan 28, 2020 6:42 am

1. You don't need the interval variable. Looks like you copied some Arduino code, busy looping with time comparision isn't the right approach for RPi/Linux code.

2. You're reading the sensor values once at the start of your program, then writing the same values to the database once per hour. If you want new values, you need to read the sensor again inside yoour loop.

PS. the commit() call should be immediately after the cursor.execute() instead of after the sleep() call.

BarnyardBarnyard
Posts: 7
Joined: Wed Jan 08, 2020 8:28 pm

Re: Trouble writing BME280 sensor data to mariadb

Tue Jan 28, 2020 6:58 am

Thanks again for that. Last question then, how do I build that loop?

markkuk
Posts: 290
Joined: Thu Mar 22, 2018 1:02 pm
Location: Finland

Re: Trouble writing BME280 sensor data to mariadb

Tue Jan 28, 2020 7:54 am

You already have the loop in your code, just move the code to read the sensor inside it:

Code: Select all

#!/usr/bin/python
import mysql.connector as mariadb
import bme280
import smbus2
from time import sleep

mariadb_connection = mariadb.connect(user='pi', password='password', database='weather')
cursor = mariadb_connection.cursor()
port = 1
address = 0x77
bus = smbus2.SMBus(port)

bme280.load_calibration_params(bus,address)

while True:
    bme280_data = bme280.sample(bus,address)
    temperature = bme280_data.temperature
    pressure = bme280_data.pressure
    humidity = bme280_data.humidity
    cursor.execute("insert into WEATHER_MEASUREMENT1(GROUND_TEMPERATURE, AIR_PRESSURE, HUMIDITY) values (%s, %s, %s);", (temperature, pressure, humidity))
    mariadb_connection.commit() 
    sleep(3600)

BarnyardBarnyard
Posts: 7
Joined: Wed Jan 08, 2020 8:28 pm

Re: Trouble writing BME280 sensor data to mariadb

Tue Jan 28, 2020 11:10 am

Awesome.

I'm getting readings on frequency that are unique. This is awesome. Thanks again. I've got more I want to do with this, but for now this is great.

Here is the code:

Code: Select all

import mysql.connector as mariadb
mariadb_connection = mariadb.connect(user='pi', password='password', database='weather')
cursor = mariadb_connection.cursor()
import time
#interval = 5
#start_time = time.time()
import bme280
import smbus2
from time import sleep

port = 1
address = 0x77
bus = smbus2.SMBus(port)

bme280.load_calibration_params(bus,address)
#!/usr/bin/python


#print(humidity, pressure, ambient_temperature)


#while True:
      #start_time = time.time()

while True:
     #if time.time() - start_time <= interval:
          bme280_data = bme280.sample(bus,address)
          temperature = bme280_data.temperature
          pressure = bme280_data.pressure
          humidity = bme280_data.humidity
          cursor.execute("insert into WEATHER_MEASUREMENT1(GROUND_TEMPERATURE, AIR_PRESSURE, HUMIDITY) values (%s, %s, %s);",(temperature, pressure, humidity))
          mariadb_connection.commit() 
          sleep(1800)

Ian_M
Posts: 8
Joined: Mon Dec 16, 2013 2:35 pm

Re: Trouble writing BME280 sensor data to mariadb

Thu May 28, 2020 9:10 pm

Hi
I am working on an adaptation of the same project. I am new to the whole database thing and so have been testing with a simpler set up. I have a database in Mariadb which has a table (testw) that consists of an ID, TEMP1 and a timestamp. I am trying to write to the table using python - here is the relevant part of my code

Code: Select all

        nowtemp=28.12
	try:
		cursor.execute("INSERT INTO testw(TEMP1) VALUES(%s)", (nowtemp))
		mariadb_connection.commit()
		
This gives an error from mariadb saying that I have an error in my SQL syntax error near %s).

If I simply put a numerical value in place of the %s it inserts the value into the database table - however when I try to insert the variable nowtemp then I get the error.
Can't see where I am going wrong and am pulling my hair out.
Help appreciated.
Thanks,
Ian

markkuk
Posts: 290
Joined: Thu Mar 22, 2018 1:02 pm
Location: Finland

Re: Trouble writing BME280 sensor data to mariadb

Fri May 29, 2020 9:54 am

Ian_M wrote:
Thu May 28, 2020 9:10 pm

Code: Select all

        nowtemp=28.12
	try:
		cursor.execute("INSERT INTO testw(TEMP1) VALUES(%s)", (nowtemp))
		mariadb_connection.commit()
		
This gives an error from mariadb saying that I have an error in my SQL syntax error near %s).
The substitution values for parameters in the query must be given as a sequence even when there's only one of them. Use either an one-element tuple:

Code: Select all

		cursor.execute("INSERT INTO testw(TEMP1) VALUES(%s)", (nowtemp,))
or a list:

Code: Select all

		cursor.execute("INSERT INTO testw(TEMP1) VALUES(%s)", [nowtemp])

Return to “Python”